to the best of my knowledge the best practice in designing a topology
to mirror a database that has been setup as the publisher in a
transactional replication setup is the following:
IDEAL_CASE server_A: (principal), publisher in transactional replication
server_B: (mirror), automatically becomes publisher on fail over
server_C: (witness & distributor) is the witness of mirroring between server_A and server_B
is also the distributor in the transactional replication
Using the above setup, we can afford to lose any one of servers A,B or C :
a) if server_A fails, then SQL automatically fails over to server_B which immediately
becomes the publisher in the transactional replication
b) if server_B fails, the system continues to work using server_A
c) if server_C fails, the witness is no longer available, thus no automatic failover
is possible. Also the distributor is not available, so subscribers cannot be updated.
Thus on all (a),(b),(c) cases above, the database is available. My question is:
I have a similar case to the suggested above, but the distributor lies on server_A,
so server_C is only setup as witness.
Is it possible without breaking the existing publication (or dropping existing subscribers) to move the distributor from server_A to server_C? Please advice and comment if you agree that
IDEAL_CASE as described above
is indeed the best practice for setting up both transactional replication and
mirroring on the same database. Thank you.