asked
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.
If you mean server names, that shouldn't be an issue (just make sure to update the script from step 1 to account for the new names). If you mean data, that's a harder problem. If only a subset of tables will change you might consider removing them from the publication, subscribing with replication support only, then adding them in afterwards and snapshotting just those. If that's not an option then maybe you could keep track of what's changed after you stopped replication based on a datetime column (assuming that datetime column is updated every time a row changes). You could use SSIS to export the data that's changed from the publisher to the subscriber and then use the MERGE command to sync up the data. Obviously that won't work if we're talking about a lot of tables or a lot of change. Is it possible to stop changes from happening at the publisher for just a few minutes? You could do the move to new distributor and add the subscription (which would be pretty quick) but keep the distribution agent stopped while you handle the restore at the subscriber (which would take longer).
