March 22, 2011 at 10:25 am
Hello,
My company just purchased a new publication/distribution server and would like me to come up with a way to swap it out.
The current server is set up with 5 databases that total about 100GB of data.
It uses merge replication to keep 13 servers synced up.
All servers involved are running SQL Server 2005 SP2, mostly x86 but some are x64.
With the new server setup they would like to start to use SQL 2008 R2.
It is my understanding that the publisher has to be of the same or newer version of SQL than the subscribers.
So, the plan so far would be to grab backups on the old server and restore them on the new server, then recreate all the publications and subscriptions.
The problem is when I do so, all 100GB of data will need to be repushed to each subscribing server even though it's already there.
Is there any way to perform a fresh install of SQL 2008 R2 on a new server without having to repush all of the data?
Thanks,
Ben
March 23, 2011 at 1:17 pm
Assuming the publisher data isnt going to change you can resetup the replication as a 'nosync' subscription.
When using the sp_addsubscription proc to readd the subscriber set the @sync_type to 'none' or 'replication support only'.
March 24, 2011 at 8:42 am
I read the MSDN page on @sync_type of 'replication support only' and not sure if I understand it correctly.
From what I gather, this will initially not repush any data but will keep the data/schema in sync normally afterward, right?
March 24, 2011 at 10:21 am
We always use the none option but the principle is the same. As you are not changing the subscribers they will require no changes so long as you are using PUSH replication.
When doing this, the MS repl procs and the data will not be pushed to the subscribers.
You will need to stop the log reader agents on the publisher and ensure all replication commands are distruibuted to the subscribers BEFORE shuting down the distribution agents and taking the db backups.
March 24, 2011 at 4:47 pm
I setup a new merge publication on the new server, then a subscription to our test server; both using the wizard.
I had it generate the script when it created the subscription and used that as a base.
The script does not set up the subscription using sp_addsubscription as you suggested, it instead uses sp_addmergesubscription.
There is an option to @Sync_Type to 'none' that would appear to not require the data to be reinitialized, but it strongly suggests against it.
To use this option, I would need to manually initialize each subscription (which is also not recommended).
In testing, this manual initialization did not work as this article suggests.
What type of replication are you using?
Is there any way to skip the initial reinitialization when using merge replication?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply