SQL Replication and Renamed Imaged Servers
I've run into various issues when rolling out Microsoft SQL Server 2000 to multiple locations using disk imaging. The roll-out process usually involves creating a disk image with SQL Server already installed, applying that image on multiple server machines at a production center, then distributing and renaming each server once it gets to its final destination (no, I'm not involved with the licensing issues). There are multiple locations, each with one server with a handful of clients.
The problems arise when we need to have SQL Server replication (a pushed one-way merge publication) deployed at each location. I now know the best strategy is to install replication via some scripts after the server and clients are all renamed and deployed. However, I recently ran into a case where replication had been installed before the master image was created, then the image was deployed to a server, then the machine name of the server was changed. Here are the issues and how I dealt with them.
Because all of the names of the machines had changed, I needed to uninstall and re-install the replication setup. The common strategy is to script out the current publication, drop and disable publishing, then reinstall replication using the generated scripts. However, when the machine name was changed and the machine was rebooted, the value of the server property "ServerName" was updated, but the value of @@servername was not changed. If you run the queries "select @@servername" and "select ServerProperty('ServerName')," you will see that they return different values. If you do a select from sysservers, you will also see that server id 0 still has the value of the previous machine name. To correct this, run sp_dropserver with the old machine name, then run sp_addserver with the new machine name (see this MS article for more info).
Before adjusting those values, I did a few other things. First, I right-clicked on my publication and scripted it out. Then I used Enterprise Manager to completely disable publishing at the server (Tools->Replication->Disable Publishing). You may have to register the new server name in EM if you had explicitly referred to the previous server name. When you then disable publishing, you will receive an error about MSX server jobs. This is due to the fact that sysjobs in the msdb database contains jobs that have an originating server value referencing the previous server name. At this point, I adjusted the server name using the dropserver/addserver steps above. Then I restarted the MSSQLSERVER service.
Now that the servername values were in sync, I deleted all rows in sysjobs, sysjobsteps, and sysjobschedules. Then I tried to disable publishing again. This time it completed with no errors.
At this point, I had to clean up the subscriber machines who still thought they were being pushed a subscription from the old server name. I ran sp_removedbreplication on all of the subscribers. Next, I edited the replication scripts I had generated to make sure the correct publisher and subscriber names were in them. Finally, I was then able to execute the scripts to recreate the publication. No errors. YMMV.
The problems arise when we need to have SQL Server replication (a pushed one-way merge publication) deployed at each location. I now know the best strategy is to install replication via some scripts after the server and clients are all renamed and deployed. However, I recently ran into a case where replication had been installed before the master image was created, then the image was deployed to a server, then the machine name of the server was changed. Here are the issues and how I dealt with them.
Because all of the names of the machines had changed, I needed to uninstall and re-install the replication setup. The common strategy is to script out the current publication, drop and disable publishing, then reinstall replication using the generated scripts. However, when the machine name was changed and the machine was rebooted, the value of the server property "ServerName" was updated, but the value of @@servername was not changed. If you run the queries "select @@servername" and "select ServerProperty('ServerName')," you will see that they return different values. If you do a select from sysservers, you will also see that server id 0 still has the value of the previous machine name. To correct this, run sp_dropserver with the old machine name, then run sp_addserver with the new machine name (see this MS article for more info).
Before adjusting those values, I did a few other things. First, I right-clicked on my publication and scripted it out. Then I used Enterprise Manager to completely disable publishing at the server (Tools->Replication->Disable Publishing). You may have to register the new server name in EM if you had explicitly referred to the previous server name. When you then disable publishing, you will receive an error about MSX server jobs. This is due to the fact that sysjobs in the msdb database contains jobs that have an originating server value referencing the previous server name. At this point, I adjusted the server name using the dropserver/addserver steps above. Then I restarted the MSSQLSERVER service.
Now that the servername values were in sync, I deleted all rows in sysjobs, sysjobsteps, and sysjobschedules. Then I tried to disable publishing again. This time it completed with no errors.
At this point, I had to clean up the subscriber machines who still thought they were being pushed a subscription from the old server name. I ran sp_removedbreplication on all of the subscribers. Next, I edited the replication scripts I had generated to make sure the correct publisher and subscriber names were in them. Finally, I was then able to execute the scripts to recreate the publication. No errors. YMMV.
