Changing the name of a SQL Server machine
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 (7.0)
The SQL Server service will not start after the machine name change. You will get a Windows error message box: "Your SQL Server installation is either corrupt or had been tampered with (unknown package id) Please rerun setup."
All you have to do is to follow the advice. Pop in the SQL Server CD and run setup again. Setup will not reinstall anything, it will only make the adjustments needed so you can start SQL Server again.
Step 2 (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_addserver '' , 'local'
GO
You need to replace above with the old machine name and with the new machine name.
Step 3 (7.0 and 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.
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:
You need to replace 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":
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.
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.
 |
| Changing the name of a SQL Server machine |
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 (7.0)
The SQL Server service will not start after the machine name change. You will get a Windows error message box: "Your SQL Server installation is either corrupt or had been tampered with (unknown package id) Please rerun setup."
All you have to do is to follow the advice. Pop in the SQL Server CD and run setup again. Setup will not reinstall anything, it will only make the adjustments needed so you can start SQL Server again.
Step 2 (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 '' GOEXEC sp_addserver '
GO
You need to replace
Step 3 (7.0 and 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 = '' You need to replace
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\ResourceMgrIDIf 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.