Home » Blog » Renaming a Windows Machine hosting SQL Server

Renaming a Windows Machine hosting SQL Server

2012-08-26 - General

Introduction

Renaming a Windows server hosting a SQL Server installation is fairly straight forward. Most of the changes necessary for SQL Server to continue to work happen automatically the next time SQL Server restarts. As a Windows rename requires a restart of the machine, SQL Server will restart at that time anyway.

After SQL Server comes back up everything looks like the rename succeeded so the next step is often forgotten. However there are a few places were not executing this step is going to cause problems.

Replication

One of these areas is configuring distribution when setting up replication. For the wizard to work properly it needs to know the actual server name. It gets that information from the @@SERVERNAME variable. However this value does not automatically change when the underlying windows installation is renamed. The error you will get looks like this:

It complains that you did not use the old name to connect to SQL Server.

The sys.servers catalog view

The information that @@SERVERNAME returns is also accessible using the sys.servers catalog view:

[sql] SELECT server_id,name,is_linked
FROM sys.servers;
[/sql]

server_id	name	is_linked
0	OLDNAME\S12A	0
1	SQL0\S12B	1

This query returns all linked servers. It also holds one entry for the local server.

All we need to do to get things back in order is to replace that first entry with the correct one. For that purpose SQL Server provides two procedures: sys.sp_dropserver and sys.sp_addserver. We can use them to just drop the old entry and add a new correct one as described here: : Rename a Computer that Hosts a Stand-Alone Instance of SQL Server

The Script

I recently had to do this with a lot of servers. To reduce the manual work required I came up with a script to automate this step:

[sql] DECLARE @OldServerName NVARCHAR(MAX), @NewServerName NVARCHAR(MAX);
SELECT @OldServerName = @@SERVERNAME, @NewServerName = CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(MAX));
IF(@OldServerName <> @NewServerName)
BEGIN
RAISERROR('Renaming server %s to %s...',0,1,@OldServerName,@NewServerName)WITH NOWAIT;
EXEC sys.sp_dropserver @OldServerName;
EXEC sys.sp_addserver @NewServerName, 'local';
RAISERROR('Done.',0,1)WITH NOWAIT;
END
ELSE
BEGIN
RAISERROR('Server %s does not need to be renamed...',0,1,@OldServerName)WITH NOWAIT;
END;
[/sql]

This script automatically figures out which server entry to drop and which to create, so you can run it against multiple servers without change.

Conclusion

The above script provides an easy way to fix the situation where @@SERVERNAME does not return the correct server name after the host machine was renamed.

Before you consider renaming a SQL Server hosting machine you should however read this BOL entry that list supported and unsupported cases: Rename a Computer that Hosts a Stand-Alone Instance of SQL Server