Rename SQL Server
Sometimes you make a mistake, and forget to rename a syspred’d server before installing SQL Server. Or perhaps your corporate naming standard has changed, and you need to rename a server. Maybe you like to waste the time involved in troubleshooting connection issues after a server rename. In any case, you now find yourself where the name of the SQL Server is different than the physical name of the server itself, and you need to rename SQL Server to match the server’s physical name.
You could always rerun the setup program to rename the server. Fortunately, SQL Server provides an easier way to do this. You just need to run two stored procedures: sp_dropserver and sp_addserver. The following script demonstrates this concept. First, it will get the current name of the SQL Server name, the name of the computer, and the name of the SQL Server instance. Next, if the computer name plus the instance name is not the same as the SQL Server name, then it runs the sp_dropserver and sp_addserver stored procedures to rename SQL Server. The “LOCAL” parameter of sp_addserver denotes that this is the name of the local server. Consequently, you will need to restart the instance for the name change to take effect.
Before you just run this script, there are a few things to take into consideration:
- If this is part of a SQL Server failover cluster, then a different process is needed. See this link to rename the cluster’s virtual SQL Server name. To rename the individual nodes, each node must be evicted from the cluster, the instance renamed (per this script), and then the node added back to the cluster.
- SQL Server does not support renaming a server involved in replication.
- Renaming a server that runs Reporting Services (SSRS) may result in SSRS not being available after the rename. If this happens, see this link.
- When using database mirroring, you need to stop the mirroring before the rename, and reestablish it when finished.
- If Remote Logins, Linked Servers, or Client Alias Names are used, see the “Other Considerations” section in this link.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
/****************************************************************************** Rename a SQL Server instance to match the computer name. Reference: //msdn.microsoft.com/en-us/library/ms143799.aspx ******************************************************************************* MODIFICATION LOG ******************************************************************************* 2016-08-27 WGS Initial creation. ******************************************************************************/ DECLARE @OldServerName sysname, @NewServerName sysname, @InstanceName sysname; SELECT @OldServerName = @@SERVERNAME, @NewServerName = CONVERT(sysname, SERVERPROPERTY('ComputerNamePhysicalNetBIOS')), @InstanceName = CONVERT(sysname, SERVERPROPERTY('InstanceName')); IF @InstanceName IS NOT NULL SET @NewServerName = @NewServerName + N'\' + @InstanceName; IF @NewServerName <> @OldServerName BEGIN SELECT * FROM sys.servers; EXECUTE sp_dropserver @OldServerName; EXECUTE sp_addserver @NewServerName, local; RAISERROR('Restart SQL Instance service to have the name change take effect', 10, 1) WITH NOWAIT; END; GO SELECT * FROM sys.servers; |
