Deploying SQL Server 2016 Basic Availability Groups Without Active Directory. Part 1: Building the Platform

Posted by Edwin M Sarmiento on October 31, 2017
Share on Facebook0Share on Google+0Share on LinkedIn19Share on Reddit7Tweet about this on Twitter3
5/5 (3)
5/53

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.

SQL Server 2016 logo

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.

Want to avoid the confusion of designing and implementing SQL Server Always On Availability Groups?

I know how it feels like – getting called up at 2:00 (and it has to be at this time) in the morning to resolve an outage and not have any clue at all what to do. And having SQL Server Always On Availability Groups in the mix makes it even more complicated and confusing. Sounds familiar?

I’ve been thinking about those heart-pounding moments when I need to resolve an outage in the middle of the night involving SQL Server Always On Availability Groups. It doesn’t have to be painful and extremely complex.

That’s why I created this training class on SQL Server Always On Availability Groups: The Senior DBA’s Field Guide. Because I hate complicated, complex and confusing documentation from Microsoft. I want to help SQL Server database administrators learn complex technologies in very simple terms, using analogies that anyone can relate to.

By the way, wanna level up your skills in Microsoft SQL Server?

Join the Edwin M Sarmiento course: SQL Server Always On Availability Groups: The Senior DBA’s Field Guide

Related materials:

Views All Time
36
Views Today
70

Please rate this

To download the software products, please, make your choice below. An installer link and a license key will be sent to the e-mail address you’ve specified. If you consider StarWind Virtual SAN but are uncertain of the version, please check the following document Free vs. Paid. The recent build of Release Notes. A totally unrestricted NFR (Not For Resale) version of StarWind Virtual SAN is available for certain use cases. Learn more details here.



Return to all posts

Looking at the human factors in security breaches
vSphere Upgrade Options
The following two tabs change content below.
Edwin M Sarmiento
Edwin M Sarmiento
Edwin M Sarmiento is a Microsoft Data Platform MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures running on the Microsoft server technology stack ranging from Active Directory to SharePoint and anything in between.