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

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:


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:


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
more ▼

asked Nov 07, 2010 at 04:03 PM in Default

Matt Hamilton gravatar image

Matt Hamilton
11 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Nov 08, 2010 at 01:25 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

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, 2010 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, 2010 at 01:43 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Nov 07, 2010 at 04:03 PM

Seen: 1063 times

Last Updated: Nov 07, 2010 at 04:03 PM