DSCResources/MSFT_xSQLAlwaysOnAGDBMembership/MSFT_xSQLAlwaysOnAGDBMembership.psm1
|
Import-Module -Name (Join-Path -Path (Split-Path (Split-Path $PSScriptRoot -Parent) -Parent) ` -ChildPath 'xSQLHelper.psm1') ` -Force Import-Module -Name (Join-Path -Path (Split-Path -Path $PSScriptRoot -Parent) ` -ChildPath 'CommonResourceHelper.psm1') $script:localizedData = Get-LocalizedData -ResourceName 'MSFT_xSQLAlwaysOnAGDBMembership' <# .SYNOPSIS Gets the database membership of the specified availability group. .PARAMETER DatabaseName The name of the database(s) to add to the availability group. This accepts wildcards. .PARAMETER SQLServer Hostname of the SQL Server where the primary replica of the availability group lives. If the availability group is not currently on this server, the resource will attempt to connect to the server where the primary replica lives. .PARAMETER SQLInstanceName Name of the SQL instance where the primary replica of the availability group lives. If the availability group is not currently on this instance, the resource will attempt to connect to the instance where the primary replica lives. .PARAMETER AvailabilityGroupName The name of the availability group in which to manage the database membership(s). .PARAMETER BackupPath The path used to seed the availability group replicas. This should be a path that is accessible by all of the replicas. #> function Get-TargetResource { [CmdletBinding()] [OutputType([System.Collections.Hashtable])] param ( [Parameter(Mandatory = $true)] [System.String[]] $DatabaseName, [Parameter(Mandatory = $true)] [System.String] $SQLServer, [Parameter(Mandatory = $true)] [System.String] $SQLInstanceName, [Parameter(Mandatory = $true)] [System.String] $AvailabilityGroupName, [Parameter(Mandatory = $true)] [System.String] $BackupPath ) # Create an object that reflects the current configuration $currentConfiguration = @{ DatabaseName = @() SQLServer = $SQLServer SQLInstanceName = $SQLInstanceName AvailabilityGroupName = '' BackupPath = '' Ensure = '' Force = $false MatchDatabaseOwner = $false } # Connect to the instance $serverObject = Connect-SQL -SQLServer $SQLServer -SQLInstanceName $SQLInstanceName # Get the Availability group object $availabilityGroup = $serverObject.AvailabilityGroups[$AvailabilityGroupName] if ( $availabilityGroup ) { $currentConfiguration.AvailabilityGroupName = $AvailabilityGroupName # Get the databases in the availability group $currentConfiguration.DatabaseName = $availabilityGroup.AvailabilityDatabases | Select-Object -ExpandProperty Name } else { Write-Verbose -Message ($script:localizedData.AvailabilityGroupDoesNotExist -f $AvailabilityGroupName) } return $currentConfiguration } <# .SYNOPSIS Adds or removes databases to the specified availability group. .PARAMETER DatabaseName The name of the database(s) to add to the availability group. This accepts wildcards. .PARAMETER SQLServer Hostname of the SQL Server where the primary replica of the availability group lives. If the availability group is not currently on this server, the resource will attempt to connect to the server where the primary replica lives. .PARAMETER SQLInstanceName Name of the SQL instance where the primary replica of the availability group lives. If the availability group is not currently on this instance, the resource will attempt to connect to the instance where the primary replica lives. .PARAMETER AvailabilityGroupName The name of the availability group in which to manage the database membership(s). .PARAMETER BackupPath The path used to seed the availability group replicas. This should be a path that is accessible by all of the replicas. .PARAMETER Ensure Specifies the membership of the database(s) in the availability group. The options are: - Present: The defined database(s) are added to the availability group. All other databases that may be a member of the availability group are ignored. - Absent: The defined database(s) are removed from the availability group. All other databases that may be a member of the availability group are ignored. The default is 'Present'. .PARAMETER Force When used with "Ensure = 'Present'" it ensures the specified database(s) are the only databases that are a member of the specified Availability Group. This parameter is ignored when 'Ensure' is 'Absent'. .PARAMETER MatchDatabaseOwner If set to $true, this ensures the database owner of the database on the primary replica is the owner of the database on all secondary replicas. This requires the database owner is available as a login on all replicas and that the PSDscRunAsCredential has impersonate permissions. If set to $false, the owner of the database will be the PSDscRunAsCredential. The default is '$true'. #> function Set-TargetResource { [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [System.String[]] $DatabaseName, [Parameter(Mandatory = $true)] [System.String] $SQLServer, [Parameter(Mandatory = $true)] [System.String] $SQLInstanceName, [Parameter(Mandatory = $true)] [System.String] $AvailabilityGroupName, [Parameter(Mandatory = $true)] [System.String] $BackupPath, [Parameter()] [ValidateSet('Present', 'Absent')] [System.String] $Ensure = 'Present', [Parameter()] [Boolean] $Force, [Parameter()] [Boolean] $MatchDatabaseOwner ) Import-SQLPSModule # Connect to the defined instance $serverObject = Connect-SQL -SQLServer $SQLServer -SQLInstanceName $SQLInstanceName # Get the Availability Group $availabilityGroup = $serverObject.AvailabilityGroups[$AvailabilityGroupName] # Make sure we're communicating with the primary replica in order to make changes to the replica $primaryServerObject = Get-PrimaryReplicaServerObject -ServerObject $serverObject -AvailabilityGroup $availabilityGroup $getDatabasesToAddToAvailabilityGroupParameters = @{ DatabaseName = $DatabaseName Ensure = $Ensure ServerObject = $primaryServerObject AvailabilityGroup = $availabilityGroup } $databasesToAddToAvailabilityGroup = Get-DatabasesToAddToAvailabilityGroup @getDatabasesToAddToAvailabilityGroupParameters $getDatabasesToRemoveFromAvailabilityGroupParameters = @{ DatabaseName = $DatabaseName Ensure = $Ensure Force = $Force ServerObject = $primaryServerObject AvailabilityGroup = $availabilityGroup } $databasesToRemoveFromAvailabilityGroup = Get-DatabasesToRemoveFromAvailabilityGroup @getDatabasesToRemoveFromAvailabilityGroupParameters # Create a hash table to store the databases that failed to be added to the Availability Group $databasesToAddFailures = @{} # Create a hash table to store the databases that failed to be added to the Availability Group $databasesToRemoveFailures = @{} if ( $databasesToAddToAvailabilityGroup.Count -gt 0 ) { Write-Verbose -Message ($script:localizedData.AddingDatabasesToAvailabilityGroup -f $AvailabilityGroupName, ( $databasesToAddToAvailabilityGroup -join ', ' )) # Get only the secondary replicas. Some tests do not need to be performed on the primary replica $secondaryReplicas = $availabilityGroup.AvailabilityReplicas | Where-Object -FilterScript { $_.Role -ne 'Primary' } # Ensure the appropriate permissions are in place on all the replicas if ( $MatchDatabaseOwner ) { $impersonatePermissionsStatus = @{} foreach ( $availabilityGroupReplica in $secondaryReplicas ) { $currentAvailabilityGroupReplicaServerObject = Connect-SQL -SQLServer $availabilityGroupReplica.Name $impersonatePermissionsStatus.Add( $availabilityGroupReplica.Name, ( Test-ImpersonatePermissions -ServerObject $currentAvailabilityGroupReplicaServerObject ) ) } if ( $impersonatePermissionsStatus.Values -contains $false ) { $impersonatePermissionsMissingParameters = @( [System.Security.Principal.WindowsIdentity]::GetCurrent().Name, ( ( $impersonatePermissionsStatus.GetEnumerator() | Where-Object -FilterScript { -not $_.Value } | Select-Object -ExpandProperty Key ) -join ', ' ) ) throw ($script:localizedData.ImpersonatePermissionsMissing -f $impersonatePermissionsMissingParameters ) } } foreach ( $databaseToAddToAvailabilityGroup in $databasesToAddToAvailabilityGroup ) { $databaseObject = $primaryServerObject.Databases[$databaseToAddToAvailabilityGroup] <# Verify the prerequisites prior to joining the database to the availability group https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/prereqs-restrictions-recommendations-always-on-availability#a-nameprerequisitesfordbsa-availability-database-prerequisites-and-restrictions #> # Create a hash table to store prerequisite check failures $prerequisiteCheckFailures = @() $prerequisiteChecks = @{ RecoveryModel = 'Full' ReadOnly = $false UserAccess = 'Multiple' AutoClose = $false AvailabilityGroupName = '' IsMirroringEnabled = $false } foreach ( $prerequisiteCheck in $prerequisiteChecks.GetEnumerator() ) { if ( $databaseObject.($prerequisiteCheck.Key) -ne $prerequisiteCheck.Value ) { $prerequisiteCheckFailures += "$($prerequisiteCheck.Key) is not $($prerequisiteCheck.Value)." } } # Cannot be a system database if ( $databaseObject.ID -le 4 ) { $prerequisiteCheckFailures += 'The database cannot be a system database.' } # If FILESTREAM is enabled, ensure FILESTREAM is enabled on all replica instances if ( ( -not [System.String]::IsNullOrEmpty($databaseObject.DefaultFileStreamFileGroup) ) ` -or ( -not [System.String]::IsNullOrEmpty($databaseObject.FilestreamDirectoryName) ) ` -or ( $databaseObject.FilestreamNonTransactedAccess -ne 'Off' ) ) { $availabilityReplicaFilestreamLevel = @{} foreach ( $availabilityGroupReplica in $secondaryReplicas ) { $connectSqlParameters = Split-FullSQLInstanceName -FullSQLInstanceName $availabilityGroupReplica.Name $currentAvailabilityGroupReplicaServerObject = Connect-SQL @connectSqlParameters $availabilityReplicaFilestreamLevel.Add($availabilityGroupReplica.Name, $currentAvailabilityGroupReplicaServerObject.FilestreamLevel) } if ( $availabilityReplicaFilestreamLevel.Values -contains 'Disabled' ) { $availabilityReplicaFilestreamLevelDisabled = $availabilityReplicaFilestreamLevel.GetEnumerator() | Where-Object { $_.Value -eq 'Disabled' } | Select-Object -ExpandProperty Key $prerequisiteCheckFailures += ( 'Filestream is disabled on the following instances: {0}' -f ( $availabilityReplicaFilestreamLevelDisabled -join ', ' ) ) } } # If the database is contained, ensure contained database authentication is enabled on all replica instances if ( $databaseObject.ContainmentType -ne 'None' ) { $availabilityReplicaContainmentEnabled = @{} foreach ( $availabilityGroupReplica in $secondaryReplicas ) { $connectSqlParameters = Split-FullSQLInstanceName -FullSQLInstanceName $availabilityGroupReplica.Name $currentAvailabilityGroupReplicaServerObject = Connect-SQL @connectSqlParameters $availabilityReplicaContainmentEnabled.Add($availabilityGroupReplica.Name, $currentAvailabilityGroupReplicaServerObject.Configuration.ContainmentEnabled.ConfigValue) } if ( $availabilityReplicaContainmentEnabled.Values -notcontains 'None' ) { $availabilityReplicaContainmentNotEnabled = $availabilityReplicaContainmentEnabled.GetEnumerator() | Where-Object { $_.Value -eq 'None' } | Select-Object -ExpandProperty Key $prerequisiteCheckFailures += ( 'Contained Database Authentication is not enabled on the following instances: {0}' -f ( $availabilityReplicaContainmentNotEnabled -join ', ' ) ) } } # Ensure the data and log file paths exist on all replicas $databaseFileDirectories = @() $databaseFileDirectories += $databaseObject.FileGroups.Files.FileName | ForEach-Object { Split-Path -Path $_ -Parent } $databaseFileDirectories += $databaseObject.LogFiles.FileName | ForEach-Object { Split-Path -Path $_ -Parent } $databaseFileDirectories = $databaseFileDirectories | Select-Object -Unique $availabilityReplicaMissingDirectories = @{} foreach ( $availabilityGroupReplica in $secondaryReplicas ) { $connectSqlParameters = Split-FullSQLInstanceName -FullSQLInstanceName $availabilityGroupReplica.Name $currentAvailabilityGroupReplicaServerObject = Connect-SQL @connectSqlParameters $missingDirectories = @() foreach ( $databaseFileDirectory in $databaseFileDirectories ) { $fileExistsQuery = "EXEC master.dbo.xp_fileexist '$databaseFileDirectory'" $fileExistsResult = Invoke-Query -SQLServer $currentAvailabilityGroupReplicaServerObject.NetName -SQLInstanceName $currentAvailabilityGroupReplicaServerObject.ServiceName -Database master -Query $fileExistsQuery -WithResults if ( $fileExistsResult.Tables.Rows.'File is a Directory' -ne 1 ) { $missingDirectories += $databaseFileDirectory } } if ( $missingDirectories.Count -gt 0 ) { $availabilityReplicaMissingDirectories.Add($availabilityGroupReplica, ( $missingDirectories -join ', ' )) } } if ( $availabilityReplicaMissingDirectories.Count -gt 0 ) { foreach ( $availabilityReplicaMissingDirectory in $availabilityReplicaMissingDirectories.GetEnumerator() ) { $prerequisiteCheckFailures += "The instance '$($availabilityReplicaMissingDirectory.Key.Name)' is missing the following directories: $($availabilityReplicaMissingDirectory.Value)" } } # If the database is TDE'd, ensure the certificate or asymmetric key is installed on all replicas if ( $databaseObject.EncryptionEnabled ) { $databaseCertificateThumbprint = [System.BitConverter]::ToString($databaseObject.DatabaseEncryptionKey.Thumbprint) $databaseCertificateName = $databaseObject.DatabaseEncryptionKey.EncryptorName $availabilityReplicaMissingCertificates = @{} foreach ( $availabilityGroupReplica in $secondaryReplicas ) { $connectSqlParameters = Split-FullSQLInstanceName -FullSQLInstanceName $availabilityGroupReplica.Name $currentAvailabilityGroupReplicaServerObject = Connect-SQL @connectSqlParameters [System.Array]$installedCertificateThumbprints = $currentAvailabilityGroupReplicaServerObject.Databases['master'].Certificates | ForEach-Object { [System.BitConverter]::ToString($_.Thumbprint) } if ( $installedCertificateThumbprints -notcontains $databaseCertificateThumbprint ) { $availabilityReplicaMissingCertificates.Add($availabilityGroupReplica, $databaseCertificateName) } } if ( $availabilityReplicaMissingCertificates.Count -gt 0 ) { foreach ( $availabilityReplicaMissingCertificate in $availabilityReplicaMissingCertificates.GetEnumerator() ) { $prerequisiteCheckFailures += "The instance '$($availabilityReplicaMissingCertificate.Key.Name)' is missing the following certificates: $($availabilityReplicaMissingCertificate.Value)" } } } if ( $prerequisiteCheckFailures.Count -eq 0 ) { $databaseFullBackupFile = Join-Path -Path $BackupPath -ChildPath "$($databaseObject.Name)_Full_$(Get-Date -Format 'yyyyMMddhhmmss').bak" $databaseLogBackupFile = Join-Path -Path $BackupPath -ChildPath "$($databaseObject.Name)_Log_$(Get-Date -Format 'yyyyMMddhhmmss').trn" # Build the backup parameters. If no backup was previously taken, a standard full will be taken. Otherwise a CopyOnly backup will be taken. $backupSqlDatabaseParameters = @{ DatabaseObject = $databaseObject BackupAction = 'Database' BackupFile = $databaseFullBackupFile ErrorAction = 'Stop' } # If no full backup was ever taken, do not take a backup with CopyOnly if ( $databaseObject.LastBackupDate -ne 0 ) { $backupSqlDatabaseParameters.Add('CopyOnly', $true) } try { Backup-SqlDatabase @backupSqlDatabaseParameters } catch { # Log the failure $databasesToAddFailures.Add($databaseToAddToAvailabilityGroup, $_.Exception) # Move on to the next database continue } # Create the parameters to perform a transaction log backup $backupSqlDatabaseLogParams = @{ DatabaseObject = $databaseObject BackupAction = 'Log' BackupFile = $databaseLogBackupFile ErrorAction = 'Stop' } try { Backup-SqlDatabase @backupSqlDatabaseLogParams } catch { # Log the failure $databasesToAddFailures.Add($databaseToAddToAvailabilityGroup, $_.Exception) # Move on to the next database continue } # Add the database to the availability group on the primary instance try { Add-SqlAvailabilityDatabase -InputObject $availabilityGroup -Database $databaseToAddToAvailabilityGroup } catch { # Log the failure $databasesToAddFailures.Add($databaseToAddToAvailabilityGroup, $_.Exception) # Move on to the next database continue } # Need to restore the database with a query in order to impersonate the correct login $restoreDatabaseQueryStringBuilder = New-Object -TypeName System.Text.StringBuilder if ( $MatchDatabaseOwner ) { $restoreDatabaseQueryStringBuilder.Append('EXECUTE AS LOGIN = ''') | Out-Null $restoreDatabaseQueryStringBuilder.Append($databaseObject.Owner) | Out-Null $restoreDatabaseQueryStringBuilder.AppendLine('''') | Out-Null } $restoreDatabaseQueryStringBuilder.Append('RESTORE DATABASE [') | Out-Null $restoreDatabaseQueryStringBuilder.Append($databaseToAddToAvailabilityGroup) | Out-Null $restoreDatabaseQueryStringBuilder.AppendLine(']') | Out-Null $restoreDatabaseQueryStringBuilder.Append('FROM DISK = ''') | Out-Null $restoreDatabaseQueryStringBuilder.Append($databaseFullBackupFile) | Out-Null $restoreDatabaseQueryStringBuilder.AppendLine('''') | Out-Null $restoreDatabaseQueryStringBuilder.Append('WITH NORECOVERY') | Out-Null $restoreDatabaseQueryString = $restoreDatabaseQueryStringBuilder.ToString() # Build the parameters to restore the transaction log $restoreSqlDatabaseLogParameters = @{ Database = $databaseToAddToAvailabilityGroup BackupFile = $databaseLogBackupFile RestoreAction = 'Log' NoRecovery = $true } try { foreach ( $availabilityGroupReplica in $secondaryReplicas ) { # Connect to the replica $connectSqlParameters = Split-FullSQLInstanceName -FullSQLInstanceName $availabilityGroupReplica.Name $currentAvailabilityGroupReplicaServerObject = Connect-SQL @connectSqlParameters $currentReplicaAvailabilityGroupObject = $currentAvailabilityGroupReplicaServerObject.AvailabilityGroups[$AvailabilityGroupName] # Restore the database Invoke-Query -SQLServer $currentAvailabilityGroupReplicaServerObject.NetName -SQLInstanceName $currentAvailabilityGroupReplicaServerObject.ServiceName -Database master -Query $restoreDatabaseQueryString Restore-SqlDatabase -InputObject $currentAvailabilityGroupReplicaServerObject @restoreSqlDatabaseLogParameters # Add the database to the Availability Group Add-SqlAvailabilityDatabase -InputObject $currentReplicaAvailabilityGroupObject -Database $databaseToAddToAvailabilityGroup } } catch { # Log the failure $databasesToAddFailures.Add($databaseToAddToAvailabilityGroup, $_.Exception) # Move on to the next database continue } finally { # Clean up the backup files Remove-Item -Path $databaseFullBackupFile, $databaseLogBackupFile -Force -ErrorAction Continue } } else { $databasesToAddFailures.Add($databaseToAddToAvailabilityGroup, "The following prerequisite checks failed: $( $prerequisiteCheckFailures -join "`r`n" )" ) } } } if ( $databasesToRemoveFromAvailabilityGroup.Count -gt 0 ) { Write-Verbose -Message ($script:localizedData.RemovingDatabasesToAvailabilityGroup -f $AvailabilityGroupName, ( $databasesToRemoveFromAvailabilityGroup -join ', ' )) foreach ( $databaseToAddToAvailabilityGroup in $databasesToRemoveFromAvailabilityGroup ) { $availabilityDatabase = $primaryServerObject.AvailabilityGroups[$AvailabilityGroupName].AvailabilityDatabases[$databaseToAddToAvailabilityGroup] try { Remove-SqlAvailabilityDatabase -InputObject $availabilityDatabase -ErrorAction Stop } catch { $databasesToRemoveFailures.Add($databaseToAddToAvailabilityGroup, 'Failed to remove the database from the availability group.') } } } # Combine the failures into one error message and throw it here. Doing this will allow all the databases that can be processes to be processed and will still show that applying the configuration failed if ( ( $databasesToAddFailures.Count -gt 0 ) -or ( $databasesToRemoveFailures.Count -gt 0 ) ) { $formatArgs = @() foreach ( $failure in ( $databasesToAddFailures.GetEnumerator() + $databasesToRemoveFailures.GetEnumerator() ) ) { $formatArgs += "The operation on the database '$( $failure.Key )' failed with the following errors: $( $failure.Value -join "`r`n" )" } throw ($script:localizedData.AlterAvailabilityGroupDatabaseMembershipFailure -f $formatArgs ) } } <# .SYNOPSIS Tests the database membership of the specified Availability Group. .PARAMETER DatabaseName The name of the database(s) to add to the availability group. This accepts wildcards. .PARAMETER SQLServer Hostname of the SQL Server where the primary replica of the availability group lives. If the availability group is not currently on this server, the resource will attempt to connect to the server where the primary replica lives. .PARAMETER SQLInstanceName Name of the SQL instance where the primary replica of the availability group lives. If the availability group is not currently on this instance, the resource will attempt to connect to the instance where the primary replica lives. .PARAMETER AvailabilityGroupName The name of the availability group in which to manage the database membership(s). .PARAMETER BackupPath The path used to seed the availability group replicas. This should be a path that is accessible by all of the replicas. .PARAMETER Ensure Specifies the membership of the database(s) in the availability group. The options are: - Present: The defined database(s) are added to the availability group. All other databases that may be a member of the availability group are ignored. - Absent: The defined database(s) are removed from the availability group. All other databases that may be a member of the availability group are ignored. The default is 'Present'. .PARAMETER Force When used with "Ensure = 'Present'" it ensures the specified database(s) are the only databases that are a member of the specified Availability Group. This parameter is ignored when 'Ensure' is 'Absent'. .PARAMETER MatchDatabaseOwner If set to $true, this ensures the database owner of the database on the primary replica is the owner of the database on all secondary replicas. This requires the database owner is available as a login on all replicas and that the PSDscRunAsCredential has impersonate permissions. If set to $false, the owner of the database will be the PSDscRunAsCredential. The default is '$true'. #> function Test-TargetResource { [CmdletBinding()] [OutputType([System.Boolean])] param ( [Parameter(Mandatory = $true)] [System.String[]] $DatabaseName, [Parameter(Mandatory = $true)] [System.String] $SQLServer, [Parameter(Mandatory = $true)] [System.String] $SQLInstanceName, [Parameter(Mandatory = $true)] [System.String] $AvailabilityGroupName, [Parameter(Mandatory = $true)] [System.String] $BackupPath, [Parameter()] [ValidateSet('Present', 'Absent')] [System.String] $Ensure = 'Present', [Parameter()] [Boolean] $Force, [Parameter()] [Boolean] $MatchDatabaseOwner ) $configurationInDesiredState = $true $getTargetResourceParameters = @{ DatabaseName = $DatabaseName SQLServer = $SQLServer SQLInstanceName = $SQLInstanceName AvailabilityGroupName = $AvailabilityGroupName BackupPath = $BackupPath } $currentConfiguration = Get-TargetResource @getTargetResourceParameters # Connect to the defined instance $serverObject = Connect-SQL -SQLServer $SQLServer -SQLInstanceName $SQLInstanceName # Get the Availability Group if it exists if ( -not [string]::IsNullOrEmpty($currentConfiguration.AvailabilityGroupName) ) { $availabilityGroup = $serverObject.AvailabilityGroups[$AvailabilityGroupName] # Make sure we're communicating with the primary replica in order to make changes to the replica $primaryServerObject = Get-PrimaryReplicaServerObject -ServerObject $serverObject -AvailabilityGroup $availabilityGroup $matchingDatabaseNames = Get-MatchingDatabaseNames -DatabaseName $DatabaseName -ServerObject $primaryServerObject $databasesNotFoundOnTheInstance = @() if ( ( $Ensure -eq 'Present' ) -and $matchingDatabaseNames.Count -eq 0 ) { $configurationInDesiredState = $false Write-Verbose -Message ($script:localizedData.DatabasesNotFound -f ($DatabaseName -join ', ')) } else { $databasesNotFoundOnTheInstance = Get-DatabaseNamesNotFoundOnTheInstance -DatabaseName $DatabaseName -MatchingDatabaseNames $matchingDatabaseNames # If the databases specified are not present on the instance and the desired state is not Absent if ( ( $databasesNotFoundOnTheInstance.Count -gt 0 ) -and ( $Ensure -ne 'Absent' ) ) { $configurationInDesiredState = $false Write-Verbose -Message ($script:localizedData.DatabasesNotFound -f ( $databasesNotFoundOnTheInstance -join ', ' )) } $getDatabasesToAddToAvailabilityGroupParameters = @{ DatabaseName = $DatabaseName Ensure = $Ensure ServerObject = $primaryServerObject AvailabilityGroup = $availabilityGroup } $databasesToAddToAvailabilityGroup = Get-DatabasesToAddToAvailabilityGroup @getDatabasesToAddToAvailabilityGroupParameters if ( $databasesToAddToAvailabilityGroup.Count -gt 0 ) { $configurationInDesiredState = $false Write-Verbose -Message ($script:localizedData.DatabaseShouldBeMember -f $AvailabilityGroupName, ( $databasesToAddToAvailabilityGroup -join ', ' )) } $getDatabasesToRemoveFromAvailabilityGroupParameters = @{ DatabaseName = $DatabaseName Ensure = $Ensure Force = $Force ServerObject = $primaryServerObject AvailabilityGroup = $availabilityGroup } $databasesToRemoveFromAvailabilityGroup = Get-DatabasesToRemoveFromAvailabilityGroup @getDatabasesToRemoveFromAvailabilityGroupParameters if ( $databasesToRemoveFromAvailabilityGroup.Count -gt 0 ) { $configurationInDesiredState = $false Write-Verbose -Message ($script:localizedData.DatabaseShouldNotBeMember -f $AvailabilityGroupName, ( $databasesToRemoveFromAvailabilityGroup -join ', ' )) } } } else { $configurationInDesiredState = $false Write-Verbose -Message ($script:localizedData.AvailabilityGroupDoesNotExist -f ($DatabaseName -join ', ')) } return $configurationInDesiredState } <# .SYNOPSIS Get the databases that should be members of the Availability Group. .PARAMETER DatabaseName The name of the database(s) to add to the availability group. This accepts wildcards. .PARAMETER Ensure Specifies the membership of the database(s) in the availability group. The options are: - Present: The defined database(s) are added to the availability group. All other databases that may be a member of the availability group are ignored. - Absent: The defined database(s) are removed from the availability group. All other databases that may be a member of the availability group are ignored. .PARAMETER ServerObject The server object the databases should be in. .PARAMETER AvailabilityGroup The availability group object the databases should be a member of. #> function Get-DatabasesToAddToAvailabilityGroup { [CmdletBinding()] param ( [Parameter()] [System.String[]] $DatabaseName, [Parameter(Mandatory = $true)] [System.String] $Ensure, [Parameter(Mandatory = $true)] [Microsoft.SqlServer.Management.Smo.Server] $ServerObject, [Parameter(Mandatory = $true)] [Microsoft.SqlServer.Management.Smo.AvailabilityGroup] $AvailabilityGroup ) $matchingDatabaseNames = Get-MatchingDatabaseNames -DatabaseName $DatabaseName -ServerObject $ServerObject # This is a hack to allow Compare-Object to work on an empty object if ( $null -eq $matchingDatabaseNames ) { $MatchingDatabaseNames = @('') } $databasesInAvailabilityGroup = $AvailabilityGroup.AvailabilityDatabases | Select-Object -ExpandProperty Name # This is a hack to allow Compare-Object to work on an empty object if ( $null -eq $databasesInAvailabilityGroup ) { $databasesInAvailabilityGroup = @('') } $comparisonResult = Compare-Object -ReferenceObject $matchingDatabaseNames -DifferenceObject $databasesInAvailabilityGroup $databasesToAddToAvailabilityGroup = @() if ( $Ensure -eq 'Present' ) { $databasesToAddToAvailabilityGroup = $comparisonResult | Where-Object -FilterScript { $_.SideIndicator -eq '<=' } | Select-Object -ExpandProperty InputObject } return $databasesToAddToAvailabilityGroup } <# .SYNOPSIS Get the databases that should not be members of the Availability Group. .PARAMETER DatabaseName The name of the database(s) to add to the availability group. This accepts wildcards. .PARAMETER Ensure Specifies the membership of the database(s) in the availability group. The options are: - Present: The defined database(s) are added to the availability group. All other databases that may be a member of the availability group are ignored. - Absent: The defined database(s) are removed from the availability group. All other databases that may be a member of the availability group are ignored. .PARAMETER Force When used with "Ensure = 'Present'" it ensures the specified database(s) are the only databases that are a member of the specified Availability Group. This parameter is ignored when 'Ensure' is 'Absent'. .PARAMETER ServerObject The server object the databases should not be in. .PARAMETER AvailabilityGroup The availability group object the databases should not be a member of. #> function Get-DatabasesToRemoveFromAvailabilityGroup { [CmdletBinding()] param ( [Parameter()] [System.String[]] $DatabaseName, [Parameter(Mandatory = $true)] [System.String] $Ensure, [Parameter()] [Boolean] $Force, [Parameter(Mandatory = $true)] [Microsoft.SqlServer.Management.Smo.Server] $ServerObject, [Parameter(Mandatory = $true)] [Microsoft.SqlServer.Management.Smo.AvailabilityGroup] $AvailabilityGroup ) $matchingDatabaseNames = Get-MatchingDatabaseNames -DatabaseName $DatabaseName -ServerObject $ServerObject if ( $null -eq $matchingDatabaseNames ) { $MatchingDatabaseNames = @('') } $databasesInAvailabilityGroup = $AvailabilityGroup.AvailabilityDatabases | Select-Object -ExpandProperty Name # This is a hack to allow Compare-Object to work on an empty object if ( $null -eq $databasesInAvailabilityGroup ) { $databasesInAvailabilityGroup = @('') } $comparisonResult = Compare-Object -ReferenceObject $matchingDatabaseNames -DifferenceObject $databasesInAvailabilityGroup -IncludeEqual $databasesToRemoveFromAvailabilityGroup = @() if ( 'Absent' -eq $Ensure ) { $databasesToRemoveFromAvailabilityGroup = $comparisonResult | Where-Object -FilterScript { '==' -eq $_.SideIndicator } | Select-Object -ExpandProperty InputObject } elseif ( ( 'Present' -eq $Ensure ) -and ( $Force ) ) { $databasesToRemoveFromAvailabilityGroup = $comparisonResult | Where-Object -FilterScript { '=>' -eq $_.SideIndicator } | Select-Object -ExpandProperty InputObject } return $databasesToRemoveFromAvailabilityGroup } <# .SYNOPSIS Get the database names that were specified in the configuration that do not exist on the instance. .PARAMETER DatabaseName The name of the database(s) to add to the availability group. This accepts wildcards. .PARAMETER MatchingDatabaseNames All of the databases names that match the supplied names and wildcards. #> function Get-MatchingDatabaseNames { [CmdletBinding()] param ( [Parameter()] [System.String[]] $DatabaseName, [Parameter(Mandatory = $true)] [Microsoft.SqlServer.Management.Smo.Server] $ServerObject ) $matchingDatabaseNames = @() foreach ( $dbName in $DatabaseName ) { $matchingDatabaseNames += $ServerObject.Databases | Where-Object -FilterScript { $_.Name -like $dbName } | Select-Object -ExpandProperty Name } return $matchingDatabaseNames } <# .SYNOPSIS Get the database names that were defined in the DatabaseName property but were not found on the instance. .PARAMETER DatabaseName The name of the database(s) to add to the availability group. This accepts wildcards. .PARAMETER MatchingDatabaseNames All of the database names that were found on the instance that match the supplied DatabaseName property. #> function Get-DatabaseNamesNotFoundOnTheInstance { [CmdletBinding()] param ( [Parameter()] [System.String[]] $DatabaseName, [Parameter()] [System.String[]] $MatchingDatabaseNames ) $databasesNotFoundOnTheInstance = @{} foreach ( $dbName in $DatabaseName ) { # Assume the database name was not found $databaseToAddToAvailabilityGroupNotFound = $true foreach ( $matchingDatabaseName in $matchingDatabaseNames ) { if ( $matchingDatabaseName -like $dbName ) { # If we found the database name, it's not missing $databaseToAddToAvailabilityGroupNotFound = $false } } $databasesNotFoundOnTheInstance.Add($dbName, $databaseToAddToAvailabilityGroupNotFound) } $result = $databasesNotFoundOnTheInstance.GetEnumerator() | Where-Object -FilterScript { $_.Value } | Select-Object -ExpandProperty Key return $result } |