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 name\INSTANCE 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
• Check for Replication
• Check for Reporting Services
• Check for Database Mirroring

-- 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

-- 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

--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

-- Check for SSRS, check if ReportServer database exist on server
-- Need to Stop Reporting Services service

Rename Database Server Name

Make sure you are login to the new server.

-- Check current server name.
SELECT @@SERVERNAME

-- for a Default Instance
sp_dropserver <old_name>;  
GO  
sp_addserver <new_name>, local;  
GO  

Remember to restart SQL Server 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

Reconfigure Reporting Services

1. Go go Reporting Services Configuration Manager. If there are multiple versions, make sure you go to the correct version of Reporting Services Configuration Manager, or else you will not be able to connect.
2. Login to the new server. Make sure you are not logged on to the old server.
3. Reconfigure Database section to point database to new server name.
4. If the server is created from a clone, you may need to backup the Encryption Keys from the original server, move the key file to the new server, and restore Encryption Keys from the key file.

Sources:

https://www.mssqltips.com/sqlservertip/2525/steps-to-change-the-server-name-for-a-sql-server-machine/

https://docs.microsoft.com/en-us/sql/database-engine/install-windows/rename-a-computer-that-hosts-a-stand-alone-instance-of-sql-server?view=sql-server-ver15