Is the name of your SQL Server correct?

Why Worry About The Server Name In SQL?

When the name of the actual host machine’s server name does not match the server name stored in SQL Server a lot of things can start to go wrong.

Reporting services can break, Connections denied, maintenance plans fail. Generally chaos can happen, but not always right away.

How does these names get out of sync? Usually is is from some sort of move or rebuild of a server. I experienced it with one client that physically moved data centers, and in the process decided they wanted to change the IP address and name for every server. We are talking thousands of servers moved, and every one of them renamed. That included over 750 SQL Servers.

I came onto the project after the move had been done and it took a while to realize that a lot of maintenance plans were failing. Turns out that the detailed checklist for the data center move, including renaming the servers, had left out the critical step of resetting the server name on each SQL Server. Production and development system were affected and needless to say it made for a fun week to get everything fixed as fast as possible.

Exceptions

There are a few exceptions to remain aware of when you are looking to rename a SQL Server:

• Fail-over Clusters – When as instance is part of a SQL Server fail-over cluster, the renaming process outlined here will not work.
• Replication – renaming computers involved in replication, except for log shipping with replication, as not supported. You need to remove replication for the change to be able to be done. Be sure to script out all your settings first so you can more easily rebuild it after the server name change. If you are using log shipping with replication and you completely lose the primary, you can rename the secondary. You can get more info on this scenario in the MSDN library here: Log Shipping and Replication (SQL Server)
• Reporting Services – After a rename, reporting services may not be available. To fix this you need to edit the RSReportServer.config file in a couple of ways. Details can be found in MSDN here: Rename a Report Server Computer.
• Database Mirroring – Mirroring needs to be turned off prior to changing the server name. Then once renamed you will need to re-establish the mirror using the new server name. Metadata for mirroring foes not update automatically.
• Windows groups – If using a hard-coded reference to the server name. This occurs if the Windows group specifies the old computer name. You would need to update the Windows group after the name change to specify the new server name.

Now that that is all out of the way, let’s get to actually changing our server name.

Changing the Server Name

First you are going to want to check if there are any remote logins. If you have any remote logins, that reference the old server name, you will receive an error when you try to change the server name.

This script will check your server for any remote logins so you can remove them.

select 
    sl.remote_name as RemoteLoginName, 
    ss.srvname
from sys.remote_logins sl 
inner join sys.sysservers ss on 
    sl.server_id = ss.srvid

Be sure to record the remote logins so you can recreate them afterwards.

To drop any remote logins you may be found, you can use the following syntax:

-- For a default instance
sp_dropremotelogin <old_physical_server_name>
GO
-- For a named instance
sp_dropremotelogin '<old_physical_server_name\instancename>'
GO

Once the remote logins are out, you can go ahead and change the name of the server with this script:

-- verify name incorrect
SELECT @@servername;
-- Remove the old name from the SQL server
sp_dropserver '<name from @@servername>';
go
-- Add the new name, with local set as well.
sp_addserver '<new server name>',LOCAL;
GO
-- verify change taken
SELECT @@servername;

To add the remote logins back after you have changed the server name you can use the following syntax:

sp_addremotelogin 
    @remoteserver = '<remoteserver>', 
    @loginame = ] '<login>', 
    @remotename = '<remote_name>'

After all the changes have been made you will need to restart the SQL Server service to be able to connect with the new server name.

The post Changing Your SQL Server Name appeared first on WaterOx Consulting.