Merge Replicate and Mirrors

Question

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?

Answer 1

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.

Answer 2

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...