Replication: Moving Distribution and Subscriber Servers to new instances
We will be migrating our replication instances to a new HA environment and our challenge is to move over a terabyte of data without re-snapshotting every object. Current configuration: Flow: Publisher -> Distribution Server -> Subscriber Versions: SQL Server 2005 EE --> 2008 R2 SE --> 2008 R2 EE We want to replace it with Publisher -> **NEW** Distribution Server -> **NEW** Subscriber Server Versions: SQL Server 2005 EE --> 2008 R2 EE --> 2008 R2 EE We would like to do this in a way so we shut down replication, backup all the databases from "Subscriber", restore onto the "New Subscriber", move the distribution duties to the "New Distribution Server", enable replication, continue feeding changes. I would hate re-snapshotting all the data again. Is it possible?
Yes, this is possible as long as you can guarantee that your data isn't changing on the publisher during the migration. Here's how I'd do it: 1. Script out your publications & subscriptions ahead of time 2. Make sure all commands have been delivered to subscribers before beginning the move. 3. Drop subscribers, drop publications, disable publishing & distribution at publisher 4. Configure new distributor, configure publisher to use new distributor 5. Restore DBs on new subscriber(s) 6. Create publications using script from step 1 7. Create suscriptions using script from step 1 - ONE IMPORTANT CHANGE HERE - in the sp_addsubscription call set this parameter: @sync_type = N'replication support only' ... this tells SQL to assume you've already got the objects and data at the subscriber (which you do). See
http://msdn.microsoft.com/en-us/library/ms181702.aspx for more information on sp_addsubscription.