1

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?

Improve this question
1
  • You can certainly do this (see answers below). 2 considerations: capacity (can 1 server handle all the databases in an outage); and licensing (as mentioned in an answer below, if you have Software Assurance you get a free passive secondary)
    – Greg
    Commented Jul 11, 2019 at 6:45

3 Answers 3

Reset to default
3

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.

Improve this answer
0
2
  • Lets say we have 2 servers, sql01 and sql02, and 10 databases to protect. Can we set this up:

Yes you can set that up. I suggest you keep this in consideration when you pick your 5 databases (for one availability group) in the example you gave. Your application should be able to run successful with one group of database being in the same Availability Group and failing over between nodes. Here is a snippet from books online.

An availability group supports a replicated environment for a discrete set of user databases, known as availability databases. You can create an availability group for high availability (HA) or for read-scale. An HA availability group is a group of databases that fail over together.

  • During failover, each server can take over as primary for all DBs.

Yes. Test if your hardware can support the workload when you host both groups in the same host.

  • Are there additional options when using AlwaysOn to spread write operations?

Your connection strings need to set correctly. Not directly related to your question but you can offload some work to read-only replica to reduce contention for your write workload. See details here.

Reading the comment I see you are looking for step by step instructions. I listed few resources here. Number 3 item is 3 part series as a follow up to the white paper (item number 1).

  1. https://msdn.microsoft.com/library/hh781257.aspx
  2. https://www.brentozar.com/archive/2011/07/how-set-up-sql-server-denali-availability-groups/
  3. https://blogs.msdn.microsoft.com/sqlalwayson/2012/10/16/how-to-migrate-to-alwayson-alwayson-from-prior-deployments-combining-database-mirroring-and-log-shipping-part-1/

SQL Server Failover Cluster Instances (FCIs) and Availability Groups

You can set up a second layer of failover at the server-instance level by implementing SQL Server failover clustering together with the WSFC cluster. An availability replica can be hosted by either a standalone instance of SQL Server or an FCI instance. Only one FCI partner can host a replica for a given availability group. When an availability replica is running on an FCI, the possible owners list for the availability group will contain only the active FCI node.

Always On availability groups does not depend on any form of shared storage. However, if you use a SQL Server failover cluster instance (FCI) to host one or more availability replicas, each of those FCIs will require shared storage as per standard SQL Server failover cluster instance installation.

Improve this answer
0
1

I think your question was answered already, but one important point wasn't mentioned. If you are only running one secondary replica and your secondary is always passive, then you don't have to license it. If you run primary databases on both nodes, then they must both be fully licensed.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.