Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I was doing some test on server name change and encounter some errors.

Here is the original setup: Server name - ServerA, SQL Server default instance - ServerA

Changes: Server name - ServerB

Before I change the SQL Server default instance 'servername'

SELECT @@SERVERNAME, SERVERPROPERTY('SERVERNAME')

Return ServerA, ServerB (since I have changed the server name)

exec sp_add_log_shipping_primary_database completed successfully. However, there was error when running the log shipping backup job,

Error: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.

As expected. So I change the SQL Server default instance 'servername',

EXEC sp_dropserver 'ServerB'
EXEC sp_addserver 'ServerA', 'local'

Restart the SQL Server service.

SELECT @@SERVERNAME, SERVERPROPERTY('SERVERNAME')

Return ServerB, ServerB. OK, I delete the previous log shipping backup job. Run the sp_add_log_shipping_primary_database but receive this error,

Msg 14234, Level 16, State 1, Procedure sp_verify_jobstep, Line 214 The specified '@server' is invalid (valid values are returned by sp_helpserver).

sp_helpserver return ServerB under name and network_name column with id 0

So question is, am I missing some steps over changing servername or is this a bug?

Version - SQL Server 2012 SP1

share|improve this question
add comment

1 Answer 1

So in SQL Server 2012 SP1 I did this:

EXEC msdb..sp_helptext 'sp_verify_jobstep';

I scanned for 14234 (the error number you received), and right above the relevant RAISERROR call, I saw this:

IF (@server IS NOT NULL) AND (NOT EXISTS (SELECT *
  FROM master.dbo.sysservers
  WHERE (UPPER(srvname) = UPPER(@server))))

So, run this code with a PRINT:

IF NOT EXISTS (SELECT *
  FROM master.dbo.sysservers
  WHERE (UPPER(srvname) = UPPER(N'ServerB')))
BEGIN
  PRINT 'Not found.';
END

Then verify exactly what is in master.dbo.sysservers and also check master.sys.servers. I suspect either those two views don't match or the name in both is not exactly ServerB. Show the actual syntax you used to call the procedure and verify that the server wasn't renamed with leading/trailing spaces, whitespace or other non-printing characters.

Another idea is to not use the system procedure to set up log shipping in the first place. Log shipping isn't all that cumbersome to set up and in the past I've always just set up the backup / restore jobs manually. Also the stored procedure you called first only takes monitor_server as a potential server name where it could get into trouble connecting somehow, and it is nullable. Are you sure you need to pass it? Why would you want the monitor server to be the same as the source server? If one goes down they're both down by definition. What happens when you don't explicitly name a server as the monitor_server and leave that and other related parameters null?

Also these various hooks and potential conflicts are why I don't ever bother to rename servers that are already running. I'd rather pay the extra cost of wiping, reinstalling and restoring than dealing with all of this after the fact - especially if the quick rename was sold as something "hassle-free."

share|improve this answer
    
I ran the suggested script and seems they are the same. Verified that the name are similar in both master.dbo.sysservers and master.sys.servers (actually already did that before I post). monitor_server parameter are not supplied (or even supplied) have no effect on this error. SSMS UI produced similar type of error. –  Travis Gan Jun 27 '13 at 5:29
    
All the encrypted MS stored procedures make it so hard for me to dive all the way in to troubleshoot. I was trying to find out what is passed as @server in sp_verify_jobstep –  Travis Gan Jun 27 '13 at 5:30
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.