Sql-server – Moving SQL server 2008 to a new Server with all DBs, replication and Jobs

migrationreplicationsql servertransactional-replication

We have to move our Production SQL server 2008 to a new Server (with latest SKU and more RAM). So we need to move all DBs, replications setup (publisher) and all jobs, means everything :). Also we want to keep the SQL instance name, so that we do not have to change this SQL instance name in web.config of the all the web servers.

So could anyone please share the steps including verification if done this earlier.

Please let me know if any more information is required from my side

Best Answer

To do this without recreating the publications and subscriptions - backup the publication, msdb, distribution, and the master databases. Then restore them on the new Publisher that has the same name as the old Publisher. Use the keep_replication switch when doing the restore. A DNS flush may be required but afterwards the Subscribers should begin synchronizing.

This may or may not work for you. I highly suggest testing the migration out in your test environment prior to production deployment.

I would also like to add that you may have to back up and restore the Service Master Key to the new Publisher as well. The is tied to the distributor_admin login that is created for the linked server when you Configure Distribution.

If this doesn't work for you - you can you can configure Distribution on the new Publisher/Distributor, and recreate the publication and subscriptions from backups. The subscriptions could be configured as replication support only if done during a maintenance window with no changes occurring. This would avoid sending down a new snapshot. If not done during a maintenance window, run a data validation to see how out of sync you are with the Subscriber and use tablediff utility or SQL Data Compare to bring the Publisher and Subscriber back into convergence. Reinitialization is an option as well.

Also, if you are upgrading the Publisher SQL Server version then there are considerations to make which are covered in Using Multiple Versions of SQL Server in a Replication Topology. It depends on your replication type.

For all types of replication, the Distributor version must be no earlier than the Publisher version. (Frequently, the Distributor is the same instance as the Publisher.)

For transactional replication, a Subscriber to a transactional publication can be any version within two versions of the Publisher version. For example, a SQL Server 2000 Publisher can have SQL Server 2008 Subscribers, and a SQL Server 2008 Publisher can have SQL Server 2000 Subscribers.

For merge replication, a Subscriber to a merge publication can be any version no later than the Publisher version. For more information about compatibility for earlier versions, see "Compatibility Level for Merge Publications" later in this topic. For more information about replication features that are supported in the various editions of SQL Server, see Features Supported by the Editions of SQL Server 2012.