Domain Independent Cluster and Availability Group

What is a Domain Independent Cluster?

Did you know that you can have a Windows cluster that is a domain independent cluster and run a SQL Server Availability Group on top of it?  The technology has been around for a while, but a lot of DBAs are not aware of it.  Why?  All the glory belongs to Windows since that is where the magic resides.  It's like SQL is an awesome Ninja Cat riding on a magic unicorn!

[caption id="" align="alignnone" width="168"] Come at me Bro![/caption]

Requirements

Okay, now that you have had time to calm down from all the awesomeness, let's talk about the requirements.  First we need Windows and the requirement is Windows Server 2016 or above.  The keen observer might have caught that "or above" I slipped into that sentence.  If you are not part of the Windows Insider program, you should check it out.  There is some really cool stuff coming in Windows that is directly applicable to clusters.  If you build or support SQL Server AlwaysOn Failover Cluster Instances or SQL Server AlwaysOn Availability Groups, you should be keeping up with Windows Server and the clustering feature.  It will pay dividends, I guarantee it.

So what about SQL Server?  The requirement here is SQL Server 2014 through 2017.  I do want to clarify this a bit too.  In other posts and documentation you will see SQL Server 2016 as the requirement.  That is only because SQL Server 2016 was current when Windows 2016 released.  Since the magic is really in Windows and not in SQL Server, you are supported with SQL 2014 since it is supported on Windows Server 2016.

Design Options

There are several combinations you can have in this configuration.  You can have nodes that are not part of a domain at all, or some that are, or some that are joined to completely different domains.  Below is a chart with the different combinations.  Note that for anything where we are joined to two different domains, we mean complete autonomy.  The assumption is that the domains are in completely separate forests and there are no domain trusts in place.  Remember that prior to this enhancement, nodes in a cluster had to be in the same domain.  Even if the domain was trusted and within the same forest, this would not work.

Options Matrix

Pre-requisites

We do have a few pre-requisites that must be met prior to creating the cluster.

• We need local accounts on each node that have identical names and passwords
• The local accounts need to be members of the local administrators group
• We need to set the LocalAccountTokenFilterPolicy in the registry
  • This only needs to be done when using a non-builtin local administrator account
• Windows Failover Clustering Feature must be installed
• Primary DNS Suffix must be configured

Setup

By now you want to know how to setup and configure this coolness.  Today is your lucky day...here is a video walk through!

https://www.youtube.com/watch?v=d6adWpf00rs\&t=970s

Code

All of the code below is what I used in the above video in my test environment. You should test this code prior to using it and you assume all responsibility as it is only provided for demonstration purposes.  You will need to run this on each replica you plan to add to your Availability Group.

Cluster Setup

[powershell]

#First we have to create a local user with the same name and password on each node.
#The account is required to be in the local Adminstrators group
$Password = Read-Host -AsSecureString
New-LocalUser "WSFC" -Password $Password -FullName "WSFC" `
-Description "Used for Domain Independent Cluster" `
-PasswordNeverExpires -UserMayNotChangePassword

Add-LocalGroupMember -Group "Users" -Member "WSFC"
Add-LocalGroupMember -Group "Administrators" -Member "WSFC"

#Next we have to set the LocalAccountTokenFilterPolicy in the registry since we are not using a built-in account
new-itemproperty -path HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System -Name LocalAccountTokenFilterPolicy -Value 1

#Next we need to make sure the machine has a primary DNS Suffix
#This should NOT be required on domain joined machines since the domain name is automatically set as the PDS
#Only run this on workgroup "Non-Domain joined machines"
$DNSSuffix = "independent.com"
Set-ItemProperty "HKLM:\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\" -Name Domain -Value $DNSSuffix
Set-ItemProperty "HKLM:\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\" -Name "NV Domain" -Value $DNSSuffix

#We changed the primary DNS Suffix so we need to reboot for the change to take affect
Restart-Computer -ComputerName $env:computername -Force

#Now we actually create the cluster
#For clarity this only gets run on the first node
New-Cluster –Name NoDomainClus -Node Server1,Demo2 -AdministrativeAccessPoint DNS `
-StaticAddress 10.0.0.41 -NoStorage -IgnoreNetwork 192.168.1.0/24

[/powershell]

Here is the code to create the endpoints and certificates as well as install the certificates. Check out this post from Kevin Farley for the source.

Create Endpoint and Certificate

[sql]

/*
— This procedure automates the creation of a local certificate and the endpoints required for a domainless AG.
— Parameters are the strong password for the cert, and the location of a share which receives the backup of the cert.
— The share should be accessible to all nodes in the AG, as they will need to read the certs for each other.
— The procedure also creates the endpoint based upon the newly created cert.
— https://blogs.technet.microsoft.com/dataplatforminsider/2015/12/15/enhanced-always-on-availability-groups-in-sql-server-2016/

EXEC CreateEndpointCert '\\demo2\temp', '1R3@llyStr0ngP@ssw0rd!'
*/

DROP PROCEDURE IF EXISTS CreateEndpointCert;
GO

CREATE PROCEDURE CreateEndpointCert
@ShareName SYSNAME ,
@StrongPassword SYSNAME
AS BEGIN

--This must be executed in the context of Master
IF (DB_NAME() <> 'master')
BEGIN
PRINT N'This SP must be executed in master. USE master and then retry.'
RETURN (-1)
END

DECLARE @DynamicSQL varchar(1000);
DECLARE @CompName varchar(250);
DECLARE @HasMasterKey INT;
SELECT @CompName = CONVERT(SysName, SERVERPROPERTY('MachineName'));

-- Only create a master key if it doesn't already exist

SELECT @HasMasterKey = is_master_key_encrypted_by_server from sys.databases where name = 'master'
IF (@HasMasterKey = 0)
BEGIN
--Create a MASTER KEY to encrypt the certificate.
SET @DynamicSQL = CONCAT('CREATE MASTER KEY ENCRYPTION BY PASSWORD = ' , QUOTENAME(@StrongPassword, ''''));
EXEC (@DynamicSQL)
END

--Create the certificate to authenticate the endpoint
IF EXISTS (SELECT name from sys.certificates WHERE name = QUOTENAME(@CompName + '-Cert'))
BEGIN
SET @DynamicSQL = CONCAT('DROP CERTIFICATE ', QUOTENAME(@CompName + '-Cert'));
EXEC (@DynamicSQL);
END
SET @DynamicSQL = CONCAT('CREATE CERTIFICATE ', QUOTENAME(@CompName + '-Cert'), ' WITH SUBJECT = ', QUOTENAME(@CompName, '''')) ;
EXEC (@DynamicSQL);

--Create the database mirroring endpoint authenticated by the certificate.
SET @DynamicSQL =
CONCAT('CREATE ENDPOINT AlwaysOn_EP
STATE = STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE ',QUOTENAME(@CompName + '-Cert'), ' , ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL)');
EXEC (@DynamicSQL);

--Back up the certificate to a common network share for import into other nodes in the cluster
SET @DynamicSQL = CONCAT('BACKUP CERTIFICATE ',QUOTENAME(@CompName + '-Cert'),' To FILE = ', QUOTENAME( @ShareName + '\SQL-' + @CompName + '.cer', ''''));
EXEC (@DynamicSQL);
END
GO

[/sql]

Install Endpoint Certificate

[sql]

/*
— This procedure assumes that a certificate has been created on another node in the AG, and backed up to a common network share.
— Parameters:
— @CompName – The name of the computer whose certificate needs to be installed here. i.e. the other replica that this node needs to communicate with.
— @ShareName – A common network share to which certificates were backed up from each machine in the cluster/AG.
— @StrongPassword – A strong password to be used for the login created to log in on behalf of the endpoint on the other node.
—
— This procedure assumes that each node has run CreateEndpointCert and that all cert backup files reside on the share pointed to by the second parameter.
— The procedure creates a login and a user for the remote machine, and then created a certificate to authorize the user when the certificate is used as authentication from the remote endpoint.
-- https://blogs.technet.microsoft.com/dataplatforminsider/2015/12/15/enhanced-always-on-availability-groups-in-sql-server-2016/

EXEC dbo.InstallEndpointCert 'Server1', '\\demo2\temp', '1R3@llyStr0ngP@ssw0rd!'
EXEC dbo.InstallEndpointCert 'Demo2', '\\demo2\temp', '1R3@llyStr0ngP@ssw0rd!'

*/

DROP PROCEDURE IF EXISTS [dbo].[InstallEndpointCert];
GO

CREATE PROCEDURE [dbo].[InstallEndpointCert]
@CompName SYSNAME,
@ShareName SYSNAME,
@StrongPassword SYSNAME
AS BEGIN
DECLARE @DynamicSQL varchar(1000);
DECLARE @MyCompName varchar(250);
SELECT @MyCompName = CONVERT(SysName, SERVERPROPERTY('MachineName'));
--Don't need to create LOGINs for the local system
IF (@MyCompName <> @CompName)
BEGIN
IF EXISTS (SELECT name from sys.certificates WHERE name = QUOTENAME(@CompName + '-Cert'))
BEGIN
SET @DynamicSQL = CONCAT('DROP CERTIFICATE ', QUOTENAME(@CompName +'-Cert'));
EXEC (@DynamicSQL);
END
SET @DynamicSQL = CONCAT('DROP USER ', QUOTENAME( @CompName + '-User'));
EXEC (@DynamicSQL);
--SET @DynamicSQL = CONCAT('DROP LOGIN ', QUOTENAME (@CompName + '-Login'));
--EXEC (@DynamicSQL);

SET @DynamicSQL = CONCAT('CREATE LOGIN ', QUOTENAME (@CompName + '-Login'), ' WITH PASSWORD= ', QUOTENAME( @StrongPassword, ''''));
EXEC (@DynamicSQL);

SET @DynamicSQL = CONCAT('CREATE USER ', QUOTENAME( @CompName + '-User'), ' FOR LOGIN ', QUOTENAME(@CompName + '-Login'));
EXEC (@DynamicSQL);

SET @DynamicSQL = CONCAT('CREATE CERTIFICATE ', QUOTENAME(@CompName +'-Cert'), ' AUTHORIZATION ', QUOTENAME(@CompName +'-User'), ' FROM FILE = ', QUOTENAME(@ShareName + '\SQL-' + @CompName + '.cer' , ''''));
EXEC (@DynamicSQL);

SET @DynamicSQL = CONCAT('GRANT CONNECT ON ENDPOINT::AlwaysON_EP TO ', QUOTENAME(@CompName +'-Login'));
EXEC (@DynamicSQL);
END
END

GO

[/sql]

Availability Group Setup (Run in SQLCMD Mode)

[sql]

/*
Author: Ryan Adams
Website: https://www.ryanjadams.com
Twitter: @ryanjadams

This script must be executed in SQLCMD mode. This script was designed to setup an AlwaysOn Availability Group
in a custom lab environment. Use at your own risk and DO NOT run this in production. Make sure you read and understand
it thoroughly.
*/

/*
First we connect to each replica in order and create logins for the account running the SQL Server Service on the other replicas
Next we connect to each replica and create the endpoint.
We also grant connect permission on the endpoint to all of the other replica service accounts.
In a Domain Independent Cluster this must be done using certificates. Use the following scripts to do that.
CreateEndpointCert.sql
InstallEndpointCert.sql
*/

/* Here we connect to each replica and ensure that the AlwaysOn extended events session is set to run at startup and that it is also started */
:Connect DEMO2

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AlwaysOn_health')
BEGIN
ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);
END

IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='AlwaysOn_health')
BEGIN
ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;
END

GO

:Connect SERVER1 -U MyAdmin -P NotGettingThisPassword

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AlwaysOn_health')
BEGIN
ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);
END

IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='AlwaysOn_health')
BEGIN
ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;
END

GO

/*
There will be 1 database included in our AG and it currently exists on DEMO2.
Prior to SQL 2016 we would have to do the following:
Backup each database data file to a share and then restore it on each replica making sure to use the "NORECOVERY" clause
Backup each database log file to a share and then restore it on each replica making sure to use the "NORECOVERY" clause

With SQL 2016 we can use Direct Seeding instead.
*/

/* Here we connect to our primary replica (DEMO2) and create our AG. */
:Connect DEMO2

/* We can use trace flag 9567 to enable compression for the VDI backup for the seeding process */
DBCC TRACEON (9567, -1);
GO

USE [master];
GO

CREATE AVAILABILITY GROUP [MyAG]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY, FAILURE_CONDITION_LEVEL = 3, HEALTH_CHECK_TIMEOUT = 30000, DB_FAILOVER = ON, DTC_SUPPORT = NONE) /* You can also use SECONDARY_ONLY, PRIMARY, or NONE for backup preference
Failure condition levels are from the least restrcitive of 1(SQL Service is down) to 5(Exhaustion of worker threads). 3 is the default.
Health check default is 30 seconds, represented in milliseconds. This is how long we wait for sp_server_diagnostics to return. */
FOR DATABASE [DomainIndependent]
REPLICA ON N'DEMO2' WITH (ENDPOINT_URL = N'TCP://DEMO2.independent.com:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = Read_Only), SESSION_TIMEOUT = 10, SEEDING_MODE = AUTOMATIC),
N'SERVER1' WITH (ENDPOINT_URL = N'TCP://SERVER1.americans.com:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 60, SECONDARY_ROLE(ALLOW_CONNECTIONS = Read_Only), SESSION_TIMEOUT = 10, SEEDING_MODE = AUTOMATIC);
GO

/* Here we create a listener for our AG */
ALTER AVAILABILITY GROUP [MyAG]
ADD LISTENER N'MyAG' (
WITH IP
((N'10.0.0.11', N'255.255.255.0'))
, PORT=1433);
GO

/* Now that the AG exists we connect to each secondary replica and join it to the group
We also have to grant the CREATE ANY DATABASE permission so Seeding can create the DBs */
:Connect SERVER1 -U MyAdmin -P NotGettingThisPassword

ALTER AVAILABILITY GROUP [MyAG] JOIN;
GO
ALTER AVAILABILITY GROUP [MyAG] GRANT CREATE ANY DATABASE;
GO

/* This query let's us view the progress of the seeding operation
SELECT start_time,
ag.name,
db.database_name,
current_state,
performed_seeding,
failure_state,
failure_state_desc
FROM sys.dm_hadr_automatic_seeding autos
JOIN sys.availability_databases_cluster db ON autos.ag_db_id = db.group_database_id
JOIN sys.availability_groups ag ON autos.ag_id = ag.group_id;
GO

SELECT * FROM sys.dm_hadr_physical_seeding_stats;
GO
*/

/* Prior to SQL 2016 we had to connect to each secondary replica and add our DBs to the group.
With seeding we no longer have to do this as it is joined during the seeding process

:Connect SERVER1 -U MyAdmin -P NotGettingThisPassword
ALTER DATABASE [MyAG_DB1] SET HADR AVAILABILITY GROUP = [MyAG];
ALTER DATABASE [MyAG_DB2] SET HADR AVAILABILITY GROUP = [MyAG];
ALTER DATABASE [MyAG_DB3] SET HADR AVAILABILITY GROUP = [MyAG];
GO
*/

/* Now we need to turn our trace flag back off */
:Connect DEMO2
DBCC TRACEOFF (9567, -1);
GO

[/sql]

Resources

You might also be interested in Read-Scale or Clusterless Availability Groups.

The other thing most folks find challenging with Availability Groups are how to sync users and server level objects.  Check out Sync SQL Logins for more information.