Home Forums Database Design Disaster Recovery Designing and Architecture RE: Designing and Architecture

  • July 11, 2017 at 1:44 am

    #1950294

    Grant Fritchey - Monday, July 10, 2017 7:10 PM

    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.

    And then you ensure that you have log backups, running very often, going to a 3rd location (preferably) or at minimum to the DR site and then to a 3rd location. Your database backups go there as well. These should be encrypted and versioned (you can get old versions of the backups back)  This is so that dropped tables, dropped databases or ransomware don't take the entire infrastructure out.

    You also need a way to do automatic failover of the application, so that you can completely swing to the second data center. Also consider redundant domain server and DNS server, redundant email if the app depends on it.

    Finally, consider the people. In the case of a major disaster, staff may be unavailable or more concerned with their families than the business, so you may need a distributed administration team, if you don't have one already.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass