• sqlenthu 89358 - Monday, July 10, 2017 2:18 PM

    Thanks for the insight Grant. We have two new prod servers in one location and two DR servers in another location. We have rto of 2 hours and rpo is point of failure. I know it takes lots of information and discussion to finalize such things. However I just needed a high level understanding like how the architectural view should look like (using clustering). If I can any such design patterns somewhere in diagramming, I would be thankful.

    I'm pretty much in line with Gail. The fact is, you're not going to hit zero with the servers you have listed. It's extremely complex. I also heartily recommend Denny Cherry. He knows his stuff. An alternative would be David Klee. I'm not sure if he costs more or less than Denny, but he'll get the job done.

    I'll give you an approximation on what you could do. You haven't said what version of SQL Server we're working with. Hopefully 2014 or better, preferably 2016. In that case, a bare-bones set up (and this is a vague, holding a finger in the wind to gage temperature, pressure, wind direction & speed and what color socks I'm wearing, so please treat it as such) would be to have your local servers configured in an Availability Group. Don't go for failover clusters. The technology isn't loved any more by Microsoft. All the love goes to AGs. You can have one server active, the other passive (which, I'll bet money, your business immediately nixes, most do. They don't like servers sitting around doing nothing) with the AG in synchronous connection. That gives you a pretty good chance for HA locally with as close to zero data loss as you're likely to get. Then, you can have the secondary servers in asynchronous connection through the AG. I'm not sure how you'd deal with the second server in the DR site. Also, this set up means the potential exists for data loss. You can't do synchronous over distances, latency will kill your transactions and you'll be spending all your money on disks for storing the logs and you'll lose the data anyway.

    None of that addresses backups, logs, offsite storage, testing, etc. This is not a small undertaking.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning