Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Renaming a Standalone Instance of SQL Server

I needed to upgrade the underlying IO subsystem of one of my IO intensive SQL Servers. After some discussion with various teams it was decided that the best way to achieve this whilst minimising disruption was to build a fresh server with a new name and a fresh SQL Server installation.We could then  configure that server as an exact replica of the current production box, with a different IO configuration. We could then run tests using SQLIO and other business related loads without interfering with the current production environment and at the same time ensure that the new kit was up to the the task.

When it came to switch over we would be in position whereby  we could have both machines running at the same time, with network connectivity so we could copy the necessary database backup files between servers, and start our load processing running again. As this database is only used to house read-only databases, with a weekday daily load process the switch over time (downtime) could be kept to a minimum.

Anyway, I digress, When we were ready to switch over, to avoid having to re-configure things like firewall ports we wanted to reconfigure the new server to have the same name and IP address as the old server. So at the point of switchover we would rename the server and change its IP address to be the same as the old server and turnoff the old server. (But keeping it as a failback should something go awry.) when you rename a computer housing SQL Server…SQL Server picks up the new name at start up, no re-installation is needed but you do have to update the SQL Server meta data so things like

SELECT @@ServerName 



will initially return the incorrect information, the old server name.  You can change this by running the following script, obviuolsy replacing the dummy syntax with your server details:



sp_dropserver 'oldname\instancename'
GO
sp_addserver 'newname\instancename', local
GO



As you can hopefully tell from the above script my SQL instance was a named instance, to do this on a default instance you simply drop the \instance name from each stored procedure.



sp_dropserver 'oldname'
GO
sp_addserver 'newname', local
GO



To check that the meta data has been updated correctly you can run



SELECT @@ServerName 



Maintenance plans can be affected by a rename, and may break after the rename. So you can either delete before the rename and re-create afterwards or Microsoft supply a script to fix them. This can be found here.



Some caveats:




  • This will not work for a cluster, it is only for stand-alone servers.


  • It won’t work on replicated instances


  • If your database is mirrored you will need to break mirroring and re-configure after the rename.


  • if your computer is used in reporting services a different approach is needed more information can be found here.

Comments

Posted by Anonymous on 27 February 2010

Pingback from  Latest Best Dedicated Server Auctions | World online hosting review

Posted by Anonymous on 27 February 2010

Pingback from  Latest Dedicated Server Hosting Auctions | World online hosting review

Posted by george sibbald on 28 February 2010

what about the local windows accounts SQL creates on installation, these will still reflect the old servername

Posted by David Bird on 1 March 2010

Dont forget the Report Server Connection Configuration. Yo will need to changed it if the databases are on the same server.

Posted by linkhasasickmind on 6 April 2010

I plan on performing a node by node SQL 2000 cluster to SQL 2005 migration for a default instance.

Does anyone know if you first change the virtual name of a SQL 2005 cluster, and then run sp_dropserver 'oldname' sp_addserver 'newname', local ,....if it will change default SQL server name?

I would appreciate it.

Leave a Comment

Please register or log in to leave a comment.