Nucleus Datakonsult Changing the name of a SQL Server machine

Home

About me and Nucleus
Partners

Articles, tips etc.
My blog
Links
Microsoft articles
Forums

Courses and training




Microsoft Most Valuable Professional


Overview
If you change the machine name of a machine with SQL Server installed, you need to do a few things in SQL Server after the machine name change. This article outlines those steps. The article applies to SQL Server 7.0, 2000, 2005 and 2008.

Note: If the SQL Server is installed in a cluster, you should reinstall or hire an expert (who probably will tell you to reinstall). Thanks to SQL Server MVP Geoff N. Hiten for catching that.

Step 1 (all versions)
After starting SQL Server, you need to take care of the sysservers table in the master database. SQL Server stores the local machine name here and this will not match if you have renamed the machine in Windows. This is also easy to fix:

EXEC sp_dropserver '<old_name>'
GO
EXEC sp_addserver '<new_name>''local'
GO

You need to replace <old_name> above with the old machine name and <new_name> with the new machine name.

Step 2 (2000)
In this last step, you need to handle the sysjobs table in the msdb database. There is one row in sysjobs for each SQL Server agent job. In the column originating_server, you find the name of the server where the job was created. This is to support master and target server (MSX). If a job is created on a master server, you cannot modify the job definition on the targer server; all modifications has to be done on the master server.

So, if you changed the machine name, SQL Server will think that the job originated on a master server, and you will not be able to do anything with that job definition. You can handle this in two ways:

1. Rename the machine back to the old name, script the job definitions, delete the jobs, rename the machine to the new name again and use the script to re-create the jobs. See Error 14274 Occurs When You Update a SQL Agent Job After Renaming Windows Server for more information.

2. I find it easier to modify the sysjobs table directly. Note that this isn't supported and if you don't feel confident with doing below, don't; use above steps instead.

DECLARE @srv sysname
SET @srv CAST(SERVERPROPERTY('ServerName'AS sysname)
UPDATE sysjobs SET originating_server @srv

If this is a target server (you have jobs sent from a master server), you have to exclude them, so you don't convert those jobs into local jobs:

WHERE originating_server '<old_name>'

You need to replace <old_name> above with the old machine name.

Read this if you deploy images with SQL Server installed
The "Distributed Transaction Coordinator" service (DTC) creates a GUID in the registry when it is installed. Below is an example of a path for the key, for a SQL Server instance named "Fresh":
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\FRESH\MSSQLServer\ResourceMgrID

If you image such an installation using Ghost or some other imaging software, you will have duplicates of this GUID on your machines and your distributed transactions will fail. You can delete this key and a new key with a unique value will be created next time the SQL Server is started. Make sure that you don't have any open distributed transactions when doing this.