SqlServerAlwaysOnTools

1.0.2.0

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,

        [Parameter(
            Mandatory = $false,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false
        )]
        [switch]$RemoveReplicaDatabase
    )

    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()
            }

            if ($RemoveReplicaDatabase) {
                $SecondaryReplicas = $AvailabilityGroupObject.AvailabilityReplicas.where({$_.Role -eq 'Secondary'})

                foreach ($SecondaryReplica in [Uri]$SecondaryReplicas.EndpointUrl) {
                    $DatabaseObject = Get-SmoDatabaseObject -ServerInstance $SecondaryReplica.Host -DatabaseName $DatabaseName

                    if ($PSCmdlet.ShouldProcess($DatabaseName, "Drop database from secondary node")) {
                        $StopLoop = $false
                        $Attempt = 0

                        do {
                            try {
                                $DatabaseObject.Drop()

                                $StopLoop = $true
                            }
                            catch {
                                if ($Attempt -gt 2) {
                                    Write-Warning "Unable to drop database $Database from $($SecondaryReplica.Host). Database will need to be manually removed."

                                    $StopLoop = $true
                                } else {
                                    Start-Sleep -Milliseconds 500

                                    $Attempt++
                                }
                            }
                        } while (-not $StopLoop)
                    }
                }

                $AvailabilityGroupObject.Refresh()

                Start-Sleep -Milliseconds 500
            }
        }
        catch {
            throw $_
        }
        finally {
            if ($PSCmdlet.ParameterSetName -eq 'ServerInstance') {
                Disconnect-SmoServer -SmoServerObject $AvailabilityGroupObject.Parent
            }
        }
    }

    end {
    }
}

# SIG # Begin signature block
# MIInywYJKoZIhvcNAQcCoIInvDCCJ7gCAQExDzANBglghkgBZQMEAgEFADB5Bgor
# BgEEAYI3AgEEoGswaTA0BgorBgEEAYI3AgEeMCYCAwEAAAQQH8w7YFlLCE63JNLG
# KX7zUQIBAAIBAAIBAAIBAAIBADAxMA0GCWCGSAFlAwQCAQUABCCLhWPoFj44GJvL
# Op1/9TNtSa4KPgVNk+CzYIcnrg1awKCCINswggWNMIIEdaADAgECAhAOmxiO+dAt
# 5+/bUOIIQBhaMA0GCSqGSIb3DQEBDAUAMGUxCzAJBgNVBAYTAlVTMRUwEwYDVQQK
# EwxEaWdpQ2VydCBJbmMxGTAXBgNVBAsTEHd3dy5kaWdpY2VydC5jb20xJDAiBgNV
# BAMTG0RpZ2lDZXJ0IEFzc3VyZWQgSUQgUm9vdCBDQTAeFw0yMjA4MDEwMDAwMDBa
# Fw0zMTExMDkyMzU5NTlaMGIxCzAJBgNVBAYTAlVTMRUwEwYDVQQKEwxEaWdpQ2Vy
# dCBJbmMxGTAXBgNVBAsTEHd3dy5kaWdpY2VydC5jb20xITAfBgNVBAMTGERpZ2lD
# ZXJ0IFRydXN0ZWQgUm9vdCBHNDCCAiIwDQYJKoZIhvcNAQEBBQADggIPADCCAgoC
# ggIBAL/mkHNo3rvkXUo8MCIwaTPswqclLskhPfKK2FnC4SmnPVirdprNrnsbhA3E
# MB/zG6Q4FutWxpdtHauyefLKEdLkX9YFPFIPUh/GnhWlfr6fqVcWWVVyr2iTcMKy
# unWZanMylNEQRBAu34LzB4TmdDttceItDBvuINXJIB1jKS3O7F5OyJP4IWGbNOsF
# xl7sWxq868nPzaw0QF+xembud8hIqGZXV59UWI4MK7dPpzDZVu7Ke13jrclPXuU1
# 5zHL2pNe3I6PgNq2kZhAkHnDeMe2scS1ahg4AxCN2NQ3pC4FfYj1gj4QkXCrVYJB
# MtfbBHMqbpEBfCFM1LyuGwN1XXhm2ToxRJozQL8I11pJpMLmqaBn3aQnvKFPObUR
# WBf3JFxGj2T3wWmIdph2PVldQnaHiZdpekjw4KISG2aadMreSx7nDmOu5tTvkpI6
# nj3cAORFJYm2mkQZK37AlLTSYW3rM9nF30sEAMx9HJXDj/chsrIRt7t/8tWMcCxB
# YKqxYxhElRp2Yn72gLD76GSmM9GJB+G9t+ZDpBi4pncB4Q+UDCEdslQpJYls5Q5S
# UUd0viastkF13nqsX40/ybzTQRESW+UQUOsxxcpyFiIJ33xMdT9j7CFfxCBRa2+x
# q4aLT8LWRV+dIPyhHsXAj6KxfgommfXkaS+YHS312amyHeUbAgMBAAGjggE6MIIB
# NjAPBgNVHRMBAf8EBTADAQH/MB0GA1UdDgQWBBTs1+OC0nFdZEzfLmc/57qYrhwP
# TzAfBgNVHSMEGDAWgBRF66Kv9JLLgjEtUYunpyGd823IDzAOBgNVHQ8BAf8EBAMC
# AYYweQYIKwYBBQUHAQEEbTBrMCQGCCsGAQUFBzABhhhodHRwOi8vb2NzcC5kaWdp
# Y2VydC5jb20wQwYIKwYBBQUHMAKGN2h0dHA6Ly9jYWNlcnRzLmRpZ2ljZXJ0LmNv
# bS9EaWdpQ2VydEFzc3VyZWRJRFJvb3RDQS5jcnQwRQYDVR0fBD4wPDA6oDigNoY0
# aHR0cDovL2NybDMuZGlnaWNlcnQuY29tL0RpZ2lDZXJ0QXNzdXJlZElEUm9vdENB
# LmNybDARBgNVHSAECjAIMAYGBFUdIAAwDQYJKoZIhvcNAQEMBQADggEBAHCgv0Nc
# Vec4X6CjdBs9thbX979XB72arKGHLOyFXqkauyL4hxppVCLtpIh3bb0aFPQTSnov
# Lbc47/T/gLn4offyct4kvFIDyE7QKt76LVbP+fT3rDB6mouyXtTP0UNEm0Mh65Zy
# oUi0mcudT6cGAxN3J0TU53/oWajwvy8LpunyNDzs9wPHh6jSTEAZNUZqaVSwuKFW
# juyk1T3osdz9HNj0d1pcVIxv76FQPfx2CWiEn2/K2yCNNWAcAgPLILCsWKAOQGPF
# mCLBsln1VWvPJ6tsds5vIy30fnFqI2si/xK4VC0nftg62fC2h5b9W9FcrBjDTZ9z
# twGpn1eqXijiuZQwgga0MIIEnKADAgECAhANx6xXBf8hmS5AQyIMOkmGMA0GCSqG
# SIb3DQEBCwUAMGIxCzAJBgNVBAYTAlVTMRUwEwYDVQQKEwxEaWdpQ2VydCBJbmMx
# GTAXBgNVBAsTEHd3dy5kaWdpY2VydC5jb20xITAfBgNVBAMTGERpZ2lDZXJ0IFRy
# dXN0ZWQgUm9vdCBHNDAeFw0yNTA1MDcwMDAwMDBaFw0zODAxMTQyMzU5NTlaMGkx
# CzAJBgNVBAYTAlVTMRcwFQYDVQQKEw5EaWdpQ2VydCwgSW5jLjFBMD8GA1UEAxM4
# RGlnaUNlcnQgVHJ1c3RlZCBHNCBUaW1lU3RhbXBpbmcgUlNBNDA5NiBTSEEyNTYg
# MjAyNSBDQTEwggIiMA0GCSqGSIb3DQEBAQUAA4ICDwAwggIKAoICAQC0eDHTCphB
# cr48RsAcrHXbo0ZodLRRF51NrY0NlLWZloMsVO1DahGPNRcybEKq+RuwOnPhof6p
# vF4uGjwjqNjfEvUi6wuim5bap+0lgloM2zX4kftn5B1IpYzTqpyFQ/4Bt0mAxAHe
# HYNnQxqXmRinvuNgxVBdJkf77S2uPoCj7GH8BLuxBG5AvftBdsOECS1UkxBvMgEd
# gkFiDNYiOTx4OtiFcMSkqTtF2hfQz3zQSku2Ws3IfDReb6e3mmdglTcaarps0wjU
# jsZvkgFkriK9tUKJm/s80FiocSk1VYLZlDwFt+cVFBURJg6zMUjZa/zbCclF83bR
# VFLeGkuAhHiGPMvSGmhgaTzVyhYn4p0+8y9oHRaQT/aofEnS5xLrfxnGpTXiUOeS
# LsJygoLPp66bkDX1ZlAeSpQl92QOMeRxykvq6gbylsXQskBBBnGy3tW/AMOMCZIV
# NSaz7BX8VtYGqLt9MmeOreGPRdtBx3yGOP+rx3rKWDEJlIqLXvJWnY0v5ydPpOjL
# 6s36czwzsucuoKs7Yk/ehb//Wx+5kMqIMRvUBDx6z1ev+7psNOdgJMoiwOrUG2Zd
# SoQbU2rMkpLiQ6bGRinZbI4OLu9BMIFm1UUl9VnePs6BaaeEWvjJSjNm2qA+sdFU
# eEY0qVjPKOWug/G6X5uAiynM7Bu2ayBjUwIDAQABo4IBXTCCAVkwEgYDVR0TAQH/
# BAgwBgEB/wIBADAdBgNVHQ4EFgQU729TSunkBnx6yuKQVvYv1Ensy04wHwYDVR0j
# BBgwFoAU7NfjgtJxXWRM3y5nP+e6mK4cD08wDgYDVR0PAQH/BAQDAgGGMBMGA1Ud
# JQQMMAoGCCsGAQUFBwMIMHcGCCsGAQUFBwEBBGswaTAkBggrBgEFBQcwAYYYaHR0
# cDovL29jc3AuZGlnaWNlcnQuY29tMEEGCCsGAQUFBzAChjVodHRwOi8vY2FjZXJ0
# cy5kaWdpY2VydC5jb20vRGlnaUNlcnRUcnVzdGVkUm9vdEc0LmNydDBDBgNVHR8E
# PDA6MDigNqA0hjJodHRwOi8vY3JsMy5kaWdpY2VydC5jb20vRGlnaUNlcnRUcnVz
# dGVkUm9vdEc0LmNybDAgBgNVHSAEGTAXMAgGBmeBDAEEAjALBglghkgBhv1sBwEw
# DQYJKoZIhvcNAQELBQADggIBABfO+xaAHP4HPRF2cTC9vgvItTSmf83Qh8WIGjB/
# T8ObXAZz8OjuhUxjaaFdleMM0lBryPTQM2qEJPe36zwbSI/mS83afsl3YTj+IQhQ
# E7jU/kXjjytJgnn0hvrV6hqWGd3rLAUt6vJy9lMDPjTLxLgXf9r5nWMQwr8Myb9r
# EVKChHyfpzee5kH0F8HABBgr0UdqirZ7bowe9Vj2AIMD8liyrukZ2iA/wdG2th9y
# 1IsA0QF8dTXqvcnTmpfeQh35k5zOCPmSNq1UH410ANVko43+Cdmu4y81hjajV/gx
# dEkMx1NKU4uHQcKfZxAvBAKqMVuqte69M9J6A47OvgRaPs+2ykgcGV00TYr2Lr3t
# y9qIijanrUR3anzEwlvzZiiyfTPjLbnFRsjsYg39OlV8cipDoq7+qNNjqFzeGxcy
# tL5TTLL4ZaoBdqbhOhZ3ZRDUphPvSRmMThi0vw9vODRzW6AxnJll38F0cuJG7uEB
# YTptMSbhdhGQDpOXgpIUsWTjd6xpR6oaQf/DJbg3s6KCLPAlZ66RzIg9sC+NJpud
# /v4+7RWsWCiKi9EOLLHfMR2ZyJ/+xhCx9yHbxtl5TPau1j/1MIDpMPx0LckTetiS
# uEtQvLsNz3Qbp7wGWqbIiOWCnb5WqxL3/BAPvIXKUjPSxyZsq8WhbaM2tszWkPZP
# ubdcMIIGuTCCBKGgAwIBAgIRAJmjgAomVTtlq9xuhKaz6jkwDQYJKoZIhvcNAQEM
# BQAwgYAxCzAJBgNVBAYTAlBMMSIwIAYDVQQKExlVbml6ZXRvIFRlY2hub2xvZ2ll
# cyBTLkEuMScwJQYDVQQLEx5DZXJ0dW0gQ2VydGlmaWNhdGlvbiBBdXRob3JpdHkx
# JDAiBgNVBAMTG0NlcnR1bSBUcnVzdGVkIE5ldHdvcmsgQ0EgMjAeFw0yMTA1MTkw
# NTMyMThaFw0zNjA1MTgwNTMyMThaMFYxCzAJBgNVBAYTAlBMMSEwHwYDVQQKExhB
# c3NlY28gRGF0YSBTeXN0ZW1zIFMuQS4xJDAiBgNVBAMTG0NlcnR1bSBDb2RlIFNp
# Z25pbmcgMjAyMSBDQTCCAiIwDQYJKoZIhvcNAQEBBQADggIPADCCAgoCggIBAJ0j
# zwQwIzvBRiznM3M+Y116dbq+XE26vest+L7k5n5TeJkgH4Cyk74IL9uP61olRsxs
# U/WBAElTMNQI/HsE0uCJ3VPLO1UufnY0qDHG7yCnJOvoSNbIbMpT+Cci75scCx7U
# sKK1fcJo4TXetu4du2vEXa09Tx/bndCBfp47zJNsamzUyD7J1rcNxOw5g6FJg0Im
# Iv7nCeNn3B6gZG28WAwe0mDqLrvU49chyKIc7gvCjan3GH+2eP4mYJASflBTQ3HO
# s6JGdriSMVoD1lzBJobtYDF4L/GhlLEXWgrVQ9m0pW37KuwYqpY42grp/kSYE4BU
# QrbLgBMNKRvfhQPskDfZ/5GbTCyvlqPN+0OEDmYGKlVkOMenDO/xtMrMINRJS5SY
# +jWCi8PRHAVxO0xdx8m2bWL4/ZQ1dp0/JhUpHEpABMc3eKax8GI1F03mSJVV6o/n
# mmKqDE6TK34eTAgDiBuZJzeEPyR7rq30yOVw2DvetlmWssewAhX+cnSaaBKMEj9O
# 2GgYkPJ16Q5Da1APYO6n/6wpCm1qUOW6Ln1J6tVImDyAB5Xs3+JriasaiJ7P5KpX
# eiVV/HIsW3ej85A6cGaOEpQA2gotiUqZSkoQUjQ9+hPxDVb/Lqz0tMjp6RuLSKAR
# sVQgETwoNQZ8jCeKwSQHDkpwFndfCceZ/OfCUqjxAgMBAAGjggFVMIIBUTAPBgNV
# HRMBAf8EBTADAQH/MB0GA1UdDgQWBBTddF1MANt7n6B0yrFu9zzAMsBwzTAfBgNV
# HSMEGDAWgBS2oVQ5AsOgP46KvPrU+Bym0ToO/TAOBgNVHQ8BAf8EBAMCAQYwEwYD
# VR0lBAwwCgYIKwYBBQUHAwMwMAYDVR0fBCkwJzAloCOgIYYfaHR0cDovL2NybC5j
# ZXJ0dW0ucGwvY3RuY2EyLmNybDBsBggrBgEFBQcBAQRgMF4wKAYIKwYBBQUHMAGG
# HGh0dHA6Ly9zdWJjYS5vY3NwLWNlcnR1bS5jb20wMgYIKwYBBQUHMAKGJmh0dHA6
# Ly9yZXBvc2l0b3J5LmNlcnR1bS5wbC9jdG5jYTIuY2VyMDkGA1UdIAQyMDAwLgYE
# VR0gADAmMCQGCCsGAQUFBwIBFhhodHRwOi8vd3d3LmNlcnR1bS5wbC9DUFMwDQYJ
# KoZIhvcNAQEMBQADggIBAHWIWA/lj1AomlOfEOxD/PQ7bcmahmJ9l0Q4SZC+j/v0
# 9CD2csX8Yl7pmJQETIMEcy0VErSZePdC/eAvSxhd7488x/Cat4ke+AUZZDtfCd8y
# HZgikGuS8mePCHyAiU2VSXgoQ1MrkMuqxg8S1FALDtHqnizYS1bIMOv8znyJjZQE
# Sp9RT+6NH024/IqTRsRwSLrYkbFq4VjNn/KV3Xd8dpmyQiirZdrONoPSlCRxCIi5
# 4vQcqKiFLpeBm5S0IoDtLoIe21kSw5tAnWPazS6sgN2oXvFpcVVpMcq0C4x/CLSN
# e0XckmmGsl9z4UUguAJtf+5gE8GVsEg/ge3jHGTYaZ/MyfujE8hOmKBAUkVa7NMx
# RSB1EdPFpNIpEn/pSHuSL+kWN/2xQBJaDFPr1AX0qLgkXmcEi6PFnaw5T17UdIIn
# A58rTu3mefNuzUtse4AgYmxEmJDodf8NbVcU6VdjWtz0e58WFZT7tST6EWQmx/Oo
# HPelE77lojq7lpsjhDCzhhp4kfsfszxf9g2hoCtltXhCX6NqsqwTT7xe8LgMkH4h
# Vy8L1h2pqGLT2aNCx7h/F95/QvsTeGGjY7dssMzq/rSshFQKLZ8lPb8hFTmiGDJN
# yHga5hZ59IGynk08mHhBFM/0MLeBzlAQq1utNjQprztZ5vv/NJy8ua9AGbwkMWkO
# MIIG4DCCBMigAwIBAgIQQ7s0QZ8qUnHOP66HyvajHjANBgkqhkiG9w0BAQsFADBW
# MQswCQYDVQQGEwJQTDEhMB8GA1UEChMYQXNzZWNvIERhdGEgU3lzdGVtcyBTLkEu
# MSQwIgYDVQQDExtDZXJ0dW0gQ29kZSBTaWduaW5nIDIwMjEgQ0EwHhcNMjYwOTEy
# MjEzNTQ3WhcNMjcwOTEyMjEzNTQ2WjCBhTELMAkGA1UEBhMCVVMxFzAVBgNVBAgM
# DlNvdXRoIENhcm9saW5hMREwDwYDVQQHDAhOZXdiZXJyeTEeMBwGA1UECgwVT3Bl
# biBTb3VyY2UgRGV2ZWxvcGVyMSowKAYDVQQDDCFPcGVuIFNvdXJjZSBEZXZlbG9w
# ZXIgUm9iZXJ0IEVkZXIwggIiMA0GCSqGSIb3DQEBAQUAA4ICDwAwggIKAoICAQC6
# 72ybkWUB620cgb49nkhI4VvtWKABeSD8277f0Jww+/5fKUxoX2vC77QjmaSXU+I9
# LB2YTj8yCSTDVlxIwJoS7KgsIN9P2EC6ehJe9FRI0n2Rt33/VlRY8kuSh2XsIVGg
# i6Y3RooWVjVmYCAOXuV5zrqjGY1P72nrSIekxcGCTo1Y0nmRwsECCIYn7b3ZM065
# u9b3AtYf3oI4grblWhtY0kbuHNCSeDjpp+qExQgeIs6OpFACJSDASFiDnF8+L+bB
# V+UtUcFbvlu0WpQyblXw9vdN7BEIdqZ/1fxyhjHxqpSuOoTqoZcyDjilXtnMOfWp
# gfgdKPFRc6NHwrmxUyNLAYHOsqBC+bMxamurB1qCJ/16lFbW/YWJRrJsaeA2WaPw
# 8ulkUnZUoP9JgyVE1nwZbhZOgE3YwlVLmBBsAKsRiyJWBYqG1VdaMpfzLYJVNTc8
# 4F/90uC2BvIoafcFfNyc8dIpdd7Ni17JmYuD0+/u1gqUi3p+Qdlk/y+Vgsb1x+0z
# kaMA6CzjFA83szdzwRLFDDnaUOVngyRR8+JLnSNuEw9nNM3G5WWRGBDOkLbwq+NO
# H1gWWAaJJYOlTARrg7ea2JHLl66CBJaaledp2u8hBl1Y5yQTpIkVYx0VPt1Oa7Nq
# kOkXlNjmmlYsZm5pe3fSTBPx5YdUOeIl1sAWXGH/+QIDAQABo4IBeDCCAXQwDAYD
# VR0TAQH/BAIwADA9BgNVHR8ENjA0MDKgMKAuhixodHRwOi8vY2NzY2EyMDIxLmNy
# bC5jZXJ0dW0ucGwvY2NzY2EyMDIxLmNybDBzBggrBgEFBQcBAQRnMGUwLAYIKwYB
# BQUHMAGGIGh0dHA6Ly9jY3NjYTIwMjEub2NzcC1jZXJ0dW0uY29tMDUGCCsGAQUF
# BzAChilodHRwOi8vcmVwb3NpdG9yeS5jZXJ0dW0ucGwvY2NzY2EyMDIxLmNlcjAf
# BgNVHSMEGDAWgBTddF1MANt7n6B0yrFu9zzAMsBwzTAdBgNVHQ4EFgQUCdHvF5Qw
# xL78QY6vfJNPC89uTWEwSwYDVR0gBEQwQjAIBgZngQwBBAEwNgYLKoRoAYb2dwIF
# AQQwJzAlBggrBgEFBQcCARYZaHR0cHM6Ly93d3cuY2VydHVtLnBsL0NQUzATBgNV
# HSUEDDAKBggrBgEFBQcDAzAOBgNVHQ8BAf8EBAMCB4AwDQYJKoZIhvcNAQELBQAD
# ggIBADGvhUwApFlOiMwxr4muEgH+EK8xGwyw7qZGQZYdHQZV7E6ev+4i0u2ywMbF
# H0XcYpaB4xubprYIGbltJhIXoIM+BmIB6mgzgAtKMJBIWMECnACZKPAWPJ5vp3Xu
# GLCg0gwQGZEKJInwEFLzplH3G5g8hTO8KSLmWLVWoWHTTA3WI4LgTf/XRs3QYqur
# bB1gWRWa+vx8J/4I6znbpnpRDxy/jCYh9qtv21Dk3BovIPnfaj50JOWJhWeongQ6
# Dgd4/FZhM/U1Fj/g1W7WDMal9q43MABwmrHPbxrIEK1V5vXwAhK1m9eSaZ8bqbeA
# SId0wOYzIyEziquoO5TCdf/lSi8nD4BIm2E+h738pLQXWvr6tYWyvqaUN0uk5f27
# NsXlVRYp8EUZPP83BJMaQJFgTsYPMeZejAndk3nuqPVGeCL6WW90M7eK5sPbTAmW
# WrSnYFx4pgnMR3X3s14074ytJ3o3ycKa0bxjhMcoCTfMDmV7jUUhATpW8iZ2/E4b
# +0s7DmbN37VsBngsj04vMyVxhcNLSwdFDTQEgkEwHccChlw0anfoZJ7Xui4x5RSr
# j3rOyyrf7mFYIpsbDYjVxBo5/JVJuu5h2+BRxY9MLoSMknm391tCI7aVC/XTl/zW
# rX1kJeCx0nYBnZnmjWCRrCXWOX5V8QaLAnK/R6durGeXnlo3MIIG7TCCBNWgAwIB
# AgIQCE/cM09+RU7bww+P+ZIYNTANBgkqhkiG9w0BAQsFADBpMQswCQYDVQQGEwJV
# UzEXMBUGA1UEChMORGlnaUNlcnQsIEluYy4xQTA/BgNVBAMTOERpZ2lDZXJ0IFRy
# dXN0ZWQgRzQgVGltZVN0YW1waW5nIFJTQTQwOTYgU0hBMjU2IDIwMjUgQ0ExMB4X
# DTI2MDgwNTAwMDAwMFoXDTM3MTEwNDIzNTk1OVowYzELMAkGA1UEBhMCVVMxFzAV
# BgNVBAoTDkRpZ2lDZXJ0LCBJbmMuMTswOQYDVQQDEzJEaWdpQ2VydCBTSEEyNTYg
# UlNBNDA5NiBUaW1lc3RhbXAgUmVzcG9uZGVyIDIwMjYgMTCCAiIwDQYJKoZIhvcN
# AQEBBQADggIPADCCAgoCggIBALZ7pvLJ/s1K+NSbTGWz/TjGMPh8CQ6RucZCLv5a
# nHzWJjF/NWJrFIhy24fcpKXlgRiky4WAawDfU3YP0BMxt9l3Dm5oCG5Z69AqEN1k
# gHg2epx+l+lZBcmJCcN0ASURML5uFIS80sZsDwO3BSkUxDjLJhBI+qiZP3aixAC/
# qEGLjsBNlLol9VZ7pfGEXiMlneJIC5/YKuizVzNFKZZEeoy/0B8Zm+nzKBgSWG52
# lCO1w+nCg6XpCtklTJXeIg283hw7TmmsZXR+SMbjbrEOvZ3fP2VxIgeR28Y90ZSt
# d3F9VuA5RVynb/whITPAo9b75Zr4Ta6Mj3URm26QZYMn/FnbuTegcoRcFEZ9FOqM
# 5T6MTdtr/n74lIT/ug0eeOzmZ6QTFg33otX+bFRsIolvykE1jive4PuESaT8zzVe
# FWDAMDtozNgLctkGD1ZjkEyZtJrLl5ya0m5doH/ScpaZCZVl6pNUOCybMc/kxC6E
# AmSJY24L0yYKD1Nkddsnb/ItVKi/2nXpQNMu1PT5prW83vV8d67WowuUs0HdY4H8
# AMLGvdL/WHEj3ZnqMqAQQP9u3Ai9t+5eQ02GDwy0ODjdzi0xlp70W+ow63/0++YD
# EX1M0iwgUHwbrJvfpklkZQvw3+kv3vUPItdwroczk9icflf55W1zOEKAcJVAIXpc
# MCU9AgMBAAGjggGVMIIBkTAMBgNVHRMBAf8EAjAAMB0GA1UdDgQWBBQUyWOKMC7U
# SvtulPPm40B+9ezN4jAfBgNVHSMEGDAWgBTvb1NK6eQGfHrK4pBW9i/USezLTjAO
# BgNVHQ8BAf8EBAMCB4AwFgYDVR0lAQH/BAwwCgYIKwYBBQUHAwgwgZUGCCsGAQUF
# BwEBBIGIMIGFMCQGCCsGAQUFBzABhhhodHRwOi8vb2NzcC5kaWdpY2VydC5jb20w
# XQYIKwYBBQUHMAKGUWh0dHA6Ly9jYWNlcnRzLmRpZ2ljZXJ0LmNvbS9EaWdpQ2Vy
# dFRydXN0ZWRHNFRpbWVTdGFtcGluZ1JTQTQwOTZTSEEyNTYyMDI1Q0ExLmNydDBf
# BgNVHR8EWDBWMFSgUqBQhk5odHRwOi8vY3JsMy5kaWdpY2VydC5jb20vRGlnaUNl
# cnRUcnVzdGVkRzRUaW1lU3RhbXBpbmdSU0E0MDk2U0hBMjU2MjAyNUNBMS5jcmww
# IAYDVR0gBBkwFzAIBgZngQwBBAIwCwYJYIZIAYb9bAcBMA0GCSqGSIb3DQEBCwUA
# A4ICAQCNxTphHp1SCt+ZrAmAfn0oQLFr0mLywSLaDXQIENoyKqxrFbJblzCVP/pk
# XmwXOdrOpWygLzlT12os5ipDCy35RBCg2UMeApEtrfGhz45F4Wt4WGdNdIbRWt3Y
# TYJmpR+b7lr4d7Uwn+H600u4D7RnOGf8Wj4UNgAdZkfHhHv1mx9EVh71SJelcEN/
# oORSjXzdjfw1iZH9d8Nh/thn6hH23d+VsPAr6GAYyzSA02nXD1nYLI7Ijmiv+xLC
# iYC41DSFYL3GhTiy0PxpawPtGRyaBVGzq+UiTfM8pD7KVyF5aQyWP4KhVGUUTnmm
# /RlYJoW3TiXA/+t0YcT2oRVBm3JETjajHug2AL+v5jhtKVnd3D0rbHXEu27o+Q8p
# 4sEWPMqKDB+qbceb6T/6WcwTwXmQ9lOCLLYcsQeSWmvKqzpAec9etE14jOQAzLKW
# dE3w/TCaKtLRaRT7LCkRYVnhA2D73FLje1O5b3HR5eHs0NzU/+xX7NbEdcofy0W3
# Wdwd1XOqtlpg/JgwtKfZM5dqO94lbUveOiJBI+xZEbGRsMNbXmMREUTgu+Oca7Y7
# 3MPWcslIx2VhkSKSXjDbD6rgg39H5Mh7QfieAIjWagkJNt68Yfim6cjEzVSiLSeZ
# fdkr5dtFPTW6jATlWJdYeeDRGCyatf8R1hSjzSvdN8yWQPT9gzGCBkYwggZCAgEB
# MGowVjELMAkGA1UEBhMCUEwxITAfBgNVBAoTGEFzc2VjbyBEYXRhIFN5c3RlbXMg
# Uy5BLjEkMCIGA1UEAxMbQ2VydHVtIENvZGUgU2lnbmluZyAyMDIxIENBAhBDuzRB
# nypScc4/rofK9qMeMA0GCWCGSAFlAwQCAQUAoIGEMBgGCisGAQQBgjcCAQwxCjAI
# oAKAAKECgAAwGQYJKoZIhvcNAQkDMQwGCisGAQQBgjcCAQQwHAYKKwYBBAGCNwIB
# CzEOMAwGCisGAQQBgjcCARUwLwYJKoZIhvcNAQkEMSIEIOpnBapYboZcnSRpaojq
# vxUv7VxxgjomZJ8aq+SRMvNkMA0GCSqGSIb3DQEBAQUABIICAD0ozfwjgibBpitl
# kd0J8MJ2BXLk+jaY05EzRikn34U0O7vxLwk9oOr3RR1rHkW/BWPo0/5zpfOndOKp
# qO8YgauNxSp1DyQ5/gecV02GePlRB5mQq4njrTCj3IM9M2ivmJWX5bUWM4CdiLec
# sfh6OJLZNrOcAmV4PydUSACsBrePUnYtM2ixOITNGN6tPH+gJ7LVg1rLfLz2Wnv4
# rOb/0Y3tsYW8P53yCrGnDYmuSFQdTqgQjYfs64h2o6EMhjG7KgcbOiwZZokXmJg/
# IknL+IG48pHdAzawJwKJkYwAqFMEk/OsiEzrDtbcB94NIYiH2XxM14hpt90uH7yS
# eyhDynVdexzgH8ID+mLn+5QbbSjvnFDb1XEEQu4Scp8CvSdlnp3MfBgpIxR0NJgI
# HsNeQV1hTd/HMWiKhA/437ZxaXDo9r/VwvileBitxoGPsNuH/8N3E0nIWexLMV5C
# JeRO5sMYgmwZRQSLPtq8jx/m/35SV7uPyg643YjLjdPseoNJln7jgyU8KETULLmS
# l0Zxw/HA5W9GUYSuOUgGQG/E8Cc50wcptIJ1PGsVoh/MKyOgK+4+kvZzFMbiPEWq
# WDNSC2kiDu6chzB46dBKw4RxcxO3+oUGfQFrnNn0He9z7483Wf3FHFs2nxH01qCJ
# aGGLBj+E7vd/P9VXFpoU4q4k/4EXoYIDJjCCAyIGCSqGSIb3DQEJBjGCAxMwggMP
# AgEBMH0waTELMAkGA1UEBhMCVVMxFzAVBgNVBAoTDkRpZ2lDZXJ0LCBJbmMuMUEw
# PwYDVQQDEzhEaWdpQ2VydCBUcnVzdGVkIEc0IFRpbWVTdGFtcGluZyBSU0E0MDk2
# IFNIQTI1NiAyMDI1IENBMQIQCE/cM09+RU7bww+P+ZIYNTANBglghkgBZQMEAgEF
# AKBpMBgGCSqGSIb3DQEJAzELBgkqhkiG9w0BBwEwHAYJKoZIhvcNAQkFMQ8XDTI2
# MDkxMzE5MzYwN1owLwYJKoZIhvcNAQkEMSIEIOjX+ZyWwvJtMuSs4t6sNkXrod4H
# 0Z9jcYnxRFkocMOAMA0GCSqGSIb3DQEBAQUABIICAAHKDsKp3WATmr0Xwlf6HLhr
# CqqhTd0F+21guR+2WCMPO9jdL7PYX/SCdgegUOwucPRthMjMC3vrNFqiqYWAhXWJ
# xKn3cBoEiwQMKpNbniXe+WMKB65zrwVHKQfVS2/71+9rPWH+fvtozztAVRfyLI15
# e4BSoQrn3GYJJ7I5lopiV8zspIk0eXbGiRUUnuaVjOUPEE6FEjzg0HGszG2ICPch
# YJd/S8jyk9Pd/CGl0L973Mjzd2BGz962DhEH0npuD9SLTRAHYW3bcZJUS1YSXbUA
# ih5nbdANYo2wnAxEyx+cfUh15C5x859MWYwZ3MXaXHvk38nVk3auqEDmNDcwPmaC
# xD5kf6E4BlDyqNFgPK8vDwSaASpfBOvYVazUrXnndG3nyi5hp+0V/8vHtO9e9vX6
# 5OycTb80Pss2bbM381TiSY5gudrWkm/NlBjqfF7lEDkJI4X81mOvQDMobBEHWK/U
# ohkqWHnFGIs6BR7Z6xIn21UkCTRjsG+0ccEdpzAyzoRzqcVWzKESGAwbGtgpY9sk
# Xpzo5LtQp+3Vb+myRDnXZCY3KNDCpgpxH9j3YnKI538c5bnqnShzBbfd8lLQBx4z
# qpL5/8dj8YdDyc83JrQirLVFtBSfoexW4IMG45VEgusf3Eo7ndGOa1fOBrzBQayM
# PIOQUpEEHP7clqFkG4F6
# SIG # End signature block