@@SERVERNAME Returns Old Name or NULL

— by Ginger Keys

Recently a client of mine moved their production SQL Server Cluster to a new physical environment. When the time came to ‘go-live’ with the new environment, the client wanted the new SQL cluster name and IP address (SQLCLUSTERTEMP) to be renamed to the original, legacy name and IP address (SQLCLUSTER).

Renaming a SQL cluster name and IP address is easily done through Failover Cluster Manager:

1. Right click, and take the Server Name OFFLINE
2. Open Properties and make changes
3. Bring Server Name ONLINE

In order to verify the rename we run this statement in SQL Server Management Studio (SSMS):

SELECT @@SERVERNAME

However after running this statement to verify the rename, SQL returned the old Cluster name (SQLCLUSTERTEMP). What happened!?

After renaming a SQL server name, you must execute the following statement in SSMS:

USE master

GO

EXEC sp_dropserver ‘OLDSERVERNAME’ — (SQLCLUSTERTEMP)

GO

EXEC sp_addserver ‘NEWSERVERNAME’, local — (SQLCLUSTER)

GO

Once done, restart the SQL Services.

To check both the local server name and the virtual SQL cluster name, run both of these statements:

SELECT @@SERVERNAME –Local server name

SELECT SERVERPROPERTY(‘MACHINENAME’) –Windows computer name, or Virtual name if clustered

These statements normally return the same results, but in our case, they returned different values and the @@SERVERNAME was still incorrect. In fact, the results returned a NULL value for the @@SERVERNAME, and an error stating the Servername (SQLCLUSTER) already exists.

If this happens with your environment, run the following statement dropping and adding the same server name:

USE master

GO

EXEC sp_dropserver ‘NEWSERVERNAME’ — (SQLCLUSTER)

GO

EXEC SP_addserver ‘NEWSERVERNAME’,‘LOCAL’ — (SQLCLUSTER)

GO

Restart the SQL Server Service again, and this should clear up any confusion SQL was having, and will return the correct name.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!