Infrastructure at your Service

David Barbarin

SQL Server 2016 : availability groups with no domain dependencies

Are you aware about the new features of the next Windows version? Currently we’re in TP3 and during my investigation, I was very happy to see the new features for Windows clustering (cf. this blog post from Microsoft).

By reading this article we can see:

Workgroup Clusters: Clusters with nodes which are member servers / workgroup (not domain joined)

If you remember, Microsoft had introduced Active Directory-Detached Cluster feature with Windows 2012 but the dependency enhancement concerned only the network names. Unfortunately, we had to continue with domain accounts. Now Microsoft provides a complete Windows Failover Cluster architecture without domain dependencies and this is particularly interesting when we talk about SQL Server availability groups. Why? Because with the next version of SQL Server, it seems that we will be able to use availability groups in conjunction of the Windows Failover Cluster in a workgroup context. This is a very good news and we can begin to draw up some additional scenarios like DMZ or certainly others…

So I decided to install and test a basic environment that includes a SQL Server 2016 CTP2.3 on the top of the Windows 2016 TP3 in a workgroup environment. Let me show you the result.

First, my two cluster nodes SQL161W and SQL162W are configured in a workgroup. In addition, according to the Microsoft blog post, I added the DNS suffix manually on each node as shown below:

 blog 64- 1 - first node cfg blog 64- 2 - second node cfg

Let’s switch on the DNS configuration. I simply added a primary forward lookup zone DNS as shown below:

blog 64- 3 - dns

The dbi-services.workgroup zone includes the following records related to the Microsoft Windows Cluster (WIN2016), the cluster nodes (SQL161W and SQL162W) and my availability group listener (lst-grp).

blog 64- 3 - dns records

Now, let’s take a look at the cluster resources. Just to clarify here, we have to use the PowerShell cmdlets in this context. GUI is not suitable in this case.

blog 64- 5 - cluster resources

A classic configuration as you can notice. We have resources from the Windows Failover core resources as well as the availability groups (dummy resources).

Go ahead and move on my availability group configuration (dummygrp). Here’s a picture of my availability group from the Dashboard:

blog 64- 6 - aag dashboard

The trickiest part here was the configuration of the different endpoints. Indeed, in this scenario we have to configure the authentication with certificates (for those who already use mirroring architectures in workgroup or different domains, you know what I mean). So, it is necessary to configure correctly inbound and outbound connections for both replicas. Here’s the definition script of one of my endpoint:

CREATE ENDPOINT [Hadr_Endpoint] 
STATE=STARTED
AS TCP (
	LISTENER_PORT = 5022, 
	LISTENER_IP = ALL
)
FOR DATA_MIRRORING 
(
	ROLE = ALL, 
	AUTHENTICATION = CERTIFICATE [hadr_sql161w_cert], 
	ENCRYPTION = REQUIRED ALGORITHM AES
)
GO

Finally,  Let’s have a look at the SQL Server instances service accounts. I still use virtual accounts (NT SERVICE\MSSQL$SQL16) on both replicas rather than domain accounts in usual scenarios.

blog 64- 7 - service account

But even if this new feature seems to be very interesting, we will have to deal with the following restrictions:

  • Administering the Windows Failover Cluster can be only done by the PowerShell cmdlets. You cannot use the GUI here. System administrators, it’s time to seriously learn PowerShell !
  • We are in a domain-less environment so it means that we will not be able to use Kerberos and SQL Server authentication is recommended in this case. But in this context, this is not a big deal.
  • The file share witness is not supported in this scenario, so you have no choice to use either a shared disk or a cloud witness (cf. my blog here)

An open point remains for the moment: Is this configuration supported with the previous versions of SQL Server? Well, the Microsoft blog post doesn’t really provide a clue. I will update my blog when I get more info on this topic.

See you

 

 

Leave a Reply


3 + = six

David Barbarin
David Barbarin

Senior Consultant & Microsoft Technology Leader