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?