The Coeo Blog

Domain-Independent Windows Failover Cluster for SQL Server AlwaysOn Availability Group

Written by Dharam Patel | 31-Oct-2022 17:09:05

This blog is part of Advanced Failover Cluster options series, which demonstrates and provides information for deploying ‘Domain-independent Windows Failover Cluster for SQL Server AlwaysOn Availability Group’. You will find commonalities between the deployment of ‘Active Directory-Detached Cluster’ and ‘Domain-Independent Cluster with AlwaysOn Availability Group.’

Disclaimer – The information contained in this blog is provided solely for demonstration purposes only, for how to deploy Domain-Independent Cluster with AlwaysOn Availability Group and does not take into perspective all best practice configurations.

 

How Windows Failover Cluster has evolved

A traditional Windows Failover Cluster (WSFC), with availability group spanning more than one data centre, required all participating servers joined to the same Active Directory domain. As such, different domains, even trusted domains, did not work. The Windows Server 2012 R2 introduced the option of creating an Active Directory-Detached Cluster, which can be used with availability groups. This type of WSFC still required all nodes to be domain-joined and to the same Active Directory domain, but in this case the WSFC used DNS only. Since a domain is still involved, an Active Directory-Detached Cluster did not provide a completely domain-free experience. Therefore, Windows Server 2016 introduced a new kind of Windows Server failover cluster, based on the foundation of the Active Directory-Detached Cluster, to give you further options in the present day to remove the constraints you may have encountered previously. See the matrix below for the Domain-Independent WFSC:

 

The matrix above demonstrates the following:

  • Participating nodes can be part of different active directory domains.
  • Either of the participating nodes can be part of active directory domain and another node part of workgroup.
  • All participating nodes could be part of workgroups.

The matrix shows there are several combinations you could have for deploying Domain-Independent Cluster. Thus, giving you design flexibility that would have been limited traditionally.

Tip: Commonly you will come across this deployment being used with SQL Server 2016, but it can be used with prior SQL Server versions.

What limitations do I need to be aware of?

  • The available witness resources are disk and, new in Windows Server 2016, cloud witness. However, disk can be problematic since with an availability group as there are typically no shared disks.
  • To deploy the underlying Workgroup Cluster variant of a WSFC you must use Windows PowerShell rather than Failover Cluster Manager; it can then be administered using the Failover Cluster Manager.
  • For Kerberos Authentication, you must deploy a standard WSFC that is attached to an Active Directory domain, as Domain Independent Availability Group does not support Kerberos.
  • While a listener can be configured, it must be registered in DNS to be usable; there is no Kerberos support for the listener.
  • Applications connecting to SQL Server should solely use SQL Server Authentication.
  • SQL Server Failover Clustered Instance (FCI) is not supported with Domain-Independent Cluster.
  • Certificates must be used for configuration of the AlwaysOn Availability Group to allow the end points to communicate.
  • You cannot copy a clustered role between failover clusters that use different type of administrative access points.
  • You can set the type of administrative access point only when you create the cluster. You cannot change it after the cluster is deployed.
  • All servers must be running Windows Server 2016 or newer.
  • All servers must have the Failover Clustering feature installed.
  • Cluster service ports must be opened between the nodes.
  • All servers must use supported hardware and the collection of servers must pass all cluster validation test.
  • Primary DNS Suffix must be configured.
  • Configure the LocalAccountTokenFilterPolicy in the registry on all nodes. This only needs to be done when using a non-builtin local administrator account.

How to deploy a Domain Independent Failover Cluster

The code below is used to deploy the environment demonstrated part of this article. For your own use you should alter to your requirements and test.

 

Step 1: Informational

Configuration

Primary DNS Suffix: demo-test.com

Cluster: SQL-Cluster

Nodes: SQL-C, SQL-D

Workgroup: demo-test.com

Quorum: Cloud Witness or Disk Witness

 

Step 2: Create user account (Open Powershell ISE)

$Password = Read-Host -AsSecureString #($trongPa55word!)

New-LocalUser "WSFCAdminUser" -Password $Password -FullName "LocalAdminUser" -Description "Domainless Cluster Admin" -PasswordNeverExpires -UserMayNotChangePassword

Add-LocalGroupMember -Group "Administrators" -Member "WSFCAdminUser"

 

 Verify that local user accounts are consistent on all nodes of the clusters

 net localgroup administrators

 
Update registry values

#Changing the remote User Account Control (UAC) registry setting effects how administrator credentials are applied to remotely administer the server. When using non-built-in account, such as local user or domain account, the registry value must be updated as the credentials are applied to remotely administer the servers, and the credentials would be passed from one of the member servers/nodes within the WSFC to another, to perform administrative tasks. The following can be done via PowerShell and must be done on all member servers/nodes in the WSFC.

 New-ItemProperty -path HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System -Name LocalAccountTokenFilterPolicy -Value 1

 

Step 3: Configure DNS Suffix for each of the participating nodes

#A common DNS suffix is necessary for a Domain Independent Availability Group's Workgroup Cluster.

 $DNSSuffix = "demo-test.com"

Set-ItemProperty "HKLM:\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\" -Name Domain -Value $DNSSuffix

Set-ItemProperty "HKLM:\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\" -Name "Domain" -Value $DNSSuffix

  

Step 4: Update host files for the nodes part of workgroup to allow server name resolution.

Update host files for the servers that will participate part of the cluster. This will allow communication between the servers using server names alternatively, use IP address. Otherwise, the cluster creation will fail.

  • Open ‘RUN’ Then type ‘drivers’ Then ‘OK’

This will open driver directory. Optionally go directly to: C:\Windows\System32\drivers\etc

  • Select host file then open with Notepad as administrator.
  • Type the private IP addresses and FQDN of the servers.
  • Save the host file.

Step 5: Install Windows failover cluster

#Install Failover Clustering Feature

Install-WindowsFeature -Name Failover-Clustering –IncludeManagementTools

 

 #Validate the cluster configuration

Test-Cluster –Node SQL-C,SQL-D

#New Cluster with administrative access point of type DNS

New-Cluster -Name sql-cluster –Node SQL-C,SQL-D –StaticAddress 10.10.0.74 -NoStorage –AdministrativeAccessPoint Dns

 

 

Step 6: Verify cluster failover & administrative access point

 

#Test cluster group failover

Get-ClusterGroup -Name "Cluster Group"

 

 Move-ClusterGroup -Name "Cluster Group"

 

 

# Verify administrative access point

(Get-Cluster).AdministrativeAccessPoint

 

 

Traditionally, you would have seen AD/Dns.

 

#Update Cluster Name in DNS (Optimal if participating noes are domain joined)

Get-ClusterResource -Name "Cluster Name" | Update-ClusterNetworkNameResource

 

Deploy AlwaysOn Availability Group for Domain Independent Failover Cluster

Step 7: SQL Server Setup

#Install SQL Server with SQL Authentication and Configure

#Enable and Start SQL Server Agent

Get-Service -Displayname "SQL*"

Set-Service -Name SQLSERVERAGENT -StartupType Automatic

Start-Service -Name SQLSERVERAGENT

 

#Install the SqlServer PowerShell module

Install-Module -Name SqlServer

Get-Module SqlServer -ListAvailable

Import-Module SqlServer

 

 

# Enable the AlwaysOn Availability Groups feature

Enable-SqlAlwaysOn -Path SQLSERVER:\SQL\SQL-C\DEFAULT

 

 # Validate

Step 8: SQL Server DMK, Cert and Login configuration

 

#Create Database Master Key (DMK) on each instance

create master key encryption by password = '$trongPa55word!';

 

#Validate

select name, principal_id, symmetric_key_id, key_length, key_algorithm, algorithm_desc, create_date, modify_date

from sys.symmetric_keys

where name = '##MS_DatabaseMasterKey##';

 

 

#Create a certificate on all replicas

create certificate [sql-C_cert]

with subject = 'SQL-C Certificate',

expiry_date = '2027-06-16T17:00:00'

 

 #Validate

select name, certificate_id, principal_id, pvt_key_encryption_type_desc, issuer_name, expiry_date, start_date, key_length

from sys.certificates

where name = 'sql-C_cert';

 

 #Backup certificate

backup certificate [sql-C_cert]

to file = 'C:\certs\sql-C_cert.cer';

#On each replica create a login for the other replicas (primary example)

create login [sql-D_login]

with password = '$trongPa55word!';

 

#On each replica create a database user for each login

create user [sql-D_user]

for login [sql-D_login];

 

#Validate

select name, principal_id, type_desc, create_date, modify_date

from sys.database_principals

where name = 'sql-D_user'

 

 

#Restore each of the certificates onto the other replicas (primary example)

create certificate [sql-D_cert]

authorization [sql-D_user]

from file = 'C:\certs\sql-D_cert.cer';

 

#Validate

select name, certificate_id, principal_id, pvt_key_encryption_type_desc, issuer_name, expiry_date, start_date, key_length

from sys.certificates

where name = 'sql-D_cert';

 

 

Step 9: Configure Endpoint

#Create the endpoint for use by the availability group on each instance

create endpoint database_mirroring

state = started

as tcp (

listener_port = 7022,

listener_ip = all

)

for database_mirroring (

authentication = certificate [sql-C_cert],

encryption = required algorithm aes,

role = all

)

#Validate

select name, endpoint_id, principal_id, protocol_desc, type_desc, state_desc, role_desc, is_encryption_enabled, connection_auth_desc, encryption_algorithm_desc

from sys.database_mirroring_endpoints

 

 

#Grant endpoint access to each login (primary example)

grant connect on endpoint::database_mirroring

to [sql-D_login]

 

#The [NT AUTHORITY\SYSTEM] account is used by SQL Server Always On health detection to connect to the SQL Server computer and to monitor health. When availability group is created and the primary replica in the availability group comes online, health detection is initiated. If the [NT AUTHORITY\SYSTEM] account does not exist or have sufficient permissions, health detection cannot be initiated, as such, availability group creation process will fail. Make sure that these permissions exist on each SQL Server computer that could host the primary replica of the availability group.

 

USE [master]

GO

GRANT ALTER ANY AVAILABILITY GROUP TO [NT AUTHORITY\SYSTEM]

GO

GRANT CONNECT SQL TO [NT AUTHORITY\SYSTEM]

GO

GRANT VIEW SERVER STATE TO [NT AUTHORITY\SYSTEM]

GO

 

Step 10: SQL Server AlwaysOn Availability Group & Database Configuration

 

#Configure Full Recovery

alter database [database_name] set recovery full;

 

#Validate

select name, recovery_model_desc

from sys.databases

where database_id > 4

order by name;

 

 

#Backup Database (if no backup)

backup database [database_name]

to disk = N'<Path>\<databasebackupname>.bak'

with checksum, stats = 10;

 

#Create Availability Group

use [master];

go

CREATE AVAILABILITY GROUP Demo_AG

   WITH (

     AUTOMATED_BACKUP_PREFERENCE = SECONDARY,

     FAILURE_CONDITION_LEVEL = 3,  

     HEALTH_CHECK_TIMEOUT = 600000

       )

   --FOR DATABASE []

   REPLICA ON  

     'SQL-C' WITH  

         (

         ENDPOINT_URL = 'TCP://SQL-C.demo-test.com:7022',

         failover_mode = automatic,

         availability_mode = synchronous_commit,

         session_timeout = 10,

         seeding_mode = automatic,

         primary_role(allow_connections = all),

         secondary_role(allow_connections = no)

 

         ),  

     'SQL-D' WITH  

         (

         ENDPOINT_URL = 'TCP://SQL-D.demo-test.com:7022',

         failover_mode = automatic,

         availability_mode = synchronous_commit,

         session_timeout = 10,

         seeding_mode = automatic,

       primary_role(allow_connections = all),

         secondary_role(allow_connections = no)

         );

go

 

#Join Secondary Replica to Availability Group (Secondary)

alter availability group [Demo_AG] join;

 

 #Allow Automatic Seeding to Start

alter availability group [Demo_AG]

grant create any database;

#You can now add database into AAG, if the database pre-requisites are met.

 

References

Microsoft, 2021. Create a domain-independent availability group - SQL Server Always On. [Online]
Available at: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/domain-independent-availability-groups?view=sql-server-ver15
[Accessed 12 2021].

Patel, D., 2021. Windows Active Directory Detached Cluster with SQL Server Basic Availability Group. [Online]
Available at: https://blog.coeo.com/advanced-sql-server-windows-cluster-options
[Accessed 12 2021].

 
View full post