locked
SQL DB design - Multiple Data centers RRS feed

  • Question

  • Hi everyone,

    Just as a forewarning, I don't work on the SQL side of the house, but we're trying to drive a solution to get an application/website redesigned for better availability. :)

    We've got a website with a DB backend that's in a single data center right now, and while the data is replicated/website can be VMotioned to the other, there's a lot of manual effort behind it to get it up and running again. We're trying to determine what it would take on the SQL side to be able to use a DNS load balancer (like a F5) that could direct traffic to either location's website, and write to the database. Assuming the DB servers hosting the data at each Data Center were on separate IP subnets (for example, 10.1.10.x at DC1, 10.2.10.x at DC2 - we're staying away from stretched Layer 2), what is the strategy we should be looking to employ here? I had been looking into SQL stretched clusters, but not sure if that was the correct route. I also wasn't sure, for example, if the Data Centers become severed, how we design to achieve quorum so that we don't go split-brain in whatever solution we go for. 

    I suppose I'm just looking for advice on proper design so that rather than a bunch of phone calls and manual intervention, we can leverage the DNS load balancing to drive clients to an available resource.

    Any insight is appreciated!

    Saturday, July 2, 2016 2:02 AM

Answers

  • I think what you are looking for is high availability solution and this depends on which version\edition of SQL you are using. and If that does not work, would you be  able to upgrade

    Starting from SQL 2012, we have Always on Availability groups that can work with multisubnet configuration and they can also share the load( writes go to primary and reads can be shared). Google AlwaysON Availability Groups to learn more. but this is enterprise edition feature only.

    You can use regular failover clustering for high availability but this does not give the feasibility of sharing the load like AAG.

    Another Option is Database mirroring - but this feature is deprecated, so, per say, it is not a wise idea to implement this.  you  can create snapshot on mirror instance and use that for read only queries but there will be some manual\script work needs to be done.


    Hope it Helps!!

    Saturday, July 2, 2016 3:29 AM

All replies

  • I think what you are looking for is high availability solution and this depends on which version\edition of SQL you are using. and If that does not work, would you be  able to upgrade

    Starting from SQL 2012, we have Always on Availability groups that can work with multisubnet configuration and they can also share the load( writes go to primary and reads can be shared). Google AlwaysON Availability Groups to learn more. but this is enterprise edition feature only.

    You can use regular failover clustering for high availability but this does not give the feasibility of sharing the load like AAG.

    Another Option is Database mirroring - but this feature is deprecated, so, per say, it is not a wise idea to implement this.  you  can create snapshot on mirror instance and use that for read only queries but there will be some manual\script work needs to be done.


    Hope it Helps!!

    Saturday, July 2, 2016 3:29 AM
  • Thanks Stan. I appreciate this information. 

    This sounds exactly like what we're looking for -- writes to the primary/reads from both should suit us well. Load is not so much of an issue (right now), but as we scale out I think this would be great to have in place already.

    Tuesday, July 5, 2016 2:24 PM