I am preparing some scripts to setup merge replication in our production environment. After completing the export from UAT, I've come across something that doesn't appear like it would work in the production environment?

exec sp_addmergesubscription
    @publication = N'<snip/>',
    @subscriber = N'<Server Name>',
    @subscriber_db = N'<snip/>',
    @subscription_type = N'pull',
    @subscriber_type = N'local',
    @subscription_priority = 0,
    @sync_type = N'Automatic'

The problem I have with this is that the subscriber name is hardcoded to the physical server name of the subscriber DB. In UAT that's fine because the subscriber is a single server, in production that's a problem because there are three production subscribers with mirror partners.

What name should I be using for the subscriber? If I leave it as NULL as the documentation indicates I can, what are the implications of that?


The only answer here, unfortunately is that mirroring is only supported for the pulbisher and only if the distributor is on a different server from with the principal or mirror. I would recommend a dedicated server for the distributor, but even using the subscriber server would work.

Database mirroring is not supported for distributors or for subscribers, and the work-around for mirroring a subscriber as detailed in the whitepaper by SQLCat only works for transactional replication. You could still use the manual sync option if you want to try to do something similar, but you'd have to get the data in perfect sync yourself which is very difficult to do unless you stop traffic to the publisher.

| improve this answer | |
  • I had previously setup replication using Microsoft's Sync Framework, whilst it worked great and supported mirroring, schema changes were an absoloute nightmare. Thanks for the comment. – M Afifi Jun 27 '12 at 8:51

I would recommend you read Replication and Database Mirroring:

Supported starting in SQL Server 2008 for transactional replication with manual failover and configuration. The replication agents that connect to the subscription database are not mirror-aware. If the principal subscription database fails, failover to the secondary database requires that you perform several manual steps to restore the replication stream. For more information, see SQL Server Replication: Providing High Availability Using Database Mirroring.

The linked document in turn states:

Prior to the release of SQL Server 2008, mirroring the subscriber was not a practicable solution because the potential advantages of database mirroring were mostly negated by the requirement to perform a full initialization of the Subscriber after a failover. The subscriber failover procedures described in this document take advantage of the initialize from lsn option introduced in SQL Server 2008. Creating the new subscription with initialize from lsn allows you to quickly synchronize the new subscription database with the publication database without resorting to initializing from a snapshot or backup, and without taking the publication or distribution databases offline.

Note that all the benefits of initialize from lsn apply to transactional replication. This TechNet note describes how the manual failover should be done Replication Subscribers and AlwaysOn (the article is for AlwaysOn but the same applies to DBM). I reckon I never tried this, but if I read the document correctly it means that:

  • you need to subscribe individually both the principal and the mirror, as separate subscriptions
  • after a failover you need to manually initialize the new principal's subscription (avoiding an expensive full re-initialization is critical)

You'll basically have two distinct subscriptions and manually have to 'failover' to switch between the active subscription (the principal) and the passive one (the mirror). Of course, this is quite bad, somehow I which I'm wrong and someone knows a better way.

Perhaps you should consider Tx replictaion instead which at least has some support. for failover. Or replace it entirely with something more friendly toward mirrored topologies...

This started as a short response but turned out into an investigation of its own, forgive the lack of narrative...

| improve this answer | |
  • @user708 that's terrible... Specially the comments about the distributor. Essentially if my distributor fails (which is a database today on the publisher), I need to resetup merge replicate. – M Afifi Jun 26 '12 at 10:03
  • I asked on #sqlhelp twitter, there may be some work around. I'm not a replication expert by any stretch... – Remus Rusanu Jun 26 '12 at 10:07
  • In a Transactional Replication topology, I don't really see there being much point in Mirroring the Subscriber, you could just as easily have another Subscription (of the same Publication) on another server. It would be interesting to get some more info about the design goals of the architecture. I wonder if Merge Replication is not the appropriate choice. What about Peer-To-Peer or perhaps a multi tiered replication topology. – John Sansom Jun 26 '12 at 10:30
  • @JohnSansom: the mirroring would mirror everything in the db. The subscriber DB may have much more content than just the subscription articles. Not to mention the automatic failover, the TDS support for connecting to the current principal etc. – Remus Rusanu Jun 26 '12 at 11:14
  • @JohnSansom this is almost workable. Failover in this particular deployment is not automated as we're not using witness servers. The subscribers hold no additional data beyond what they have subscribed to (the publisher does though). The only difficulty then is if the distributor fails. – M Afifi Jun 26 '12 at 12:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.