SQL Always Availablity Groups

Welcome Forums DSC (Desired State Configuration) SQL Always Availablity Groups

Viewing 0 reply threads
  • Author
    Posts
    • #201600
      Participant
      Topics: 1
      Replies: 0
      Points: 13
      Rank: Member

      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 [email protected](
      
              @{
      
                  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 
      
      }

       

       

Viewing 0 reply threads
  • The topic ‘SQL Always Availablity Groups’ is closed to new replies.