How to change sql server name

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

 

Leave a Reply

  

  

  


3 + = 5