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
GOCheck 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
GOCheck 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
GORename 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 @@SERVERNAMEAs 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
