How to Change Server Name for SQL Server Machine

As you may know, the internal SQL Server name is based on the physical server name, but changing the physical server name does not update the metadata within SQL Server.

When connecting to SQL Server, we use the physical server name for the default instance and physical server nameINSTANCE Name for a named instance. If you change the physical server name, there is no need to re-install SQL Server to reset the physical server name. You can follow the below steps to update the SQL Server system metadata which stores the physical server name in sys.servers.

Pre Update Steps

Check for Remote Logins

-- Query to check remote login
select 
srl.remote_name as RemoteLoginName, 
sss.srvname
from sys.remote_logins srl join sys.sysservers sss on srl.server_id = sss.srvid
GO

Check for Replication

-- Query to check if the SQL Instance databases are involved in replication
select name,is_published,is_subscribed,is_distributor from sys.databases
where is_published = 1 or is_subscribed =1 or is_distributor = 1
GO

Check for Reporting Services

--Query to find out the databases involved in DB mirroring
select database_id, db_name(database_id)
from sys.database_mirroring where mirroring_guid is not null
GO

Rename Database Server Name

1. Login into the SQL instance using SSMS then run below T-SQL command to get the old server name in metadata.

-- Check current server name.
SELECT @@SERVERNAME

As you can see, the physical server name “prod-server” does not update in the metadata within SQL Server.

2. Create a new query then execute below commands.

-- for a Default Instance
sp_dropserver "lab-server";  
GO  
sp_addserver "prod-server", local;  
GO

3. Once the commands completed successfully, remember to restart SQL Server service for the change to go into effect.

Post Update Steps

• Add the remote logins
• Reconfigure Replication if this was setup.
• Reconfigure Database Mirroring if this was setup.
• Reconfigure Reporting Services