We're considering different options for HA/DR with SQL Server 2016.
AlwaysOn Availability Groups are obviously an option.
I understand we can read from secondary replicas, unlike DB mirroring for example.
Can we spread the write load during normal operations, by using each server in the cluster to be a primary database/replica for some databases, and a secondary database/replica for others?
Lets say we have 2 servers, sql01 and sql02, and 10 databases to protect.
Can we set this up:
- sql01 holds db01-db05 as primary, db06-db10 as secondary.
- sql02 holds db06-db10 as primary, db01-db05 as secondary.
During failover, each server can take over as primary for all DBs.
Are there additional options when using AlwaysOn to spread write operations?
Best Answer
You can and that's one of the beautiful things with availability groups over traditional clustering. You'll need to set up two (or more if you wanted) AGs, one would initially have SQL01 as it's primary node and contain db01-db05 and the other would initially have SQL02 as it's primary node and contain db06-db10.
You are then free to fail them over independently as you wish, you could put all AGs on one server or split them out over the two.
I can't find any documentation or demos showing the setup of two AGs (perhaps it could be a subject for a future blog post). The big difference between traditional clustering and using AGs is that the SQL servers aren't clustered in the way that your DBAs are probably thinking and there's no need for shared storage. Basically, the SQL instances are installed as stand alone instances on each node of the cluster. The AGs themselves are created as individual roles within the cluster and because of that can be failed over individually.
Hopefully that makes some sense, it's quite tricky to explain without an example.