This DSC configuration assumes you have already created the gMSAs (group managed service accounts) with appropriate SPNs for your deployment, as well as the computer accounts used for the Windows failover cluster and the SQL always on group listener. The configuration also assumes you have a file share setup with the SQL install media and Windows SXS folders for the versions of Windows and SQL server you will be deploying.
Overview
The configuration will create a new Windows failover cluster and SQL Always On group. The primary replica configuration will ensure the existence of the failover cluster and always on group. Each secondary node will wait for the Windows cluster to be created then join as a secondary node. After the SQL configuration tasks have been completed the secondary node will wait for the Always On group to be available then configure itself as a replica.
Configuration Data
The configuration is setup to support SQL server 2016, 2017, and 2019 on Windows server 2016, and 2019. It assumes you are using gMSA accounts for the SQL service and SQL agent service. “PSDscAllowDomainUser” and “PSDscAllowPlainTextPassword” are included to simplify the example. You would ideally secure the MOF file with a certificate.
$CD = @{
AllNodes = @(
@{
NodeName = '*'
InstanceName = 'MSSQLSERVER'
SqlVersion = "2019"
WindowsVersion = "2019"
SQLSysAdminAccounts = 'SQLAdminAccounts'
Features = 'SQLENGINE,FullText,Replication'
SQLSvcAccountName = 'gMSASvcAccount'
AgtSvcAccountName = 'GMSAAgtAccount'
AvailabilityGroupName = 'SQLAGN'
AvailabilityGroupIPAddress = '192.168.1.101/255.255.255.0'
ClusterName = 'Clustername'
ClusterIPAddress = '192.168.1.100/24'
FileShareWitness = '\\FileShareWitnessPath\Clustername'
PSDscAllowDomainUser = $true
PSDscAllowPlainTextPassword = $true
},
@{
NodeName = 'TestNodeOne'
SqlAGRole = 'PrimaryReplica'
},
@{
NodeName = 'TestNodeTwo'
SqlAGRole = 'SecondaryReplica'
}
)
}Configuration Block
The configuration takes two parameters, $SqlAdministratorCredential will be used to perform all SQL specific administrative tasks $ActiveDirectoryAdministratorCredential will be used for the Windows failover cluster tasks. The SqlServerDsc and xFailOverCluster DSC resources are required for this configuration.
Configuration DSC_SQL_AG
{
param
(
[Parameter(Mandatory = $true)]
[System.Management.Automation.PSCredential]$SqlAdministratorCredential,
[Parameter(Mandatory = $true)]
[System.Management.Automation.PSCredential]$ActiveDirectoryAdministratorCredential
)
Import-DscResource -ModuleName SqlServerDsc
Import-DscResource -ModuleName xFailOverClusterSQL Setup Dependencies
Two switch blocks are used to configure the unique dependencies for the the primary and secondary role SQL setup resource.
The primary replica’s SQL setup resource will have a dependency setup for the “CreateCluster” resource, while the secondary replica will have a dependency setup for the “JoinSecondNodeToCluster” resource.
The SQL version switch assumes you have setup a file share that contains the install media for each of the SQL versions you would like the configuration to be able to deploy.
# Switch statement defines role specific dependencies for SQL setup resource
Switch($Node.SqlAGRole)
{
'PrimaryReplica'
{
$RoleDependsOn = '[xCluster]CreateCluster'
}
'SecondaryReplica'
{
$RoleDependsOn = '[xCluster]JoinSecondNodeToCluster'
}
}
# Switch statement defines SQL version specific dependencies for SQL setup resource
Switch($Node.SqlVersion)
{
"2016"
{
$SqlSourcePath = '\\SqlSourcePath\SQL-2016'
$NetFramework35 = $True
$SqlDependsOn = '[WindowsFeature]NetFramework35', '[WindowsFeature]NetFramework45', $RoleDependsOn
}
"2017"
{
$SqlSourcePath = '\\SqlSourcePath\SQL-2017'
$NetFramework35 = $True
$SqlDependsOn = '[WindowsFeature]NetFramework35', '[WindowsFeature]NetFramework45', $RoleDependsOn
}
"2019"
{
$SqlSourcePath = '\\SqlSourcePath\SQL-2019'
$SqlDependsOn = '[WindowsFeature]NetFramework45', $RoleDependsOn
}
}Service Account Variables
Next we will configure two variables for use in the SQL resources. First we take the name of the SQL service and SQL Agent service accounts and convert them to domain\UserName format.
# Convert the gMSA account names into domain\username format for future use
$SqlSvcAccount = ('DomainName\' + $Node.SQLSvcAccountName + '$')
$SqlAgtAccount = ('DomainName\' + $Node.AgtSvcAccountName + '$')Then we create PSCredentail objects that will be used with the SQL setup resource to define the service accounts.
If a service account username has a dollar sign at the end of the name it will be considered a Managed Service Account. Any password passed in the credential object will be ignored. Initially we used a NULL password in the PSCredential objects but we received an error indicating NULL passwords where not allowed. To get around that error we configured a fake password string to be used when creating the credential objects.
# Generate a fake password string to prevent NULL password errors during execution
$password = ('temppassword' | ConvertTo-SecureString -AsPlainText -Force)
# Create PSCredential objects for use in SQL setup resource
$SqlSvcCred = new-object -typename System.Management.Automation.PSCredential -argumentlist $SqlSvcAccount,$password
$SqlAgtCred = new-object -typename System.Management.Automation.PSCredential -argumentlist $SqlAgtAccount,$passwordWindows Cluster Resources
The Windows clustering feature is added to all nodes. The primary replica will create the cluster based on the cluster name and IP address provided in the configuration data. Each of the secondary replicas will wait for the cluster to be created then attempt to join as a secondary node.
WindowsFeature AddFailoverFeature
{
Ensure = 'Present'
Name = 'Failover-clustering'
}
WindowsFeature AddRemoteServerAdministrationToolsClusteringPowerShellFeature
{
Ensure = 'Present'
Name = 'RSAT-Clustering-PowerShell'
DependsOn = '[WindowsFeature]AddFailoverFeature'
}
WindowsFeature AddRemoteServerAdministrationToolsClusteringCmdInterfaceFeature
{
Ensure = 'Present'
Name = 'RSAT-Clustering-CmdInterface'
DependsOn = '[WindowsFeature]AddRemoteServerAdministrationToolsClusteringPowerShellFeature'
}
if ($Node.SqlAGRole -eq 'PrimaryReplica')
{
xCluster CreateCluster
{
Name = $Node.ClusterName
StaticIPAddress = $Node.ClusterIPAddress
DomainAdministratorCredential = $ActiveDirectoryAdministratorCredential
DependsOn = '[WindowsFeature]AddRemoteServerAdministrationToolsClusteringCmdInterfaceFeature'
}
xClusterQuorum SetQuorumToNodeAndFileShareMajority
{
IsSingleInstance = 'Yes'
Type = 'NodeAndFileShareMajority'
Resource = $Node.FileShareWitness
DependsOn = '[xCluster]JoinSecondNodeToCluster'
}
}
if ($Node.SqlAGRole -eq 'SecondaryReplica')
{
xWaitForCluster WaitForClusterCreation
{
Name = $Node.ClusterName
RetryIntervalSec = 10
RetryCount = 60
DependsOn = '[WindowsFeature]AddRemoteServerAdministrationToolsClusteringCmdInterfaceFeature'
}
xCluster JoinSecondNodeToCluster
{
Name = $Node.ClusterName
StaticIPAddress = $Node.ClusterIPAddress
DomainAdministratorCredential = $ActiveDirectoryAdministratorCredential
DependsOn = '[xWaitForCluster]WaitForClusterCreation'
}
}SQL Server Resources
In the SQL setup resource we have defined custom directory paths for the user database, temp database, and log directories. You will need to update these fields to be relevant to your drive configuration.
SqlSetup 'InstallSqlInstance'
{
InstanceName = $Node.InstanceName
Features = $Node.Features
SQLCollation = 'SQL_Latin1_General_CP1_CI_AS'
SQLSysAdminAccounts = $Node.SQLSysAdminAccounts
InstallSharedDir = 'C:\Program Files\Microsoft SQL Server'
InstallSharedWOWDir = 'C:\Program Files (x86)\Microsoft SQL Server'
InstanceDir = 'C:\Program Files\Microsoft SQL Server'
SQLUserDBDir = 'G:\MSSQL\Data'
SQLUserDBLogDir = 'F:\MSSQL\Data'
SQLTempDBDir = 'T:\MSSQL\Data'
SQLTempDBLogDir = 'T:\MSSQL\Data'
SQLBackupDir = 'G:\SQLBackups'
SourcePath = $SqlSourcePath
SQLSvcAccount = $SqlSvcCred
AgtSvcAccount = $SqlAgtCred
UpdateEnabled = 'False'
ForceReboot = $false
PsDscRunAsCredential = $SqlAdministratorCredential
DependsOn = $SqlDependsOn
}After the SQL install we configure SQL user accounts and permissions in preparation for the Always On group. Next we use the SqlServerMemory resource to dynamically set the memory assigned to SQL based on the SQL Max Memory Calculator website.
SqlServerLogin Add_WindowsUserSqlSvc
{
Ensure = 'Present'
Name = $SqlSvcAccount
LoginType = 'WindowsUser'
ServerName = 'LocalHost'
InstanceName = $Node.InstanceName
PsDscRunAsCredential = $SqlAdministratorCredential
DependsOn = '[SqlSetup]InstallSqlInstance'
}
SqlServerLogin Add_WindowsUserSqlAgt
{
Ensure = 'Present'
Name = $SqlAgtAccount
LoginType = 'WindowsUser'
ServerName = 'LocalHost'
InstanceName = $Node.InstanceName
PsDscRunAsCredential = $SqlAdministratorCredential
DependsOn = '[SqlSetup]InstallSqlInstance'
}
SqlServerLogin Add_WindowsUserClusSvc
{
Ensure = 'Present'
Name = 'NT SERVICE\ClusSvc'
LoginType = 'WindowsUser'
ServerName = 'LocalHost'
InstanceName = $Node.InstanceName
PsDscRunAsCredential = $SqlAdministratorCredential
DependsOn = '[SqlSetup]InstallSqlInstance'
}
SqlServerPermission SQLConfigureServerPermissionSYSTEMSvc
{
Ensure = 'Present'
ServerName = 'LocalHost'
InstanceName = $Node.InstanceName
Principal = $SqlSvcAccount
Permission = 'AlterAnyAvailabilityGroup', 'ViewServerState', 'AlterAnyEndPoint', 'ConnectSql'
DependsOn = '[SqlServerLogin]Add_WindowsUserSqlSvc'
PsDscRunAsCredential = $SqlAdministratorCredential
}
SqlServerPermission SQLConfigureServerPermissionSYSTEMAgt
{
Ensure = 'Present'
ServerName = 'LocalHost'
InstanceName = $Node.InstanceName
Principal = $SqlAgtAccount
Permission = 'AlterAnyAvailabilityGroup', 'ViewServerState', 'AlterAnyEndPoint', 'ConnectSql'
DependsOn = '[SqlServerLogin]Add_WindowsUserSqlAgt'
PsDscRunAsCredential = $SqlAdministratorCredential
}
SqlServerPermission AddNTServiceClusSvcPermissions
{
Ensure = 'Present'
ServerName = 'LocalHost'
InstanceName = $Node.InstanceName
Principal = 'NT SERVICE\ClusSvc'
Permission = 'AlterAnyAvailabilityGroup', 'ViewServerState'
DependsOn = '[SqlServerLogin]Add_WindowsUserClusSvc'
PsDscRunAsCredential = $SqlAdministratorCredential
}
SqlServerRole Add_ServerRole_AdminSqlforBI
{
Ensure = 'Present'
ServerRoleName = 'sysadmin'
MembersToInclude = $SqlSvcAccount, $SqlAgtAccount
ServerName = 'LocalHost'
InstanceName = $Node.InstanceName
DependsOn = '[SqlServerLogin]Add_WindowsUserSqlSvc', '[SqlServerLogin]Add_WindowsUserSqlAgt'
PsDscRunAsCredential = $SqlAdministratorCredential
}
SqlServerMemory Set_SQLServerMaxMemory_ToAuto
{
Ensure = 'Present'
DynamicAlloc = $true
ServerName = 'LocalHost'
InstanceName = $Node.InstanceName
DependsOn = '[SqlSetup]InstallSqlInstance'
PsDscRunAsCredential = $SqlAdministratorCredential
}Windows Service Resources
When I first started deploying SQL with gMSA accounts I ran into an issue where the SQL service was unable to retrieve the gMSA account password when the service first attempted to start after a reboot. Based on this blog post from Wayne Sheffield I have included the following resources to reconfigure the service for delayed startup and set dependencies on the W32Time and netlogon services. This prevents the SQL service from attempting to start before the netlogon service which ensures your server will be able to retrieve the gMSA password when it attempts to start SQL.
# Configure service dependencies to prevent SQL service from trying to authenticate the gMSA accounts before the netlogon service has started.
Service SqlSvcDependencies
{
Name = 'MSSQLSERVER'
Ensure = 'Present'
Dependencies = 'W32Time','Netlogon'
DependsOn = '[SqlSetup]InstallSqlInstance'
PsDscRunAsCredential = $SqlAdministratorCredential
}
Registry SqlSvcDelayAutoStart
{
Key = 'HKLM:\SYSTEM\CurrentControlSet\Services\MSSQLSERVER'
Ensure = 'Present'
ValueName = 'DelayedAutoStart'
ValueType = 'Binary'
ValueData = '0x01'
Force = $true
DependsOn = '[Service]SqlSvcDependencies'
PsDscRunAsCredential = $SqlAdministratorCredential
}
Registry SqlAgtDelayAutoStart
{
Key = 'HKLM:\SYSTEM\CurrentControlSet\Services\sqlserveragent'
Ensure = 'Present'
ValueName = 'DelayedAutoStart'
ValueType = 'Binary'
ValueData = '0x01'
Force = $true
DependsOn = '[Service]SqlSvcDependencies'
PsDscRunAsCredential = $SqlAdministratorCredential
}SQL Always On Resources
Finally we get to the Always On Resources. We configure and enable an HADR endpoint on all nodes in the configuration. The primary replica node will create the Always On group and listener based on the configuration data. Each secondary node will wait for the availability group to become available then add itself as a replica.
SqlServerEndpoint HADREndpoint
{
EndPointName = 'HADR'
Ensure = 'Present'
Port = 5022
ServerName = $Node.NodeName
InstanceName = $Node.InstanceName
DependsOn = '[SqlSetup]InstallSqlInstance'
PsDscRunAsCredential = $SqlAdministratorCredential
}
SqlAlwaysOnService EnableHADR
{
Ensure = 'Present'
InstanceName = $Node.InstanceName
ServerName = $Node.NodeName
DependsOn = '[SqlServerEndpoint]HADREndpoint'
PsDscRunAsCredential = $SqlAdministratorCredential
}
if ( $Node.SqlAGRole -eq 'PrimaryReplica' )
{
SqlAG AddAG
{
Ensure = 'Present'
Name = $Node.AvailabilityGroupName
InstanceName = $Node.InstanceName
ServerName = $Node.NodeName
AvailabilityMode = 'SynchronousCommit'
FailoverMode = 'Automatic'
DependsOn = '[SqlAlwaysOnService]EnableHADR', '[SqlServerEndpoint]HADREndpoint', '[SqlServerPermission]AddNTServiceClusSvcPermissions'
PsDscRunAsCredential = $SqlAdministratorCredential
}
SqlAGListener AvailabilityGroupListener
{
Ensure = 'Present'
ServerName = $Node.NodeName
InstanceName = $Node.InstanceName
AvailabilityGroup = $Node.AvailabilityGroupName
Name = $Node.AvailabilityGroupName
IpAddress = $Node.AvailabilityGroupIPAddress
Port = 1433
DependsOn = '[SqlAG]AddAG'
PsDscRunAsCredential = $SqlAdministratorCredential
}
}
if ( $Node.SqlAGRole -eq 'SecondaryReplica' )
{
# Wait for SQL AG to be created on primary node before attempting to join secondary node
SqlWaitForAG SQLConfigureAGWait
{
Name = $Node.AvailabilityGroupName
RetryIntervalSec = 20
RetryCount = 30
PsDscRunAsCredential = $SqlAdministratorCredential
}
SqlAGReplica AddReplica
{
Ensure = 'Present'
Name = $Node.NodeName
AvailabilityGroupName = $Node.AvailabilityGroupName
ServerName = $Node.NodeName
InstanceName = $Node.InstanceName
AvailabilityMode = 'SynchronousCommit'
FailoverMode = 'Automatic'
PrimaryReplicaServerName = ( $AllNodes | Where-Object { $_.SqlAGRole -eq 'PrimaryReplica' } ).NodeName
PrimaryReplicaInstanceName = ( $AllNodes | Where-Object { $_.SqlAGRole -eq 'PrimaryReplica' } ).InstanceName
DependsOn = '[SqlAlwaysOnService]EnableHADR', '[SqlWaitForAG]SQLConfigureAGWait'
PsDscRunAsCredential = $SqlAdministratorCredential
}Here is the full configuration.