Introduction
When Availability Groups were introduced in SQL Server 2012, they were only available in Enterprise Edition. This made it challenging to move from Database Mirroring to Availability Groups, especially if you’re running Standard Edition. To upgrade and migrate from Database Mirroring in Standard Edition, you either choose to upgrade to a more expensive Enterprise Edition license and implement Availability Groups or stick with Database Mirroring and hope that everything works despite being deprecated.
SQL Server 2016 introduced Basic Availability Groups in Standard Edition, allowing customers to run some form of limited Availability Groups. Customers now have a viable replacement for Database Mirroring in Standard Edition. However, unlike Database Mirroring, Availability Groups require a Windows Server Failover Cluster (WSFC). SQL Server database administrators now need to be highly skilled in designing, implementing and managing a WSFC outside of SQL Server. Because the availability of the SQL Server databases relies heavily on the WSFC.
What isn’t explicitly mentioned in most of the Microsoft documentation is that a WSFC requires Active Directory. WSFC’s dependency on Active Directory is a more challenging hurdle to overcome, especially if the existing Database Mirroring configuration does not use Active Directory. This also means that SQL Server database administrators need to have an understanding of Active Directory and how a WSFC depends on it to achieve high availability. You certainly don’t want to be implementing Active Directory exclusively for Availability Groups.
Enter Windows Server 2016
Previous versions of WSFC required tight integration with Active Directory. When you create a WSFC, it creates a computer name object (CNO) in Active Directory that is kinda like a virtual computer object. This, in turn, creates a corresponding DNS entry to access the WSFC. Every resource you create in a WSFC does the exact same thing – create a virtual computer object in Active Directory and a corresponding DNS entry. When you create a listener name in a SQL Server Availability Group, the listener name is created as a virtual computer object in Active Directory with a corresponding DNS entry. I may have been repeating myself here but it is very important to highlight this critical point: the availability of the listener name and the Availability Group relies heavily on Active Directory and DNS. As a SQL Server database administrator, you really need to know this tight dependency between your Availability Group, the WSFC and Active Directory and DNS. Because your job depends on it.
Windows Server 2016 introduced the ability to create WSFCs without any dependency in Active Directory. You can have member servers that are in a workgroup or in different Active Directory domains or forests. This allows you to deploy SQL Server Availability Groups without Active Directory, much like having Database Mirroring without Active Directory.
The Right Combination
In order to setup and configure Basic Availability Groups without Active Directory, you need to have Windows Server 2016 and SQL Server 2016 Standard Edition. It’s not enough to just have SQL Server 2016. The ability to deploy a WSFC without Active Directory is a Windows Server 2016 feature, not a SQL Server 2016 feature. Sure, you can create a Basic Availability Group with SQL Server 2016 Standard Edition and Windows Server 2012 R2. But you will need Active Directory to pull that one off.
So, in order to get this configuration properly setup, you would need:
- Windows Server 2016 (Standard or Datacenter Edition; although the licensing cost is nothing compared to SQL Server licenses)
- SQL Server 2016 Standard Edition
- Node majority, disk witness or cloud witness quorum type
Discussion of the different quorum types is beyond the scope of this blog post. In fact, the concepts around quorum are so complicated and complex that it deserves an entire training course just on this very topic alone.
Setup and Configure Windows Server 2016 Failover Cluster without Active Directory
I’ve decided to break this process into multiple parts. Mainly because in large organizations, the people responsible for setting up and configuring the WSFC are different from the SQL Server database administrators. If you happen to be responsible for both tasks, be sure to check out the next part of this series.
I’ve also decided to just show you a video walkthrough on how it is done. In my environment, I built a 3-node, multi-site Windows Server 2016 failover cluster with the shared disk as a witness type because I really don’t like the idea of exposing my mission-critical databases to the internet by using a cloud witness. If the servers are in the same data center, node majority quorum would suffice. You will only install SQL Server on two nodes for the Basic Availability Group and just use the third node as an extra vote to meet quorum.
Here it is. Watch and learn.
In the next part of this series, I’ll walk you thru the process of setting up and configuring SQL Server 2016 Basic Availability Group. Be sure to watch out for that.
