In recent past, I have renamed my VM and wanted to make sure that everything is fine. This renaming process is also needed for changing the server names called inside of SQL Server too. So I searched to know various places from where we can get host name and SQL Server name. Let us learn in this table how to change server name? Finally, I formed this query which can gather same details from various sources:
SELECT HOST_NAME() AS 'host_name()', @@servername AS 'ServerName\InstanceName', SERVERPROPERTY('servername') AS 'ServerName', SERVERPROPERTY('machinename') AS 'Windows_Name', SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS 'NetBIOS_Name', SERVERPROPERTY('instanceName') AS 'InstanceName', SERVERPROPERTY('IsClustered') AS 'IsClustered'
Most of these server properties have been there for a while and sometimes I think it requires a refresher like this to remember them again. When we run this query on the non-clustered machine, we should see only two distinguished names. Machine name and Instance name. If you have default instance, then Instance Name would be NULL in the query.
If there was a rename of host, then you would see host_name would be different and that’s not something which should exist.
Note that Instance Name rename is unsupported. In named instance the server name is in format of ServerName\InstanceName. So, you can change it to NewName\InstanceName but NOT to ServerName\ NewInstanceName. I am sure this is a fantastic trivia to know.
If you find any mismatch, then you need to follow below steps:
- Execute below to drop the current server name
EXEC sp_DROPSERVER 'oldservername'
- Execute below to add a new server name. Make sure local is specified.
EXEC sp_ADDSERVER 'newservername', 'local'
- Restart SQL Services.
- Verify the new name using:
SELECT * FROM sys.servers WHERE server_id = 0
I must point out that you should not perform rename if you are using:
- SQL Server is clustered.
- Using replication.
- Reporting Service is installed.
I would like to know if anyone has ever tried doing this in your environment? What is the typical reason for you doing the same? I hope this helps in case you get into a tangle like this. Let me know your thoughts via comments.
Reference: Pinal Dave (https://blog.sqlauthority.com)