Tuesday, January 20, 2009

Changing SQL Server name on copied virtual servers

I use virtual servers to create multiple development environments for different projects, allowing me to replicate specific client environments for development and testing. This works great once each environment is setup, but the setup and re-configuration of a VM can be time consuming and occasionally tedious.

I recently made a copy of a base VM with SQL Server, Visual Studio, and Office, but without GP. I then used Sysinternals NewSid on the VM to change the machine name and SID. After a reboot, I installed GP and attempted to run GP Utilities.

GP Utilities displayed several errors that didn't tell me much, but then I finally received a message that referenced "DEV1\GP10". DEV1 is the name of my base VM that I had copied. Even though I had renamed the machine, GP utilities was still picking up the old machine name for the SQL Server.

This is a common issue that comes up when you change the name of a SQL Server. Even though the new name appears in SQL Management Studio, any SQL statements that use the @@servername parameter will return the old SQL Server name. (select @@servername)

I have dealt with this before, but I always forget the command to update the SQL Server name. Here for posterity are the simple commands to perform the update (example assumes an instance name of GP10):

sp_dropserver 'oldname\GP10'
sp_addserver 'newname\GP10', 'LOCAL'

Great, I thought, all done with that, and ahead with GP Utilities. But, to my surprise, I then received a new message indicating that the server had NO name at all, which was a new one for me.

After Googling a bit more, I found a tip that you need to restart the SQL Server service for a change to the 'local' server name to take effect.

After checking Books Online, I see that this caveat is mentioned under sp_addserver, but it is easy to miss.

No comments: