Befriending Dragons

Transform Tech with Anti-bullying Cultures

How to Rename SQL Server

2 Comments

How to rename a SQL Server varies a bit depending on the SQL version, whether it is clustered or not, and whether you want to rename the server/virtual server part of the name (works except for SQL 2000 clusters) or the instance part of the name (requires a reinstall). Also, you do not want to try renaming a server involved in replication as it will break replication (you have to drop/recreate all replication after a rename), and there are extra steps if mirroring is involved (stop mirroring before the rename, change configuration after). Be very careful to include the keyword “local” in the sp_addserver part of the steps (applies only to stand alone systems) and check @@SERVERNAME afterwards to make sure you have completed the steps correctly.

 

SQL 2008:

·         How to: Rename a SQL Server Failover Cluster Instance http://msdn.microsoft.com/en-us/library/ms178083.aspx

·         How to: Rename a Computer that Hosts a Stand-Alone Instance of SQL Server http://msdn.microsoft.com/en-us/library/ms143799.aspx

 

SQL 2005:

·         How to: Rename a SQL Server 2005 Virtual Server http://msdn.microsoft.com/en-us/library/ms178083(SQL.90).aspx

·         How to: Rename a Computer that Hosts a Stand-Alone Instance of SQL Server 2005 http://msdn.microsoft.com/en-us/library/ms143799(SQL.90).aspx

 

SQL 2000:

·         The SQL Server Network Name resource cannot be renamed http://support.microsoft.com/kb/307336

·         Renaming a Server http://msdn.microsoft.com/en-us/library/aa197071(SQL.80).aspx

 

From the BOL topics we can see that you can NOT rename the instance part of the name in SQL Server 2000, 2005, or 2008:

 

SQL 2005 cluster:

“The name of the virtual server is always the same as the name of the SQL Network Name (the SQL Virtual Server Network Name). Although you can change the name of the virtual server, you cannot change the instance name. For example, you can change a virtual server named VS1instance1 to some other name, such as SQL35instance1, but the instance portion of the name, instance1, will remain unchanged.”

 

SQL 2005 standalone:

“These steps can be used only to rename the part of the instance name that corresponds to the computer name. For example, you can change a computer named MB1 that hosts an instance of SQL Server named Instance1 to another name, such as MB2. However, the instance portion of the name, Instance1, will remain unchanged. In this example, the \ComputerNameInstanceName would be changed from \MB1Instance1 to \MB2Instance1.”

 

SQL 2008 cluster:

“Although you can change the name of the virtual server, you cannot change the instance name. For example, you can change a virtual server named VS1instance1 to some other name, such as SQL35instance1, but the instance portion of the name, instance1, will remain unchanged.”

 

SQL 2008 standalone:

“The following steps cannot be used to rename an instance of SQL Server. They can be used only to rename the part of the instance name that corresponds to the computer name. For example, you can change a computer named MB1 that hosts an instance of SQL Server named Instance1 to another name, such as MB2. However, the instance part of the name, Instance1, will remain unchanged. In this example, the \ComputerNameInstanceName would be changed from \MB1Instance1 to \MB2Instance1.”

 

 

2 thoughts on “How to Rename SQL Server

  1. Hey Cindy,

           Nice summary.

    It is surprising how many production environments get stuck on this.

  2. Any help for AQL 2012 renaming? SP_addserver does not work in 2012

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s