functions/Test-DbaAvailabilityGroup.ps1
|
function Test-DbaAvailabilityGroup { <# .SYNOPSIS Tests the health of an Availability Group and prerequisites for changing it. .DESCRIPTION Tests the health of an Availability Group. Can also test whether all prerequisites for Add-DbaAgDatabase are met. .PARAMETER SqlInstance The primary replica of the Availability Group. .PARAMETER SqlCredential Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential). Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported. For MFA support, please use Connect-DbaInstance. .PARAMETER AvailabilityGroup The name of the Availability Group to test. .PARAMETER Secondary Not required - the command will figure this out. But use this parameter if secondary replicas listen on a non default port. .PARAMETER SecondarySqlCredential Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential). Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported. For MFA support, please use Connect-DbaInstance. .PARAMETER AddDatabase Test whether all prerequisites for Add-DbaAgDatabase to add these databases to the Availability Group are met. Use Secondary, SecondarySqlCredential, SeedingMode, SharedPath and UseLastBackup with the same values that will be used with Add-DbaAgDatabase later. .PARAMETER SeedingMode Only used when AddDatabase is used. See documentation at Add-DbaAgDatabase for more details. .PARAMETER SharedPath Only used when AddDatabase is used. See documentation at Add-DbaAgDatabase for more details. .PARAMETER UseLastBackup Only used when AddDatabase is used. See documentation at Add-DbaAgDatabase for more details. .PARAMETER EnableException By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message. This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting. Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch. .NOTES Tags: AvailabilityGroup, HA, AG, Test Author: Andreas Jordan (@JordanOrdix), ordix.de Website: https://dbatools.io Copyright: (c) 2021 by dbatools, licensed under MIT License: MIT https://opensource.org/licenses/MIT .LINK https://dbatools.io/Test-DbaAvailabilityGroup .EXAMPLE PS C:\> Test-DbaAvailabilityGroup -SqlInstance SQL2016 -AvailabilityGroup TestAG1 Test Availability Group TestAG1 with SQL2016 as the primary replica. .EXAMPLE PS C:\> Test-DbaAvailabilityGroup -SqlInstance SQL2016 -AvailabilityGroup TestAG1 -AddDatabase AdventureWorks -SeedingMode Automatic Test if database AdventureWorks can be added to the Availability Group TestAG1 with automatic seeding. #> [CmdletBinding()] param ( [Parameter(Mandatory = $true, Position = 0)] [DbaInstanceParameter]$SqlInstance, [PSCredential]$SqlCredential, [Parameter(Mandatory = $true)] [string]$AvailabilityGroup, [DbaInstanceParameter[]]$Secondary, [PSCredential]$SecondarySqlCredential, [string[]]$AddDatabase, [ValidateSet('Automatic', 'Manual')] [string]$SeedingMode, [string]$SharedPath, [switch]$UseLastBackup, [switch]$EnableException ) process { try { $server = Connect-SqlInstance -SqlInstance $SqlInstance -SqlCredential $SqlCredential } catch { Stop-Function -Message "Failed to connect to $SqlInstance." -Category ConnectionError -ErrorRecord $_ -Target $SqlInstance return } try { $ag = Get-DbaAvailabilityGroup -SqlInstance $server -AvailabilityGroup $AvailabilityGroup -EnableException } catch { Stop-Function -Message "Availability Group $AvailabilityGroup not found on $server." -ErrorRecord $_ return } if (-not $ag) { Stop-Function -Message "Availability Group $AvailabilityGroup not found on $server." return } if ($ag.LocalReplicaRole -ne 'Primary') { Stop-Function -Message "LocalReplicaRole of replica $server is not Primary, but $($ag.LocalReplicaRole). Please connect to the current primary replica $($ag.PrimaryReplica)." return } # Test for health of Availability Group # Later: Get replica and database states like in SSMS dashboard # Now: Just test for ConnectionState -eq 'Connected' # Note on further development: # As long as there are no databases in the Availability Group, test for RollupSynchronizationState is not useful # The primary replica always has the best information about all the replicas. # We can maybe also connect to the secondary replicas and test their view of the situation, but then only test the local replica. $failure = $false foreach ($replica in $ag.AvailabilityReplicas) { if ($replica.ConnectionState -ne 'Connected') { $failure = $true Stop-Function -Message "ConnectionState of replica $replica is not Connected, but $($replica.ConnectionState)." -Continue } } if ($failure) { Stop-Function -Message "ConnectionState of one or more replicas is not Connected." return } # For now, just output the base information. if (-not $AddDatabase) { [PSCustomObject]@{ ComputerName = $ag.ComputerName InstanceName = $ag.InstanceName SqlInstance = $ag.SqlInstance AvailabilityGroup = $ag.AvailabilityGroup } } # Test for Add-DbaAgDatabase foreach ($dbName in $AddDatabase) { $db = $server.Databases[$dbName] if (-not $db) { Stop-Function -Message "Database $db is not found on $server." -Continue } if ($db.RecoveryModel -ne 'Full') { Stop-Function -Message "RecoveryModel of database $db is not Full, but $($db.RecoveryModel)." -Continue } if ($db.Status -ne 'Normal') { Stop-Function -Message "Status of database $db is not Normal, but $($db.Status)." -Continue } $backups = @( ) if ($UseLastBackup) { try { $backups = Get-DbaDbBackupHistory -SqlInstance $server -Database $db.Name -IncludeCopyOnly -Last -EnableException } catch { Stop-Function -Message "Failed to get backup history for database $db." -ErrorRecord $_ -Continue } if ($backups.Type -notcontains 'Log') { Stop-Function -Message "Cannot use last backup for database $db. A log backup must be the last backup taken." -Continue } } if ($ag.AvailabilityDatabases.Name -contains $db.Name) { Stop-Function -Message "Database $db is already joined to Availability Group $AvailabilityGroup." -Continue # Note on further development: It should be possible to add a database to a replica where the database is currently not synchronized to. # Use case: Have a three node AG and in the first step only add the database to primary and one of the secondaries. Then add the database to the third replica later. # Use case: Have a two node AG and later add a new replica. Add-DbaAgReplica only adds the replica itself, not the databases. } if ($SeedingMode -eq 'Automatic' -and $server.VersionMajor -lt 13) { Stop-Function -Message "Automatic seeding mode only supported in SQL Server 2016 and above." -Continue } # Try to connect to secondary replicas as soon as possible to fail the command before making any changes to the Availability Group. # Also test if these are really secondary replicas for that availability group. Only needed if -Secondary is used, but will do it anyway to simplify code. # Also test if database is already at the secondary and if so if Status is Restoring. # We store the server SMO in a hashtable based on the DomainInstanceName of the server as this is equal to the name of the replica in $ag.AvailabilityReplicas. if ($Secondary) { $secondaryReplicas = $Secondary } else { $secondaryReplicas = ($ag.AvailabilityReplicas | Where-Object { $_.Role -eq 'Secondary' }).Name } $replicaServerSMO = @{ } $restoreNeeded = @{ } $backupNeeded = $false $failure = $false foreach ($replica in $secondaryReplicas) { try { $replicaServer = Connect-SqlInstance -SqlInstance $replica -SqlCredential $SecondarySqlCredential } catch { $failure = $true Stop-Function -Message "Failed to connect to $replica." -Category ConnectionError -ErrorRecord $_ -Target $replica -Continue } try { $replicaAg = Get-DbaAvailabilityGroup -SqlInstance $replicaServer -AvailabilityGroup $AvailabilityGroup -EnableException $replicaName = $replicaAg.Parent.DomainInstanceName } catch { $failure = $true Stop-Function -Message "Availability Group $AvailabilityGroup not found on replica $replicaServer." -ErrorRecord $_ -Continue } if (-not $replicaAg) { $failure = $true Stop-Function -Message "Availability Group $AvailabilityGroup not found on replica $replicaServer." -Continue } if ($replicaAg.LocalReplicaRole -ne 'Secondary') { $failure = $true Stop-Function -Message "LocalReplicaRole of replica $replicaServer is not Secondary, but $($replicaAg.LocalReplicaRole)." -Continue } $replicaDb = $replicaAg.Parent.Databases[$db.Name] if ($replicaDb) { # Database already present on replica, so test if we can use it. if ($replicaDb.Status -ne 'Restoring') { $failure = $true Stop-Function -Message "Status of database $db on replica $replicaName is not Restoring, but $($replicaDb.Status)" -Continue } if ($UseLastBackup) { $failure = $true Stop-Function -Message "Database $db is already present on $replicaName, so -UseLastBackup must not be used. Please remove database from replica to use -UseLastBackup." -Continue } Write-Message -Level Verbose -Message "Database $db is already present in restoring status on replica $replicaName." } else { # No database on replica, so test if we need a backup. # We need to restore a backup if the desired or the current seeding mode is manual. # To have a detailed verbose message, we test in small steps. if ($SeedingMode -eq 'Automatic') { if ($ag.AvailabilityReplicas[$replicaName].SeedingMode -eq 'Automatic') { Write-Message -Level Verbose -Message "Database $db will use automatic seeding on replica $replicaName. The replica is already configured accordingly." } else { Write-Message -Level Verbose -Message "Database $db will use automatic seeding on replica $replicaName. The replica will be configured accordingly." } if ($db.LastBackupDate.Year -eq 1) { # Automatic seeding only works with databases that are really in RecoveryModel Full, so a full backup has been taken. Write-Message -Level Verbose -Message "Database $db will need a backup first. This is ok if one of the other replicas uses manual seeding." $backupNeeded = $true } } elseif ($SeedingMode -eq 'Manual') { if ($ag.AvailabilityReplicas[$replicaName].SeedingMode -eq 'Manual') { Write-Message -Level Verbose -Message "Database $db will need a restore on replica $replicaName. The replica is already configured accordingly." } else { Write-Message -Level Verbose -Message "Database $db will need a restore on replica $replicaName. The replica will be configured accordingly." } $restoreNeeded[$replicaName] = $true } else { if ($ag.AvailabilityReplicas[$replicaName].SeedingMode -eq 'Automatic') { Write-Message -Level Verbose -Message "Database $db will use automatic seeding on replica $replicaName." if ($db.LastBackupDate.Year -eq 1) { # Automatic seeding only works with databases that are really in RecoveryModel Full, so a full backup has been taken. Write-Message -Level Verbose -Message "Database $db will need a backup first. This is ok if one of the other replicas uses manual seeding." $backupNeeded = $true } } else { Write-Message -Level Verbose -Message "Database $db will need a restore on replica $replicaName." $restoreNeeded[$replicaName] = $true } } } $replicaServerSMO[$replicaName] = $replicaAg.Parent } if ($failure) { Stop-Function -Message "Availability Group $AvailabilityGroup or database $db not found in suitable state on all secondary replicas." -Continue } if ($restoreNeeded.Count -gt 0 -and -not $SharedPath -and -not $UseLastBackup) { Stop-Function -Message "A restore of database $db is needed on one or more replicas, but -SharedPath or -UseLastBackup are missing." -Continue } if ($backupNeeded -and $restoreNeeded.Count -eq 0) { Stop-Function -Message "All replicas are configured to use automatic seeding, but the database $db was never backed up. Please backup the database or use manual seeding." -Continue } [PSCustomObject]@{ ComputerName = $ag.ComputerName InstanceName = $ag.InstanceName SqlInstance = $ag.SqlInstance AvailabilityGroupName = $ag.Name DatabaseName = $db.Name AvailabilityGroupSMO = $ag DatabaseSMO = $db PrimaryServerSMO = $server ReplicaServerSMO = $replicaServerSMO RestoreNeeded = $restoreNeeded Backups = $backups } } } } |