Table of Contents
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
-- 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