Changing the Server Name of your SQL Server

In this day and age of virtualization, it’s not uncommon to copy a base VM, rename it and away you go.  But what if SQL Server 2005 is already installed on it?  How does renaming affect it?

At first glance it may appear that it has had no affect at all, queries will still work, logins still work.  However, there are a few subtle and not so subtle side affects.  The first is what SQL Server thinks it’s name is.  If you run the following statement:

SELECT @@SERVERNAME

You’ll notice that it returns the old server name, not the new one.  This happens even though the name shown in the Object Explorer in Management Studio is showing the correct new name.  This will cause a clean Dynamics GP 10 install to fail when attempting to create the “Remove Posted PJOURNALs From All Companies” SQL Agent job as it uses the @@SERVERNAME variable to set that up.

To configure SQL Server to know its new name you’ll have to run the following two statements:

EXEC sp_dropserver 'OLDSERVERNAME'
EXEC sp_addserver 'NEWSERVERNAME', local

Once you’ve run these statements, restart the SQL Server service.  This will cause SQL Server to now return the new server name from the @@SERVERNAME variable.

A more noticeable side affect of the server name change is that SQL Reporting Services (SSRS) will not work.  This is because it is looking to the old server name for the reporting services databases (ReportServer and ReportServerTempDB).  To modify this, perform the following steps:

1. Run the Reporting Services Configuration tool (Start/All Programs/Microsoft SQL Server 2005/Configuration Tools/Reporting Services Configuration).
2. Navigate to the “Database Setup” page.
3. Change the Server Name to the new server name and click Apply.

SSRS will now work.

Now what about changing domains?  That’ll have to be another post.