0

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: Transactional, Snapshot, Merging. But have only been able to settle on Transactional 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

Additional details The focus would be labeled as HA as opposed to DR. I agree completely that if this is life and death then the client should be made aware that this approach is suboptimal and have made my concerns known. My motivation to bring this to the forums was to seek out a unseen solution, if it existed. (RTO < 1 hour, RPO < 5 mins)

Improve this question
12
  • 3
    Life-or-death and not enough money to afford enough disk space to do mirroring???
    – Hannah Vernon ♦
    Dec 1, 2014 at 18:47
  • 3
    It is unlikely that this approach will meet your goals of 'matching the source verbatim' and be available "seconds after failure." You should (1) can get more disk space for mirroring, and/or (2) redefine the acceptable currency of data and failover to match what you are able to do, ... etc.
    – RLF
    Dec 1, 2014 at 19:59
  • 5
    Yeah you need to set the RTO and RPO with the client realistically for what they want to spend. What was the RPO and RTO given?
    – JNK
    Dec 1, 2014 at 20:03
  • 3
    @MaxVernon I guess that means the death of untold lives is less than EE licensing and/or increased disk capacity. Pity too, lives these days are just falling in price. In my day, a life would buy you a steak and kidney pie, a cup of coffee, a slice of cheesecake and a newsreel. With enough change left over to ride the trolley from Battery Park to the polo grounds.
    – billinkc
    Dec 1, 2014 at 20:08
  • 3
    @Cameron - this is quite the word problem. what are you attempting to achieve? HA? DR? Both? If it's truly "life-or-death" then you, as the consultant/contractor need to influence them to at least invest in disk space for full recovery model/log backups. That gives you log shipping as an option. Just going along with this plan without raising documented concerns does neither the client nor your firm any favors at all, in any capacity.
    – swasheck
    Dec 1, 2014 at 20:11

1 Answer 1

Reset to default
4

Please don't. You don't want replication. It's a data distribution technology: it's not suitable for HA. Just to name one of the many shortcomings of using replication for HA, you won't have automatic failover (which seems to be a requirement) and you'll need to code it yourself.

If you need HA, pick one among clustering (you can have a two-node cluster with Standard Edition), mirroring or log shipping.

Log shipping is the simplest solution, but it won't achieve "matching the source verbatim" and it requires manual failover.

Mirroring requires full recovery, but it's a perfectly reasonable requirement for a production database. I'm having a hard time thinking of a situation where HA is a must and full recovery is not. What's your RPO? Can you achieve it with simple recovery? Quite unlikely.

Clustering requires shared storage and I understand it could be a hard constraint on the money.

If it is really a matter of life or death, Enterprise Edition is a MUST. How are you maintaining your indexes without online operations? Are people dying during index maintenance?

If you switch to EE, Always-On Availability Groups is another (great) option.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.