Changing SQL Server Instance name is not a routine task. Although, sometimes we get requirements to change SQL Instance name. There are multiple scenarios in which we need to rename SQL Server Instance like:
- If you have changed database server name then we should change SQL Server Instance name as well.
- If you have cloned a database server from DBSERVER1 to DBSERVER2. Here, SQL Server installed on DBSERVER1 will be copied with same name to DBSERVER2. You need to change the SQL Server instance name on DBSERVER2.
- Naming convention is not met.
Please keep in mind that we cannot change complete name of SQL Server named instance. Suppose you have installed a named instance SERVERNAME\DBInstance1 on your server. If you want to rename this named instance then we can only change first part of this name i.e. SERVERNAME. You cannot change DBInstance1 so your named instance will look like DBSERVERNAME\DBInstance1 post name change.
Impact of Changing SQL Server Instance Name
We need to plan carefully before renaming any SQL Server Instance. Below is the list of possible impacts that you should go through while renaming SQL Server Instance name.
- Those domain users who are accessing or connecting to the SQL Server Instance as part of Windows group will not be able to connect to renamed instance of SQL Server. Make sure to update the Windows group to specify the new computer name to establish connectivity to the SQL Server instance.
- You would not be able to access reporting services after renaming the instance name. You should follow steps mentioned in attached article to rename SQL Server instance that is running with reporting services.
- If you have database mirroring on the SQL Server instance then you need to drop the mirroring and then reconfigure it post renaming the SQL Server Instance.
- Renaming SQL Server instance name does not support if your instance has replication, except when you use log shipping with replication.
- You can change the name of SQL Server instance on secondary server in log shipping if the primary server is permanently lost.
- If you have linked server configurations then it will be affected by the computer renaming operation. Use sp_addlinkedserveror sp_setnetname to update computer name references.
Rename SQL Server Instance
SQL Server instance name along with other system metadata stores in sys.servers system object. We need to update details in system metadata to reflect new name. Also i would suggest to not change SQL Server Instance name directly on your production server.
Suppose you have installed default instance of SQL Server in your database server. If you have changed the name of your database server then you need to update the name of default SQL Server instance as well in sys.servers system table.
First, we need to drop the existing SQL Server instance name then we will add new name as per our choice. Follow below steps to change SQL Server instance name.
--Drop existing name
sp_dropserver [old_Instance_name];
GO
--Update New name
sp_addserver [new_Instance_name], local;
GO
If you have installed a named instance on your database server then you can run below command to change SQL Server Instance name.
--Drop existing name
sp_dropserver [old_name\instancename];
GO
--Update New name
sp_addserver [new_name\instancename], local;
GO
You can verify the changes by running below commands or by querying sys.servers system table. If you have not restarted SQL Server services then you might be getting older SQL Server instance name. So restart SQL Server service to apply new changes.
SELECT @@SERVERNAME
