Often during a side by side upgrade, the application team refuses to change the connection string. They want to avoid altering it from the application end. They are unable to point the application to the new SQL server. In such a scenario, they ask you to rename the SQL server with the existing server. This way, when the application comes up, it will connect with the new SQL server without any issue.

Recently I came across the same situation. The application team was not allowing us the side by side upgrade approach. They don’t want to make any changes on the application end. So we provided them the solution. We decided to build the new server first. At the time of final cutover, we will rename the SQL server with the old name.

At the time of final cutover, we rename the SQL server from windows side and started the application. Unfortunately application was not able to connect to the database. Application was throwing error that unable to connect to the database. After investigation we found that only renaming of server is not enough. You need to rename the SQL server from SQL side as well. Yes, you read it correct. You need to alter or update the New name in System Metadata that is stored in “sys. servers” and reported by the system function “@@SERVERNAME” to rename SQL server.

Rename the SQL server:

To change or rename the SQL server, please follow below steps.

1. If the server hosts the Default instance of SQL Server then, execute below queries.

sp_dropserver <old_name>;  
GO  
sp_addserver <new_name>, local;  
GO  

To get these changes effective you need to restart the SQL Services.

2. If the server hosts the Named instance of SQL Server then, execute below queries.

sp_dropserver <old_name\instancename>;  
GO  
sp_addserver <new_name\instancename>, local;  
GO

To get these changes effective you need to restart the SQL Services.

Note: – In Named instance the server name is in format of ServerName\InstanceName. You can change it to NewServerName\InstanceName, but you cannot change it to ServerName\NewInstanceName.

Story don’t end here, sometime even if you execute above queries, it will not work or you will get the error.

In below scenarios you get errors after running above queries.

1. When Remote logins are present

Error Message:
Server: Msg 15190, Level 16, State 1, Procedure sp_dropserver, 
Line 44 There are still remote logins for the server 'SERVER A'

2. When Logins associated with Linked server are present

Error Message:
Server: Msg 15190, Level 16, State 1, Procedure sp_dropserver, 
Line 44 There are still remote logins or linked logins for the server 'SERVER A'

Check all related articles