How can I add a new publisher to my merge replication schema?

Question

I have a large-ish (30GB) SQL 2005 database that is being merge-replicated around our wan in a "hub and spoke" topology like this:

cor
|--sub1
|--sub2
|--sub3
|--sub4
+--sub5

All of the subscriber servers replicate twice a night, so each day they all have each other's data. It works really well.

I have just set up a new server ("cor2") with SQL 2008 R2, and I want to introduce it into the replication schema with a view to phasing out the old subscribers and swapping in newer ones pointing to this new server.

So I'm wondering if I can back up the database on "cor" and restore it onto "cor2", then make "cor2" a publisher/distributor and have "cor" subscribe to it. Like this:

cor2
 +--cor
    |--sub1
    |--sub2
    |--sub3
    |--sub4
    +--sub5

So I guess my questions are:

Is it possible to make a "merge-replication publisher" a subscriber after the fact?
What steps should I follow if I want to subscribe "cor" to "cor2" and not lose any data?
Is there a better way? :)

Thanks, Matt

Answer 1

0

Matt, I have never done what you are describing and I cannot see your reasons for keeping 'cor' active, maybe they lie outside the replication processes? Personally I would stop replication on each of the subscribers and then the publisher - cor. Once that was all closed down I would backup the database and restore it to cor2 and then rebuild replication by setting cor2 as a publisher and then subscribing to it from each of the other servers. You can generate script to drop and create replication but in all honesty I have never seen them run without problems - going through the wizards always seems more reliable. YMMV.

HTH

answered Nov 08 '10 at 01:25 AM

Fatherjack ♦♦
38.8k ● 55 ● 69 ● 104

Thanks Fatherjack. Our main reason for wanting to keep "cor" in play is that the various subscribers are in remote sites (up to four hours away) and I didn't want to risk losing a remote desktop connection to them part way through the process. It's probably a small risk but one I wanted to mitigate.

Nov 08 '10 at 01:32 AM Matt Hamilton

Once you 'commit' to the change then simply stopping SQL Agent will stop all jobs on the subscribers (I assume you are based where the publishers are?) and you can then take your time to stop and remove replication on each on in turn. If you lose the connection then nothing will happen until you get connected again. I, personally, would be more concerned about getting data out of sync between to publishers of the same data....

Nov 08 '10 at 01:43 AM Fatherjack ♦♦

(comments are locked)

How can I add a new publisher to my merge replication schema?

Follow this question

Related Questions