Sql-server – Best way to migrate a database involved in bidirectional replication

migrationsql-server-2005sql-server-2008-r2transactional-replication

I have two SQL Server 2005 that are to be migrated to SQL Server 2008 R2.

The original pair of servers contain a database that are involved in bidirectional transactional replication between them. In other words SQL2005ServerA.DatabaseA and SQL2005ServerB.DatabaseA are involved in bidirectional transactional replication.

Now I have to move these databases to two new servers, call them SQL2008ServerA and SQL2008ServerB.

Is this pretty straight-forward? After disconnecting the application server, do I just backup and restore to the new servers, and then recreate the replication without snapshots?

Best Answer

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 :

Related Question