Thursday, 25 February 2010

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'
sp_addserver 'newname\instancename', local

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'
sp_addserver 'newname', local

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.

No comments:

Post a Comment

Featured post

OUT OF SUPPORT: SQL Server 2008 and SQL Server 2008 R2

Welcome to this post, on SQL Server 2008 and 2008 R2, both of these versions of SQL server will go out of  extended support with Microsoft t...