Which Replication Databases Can I Mirror?

Q: We recently had some database corruption caused by a faulty drive, and I’d now like to add protection to some of our databases using database mirroring. It won’t be a problem to implement database mirroring on most of these databases, but some of the databases are involved in transactional replication and I’ve heard conflicting information about whether it’s possible to mirror replication databases. Can you shed some light on this topic?

A: It depends on which database you’d like to mirror and which version of SQL Server you’re using. A transactional replication topology has three databases: the publication database, the distribution database, and the subscription database (with possibly multiple subscription databases). Note that I’m not considering peer-to-peer replication here—that’s a whole other level of complexity that usually precludes mixing in database mirroring.

I’ll start with the easy case: the distribution database can’t be mirrored. The mirror database in a database mirroring partnership resides on a separate Windows server from the principal database, and the Windows server has a different server name from the principal Windows server. When a mirroring failover occurs (automatically or manually), the mirror database becomes the principal database and is therefore hosted on a different server name. The name of the transactional replication Distributor can’t change or replication stops working.

The publication database is the easiest database to mirror because the Log Reader Agent job that runs on the Distributor and processes the transaction log of the publication database is specifically designed to work with mirroring on SQL Server 2005 and later. There’s a PublisherFailoverPartner parameter that’s set to the mirror server name that lets the Log Reader Agent automatically connect to the new principal database after a mirroring failover occurs. This also applies to the Snapshot Agent job.

The subscription database can be mirrored on SQL Server 2005, but after a mirroring failover a new subscription has to be created and fully reinitialized from a backup or a snapshot, which usually isn’t palatable.

In SQL Server 2008, a new subscription must still be created after a mirroring failover of a subscription database but there’s a new initialization method, initialize-by-LSN, which makes this configuration much more attractive. The initialize-by-LSN method lets the subscription database be initialized by using unapplied changes from the distribution database, instead of having to completely reinitialize the database.

For example, imagine a mirrored subscription database called PaulsDB database. The distribution database contains a set of changes that have been harvested from the publication database, up to time T3, the most recent time. The changes up to time T2 have already been applied to the principal PaulsDB database by the Distribution Agent. The mirror PaulsDB database might be a little behind the principal PaulsDB database, and it has received only the changes up to time T1 using database mirroring.

If the principal PaulsDB database then becomes unavailable and a mirroring failover occurs, the new principal PaulsDB database is missing the changes from T1 to T2 (i.e., those that had been applied to the old principal PaulsDB database, but not yet mirrored) and from T2 to T3 (i.e., those that hadn’t yet been applied to the old principal PaulsDB database). As long as the distribution database still has all the changes back to time T1, the new subscription that’s created can use the initialize-by-LSN method and specify T1 as that starting point.

To summarize, this means that only those replicated changes that hadn’t yet been applied to the old mirror database are required after the mirroring failover. To permit this, a minimum retention period must be set on the distribution database to ensure that replicated changes remain in the distribution database for enough time to perform the reinitialization. For more information, see the white paper I wrote for Microsoft that explains in great detail how to combine transactional replication and database mirroring—“SQL Server Replication: Providing High-Availability Using Database Mirroring."