Many times you have to change the server name that has sql server installed on it, while doing so you have to also change name in sql server. Whenever you install sql server it takes machine name as sql server name by default. Using this name, we use to connect sql server and perform daily task.
If you change the physical server name, it doesnt update sql server name automatically. You have to manully change sql server after remaing physical machine on standalone server. Information related to servers are stored in sys.server.
Pre Checklist before renaming name in sql server
1. Confirm whether any remote logins or linked logins exists or not, if exists you have to drop them first using below query to check remote login.
select rlg.remote_name, srv.srvname from sys.remote_logins rlg join sys.servers srv
on rlg.server_id=srv.srvid
To remove the remote login by executing below query in master database.
sp_dropremotelogin ‘oldservername’ — default instance
sp_dropremotelogin ‘oldservername\instancename’ — Named instance
2. Confirm whether replication is configure on sql server or not, if yes then remove it otherwise you will face below error.
Msg 15185, Level 16, State 1, Procedure sp_dropremotelogin, Line 68
There is no remote user ‘(null)’ mapped to local user ‘(null)’ from the remote server ‘sqlserver’.
Run the below query to check if any 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
3. Confirm whether any database is involved in database mirroring or not. if yes, you have to remove database mirroring and reconfigure again after updating metadata.
Use below query to find out database involved in database mirroring
select database_id, db_name(database_id) from sys.database_mirroring
where mirroring_guid is not null
To turn off the database mirroring
ALTER database set partner off
Now rename sql server name
sp_dropserver ‘oldservername’
sp_addserver ‘Newservername’, local — default instance
sp_addserver ‘Newservername’, ‘Newservername\sql1’– Namedinstance
Post update
Add remote (change linked server setting pointing to the server )
Reconfigure database mirroring
Recongiure replication