SqlServerAlwaysOnTools.psm1
|
Set-StrictMode -Version Latest function Add-SmoAvailabilityDatabase { <# .EXTERNALHELP SqlServerAlwaysOnTools-Help.xml #> [System.Diagnostics.DebuggerStepThrough()] [CmdletBinding( PositionalBinding = $false, SupportsShouldProcess = $true, ConfirmImpact = 'Low', DefaultParameterSetName = 'ServerInstance' )] [OutputType([Microsoft.SqlServer.Management.Smo.AvailabilityDatabase])] param ( [Parameter( Mandatory = $true, ValueFromPipeline = $false, ValueFromPipelineByPropertyName = $false, ParameterSetName = 'ServerInstance' )] [ValidateLength(1, 128)] [Alias('SqlServer')] [string]$ServerInstance, [Parameter( Mandatory = $true, ValueFromPipeline = $false, ValueFromPipelineByPropertyName = $false, ParameterSetName = 'ServerInstance' )] [ValidateLength(1, 128)] [string]$AvailabilityGroupName, [Parameter( Mandatory = $true, ValueFromPipeline = $false, ValueFromPipelineByPropertyName = $false, ParameterSetName = 'ServerInstance' )] [Parameter( Mandatory = $true, ValueFromPipeline = $false, ValueFromPipelineByPropertyName = $false, ParameterSetName = 'SmoAvailabilityGroup' )] [ValidateLength(1, 128)] [string]$DatabaseName, [Parameter( Mandatory = $true, ValueFromPipeline = $false, ValueFromPipelineByPropertyName = $false, ParameterSetName = 'SmoAvailabilityGroup' )] [Microsoft.SqlServer.Management.Smo.AvailabilityGroup]$AvailabilityGroupObject ) begin { try { if ($PSCmdlet.ParameterSetName -eq 'ServerInstance') { $AvailabilityGroupObjectParameters = @{ 'ServerInstance' = $ServerInstance 'AvailabilityGroupName' = $AvailabilityGroupName } $AvailabilityGroupObject = Get-SmoAvailabilityGroup @AvailabilityGroupObjectParameters } } catch { throw $_ } } process { try { if ($PSCmdlet.ShouldProcess($DatabaseName, 'Add database to availability group.')) { $SmoAvailabilityDatabase = [Microsoft.SqlServer.Management.Smo.AvailabilityDatabase]::New($AvailabilityGroupObject, $DatabaseName) $SmoAvailabilityDatabase.Create() if (-not $SmoAvailabilityDatabase.IsJoined) { $SmoAvailabilityDatabase.JoinAvailabilityGroup() } [void]$SmoAvailabilityDatabase.Initialize() $SmoAvailabilityDatabase } } catch { throw $_ } } end { } } function Get-SmoAvailabilityDatabase { <# .EXTERNALHELP SqlServerAlwaysOnTools-Help.xml #> [System.Diagnostics.DebuggerStepThrough()] [CmdletBinding( PositionalBinding = $false, SupportsShouldProcess = $false, ConfirmImpact = 'Low', DefaultParameterSetName = 'ServerInstance' )] [OutputType([Microsoft.SqlServer.Management.Smo.AvailabilityDatabase])] param ( [Parameter( Mandatory = $true, ValueFromPipeline = $false, ValueFromPipelineByPropertyName = $false, ParameterSetName = 'ServerInstance' )] [ValidateLength(1, 128)] [Alias('SqlServer')] [string]$ServerInstance, [Parameter( Mandatory = $true, ValueFromPipeline = $false, ValueFromPipelineByPropertyName = $false )] [ValidateLength(1, 128)] [string]$AvailabilityGroupName, [Parameter( Mandatory = $true, ValueFromPipeline = $false, ValueFromPipelineByPropertyName = $false, ParameterSetName = 'SmoServer' )] [Microsoft.SqlServer.Management.Smo.Server]$SmoServerObject, [Parameter( Mandatory = $false, ValueFromPipeline = $false, ValueFromPipelineByPropertyName = $false )] [ValidateLength(1, 128)] [string]$DatabaseName ) begin { try { if ($PSCmdlet.ParameterSetName -eq 'ServerInstance') { $SmoServerParameters = @{ 'ServerInstance' = $ServerInstance 'DatabaseName' = 'master' } $SmoServerObject = Connect-SmoServer @SmoServerParameters } } catch { if ($PSCmdlet.ParameterSetName -eq 'ServerInstance') { if (Test-Path -Path Variable:\SmoServerObject) { if ($SmoServerObject -is [Microsoft.SqlServer.Management.Smo.Server]) { Disconnect-SmoServer -SmoServerObject $SmoServerObject } } } throw $_ } } process { Try { $AvailabilityGroup = $SmoServerObject.AvailabilityGroups[$AvailabilityGroupName] if ($PSBoundParameters.ContainsKey('DatabaseName')) { $AvailabilityDatabases = $AvailabilityGroup.AvailabilityDatabases[$DatabaseName] if ($null -eq $AvailabilityDatabases) { throw [System.Management.Automation.ErrorRecord]::New( [Exception]::New('Availability Group database not found.'), '1', [System.Management.Automation.ErrorCategory]::ObjectNotFound, $DatabaseName ) } } else { $AvailabilityDatabases = $AvailabilityGroup.AvailabilityDatabases } $AvailabilityDatabases } Catch { throw $_ } finally { if ($PSCmdlet.ParameterSetName -eq 'ServerInstance') { Disconnect-SmoServer -SmoServerObject $SmoServerObject } } } end { } } function Get-SmoAvailabilityDatabaseReplicaState { <# .EXTERNALHELP SqlServerAlwaysOnTools-Help.xml #> [System.Diagnostics.DebuggerStepThrough()] [CmdletBinding( PositionalBinding = $false, SupportsShouldProcess = $false, ConfirmImpact = 'Low', DefaultParameterSetName = 'ServerInstance' )] [OutputType([Microsoft.SqlServer.Management.Smo.DatabaseReplicaState])] param ( [Parameter( Mandatory = $true, ValueFromPipeline = $false, ValueFromPipelineByPropertyName = $false, ParameterSetName = 'ServerInstance' )] [ValidateLength(1,128)] [string]$ServerInstance, [Parameter( Mandatory = $true, ValueFromPipeline = $false, ValueFromPipelineByPropertyName = $false, ParameterSetName = 'SmoServerObject' )] [Microsoft.SqlServer.Management.Smo.Server]$SmoServerObject, [Parameter( Mandatory = $false, ValueFromPipeline = $false, ValueFromPipelineByPropertyName = $false )] [ValidateLength(1, 128)] [string]$AvailabilityGroupName, [Parameter( Mandatory = $false, ValueFromPipeline = $false, ValueFromPipelineByPropertyName = $false )] [ValidateLength(1,128)] [string]$DatabaseName ) begin { try { $ServerInstanceParameterSets = @('ServerInstance') if ($PSCmdlet.ParameterSetName -in $ServerInstanceParameterSets) { $SmoServerParameters = @{ 'ServerInstance' = $ServerInstance 'DatabaseName' = 'master' } $SmoServerObject = Connect-SmoServer @SmoServerParameters } $SmoServerObject.Refresh() } catch { if ($PSCmdlet.ParameterSetName -in $ServerInstanceParameterSets) { if (Test-Path -Path Variable:\SmoServerObject) { if ($SmoServerObject -is [Microsoft.SqlServer.Management.Smo.Server]) { Disconnect-SmoServer -SmoServerObject $SmoServerObject } } } throw $_ } } process { try { $AvailabilityGroupParameters = @{ 'SmoServerObject' = $SmoServerObject } if ($PSBoundParameters.ContainsKey('AvailabilityGroupName')) { $AvailabilityGroupParameters.Add('AvailabilityGroupName', $AvailabilityGroupName) } $AvailabilityGroup = Get-SmoAvailabilityGroup @AvailabilityGroupParameters if ($PSBoundParameters.ContainsKey('DatabaseName')) { $Output = $AvailabilityGroup.DatabaseReplicaStates.where({$_.AvailabilityDatabaseName -eq $DatabaseName}) } else { $Output = $AvailabilityGroup.DatabaseReplicaStates } $Output } catch { throw $_ } finally { if ($PSCmdlet.ParameterSetName -in $ServerInstanceParameterSets) { if (Test-Path -Path Variable:\SmoServerObject) { Disconnect-SmoServer -SmoServerObject $SmoServerObject } } } } end { } } function Get-SmoAvailabilityGroup { <# .EXTERNALHELP SqlServerAlwaysOnTools-Help.xml #> [System.Diagnostics.DebuggerStepThrough()] [CmdletBinding( PositionalBinding = $false, SupportsShouldProcess = $false, ConfirmImpact = 'Low', DefaultParameterSetName = 'ServerInstance' )] [OutputType([Microsoft.SqlServer.Management.Smo.AvailabilityGroup])] param ( [Parameter( Mandatory = $true, ValueFromPipeline = $false, ValueFromPipelineByPropertyName = $false, ParameterSetName = 'ServerInstance' )] [ValidateLength(1, 128)] [Alias('SqlServer')] [string]$ServerInstance, [Parameter( Mandatory = $false, ValueFromPipeline = $false, ValueFromPipelineByPropertyName = $false )] [ValidateLength(1, 128)] [string]$AvailabilityGroupName, [Parameter( Mandatory = $true, ValueFromPipeline = $false, ValueFromPipelineByPropertyName = $false, ParameterSetName = 'SmoServer' )] [Microsoft.SqlServer.Management.Smo.Server]$SmoServerObject ) begin { try { if ($PSCmdlet.ParameterSetName -eq 'ServerInstance') { $SmoServerParameters = @{ 'ServerInstance' = $ServerInstance 'DatabaseName' = 'master' } $SmoServerObject = Connect-SmoServer @SmoServerParameters } } catch { if ($PSCmdlet.ParameterSetName -eq 'ServerInstance') { if (Test-Path -Path Variable:\SmoServerObject) { if ($SmoServerObject -is [Microsoft.SqlServer.Management.Smo.Server]) { Disconnect-SmoServer -SmoServerObject $SmoServerObject } } } throw $_ } } process { Try { if ($PSBoundParameters.ContainsKey('AvailabilityGroupName')) { $AvailabilityGroups = $SmoServerObject.AvailabilityGroups[$AvailabilityGroupName] if ($null -eq $AvailabilityGroups) { throw [System.Management.Automation.ErrorRecord]::New( [Exception]::New('Availability Group not found.'), '1', [System.Management.Automation.ErrorCategory]::ObjectNotFound, $AvailabilityGroupName ) } } else { $AvailabilityGroups = $SmoServerObject.AvailabilityGroups } $AvailabilityGroups } Catch { throw $_ } finally { if ($PSCmdlet.ParameterSetName -eq 'ServerInstance') { Disconnect-SmoServer -SmoServerObject $SmoServerObject } } } end { } } function Get-SmoAvailabilityGroupListener { <# .EXTERNALHELP SqlServerAlwaysOnTools-Help.xml #> [System.Diagnostics.DebuggerStepThrough()] [CmdletBinding( PositionalBinding = $false, SupportsShouldProcess = $false, ConfirmImpact = 'Low', DefaultParameterSetName = 'ServerInstance' )] [OutputType([Microsoft.SqlServer.Management.Smo.AvailabilityGroupListener])] param ( [Parameter( Mandatory = $true, ValueFromPipeline = $false, ValueFromPipelineByPropertyName = $false, ParameterSetName = 'ServerInstance' )] [ValidateLength(1,128)] [string]$ServerInstance, [Parameter( Mandatory = $true, ValueFromPipeline = $false, ValueFromPipelineByPropertyName = $false, ParameterSetName = 'SmoServerObject' )] [Microsoft.SqlServer.Management.Smo.Server]$SmoServerObject, [Parameter( Mandatory = $false, ValueFromPipeline = $false, ValueFromPipelineByPropertyName = $false )] [ValidateLength(1, 128)] [string]$AvailabilityGroupName ) begin { try { $ServerInstanceParameterSets = @('ServerInstance') if ($PSCmdlet.ParameterSetName -in $ServerInstanceParameterSets) { $SmoServerParameters = @{ 'ServerInstance' = $ServerInstance 'DatabaseName' = 'master' } $SmoServerObject = Connect-SmoServer @SmoServerParameters } $SmoServerObject.Refresh() } catch { if ($PSCmdlet.ParameterSetName -in $ServerInstanceParameterSets) { if (Test-Path -Path Variable:\SmoServerObject) { if ($SmoServerObject -is [Microsoft.SqlServer.Management.Smo.Server]) { Disconnect-SmoServer -SmoServerObject $SmoServerObject } } } throw $_ } } process { try { $AvailabilityGroupParameters = @{ 'SmoServerObject' = $SmoServerObject } if ($PSBoundParameters.ContainsKey('AvailabilityGroupName')) { $AvailabilityGroupParameters.Add('AvailabilityGroupName', $AvailabilityGroupName) } $AvailabilityGroup = Get-SmoAvailabilityGroup @AvailabilityGroupParameters $AvailabilityGroup.AvailabilityGroupListener } catch { throw $_ } finally { if ($PSCmdlet.ParameterSetName -in $ServerInstanceParameterSets) { if (Test-Path -Path Variable:\SmoServerObject) { Disconnect-SmoServer -SmoServerObject $SmoServerObject } } } } end { } } function Get-SmoAvailabilityGroupSeedingStatus { <# .EXTERNALHELP SqlServerAlwaysOnTools-Help.xml #> [System.Diagnostics.DebuggerStepThrough()] [CmdletBinding( PositionalBinding = $false, SupportsShouldProcess = $false, ConfirmImpact = 'Low', DefaultParameterSetName = 'ServerInstance' )] [OutputType([System.Data.DataRow])] param ( [Parameter( Mandatory = $true, ValueFromPipeline = $false, ValueFromPipelineByPropertyName = $false, ParameterSetName = 'ServerInstance' )] [ValidateLength(1,128)] [string]$ServerInstance, [Parameter( Mandatory = $true, ValueFromPipeline = $false, ValueFromPipelineByPropertyName = $false, ParameterSetName = 'SqlConnection' )] [Microsoft.Data.SqlClient.SqlConnection]$SqlConnection, [Parameter( Mandatory = $false, ValueFromPipeline = $false, ValueFromPipelineByPropertyName = $false )] [ValidateLength(1,128)] [string]$DatabaseName ) begin { $StatusFormatString = "SELECT dhas.ag_db_id , ag.name AS AvailabilityGroupName , adb.database_name , dhas.start_time , dhas.completion_time , dhas.current_state , dhas.performed_seeding , dhas.failure_state , dhas.failure_state_desc , ss.internal_state_desc , ss.remote_machine_name , DatabaseSizeMB = ss.database_size_bytes / 1045876 , TransferredSizeMB = ss.transferred_size_bytes / 1045876 , TransferRateMBS = ss.transfer_rate_bytes_per_second / 1045876 , TimeRemainingSec = CASE WHEN ss.transfer_rate_bytes_per_second = 0 THEN NULL ELSE (ss.database_size_bytes - ss.transferred_size_bytes) / ss.transfer_rate_bytes_per_second END , TimeRemaining = CASE WHEN ss.transfer_rate_bytes_per_second = 0 THEN NULL ELSE CASE WHEN ((ss.database_size_bytes - ss.transferred_size_bytes) / ss.transfer_rate_bytes_per_second) < 360000 THEN '0' ELSE '' END + RTRIM(((ss.database_size_bytes - ss.transferred_size_bytes) / ss.transfer_rate_bytes_per_second) / 3600) + ':' + RIGHT('0' + RTRIM(((ss.database_size_bytes - ss.transferred_size_bytes) / ss.transfer_rate_bytes_per_second) % 3600 / 60), 2) + ':' + RIGHT('0' + RTRIM(((ss.database_size_bytes - ss.transferred_size_bytes) / ss.transfer_rate_bytes_per_second) % 60), 2) END FROM sys.dm_hadr_automatic_seeding dhas JOIN sys.availability_databases_cluster adb ON dhas.ag_db_id = adb.group_database_id JOIN sys.availability_groups ag ON dhas.ag_id = ag.group_id LEFT JOIN sys.dm_hadr_physical_seeding_stats ss ON dhas.operation_id = ss.local_physical_seeding_id{0} ORDER BY ag.name , adb.database_name , dhas.completion_time DESC;" $WhereClauseFormatString = "`r`n`t`tWHERE adb.database_name = N'{0}'" } process { try { if ($PSBoundParameters.ContainsKey('DatabaseName')) { $WhereClause = [string]::Format($WhereClauseFormatString, $DatabaseName) } else { $WhereClause = '' } $SqlClientDataSetParameters = @{ 'DatabaseName' = 'master' 'SqlCommandText' = [string]::Format($StatusFormatString, $WhereClause) 'OutputAs' = 'DataRow' } if ($PSBoundParameters.ContainsKey('ServerInstance')) { $SqlClientDataSetParameters.Add('ServerInstance', $ServerInstance) } if ($PSBoundParameters.ContainsKey('SqlConnection')) { $SqlClientDataSetParameters.Add('SqlConnection', $SqlConnection) } $StatusDataTable = Get-SqlClientDataSet @SqlClientDataSetParameters $StatusDataTable } catch { throw $_ } } end { } } function Move-SqlAvailabilityGroup { <# .EXTERNALHELP SqlServerAlwaysOnTools-Help.xml #> [System.Diagnostics.DebuggerStepThrough()] [CmdletBinding( PositionalBinding = $false, SupportsShouldProcess = $true, ConfirmImpact = 'Medium', DefaultParameterSetName = 'ServerInstance' )] [OutputType([System.Void])] param ( [Parameter( Mandatory = $true, ValueFromPipeline = $false, ValueFromPipelineByPropertyName = $false, ParameterSetName = 'ServerInstance' )] [ValidateLength(1,128)] [string]$ServerInstance, [Parameter( Mandatory = $true, ValueFromPipeline = $false, ValueFromPipelineByPropertyName = $false, ParameterSetName = 'SmoServerObject' )] [Microsoft.SqlServer.Management.Smo.Server]$SmoServerObject, [Parameter( Mandatory = $true, ValueFromPipeline = $false, ValueFromPipelineByPropertyName = $false )] [ValidateLength(1, 128)] [string]$AvailabilityGroupName, [Parameter( Mandatory = $false, ValueFromPipeline = $false, ValueFromPipelineByPropertyName = $false )] [switch]$AllowDataLoss ) begin { try { $ServerInstanceParameterSets = @('ServerInstance') if ($PSCmdlet.ParameterSetName -in $ServerInstanceParameterSets) { $SmoServerParameters = @{ 'ServerInstance' = $ServerInstance 'DatabaseName' = 'master' 'StatementTimeout' = 0 } $SmoServerObject = Connect-SmoServer @SmoServerParameters } } catch { if ($PSCmdlet.ParameterSetName -in $ServerInstanceParameterSets) { if (Test-Path -Path Variable:\SmoServerObject) { if ($SmoServerObject -is [Microsoft.SqlServer.Management.Smo.Server]) { Disconnect-SmoServer -SmoServerObject $SmoServerObject } } } throw $_ } [int]$TotalSteps = 7 [int]$CurrentStep = 0 $ProgressParameters = @{ 'Id' = 0 'Activity' = 'Failover Availability Group' 'Status' = [string]::Format('Step {0} of {1}', $CurrentStep, $TotalSteps) 'CurrentOperation' = '' 'PercentComplete' = 0 } $RetryIntervalSeconds = 10 $RetryCount = 30 } process { try { $CurrentStep++ $ProgressParameters.Status = [string]::Format('Step {0} of {1}', $CurrentStep, $TotalSteps) $ProgressParameters.CurrentOperation = 'Checking primary replica...' $ProgressParameters.PercentComplete = ($CurrentStep - 1) / $TotalSteps * 100 Write-Verbose $ProgressParameters.CurrentOperation Write-Progress @ProgressParameters $AvailabilityGroup = Get-SmoAvailabilityGroup -SmoServerObject $SmoServerObject -AvailabilityGroupName $AvailabilityGroupName if ($AvailabilityGroup.PrimaryReplicaServerName -eq $SmoServerObject.NetName) { throw [System.Management.Automation.ErrorRecord]::New( [Exception]::New('The availability group is already on the primary replica.'), '1', [System.Management.Automation.ErrorCategory]::InvalidOperation, $AvailabilityGroupName ) } $CurrentStep++ $ProgressParameters.Status = [string]::Format('Step {0} of {1}', $CurrentStep, $TotalSteps) $ProgressParameters.CurrentOperation = 'Checking availability databases...' $ProgressParameters.PercentComplete = ($CurrentStep - 1) / $TotalSteps * 100 Write-Verbose $ProgressParameters.CurrentOperation Write-Progress @ProgressParameters $AvailabilityDatabases = Get-SmoAvailabilityDatabase -SmoServerObject $SmoServerObject -AvailabilityGroupName $AvailabilityGroupName if ($AvailabilityDatabases.where({$_.IsFailoverReady -eq $false}).Count -gt 0) { throw [System.Management.Automation.ErrorRecord]::New( [Exception]::New('One or more databases in the availability group are not failover ready.'), '1', [System.Management.Automation.ErrorCategory]::InvalidOperation, $AvailabilityGroupName ) } $CurrentStep++ $ProgressParameters.Status = [string]::Format('Step {0} of {1}', $CurrentStep, $TotalSteps) $ProgressParameters.CurrentOperation = 'Checking availability mode...' $ProgressParameters.PercentComplete = ($CurrentStep - 1) / $TotalSteps * 100 Write-Verbose $ProgressParameters.CurrentOperation Write-Progress @ProgressParameters $TargetReplica = $AvailabilityGroup.AvailabilityReplicas.Where({$_.Name -ne $SmoServerObject.NetName}) $PrimaryReplica = $AvailabilityGroup.AvailabilityReplicas.Where({$_.Name -eq $AvailabilityGroup.PrimaryReplicaServerName}) if ($TargetReplica.AvailabilityMode -ne 'SynchronousCommit') { if (-not $AllowDataLoss) { throw [System.Management.Automation.ErrorRecord]::New( [Exception]::New('The availability replica is not configured for synchronous commit. Use the -AllowDataLoss switch to allow failover with potential data loss.'), '1', [System.Management.Automation.ErrorCategory]::InvalidOperation, $TargetReplica.Name ) } else { Write-Warning 'The availability replica is not configured for synchronous commit. Proceeding with failover with potential data loss.' } } $CurrentStep++ $ProgressParameters.Status = [string]::Format('Step {0} of {1}', $CurrentStep, $TotalSteps) $ProgressParameters.CurrentOperation = 'Checking replica states...' $ProgressParameters.PercentComplete = ($CurrentStep - 1) / $TotalSteps * 100 Write-Verbose $ProgressParameters.CurrentOperation Write-Progress @ProgressParameters if ($TargetReplica.MemberState -ne 'Online') { throw [System.Management.Automation.ErrorRecord]::New( [Exception]::New('The availability replica is not connected.'), '1', [System.Management.Automation.ErrorCategory]::InvalidOperation, $TargetReplica.Name ) } if ($PrimaryReplica.MemberState -ne 'Online') { if (-not $AllowDataLoss) { throw [System.Management.Automation.ErrorRecord]::New( [Exception]::New('The primary replica is not connected. Use the -AllowDataLoss switch to allow failover with potential data loss.'), '1', [System.Management.Automation.ErrorCategory]::InvalidOperation, $PrimaryReplica.Name ) } else { Write-Warning 'The primary replica is not connected. Proceeding with failover with potential data loss.' } } $CurrentStep++ $ProgressParameters.Status = [string]::Format('Step {0} of {1}', $CurrentStep, $TotalSteps) $ProgressParameters.CurrentOperation = 'Checking for running jobs...' $ProgressParameters.PercentComplete = ($CurrentStep - 1) / $TotalSteps * 100 Write-Verbose $ProgressParameters.CurrentOperation Write-Progress @ProgressParameters $PrimaryReplicaFQDN = [System.Text.RegularExpressions.Regex]::Match($PrimaryReplica.EndpointUrl, '(?<=:\/\/)[^:\/]+').Value $PrimaryReplicaSmoServer = Connect-SmoServer -ServerInstance $PrimaryReplicaFQDN -DatabaseName 'master' $PrimaryReplicaJobServer = $PrimaryReplicaSmoServer.JobServer $TargetReplicaJobServer = $SmoServerObject.JobServer $ProgressParameters1 = @{ 'Id' = 1 'ParentID' = 0 'Activity' = 'Checking for running jobs' 'Status' = [string]::Format('Step {0} of {1}', 0, $RetryCount) 'CurrentOperation' = '' 'PercentComplete' = 0 / $RetryCount * 100 } for ($i = 0; $i -lt $RetryCount; $i++) { $ProgressParameters1.Activity = 'Waiting for jobs to complete before proceeding with failover.' $ProgressParameters1.Status = [string]::Format('Check {0} of {1}', $i + 1, $RetryCount) $ProgressParameters1.CurrentOperation = [string]::Format('Check {0}', $i + 1) $ProgressParameters1.PercentComplete = $i / $RetryCount * 100 Write-Verbose $ProgressParameters1.CurrentOperation Write-Progress @ProgressParameters1 $TotalRunningJobs = $PrimaryReplicaJobServer.Jobs.Where({$_.CurrentRunStatus -eq 'Executing'}).Count + $TargetReplicaJobServer.Jobs.Where({$_.CurrentRunStatus -eq 'Executing'}).Count if ($TotalRunningJobs -eq 0) { break } else { Start-Sleep -Seconds $RetryIntervalSeconds foreach ($Jobs in @($PrimaryReplicaJobServer.Jobs, $TargetReplicaJobServer.Jobs)) { foreach ($Job in $Jobs) { $Job.Refresh() } } } } Write-Progress -Id 1 -Activity $ProgressParameters1.Activity -Completed if ($TotalRunningJobs -gt 0) { throw [System.Management.Automation.ErrorRecord]::New( [Exception]::New('One or more jobs are still running on the availability replicas. Please check for running jobs and ensure all jobs are completed before proceeding with failover.'), '1', [System.Management.Automation.ErrorCategory]::InvalidOperation, $AvailabilityGroupName ) } $CurrentStep++ $ProgressParameters.Status = [string]::Format('Step {0} of {1}', $CurrentStep, $TotalSteps) $ProgressParameters.CurrentOperation = 'Failing over availability group...' $ProgressParameters.PercentComplete = ($CurrentStep - 1) / $TotalSteps * 100 Write-Verbose $ProgressParameters.CurrentOperation Write-Progress @ProgressParameters if ($PSCmdlet.ShouldProcess($AvailabilityGroupName, 'Move availability group')) { if ($AllowDataLoss) { $AvailabilityGroup.FailoverWithPotentialDataLoss() } else { $AvailabilityGroup.Failover() } Write-Verbose "Failover of availability group $AvailabilityGroupName initiated successfully." $AvailabilityGroup.Refresh() Write-Verbose "Current primary replica for availability group $AvailabilityGroupName is $($AvailabilityGroup.PrimaryReplicaServerName)." } $CurrentStep++ $ProgressParameters.Status = [string]::Format('Step {0} of {1}', $CurrentStep, $TotalSteps) $ProgressParameters.CurrentOperation = 'Checking synchronization state...' $ProgressParameters.PercentComplete = ($CurrentStep - 1) / $TotalSteps * 100 Write-Verbose $ProgressParameters.CurrentOperation Write-Progress @ProgressParameters $ProgressParameters1 = @{ 'Id' = 1 'ParentID' = 0 'Activity' = 'Checking for running jobs' 'Status' = [string]::Format('Step {0} of {1}', 0, $RetryCount) 'CurrentOperation' = '' 'PercentComplete' = 0 / $RetryCount * 100 } if ($PSCmdlet.ShouldProcess($AvailabilityGroupName, 'Check synchronization state of availability databases after failover')) { for ($i = 0; $i -lt $RetryCount; $i++) { $ProgressParameters1.Activity = 'Waiting for jobs to complete before proceeding with failover.' $ProgressParameters1.Status = [string]::Format('Check {0} of {1}', $i + 1, $RetryCount) $ProgressParameters1.CurrentOperation = [string]::Format('Check {0}', $i + 1) $ProgressParameters1.PercentComplete = $i / $RetryCount * 100 Write-Verbose $ProgressParameters1.CurrentOperation Write-Progress @ProgressParameters1 $PostFailoverAvailabilityDatabases = Get-SmoAvailabilityDatabase -SmoServerObject $SmoServerObject -AvailabilityGroupName $AvailabilityGroupName $NotSynchronizedDatabases = $PostFailoverAvailabilityDatabases.Where({$_.SynchronizationState -ne 'Synchronized'}) if ($NotSynchronizedDatabases.Count -eq 0) { Write-Verbose "All databases in availability group $AvailabilityGroupName are synchronized after failover." break } else { Write-Verbose "Waiting for databases in availability group $AvailabilityGroupName to synchronize after failover. Attempt $($i + 1) of $RetryCount." Start-Sleep -Seconds $RetryIntervalSeconds } } Write-Progress -Id 1 -Activity $ProgressParameters1.Activity -Completed if ($NotSynchronizedDatabases.Count -gt 0) { Write-Warning "One or more databases in availability group $AvailabilityGroupName are not synchronized after failover. Please check the synchronization state of the databases and resolve any issues as necessary. Not synchronized databases: $($NotSynchronizedDatabases.Name -join ', ')." } } } catch { throw $_ } finally { if ($PSCmdlet.ParameterSetName -in $ServerInstanceParameterSets) { Disconnect-SmoServer -SmoServerObject $SmoServerObject } if (Test-Path -Path Variable:\ProgressParameters1) { Write-Progress -Id 1 -Activity $ProgressParameters1.Activity -Completed } Write-Progress -Id 0 -Activity 'Failover Availability Group' -Completed } } end { } } function Remove-SmoAvailabilityDatabase { <# .EXTERNALHELP SqlServerAlwaysOnTools-Help.xml #> [System.Diagnostics.DebuggerStepThrough()] [CmdletBinding( PositionalBinding = $false, SupportsShouldProcess = $true, ConfirmImpact = 'Medium', DefaultParameterSetName = 'ServerInstance' )] [OutputType([Microsoft.SqlServer.Management.Smo.AvailabilityGroup])] param ( [Parameter( Mandatory = $true, ValueFromPipeline = $false, ValueFromPipelineByPropertyName = $false, ParameterSetName = 'ServerInstance' )] [ValidateLength(1, 128)] [Alias('SqlServer')] [string]$ServerInstance, [Parameter( Mandatory = $true, ValueFromPipeline = $false, ValueFromPipelineByPropertyName = $false, ParameterSetName = 'ServerInstance' )] [ValidateLength(1, 128)] [string]$AvailabilityGroupName, [Parameter( Mandatory = $true, ValueFromPipeline = $false, ValueFromPipelineByPropertyName = $false, ParameterSetName = 'ServerInstance' )] [Parameter( Mandatory = $true, ValueFromPipeline = $false, ValueFromPipelineByPropertyName = $false, ParameterSetName = 'SmoAvailabilityGroup' )] [ValidateLength(1, 128)] [string]$DatabaseName, [Parameter( Mandatory = $true, ValueFromPipeline = $false, ValueFromPipelineByPropertyName = $false, ParameterSetName = 'SmoAvailabilityGroup' )] [Microsoft.SqlServer.Management.Smo.AvailabilityGroup]$AvailabilityGroupObject ) begin { try { if ($PSCmdlet.ParameterSetName -eq 'ServerInstance') { $AvailabilityGroupObject = Get-SmoAvailabilityGroup -ServerInstance $ServerInstance -AvailabilityGroupName $AvailabilityGroupName } } catch { throw $_ } } process { try { if ($DatabaseName -NotIn $AvailabilityGroupObject.AvailabilityDatabases.Name) { throw [System.Management.Automation.ErrorRecord]::New( [Exception]::New('Database not found in availability group.'), '1', [System.Management.Automation.ErrorCategory]::ObjectNotFound, $DatabaseName ) } $SmoAvailabilityDatabase = $AvailabilityGroupObject.AvailabilityDatabases[$DatabaseName] if ($PSCmdlet.ShouldProcess($DatabaseName, 'Remove database from availability group')) { $SmoAvailabilityDatabase.Drop() } } catch { throw $_ } finally { if ($PSCmdlet.ParameterSetName -eq 'ServerInstance') { Disconnect-SmoServer -SmoServerObject $AvailabilityGroupObject.Parent } } } end { } } |