We are planning to migrate two servers with MSSQL 2012 servers with configured transactional replication. New servers will have new names, ip addresses and MSSQL 2014. Subscriber is used for reporting and there are extra tables and columns needed for reporting that does not exist on publisher. What will be best way to migrate those servers? We will have enough downtime to do all needed works.

Some extra columns on reporting server are calculated on daily basis, and we would like to restore it from backup too, if that is possible.


You don't really migrate a replication topology. You simply recreate it among the new servers. Script out your replication configuration and re-create it among the new servers. So backup, and restore, followed by replication setup with ordinary initialization of the subscriber from a new snapshot.

It's possible, but more complicated, to skip the initialization of the subscriber, if you can ensure that the subscription tables were synchronized at the point-in-time of the restore. See Initializing a subscription with an alternative method.

But if you have the downtime, just clean up and recreate the replication topology from scratch on the new servers. You should script the new replication setup and test it several times before the cutover.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.