Ever since I started working on projects involving SQL Server Always On Availability Groups back in 2012, the most common issues I’ve come across with have nothing to do with SQL Server.
After my presentation on Architecting SQL Server Availability Groups at the Seacoast SQL Server User Group in New Hampshire, someone approached me to share a story about a discussion he had with their Active Directory (AD) domain administrator. It seems that their AD domain administrator is not aware of how Windows Server Failover Clustering (WSFC) interacts with AD.
This is common in today’s complex enterprise environments. It’s also why I try to cover topics that involve Active Directory and DNS in the context of SQL Server workloads running on WSFC. Because our work as SQL Server professionals is now dependent on the things outside of SQL Server.
Here’s another example in the books.
Maxing Out Your Credit Card
Have you ever tried buying something with your credit card but the transaction won’t go thru? Invalid transaction, maybe? Probably the credit card machine is disconnected? Or maybe…
Declined due to insufficient funds.
Sounds familiar?
Very frustrating especially if what you’re trying to buy is a necessity.
Your Active Directory Credit Card With A Limit
When you create a WSFC and clustered applications within the WSFC, you are also creating an Active Directory computer object – a cluster name object (CNO) for the WSFC and a virtual computer object (VCO) for the clustered applications. If we don’t have AD domain administrator privileges (which we shouldn’t have unless we’re the AD domain administrator,) we need to be granted explicit permissions to create computer objects in AD.
Just like your credit card, there is a maximum limit to the number of computer objects that you can add to AD – your quota. You’re not the AD administrator so you can’t just go ahead and keep swiping that AD credit card.
That magic number is ten (10.)
By default, every AD domain user can add up to a maximum of ten (10) computers.
How Does This Affect You?
In a previous blog post, I covered the non-technical reasons to consider when deploying a multi-instance SQL Server failover clustered instance (FCI) or multiple Availability Groups within the same WSFC. But should you decide to deploy multiple SQL Server FCIs, this is another item to consider: how many instances or Availability Groups will you deploy?
Consider this architecture: an 8-node WSFC running 6 SQL Server FCIs and configured with 4 Availability Groups. How many AD computer objects will you need for this deployment? One for the WSFC, 6 for the SQL Server FCI and 4 for the Availability Group listener name. This will definitely exceed your quota of adding 10 computer objects in AD.
And when you hit this limit, you will get Error 1194 while performing your task. Besides, you might end up deleting and creating server names and listener names in the process.
Now, I would assume that this is not something that you would encounter on a regular basis. And I certainly don’t want you to start asking for AD domain administrator privileges. That’s like asking for an unlimited credit on your card. I’m sure you wouldn’t want the accompanying liability on that.
Properly Addressing The Maximum Limit
There are couple of ways to resolve this issue in case you encounter it in your deployments. You can request your AD domain administrator to:
- – Explicitly grant the cluster name object (CNO) the Create Computer objects permission in the Computers organizational unit/container. This will override the default permission that applies to all AD domain users.
- – Pre-stage the virtual computer objects (VCO) for the WSFC, SQL Server FCI and Availability Group listener name. Because your AD domain administrator will be the one creating the VCOs when pre-staged, you won’t have to use up your quota.
- – Increase the quota for adding computers in AD. This isn’t something I would recommend because it will apply to the entire AD domain. Use this only when you’ve tried the previous recommendations. But if you do end up with doing this, only grant a maximum number equal to the maximum number of what you will need for that deployment.
This is another one of those reasons why you need to be aware of the dependencies between SQL Server FCIs and Availability Groups. Not only will it prepare you for successful deployments but also get you on the same page with your AD domain administrators.
Additional References
- Why We Need To Understand How Active Directory Affects SQL Server High Availability
- Why Active Directory Domain Services Authentication Matters
- Microsoft KB 3007532: How to troubleshoot the Cluster service account when it modifies computer objects
- Microsoft KB 251335: Domain Users Cannot Join Workstation or Server to a Domain (still relevant for Windows Server 2003 and higher AD domains)
- Event ID 1194 — Active Directory Permissions for Cluster Accounts
- Failover Cluster Step-by-Step Guide: Configuring Accounts in Active Directory
Schedule a Call
