lundi 1 décembre 2014

Replication Madness


Summary


We were contracted to build a "Hot-Standby" for a company which keep their data in the cloud. The through-put is relatively low. Database availability/accuracy is a matter of life or death. The Principle and secondary are running running SQL Server 2012 Standard Edition and reside in different regions.The secondary needs to be ready to stand in for the principle, seconds after failure and match the principle verbatim. Then after the primary is repaired and back online, the secondary needs to update the primary and switch roles back.


Issues


Peer-To-Peer is not applicable because the company will not invest the money in Enterprise edition.


Mirroring is an unlikely option because the two servers have minimal disk space and are not keeping logs.


I've researched all the methods of replication: Transnational, Snapshot, Merging. But have only been able to settle on Transnational with Queue updating. Then I hear that using this method is not advised in a disaster recovery scenario and that logs are required.


I have a theory that I have not tested yet as it would take a large amount of resources 'just to see if it works'. I have not seen the strategy recommended or even mentioned anywhere so I would appreciate if you could offer your opinion on it as well as provide any additional direction apart from it.


The Theory


Put Distribution, Publisher and a subscriber on both servers and cross chain them, so if the right side failed (principle), the left server would still be able to publish without logs and it would update the right side once it came back online. Then the roles could be switched again after everything was replicated from the left side.


Just to reiterate my problem is the right side going down, the roles switch and the left becomes primary and receives new data. Then the right comes back and changes to the left must be pushed to the right. I can't lose the distributor during this process because of the lack of logging.


Keep in mind I only have two servers. All constructive direction is appreciated.


enter image description here





Aucun commentaire:

Enregistrer un commentaire