P2V a SQL Server 2000 Machine

P2V (Physical to Virtual) is a method of migrating a Physical server to a Virtual Machine. We did this on an old SQL 2000 machine and there are some considerations to be aware of when either the IP or Hostname changes…

The following applied to SQL Server 2000, 2005, 2008.

Step 1 (All versions)

The master sysservers table holds the local machine name and this will not match if the server has been renamed.

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

If the computer has any remote logins, for example, if it is a replication publisher or distributor, sp_dropserver may generate the following error (which it did in our case)

Server: Msg 15190, Level 16, State 1, Procedure sp_dropserver, Line 44
There are still remote logins for the server 'SERVER1'

To resolve for replication, disable replication before running sp_dropserver. Otherwise, drop the remote logins.

Step 2 (SQL 2000)

When a hostname is changed, the sysjobs table in the MSDB database keeps the old hostname in the column originating_server. This creates the assumption that the jobs are target server (MSX) and not local, which can lead to issues with modifying job definition on the new server. If a job is created on a master server, you cannot modify the job definition on the target server; all modifications has to be done on the master server.

This can be resolved in 2 ways:

1. Rename machine to old name, script job definitions, delete jobs, rename machine to new name 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. If you are comfortable with msdb system tables, then modifying the sysjobs table directly makes the task a lot easier.

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>' 

Distributed Transaction Coordinator (DTC)

If deploying images with SQL Server installed, then consider that the DTC creates a GUID in the registry when installed. for a local instance in SQL 2000, it is located in:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\ResourceMgrID

For a named instance of SQL Server it is located:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\INSTANCE1\MSSQLServer\ResourceMgrID

The key can be regenerated uniquely by Deleting and then restarting SQL Server making sure that no distributed transactions are open.

Also Consider: https://dbamohsin.wordpress.com/2012/01/16/dts-packages-fail-after-changing-server-host-name/

Advertisements

One Response to P2V a SQL Server 2000 Machine

  1. Pingback: DTS packages fail after changing server host name « Mohsin's DBA Blog

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: