Hi,
I am setting DSC up for the first time and am attempting to setup a 3 node SQL server cluster with a number of AAGs spread across the 3 nodes. Each of the AAGs primary nodes are assigned to per-determined nodes.
I am having trouble generating the MOF file to including the AAG that corospsonds to the primary replica and the secondary replica that corosponds to the correct AAG. I have attached my configuration below. Would much apprciated if anyone could let me know if I am heading in the right direction or have gone about this the completly wrong way.
Thanks
@{
AllNodes = @(
@{
NodeName = '*'
Services = @(
@{
Name = 'FailoverClustering'
ServiceName = 'Failover-clustering'
Ensure = 'Present'
},
@{
Name = 'RSATClusteringPS'
ServiceName = 'RSAT-Clustering-PowerShell'
Ensure = 'Present'
DependsOn = '[WindowsFeature]FailoverClustering'
},
@{
Name = 'RSATClusteringCMD'
ServiceName = 'RSAT-Clustering-CmdInterface'
Ensure = 'Present'
DependsOn = '[WindowsFeature]RSATClusteringPS'
},
@{
Name = 'RSATClusteringMgt'
ServiceName ='RSAT-Clustering-Mgmt'
Ensure = 'Present'
DependsOn = '[WindowsFeature]RSATClusteringCMD'
},
@{
Name = 'RSATADTools'
ServiceName = 'RSAT-AD-Tools'
Ensure = 'Present'
}
)
},
@{
NodeName = 'db81.domain.local.au'
NodeType = 'Primary'
CertificateFile = '\\ms01\DSC Pull Server$\Client Certificates\DB81.cer'
IpAddress = 'x.x.x.x'
DataDriveID = '6000C29446A5EA71DBFBF4B977272861'
LogDriveID = '6000C29D63F77CB77ACF254395532E17'
BackupDriveID = '6000C2995C61DE00F7C99419C8295A4D'
},
@{
NodeName = 'db82.domain.local.au'
NodeType = 'Additional'
CertificateFile = '\\ms01\DSC Pull Server$\Client Certificates\DB82.cer'
IpAddress = 'x.x.x.x'
DataDriveID = '6000C29FB1F08C063F13546A62348670'
LogDriveID = '6000C2971AA5F2B27F271C29CCF26C14'
BackupDriveID = '6000C29A913211AD53E31FDCE9385A99'
},
@{
NodeName = 'db83.domain.local.au'
NodeType = 'Additional'
CertificateFile = '\\ms01\DSC Pull Server$\Client Certificates\DB83.cer'
IpAddress = 'x.x.x.x'
DataDriveID = '6000C294386D6E76A937DDB4BFA8A25F'
LogDriveID = '6000C29C8CABEDFBD85C95FEC79078DF'
BackupDriveID = '6000C29C3DBBD3D7843B8D5015C49BF4'
}
)
FailoverCluster = @{
ClusterName = 'CL02'
StaticIPAddress ='x.x.x.x'
}
DiskDrive = @{
DataDrive = 'G'
DataFSLabel = 'Data Drive'
LogDrive = 'L'
LogFSLabel = 'Log Drive'
BackupDrive = 'R'
BackupFSLabel = 'Backup Drive'
}
SQLConf = @{
InstanceName = 'CL02'
Features = 'SQLENGINE'
SQLCollation = "SQL_Latin1_General_CP1_CI_AS"
SQLSvcAccount = "domain\svcSQL_DBEngine$"
AgtSvcAccount = "domain\svcSQL_Agent$"
SQLSysAdminAccounts = 'domain\RBA_domain_SQL-ADMINISTRATORS'
InstallSharedDir = "$env:ProgramFiles\Microsoft SQL Server\"
InstallSharedWOWDir = "${env:CommonProgramFiles(x86)}\Microsoft SQL Server\"
InstanceDir = "$env:ProgramFiles\Microsoft SQL Server\"
InstallSQLDataDir = 'G:\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Data'
SQLUserDBDir = 'G:\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Data'
SQLUserDBLogDir = 'L:\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Data'
SQLTempDBDir = 'G:\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Data'
SQLTempDBLogDir = 'L:\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Data'
SQLBackupDir = 'R:\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup'
HADREndpointName = 'AAGEndpoint'
HADREndpointPort = 5022
SQLPort = 1434
}
SQLAAG =@(
@{
AvailabilityGroupName = 'CL02-AG1'
AvailabilityGroupListenIP = 'x.x.x.x'
AvailabilityGroupListenName = 'CL02-AG1L'
AGPort = 1433
PrimaryReplica = 'db81.domain.local.au'
SecondayReplica = @('B82.domain.local.au', 'db83.domain.local.au')
},
@{
AvailabilityGroupName = 'CL02-AG2'
AvailabilityGroupListenIP = 'x.x.x.x'
AvailabilityGroupListenName = 'CL02-AG2L'
AGPort = 1433
PrimaryReplica = 'db82.domain.local.au'
SecondayReplica = @('db81.domain.local.au', 'db83.domain.local.au')
},
@{
AvailabilityGroupName = 'CL02-AG3'
AvailabilityGroupListenIP = 'x.x.x.x'
AvailabilityGroupListenName = 'CL02-AG3L'
AGPort = 1433
PrimaryReplica = 'db83.domain.local.au'
SecondayReplica = @('db81.domain.local.au', 'db82.domain.local.au')
}
)
# Firewall Rules Configuration
FirewallRules = @(
@{
Ensure = 'Present'
Name = "SQL Server Replication (TCP-In)"
DisplayName = "SQL Server Replication (TCP-In)"
Description = 'Allows inbound Microsoft SQL connections'
Group = 'SQLServer'
Enabled = 'True'
Action = 'Allow'
Direction = 'Inbound'
LocalPort = 5022
Protocol = 'TCP'
},
@{
Ensure = 'Present'
Name = "SQL Server Data (TCP-In)"
DisplayName = "SQL Server Data (TCP-In)"
Description = 'Allows inbound Microsoft SQL connections'
Group = 'SQLServer'
Enabled = 'True'
Action = 'Allow'
Direction = 'Inbound'
LocalPort = 1433
Protocol = 'TCP'
},
@{
Ensure = 'Present'
Name = "SQL Server Browser (UDP-In)"
DisplayName = "SQL Server Browser (UDP-In)"
Description = 'Allows inbound Microsft SQL browser connections'
Group = 'SQLServer'
Enabled = 'True'
Action = 'Allow'
Direction = 'Inbound'
LocalPort = 1433
Protocol = 'TCP'
}
)
}
Configuration SQLInstall {
param (
[Parameter(Mandatory = $true)]
[pscredential] $ADUserCreds,
[Parameter(Mandatory)]
[pscredential]$svcSQLDBCreds,
[Parameter(Mandatory)]
[pscredential]$svcSQLAgentCreds
)
# Call Client ConfiurationData Environmentals
$DiskDrive = $ConfigurationData.DiskDrive
$SQLConf = $ConfigurationData.SQLConf
$SQLAAG = $ConfigurationData.SQLAAG
# Import DSC Modules
Import-DscResource -ModuleName 'PSDesiredStateConfiguration'
Import-DscResource -ModuleName 'xPSDesiredStateConfiguration'
Import-DscResource -ModuleName xfailovercluster
Import-DscResource -ModuleName StorageDsc
Import-DscResource -ModuleName sqlserverdsc
Import-DscResource -ModuleName networkingdsc
Import-DscResource -ModuleName activedirectorydsc
Node $AllNodes.NodeName
{
# Enable required features
foreach ($Service in $Node.Services){
WindowsFeature $Service.Name
{
Ensure = $Service.Ensure
Name = $Service.ServiceName
DependsOn = $Service.DependsOn
}
}
# Check for primary node for cluster creation
if ($Node.NodeType -eq 'Primary'){
# Create new Cluster
xCluster AddCluster {
Name = $ConfigurationData.FailoverCluster.ClusterName
StaticIPAddress = $ConfigurationData.FailoverCluster.StaticIPAddress
DomainAdministratorCredential = $ADUserCreds
PsDscRunAsCredential = $ADUserCreds
DependsOn = '[WindowsFeature]RSATClusteringCMD'
}
# Set Cluster Quorum Settings
xClusterQuorum SetQuorum{
IsSingleInstance = 'Yes'
Type = 'NodeMajority'
DependsOn = '[xCluster]AddCluster'
}
# Add create computer objects permission to Cluster Name Object (CNO)
ADObjectPermissionEntry AddCreateComputerAccountPermissionCNO{
Ensure = 'Present'
Path = 'OU=Servers,OU=Domain,DC=Domain,DC=local,DC=au'
IdentityReference = "Domain\" + $ConfigurationData.FailoverCluster.ClusterName + '$'
AccessControlType = 'Allow'
ActiveDirectoryRights = 'CreateChild', 'DeleteChild'
ObjectType = 'bf967a86-0de6-11d0-a285-00aa003049e2' # All computer objects
ActiveDirectorySecurityInheritance = 'All'
InheritedObjectType = '00000000-0000-0000-0000-000000000000'
DependsOn = '[xClusterQuorum]SetQuorum'
PsDscRunAsCredential = $ADUserCreds
}
$DependsOnVariable = "[xCluster]AddCluster"
} else { # Secondary Node
# Wait for Cluster creation
xWaitForCluster WaitForCluster {
Name = $ConfigurationData.FailoverCluster.ClusterName
RetryIntervalSec = 10
RetryCount = 60
DependsOn = '[WindowsFeature]RSATClusteringCMD'
}
# Create new Cluster
xCluster JoinCluster {
Name = $ConfigurationData.FailoverCluster.ClusterName
StaticIPAddress = $ConfigurationData.FailoverCluster.StaticIPAddress
DomainAdministratorCredential = $ADUserCreds
PsDscRunAsCredential = $ADUserCreds
DependsOn = '[xWaitForCluster]WaitForCluster'
}
$DependsOnVariable = "[xWaitForCluster]WaitForCluster"
}# End Else
# Set initialize disk and create volumes
WaitForDisk DataDrive {
DiskId = $Node.DataDriveID
DiskIdType = 'UniqueId'
RetryIntervalSec = 60
RetryCount = 60
DependsOn = $DependsOnVariable
}
Disk DataDrive {
DiskId = $Node.DataDriveID
DiskIdType = 'UniqueId'
DriveLetter = $DiskDrive.DataDrive
FSLabel = $DiskDrive.DataFSLabel
DependsOn = '[WaitForDisk]DataDrive'
}
WaitForDisk LogDrive {
DiskId = $Node.LogDriveID
DiskIdType = 'UniqueId'
RetryIntervalSec = 60
RetryCount = 60
DependsOn = $DependsOnVariable
}
Disk LogDrive {
DiskId = $Node.LogDriveID
DiskIdType = 'UniqueId'
DriveLetter = $DiskDrive.LogDrive
FSLabel = $DiskDrive.LogFSLabel
DependsOn = '[WaitForDisk]LogDrive'
}
WaitForDisk BackupDrive {
DiskId = $Node.BackupDriveID
DiskIdType = 'UniqueId'
RetryIntervalSec = 60
RetryCount = 60
DependsOn = $DependsOnVariable
}
Disk BackupDrive {
DiskId = $Node.BackupDriveID
DiskIdType = 'UniqueId'
DriveLetter = $DiskDrive.BackupDrive
FSLabel = $DiskDrive.BackupFSLabel
DependsOn = '[WaitForDisk]BackupDrive'
}
# Create SQL Directories
File CreateDataDir {
Ensure = 'Present'
DestinationPath = $SQLConf.InstallSQLDataDir
Type = 'Directory'
DependsOn = '[Disk]DataDrive'
}
File CreateLogDir {
Ensure = 'Present'
DestinationPath = $SQLConf.SQLUserDBLogDir
Type = 'Directory'
DependsOn = '[Disk]LogDrive'
}
File CreateBackupDir {
Ensure = 'Present'
DestinationPath = $SQLConf.SQLBackupDir
Type = 'Directory'
DependsOn = '[Disk]BackupDrive'
}
# Check and set SQL Managed Service Account
Script SetManagedServiceAccountAgent {
SetScript = {
Install-ADServiceAccount -Identity svcSQL_Agent
}
TestScript = {
Test-ADServiceAccount -Identity svcSQL_Agent
}
GetScript = {
Test-ADServiceAccount -Identity svcSQL_Agent
}
}
Script SetManagedServiceAccountDBEngine {
SetScript = {
Install-ADServiceAccount -Identity svcSQL_DBEngine
}
TestScript = {
Test-ADServiceAccount -Identity svcSQL_DBEngine
}
GetScript = {
Test-ADServiceAccount -Identity svcSQL_DBEngine
}
}
#Install SQL Server
SqlSetup BaseInstall {
InstanceName = $SQLConf.InstanceName
Action = 'Install'
SqlSvcStartupType = 'Automatic'
SQLSvcAccount = $svcSQLDBCreds
AgtSvcAccount = $svcSQLAgentCreds
SQLSysAdminAccounts = $SQLConf.SQLSysAdminAccounts
Features = $SQLConf.Features
InstallSharedDir = $SQLConf.InstallSharedDir
InstallSharedWOWDir = $SQLConf.InstallSharedWOWDir
InstanceDir = $SQLConf.InstanceDir
InstallSQLDataDir = $SQLConf.InstallSQLDataDir
SQLUserDBDir = $SQLConf.SQLUserDBDir
SQLUserDBLogDir = $SQLConf.SQLUserDBLogDir
SQLBackupDir = $SQLConf.SQLBackupDir
SQLTempDBDir = $SQLConf.SQLTempDBDir
SQLTempDBLogDir = $SQLConf.SQLTempDBLogDir
DependsOn = '[Script]SetManagedServiceAccountAgent', '[Script]SetManagedServiceAccountDBEngine', '[Disk]BackupDrive', '[Disk]LogDrive', '[Disk]DataDrive', $DependsOnVariable
PsDscRunAsCredential = $ADUserCreds
SourcePath = '\\ms01\admins\Software Replicated\Installation Software\Microsoft\SQL\SQL Server 2017\Installs'
SuppressReboot = $true
UpdateEnabled = $true
UpdateSource = '\\ms01\admins\Software Replicated\Installation Software\Microsoft\SQL\SQL Server 2017\Updates'
}
SqlServerNetwork SetTCP {
InstanceName = $SQLConf.InstanceName
ProtocolName = 'TCP'
IsEnabled = $true
TcpDynamicPort = $false
TcpPort = $SQLConf.SQLPort
RestartService = $true
DependsOn = '[SqlSetup]BaseInstall'
}
SqlServerLogin AddNTServiceClusSvc {
Ensure = 'Present'
Name = 'NT SERVICE\ClusSvc'
ServerName = $Node.NodeName
InstanceName = $SQLConf.InstanceName
DependsOn = '[SqlSetup]BaseInstall'
}
SqlServerPermission AddNTServiceClusSvcPermission {
Ensure = 'Present'
ServerName = $Node.NodeName
InstanceName = $SQLConf.InstanceName
Principal = 'NT SERVICE\ClusSvc'
Permission = 'AlterAnyAvailabilityGroup', 'ViewServerState'
DependsOn = '[SqlServerLogin]AddNTServiceClusSvc'
}
SqlAlwaysOnService EnableAAG {
ServerName = $Node.NodeName
InstanceName = $SQLConf.InstanceName
Ensure = 'Present'
DependsOn = '[SqlSetup]BaseInstall'
}
SqlServerEndpoint $SQLConf.HADREndpointName {
EndpointName = $SQLConf.HADREndpointName
Port = $SQLConf.HADREndpointPort
Ensure = 'Present'
ServerName = $Node.NodeName
InstanceName = $SQLConf.InstanceName
DependsOn = '[SqlAlwaysOnService]EnableAAG'
}
SqlServerEndpointState $SQLConf.HADREndpointName {
Name = $SQLConf.HADREndpointName
InstanceName = $SQLConf.InstanceName
ServerName = $Node.NodeName
State = 'Started'
DependsOn = "[SqlServerEndpoint]$($SQLConf.HADREndpointName)"
}
# Create Always on Availabilty Groups
ForEach ($AAG in $SQLAAG){
if ($AAG.PrimaryReplica -eq $Node.NodeName){
SqlAG $AAG.AvailabilityGroupName {
Name = $AAG.AvailabilityGroupName
Ensure = 'Present'
InstanceName = $SQLConf.InstanceName
ServerName = $Node.NodeName
FailoverMode = 'Automatic'
AvailabilityMode = 'SynchronousCommit'
# ConnectionModeInPrimaryRole = 'AllowAllConnections'
# ConnectionModeInSecondaryRole = 'AllowNoConnections'
# BasicAvailabilityGroup = $false
# DtcSupportEnabled = $true
PsDscRunAsCredential = $ADUserCreds
DependsOn = "[SqlServerEndpointState]$($SQLConf.HADREndpointName)", '[SqlServerPermission]AddNTServiceClusSvcPermission'
}
SqlAGListener $AAG.AvailabilityGroupListenName {
InstanceName = $SQLConf.InstanceName
AvailabilityGroup = $AAG.AvailabilityGroupName
ServerName = $Node.NodeName
Name = $AAG.AvailabilityGroupListenName
IpAddress = $AAG.AvailabilityGroupListenIP
Port = $AAG.AGPort
Ensure = 'Present'
DependsOn = "[SqlAG]$($AAG.AvailabilityGroupName)"
PsDscRunAsCredential = $ADUserCreds
}
}
if ($AAG.SecondayReplica -eq $Node.NodeName){
SqlWaitForAG "WaitFor$($AAG.AvailabilityGroupName)" {
Name = $AAG.AvailabilityGroupName
RetryIntervalSec = 20
RetryCount = 70
DependsOn = "[SqlServerEndpointState]$($SQLConf.HADREndpointName)"
}
SqlAGReplica $AAG.AvailabilityGroupName {
Ensure = 'Present'
Name = $AAG.PrimaryReplica
AvailabilityGroupName = $AAG.AvailabilityGroupName
ServerName = $Node.Where{$_.NodeType -eq 'Primary'}.NodeName
InstanceName = $SQLConf.InstanceName
PrimaryReplicaInstanceName = $SQLConf.InstanceName
PrimaryReplicaServerName = $AAG.PrimaryReplica
FailoverMode = 'Automatic'
AvailabilityMode = 'SynchronousCommit'
DependsOn = "[SqlWaitForAG]WaitFor$($AAG.AvailabilityGroupName)"
PsDscRunAsCredential = $ADUserCreds
}
}
}
# Add firewall rules
foreach ($Rule in $Node.FirewallRulles){
Firewall SQLServerBrowser {
Ensure = $Rule.Ensure
Name = $Rule.Name
DisplayName = $Rule.DisplayName
Description = $Rule.Description
Group = $Rule.Group
Enabled = $Rule.Enabled
Action = $Rule.Action
Direction = $Rule.Direction
LocalPort = $Rule.LocalPort
Protocol = $Rule.Protocol
}
}
}# End Primary Node
}