Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Simplify SQL Server Database Development     ====>    Webcast Registration

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

By:   |   Read Comments   |   Related Tips: More > Availability Groups


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.

Related materials:

MSSQLTips.com Sponsored Content by StarWind.

Last Update:

next webcast button

next tip button

About the author
MSSQLTips author StarWind StarWind Software is a pioneer of storage virtualization and provides a unique blend of simplicity, performance and affordability, yet with the ultimate in flexibility.

View all my tips
Related Resources

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


Learn more about SQL Server tools