{"id":5673,"date":"2015-09-27T21:00:00","date_gmt":"2015-09-27T19:00:00","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-availability-groups-with-no-domain-dependencies\/"},"modified":"2015-09-27T21:00:00","modified_gmt":"2015-09-27T19:00:00","slug":"sql-server-2016-availability-groups-with-no-domain-dependencies","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-availability-groups-with-no-domain-dependencies\/","title":{"rendered":"SQL Server 2016 : availability groups with no domain dependencies"},"content":{"rendered":"

Are you aware about the new features of the next Windows version? Currently we\u2019re in TP3 and during my investigation, I was very happy to see the new features for Windows clustering (cf. this blog post<\/a> from Microsoft).<\/p>\n

By reading this article we can see:<\/p>\n

Workgroup Clusters: Clusters with nodes which are member servers \/ workgroup (not domain joined<\/em><\/strong>)<\/em><\/p><\/blockquote>\n

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\u2026<\/p>\n

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.<\/p>\n

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:<\/p>\n\n\n\n
\u00a0\"blog<\/a><\/td>\n\"blog<\/a><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Let\u2019s switch on the DNS configuration. I simply added a primary forward lookup zone DNS as shown below:<\/p>\n

\"blog<\/a><\/p>\n

The dbi-services.workgroup<\/em> 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).<\/p>\n

\"blog<\/a><\/p>\n

Now, let\u2019s 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.<\/p>\n

\"blog<\/a><\/p>\n

A classic configuration as you can notice. We have resources from the Windows Failover core resources as well as the availability groups (dummy resources).<\/p>\n

Go ahead and move on my availability group configuration (dummygrp). Here\u2019s a picture of my availability group from the Dashboard:<\/p>\n

\"blog<\/a><\/p>\n

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\u2019s the definition script of one of my endpoint:<\/p>\n

CREATE ENDPOINT [Hadr_Endpoint] \nSTATE=STARTED\nAS TCP (\n\tLISTENER_PORT = 5022, \n\tLISTENER_IP = ALL\n)\nFOR DATA_MIRRORING \n(\n\tROLE = ALL, \n\tAUTHENTICATION = CERTIFICATE [hadr_sql161w_cert], \n\tENCRYPTION = REQUIRED ALGORITHM AES\n)\nGO<\/pre>\n
Finally,\u00a0 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.<\/p>\n
\"blog<\/a><\/p>\n
But even if this new feature seems to be very interesting, we will have to deal with the following restrictions:<\/p>\n