Sql-server – Replication DB move

Changing the IP addresses is no problem. You'll just need to force AD replication after the machine comes up with the new IP then do an IPCONFIG /flushdns on the other machines so that they can all see each other. Depending on which machine has it's IP changed and if you are doing push or pull replication you may not even need to do that.

Moving the distribution database is a lot harder to do. You can shutdown all the replication agents and do a backup and restore of the distribution database to the new machine, making sure to use the KEEP_REPLICATION flag. You'll then need to recreate all the SQL Agent jobs for replication on the new machine manually. Then you'll need to edit the replication tables manually to change the distributor to the new name, then edit all the agent jobs to give them the name of the new distributor. Once all that is done everything should work again.

Moving the publisher requires that the same approach be taken as moving the distribution database.

Is this pretty straight-forward?

Yes and No .. depends on what is your downtime (maintenance window), database size or amount of data you are replicating (whole or subset of articles).

Script out current replication setup (both create and drop).

First make sure that you don't have any pending commands to replicate and no user connections while you are doing a cut-over to new server.

Below are some of the ways you can choose to ease your migration :

• Easy and straight forward solution is to simply reset up replication. If the database is too big then you can reinitialize from backup as well followed by a snapshot and everything will be working fine.
• Set up log-shipping of current publisher databases that you need to migrate to new server or even you can set up the new server as a subscriber to the old one. Then on the day of cutover, just bring the secondary (new server) database online (restore with recovery) if log-shipping is used or just point your application to the new server and resetup replication.
• A more tedious and possibly error prone solution (if you miss some details) is to backup the publication database and restore it to the new publisher server with the KEEP_REPLICATION flag enabled. Go to the subscriber servers and update the dbo.syssubscription tables with the new publishers information. Update the subscription tables with the new publisher information. Edit all the jobs on the distributor to point to the new publisher. Update the tables in the distributor to point to the new distributor.
• Another way to do all of this is to stop SQL on the old server, copy all the system and user databases to the new SQL Server overwriting the system databases, then rename the new SQL Server so that it has the same name and IP as the old server. Then bring SQL on the new server online and everything should be working smooth.

Note:

By default, the generated scripts will have @sync_type = N'automatic' => the subscription will need initializing as a brand new subscription. Instead change it to @sync_type = N'none' or @sync_type = N'replication support only' => does not generate replication procs at subscriber.

Obviously, logins and users with appropriate permissions should be created before hand.

References :

• Minimize Downtime When Moving to a New SQL Server Environment
• SQL Server 2008 R2 Upgrade Technical Reference Guide