question

elias_chatzigeorgiou avatar image
elias_chatzigeorgiou asked

How to move the distributor to another server

Hello,
  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.
replicationmirroring
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Fatherjack avatar image
Fatherjack answered
I have not tried this or had any experience of it but I cannot see any way that it would be logically possible to move the Distributor as it is at the heart of replication. This links says no, its not possible [ http://bytes.com/topic/sql-server/answers/156838-moving-distribution-database][1] whereas this link suggests it might be [ http://msdn.microsoft.com/en-us/library/ms151740.aspx][2] although I think its referring to user databases and not Distribution but isnt specific on that. This link [ http://www.sqlservercentral.com/Forums/Topic844095-291-1.aspx][3] has details of a scary 'hack' method to do it. My recommendation would be to script out the configuration, remove replication entirely, create a new Distribution database in the desired location and then script replication back in. A lot of work maybe but I think its least risk of catastrophic failure. [1]: http://bytes.com/topic/sql-server/answers/156838-moving-distribution-database [2]: http://msdn.microsoft.com/en-us/library/ms151740.aspx [3]: http://www.sqlservercentral.com/Forums/Topic844095-291-1.aspx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.