I have a SQL 2005 database which is published with dozens of (mostly) transactional publications and dozens of subscribers . Currently we have a local distributor. I'm trying to improve our HA for this setup and am looking at mirroring the published database. Best practices say to use a remote distributor, in case we have to fail over to the mirror so that replication can continue. I've tested that and it works fine.
But what if the remote distributor fails? How do I eliminate that as the single point of failure, or better, how do I recover when it does fail?
From testing and what little I could find on the web, I need to completely undo my replication set up (remove subscribers, articles & publications, distributor) and recreate it all with the new remote distributor specified. I'd be fine with that IF I could add my subscribers back without needing to reinitialize them. I've successfully used the @subscriptionlsn input of sp_addsubscription for similar situations, where I want to resume replication without resnapshoting & reinitializing them . But by completely undoing replication, the min_autosynch_lsn is lost as are all the outstanding transactions that go with it.
I must be missing something fundamental. I know clustering my distributor adds a layer of safety, but even that could fail. So how, or can, I switch to a different distributor without reinitializing my data on them?
There isn't really a good answer.
Other replication technologies like Tungsten Replicator and built in MySQL and PostgresQL replication don't have a need for a central distribution server since both take advantage of reading the binlog on the subscriber.
I have found the following from BOL regarding DR for replication topologies.
Technet Replication paper