Sql-server – SQL Server 2016 Multiple Availability Groups and instances

Architectureavailability-groupsinstancesql server

So we are getting ready to make a major architecture shift and I get to be on the hook for it, yay!

Currently we have 22 separate availability groups each with their own windows cluster across 44 servers.

We want to move to 6 servers to hold the 22 AGs, yes I know it doesn't divide evenly.

So for example a given server (VM) would be 4 SQL Server instances, each instance would be an availability group and have its own listener.

So something like:

  • NODE1: SQLINST1, SQLINST2, SQLINST3, SQLINST4

  • NODE2: SQLINST1, SQLINST2, SQLINST3, SQLINST4

  • AGs: SQLINST1, SQLINST2, SQLINST3, SQLINST4

  • Listeners: SQLINST1Lis, SQLINST2Lis, SQLINST3Lis, SQLINST4Lis

My questions are:

  • does each SQL Server instance also need to be its own windows cluster or could one cluster suffice for all the named instances?

  • Will this work at all?

Best Answer

A) does each sql instance also need to be its own windows cluster or could one cluster suffice for all the named instances?

SQL Server doesn't have cluster membership, servers do. If the server is part of a windows cluster then all instances of SQL Server could be part of the same cluster. It is not possible to have a server be part of more than one cluster.

B) Will this work at all?

It may... only if you have an unordinary small workload. Just the worker threads alone would make me assume that you're not going to have a good time. This coupled with having 4 instances per server, that don't communicate with each other, that are all trying to step on each others' toes (so to speak) leaves me thinking this, "I am really glad I won't have to admin this!".

My personal take

I would step away from thinking of AGs such as this. If you want to consolidate, that's great and I'm all for it! However, it needs to be done in a way that doesn't end up hurting you or your customers.

If it were me, and I was tasked with the same thing, I'd immediately push back on "6" servers. I don't yet know how many I'll need... unless of course we're not going to do any scientific research or testing - in which case I'd escalate my concerns.

We don't know things such as the number of total databases, how much log generation each database creates, etc., which we'd need to start gaining an understanding of what will be needed.

Undoubtedly you can consolidate these. Is it even the right thing to put them all in a single cluster? I wouldn't.

Each cluster is a fault domain, and while there are some really awesome features such as distributed availability groups that still ends up being more than a single cluster. Think of it this way, all of your AGs are in a single cluster - what happens if you have an issue with said cluster? Are all of the AGs now down? Maybe. Probably. Either way it's not something I'd want to be dealing with at 5 am.

Some of the main things you'll see when you have too many AGs or databases in AGs:

  1. Worker thread exhaustion
  2. Slow DMV queries
  3. Slow Cluster API responses
  4. Slow or Unresponsive Instances of SQL Server

My advice would be to gather metrics:

  1. Number of database
  2. Number of needed replicas for HA and DR
  3. Log generation rate
  4. CPU/Disk/Memory usage

That'll give you a start on how the pieces will need to fit together. There needs to be much more diving into how this will turn out, but hopefully this will point you in the right direction.

Related Question