Troubleshooting a renamed SQL Server

First, thanks to Sean McCown (@KenpoDBA) at MinionWare (@heyMinionware) for the help troubleshooting.

Recently I had to rename a SQL server during an application migration to a newer version of Windows Server. The new SQL server had been all set up ahead of time and was production ready (monitoring agents installed, maintenance processes tested, etc). After all of the databases had been migrated to the new SQL server, a message came through that the vendor was requiring the new servers (app & database) be reconfigured to use the old servers’ names and IP addresses…

Ok, at this point we’re going to jump past the flurry of email messages that resulted and focus on: What broke? and How was it fixed?

Monitoring Agents – The first (and easiest) thing to be fixed were the monitoring agents (ie. MS Systems Center Operations Manager). All that took was a quick Redeploy and they came back online.

SQL Agent Jobs – The next issue was SQL Agent Jobs. Many of the maintenance jobs (eg. syspolicy_check_schedule_xxxx) have the server name hardcoded into them. This can be easily remediated by editing the job step properties and replacing the old server name with the new one.

@@SERVERNAME – The problem that took the longest to troubleshoot was the @@SERVERNAME function (thanks for helping figure this one out Sean). Many processes (stored procedure or SQL agent job) that were using @@SERVERNAME wouldn’t function correctly. If the procedure or job was using the function to determine the server’s name for a connection string, it would return either a timeout error or a ‘server not found’ status (often without explicitly stating what server name the job/process was trying to connect to). We were able to determine that @@SERVERNAME was returning the old server’s name with a simple SELECT statement.

SELECT @@SERVERNAME;

Unlike the SERVERNAME property, which changes when the network name of the server is changed, the @@SERVERNAME function is only changed through the use of the sp_dropserver and sp_addserver procedures.

EXEC sp_dropserver 'SQLTest'; -- Drop the old server name.
EXEC sp_addserver 'SQL2014Dev', 'local'; -- Add the correct server name.

After the sp_addserver statement has been run, a SQL service restart is required.

Once the SQL service has restarted, the @@SERVERNAME function returns the correct server name.