Thursday, January 9, 2014

How to rename your SQL Server

When you rename the computer for your SQL Server, the new name is automatically recognized during startup.  There are only a couple follow-up steps that you will need to run after you bring the machine up.  I have shown the rename for both a DEFAULT instance, and a named instance below. 

Please review each of these pointers BEFORE you attempt to rename the server:
   1.  If your renamed server is using Reporting Services, the SSRS instance may be unavailable after the rename.  In this
        case, you will ALSO need to perform a step within Reporting Services Configuration tool.  See here:    
   2.  If your server is using database mirroring, you must turn off the mirroring BEFORE you perform the rename.  Afterward,
        you will re-establish the mirror with the new server name.
   3.  You cannot rename the server if it is involved in replication, UNLESS you are using log shipping with the replication.  In
        this case, the secondary computer in the log shipping solution can be renamed, if the primary fails.
   4.  Do NOT use the methods below if your server is part of a failover cluster.  See this method instead:    

To rename your SQL Server:

/* For a DEFAULT instance of SQL Server */ 
     EXEC sp_dropserver 'YourOldServerName';
     EXEC sp_addserver 'YourNewServerName', local; 

/* For a named instance of SQL Server */ 
     EXEC sp_dropserver '[YourOldServerName\InstanceName]';
     EXEC sp_addserver '[YourNewServerName\InstanceName]', local; 

That's it!  This method will work with everything from v2000 forward.  You can use this to check sys.servers, and confirm that @@SERVERNAME now reflects the new name:


No comments:

Post a Comment