2

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?

Improve this question
2
  • WSFC is at the Windows layer, so you would have to make the decision on how you wanted to handle things. I will say that as the number of nodes increases in a WSFC then the longer it takes to perform validations and add other nodes. Might make a difference to you. Out of interest, why run multiple VMs and SQL instances?
    – Nic
    Apr 25, 2017 at 19:56
  • I made the mistake of designing and implementing a similar architecture since I came from an FCI world. In the above scenario, a better architecture would be to simply have a single instance on each node, with 4 availability groups/listeners splitting up primaries and secondaries. You need to disconnect instances from AGs. Multiple instances are not recommended on VMs, and for sure doing 4 instances, each with an AG is not going to end well. I would go read Allan Hirt (@SQLHA) and do more research on why, especially on VMs, it is important to unlink instances and AGs.
    – IanG
    May 8, 2017 at 16:38

2 Answers 2

Reset to default
2

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.

Improve this answer
0

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

One will suffice. 1 windows cluster. You have two nodes that will make up the windows cluster (non shared storage)

each AG\Listener will have an entry in the role section in the failover cluster mgr.

I would make sure that each AG has its own mirror endpoint vs sharing mirror end points

B) Will this work at all?

it will work provided you have the resources to run stacked SQL instances. I don't know if you plan to run active/passive or active/active.

If you run active/passive then Software Assurance allows you to have the secondary replicas for free provided they are not read-only and your are not offloading your backups to the secondary. This means you need to have enough RAM and CPU to run all 4 active instances of SQL on one node...

If you run Active/Active ( 2 active instances on each node). You will have to license both sided, but you alleviate resource contention (CPU RAM) as you have spread the workload between two nodes in the cluster.

Improve this answer

Your Answer

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

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