internal/functions/Invoke-DbMirrorValidation.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
function Invoke-DbMirrorValidation {
    <#
        .SYNOPSIS
            Validates if a mirror is ready
 
        .DESCRIPTION
            Validates if a mirror is ready
 
            Thanks to https://github.com/mmessano/PowerShell/blob/master/SQL-ConfigureDatabaseMirroring.ps1
 
        .PARAMETER Primary
            SQL Server name or SMO object representing the primary SQL Server.
 
        .PARAMETER PrimarySqlCredential
            Login to the primary instance using alternative credentials. Windows and SQL Authentication supported. Accepts credential objects (Get-Credential)
 
        .PARAMETER Mirror
            SQL Server name or SMO object representing the mirror SQL Server.
 
        .PARAMETER MirrorSqlCredential
            Login to the target instance using alternative credentials. Windows and SQL Authentication supported. Accepts credential objects (Get-Credential)
 
        .PARAMETER Witness
            SQL Server name or SMO object representing the witness SQL Server.
 
        .PARAMETER WitnessSqlCredential
            Login to the target instance using alternative credentials. Windows and SQL Authentication supported. Accepts credential objects (Get-Credential)
 
        .PARAMETER Database
                The database or databases to mirror
 
        .PARAMETER SharedPath
                The network share where the backups will be
 
        .PARAMETER InputObject
                Enables piping from Get-DbaDatabase
 
        .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: Mirror, HA
            Author: Chrissy LeMaire (@cl), netnerds.net
            dbatools PowerShell module (https://dbatools.io)
           Copyright: (c) 2018 by dbatools, licensed under MIT
            License: MIT https://opensource.org/licenses/MIT
        .EXAMPLE
            PS C:\> $params = @{
                    Primary = 'sql2017a'
                    Mirror = 'sql2017b'
                    MirrorSqlCredential = 'sqladmin'
                    Witness = 'sql2019'
                    Database = 'onthewall'
                    SharedPath = '\\nas\sql\share'
                }
 
            PS C:\> Invoke-DbMirrorValidation @params
 
            Do things
 
       #>

    [CmdletBinding()]
    param (
        [DbaInstanceParameter]$Primary,
        [PSCredential]$PrimarySqlCredential,
        [parameter(Mandatory)]
        [DbaInstanceParameter[]]$Mirror,
        [PSCredential]$MirrorSqlCredential,
        [DbaInstanceParameter]$Witness,
        [PSCredential]$WitnessSqlCredential,
        [string[]]$Database,
        [string]$SharedPath,
        [parameter(ValueFromPipeline)]
        [Microsoft.SqlServer.Management.Smo.Database[]]$InputObject,
        [switch]$EnableException
    )
    process {
        if ((Test-Bound -ParameterName Primary) -and (Test-Bound -Not -ParameterName Database)) {
            Stop-Function -Message "Database is required when SqlInstance is specified"
            return
        }

        if ($Primary) {
            $InputObject += Get-DbaDatabase -SqlInstance $Primary -SqlCredential $SqlCredential -Database $Database
        }

        foreach ($db in $InputObject) {
            $server = $db.Parent
            $dbname = $db.Name
            $canmirror = $true
            $dest = Connect-DbaInstance -SqlInstance $Mirror -SqlCredential $MirrorSqlCredential

            $endpoints = @()
            $endpoints += Get-DbaEndpoint -SqlInstance $server | Where-Object EndpointType -eq DatabaseMirroring
            $endpoints += Get-DbaEndpoint -SqlInstance $dest | Where-Object EndpointType -eq DatabaseMirroring

            if (Test-Bound -ParameterName Witness) {
                try {
                    $witserver = Connect-DbaInstance -SqlInstance $Witness -SqlCredential $WitnessSqlCredential
                    $endpoints += Get-DbaEndpoint -SqlInstance $witserver | Where-Object EndpointType -eq DatabaseMirroring
                    $witdb = Get-DbaDatabase -SqlInstance $witserver -Database $db.Name
                    $wexists = $true

                    if ($witdb.Status -ne 'Restoring') {
                        $canmirror = $false
                    }

                    if ($witdb) {
                        $witexists = $true
                    } else {
                        Write-Message -Level Verbose -Message "Database ($dbname) exists on witness server"
                        $canmirror = $false
                        $witexists = $false
                    }
                } catch {
                    $wexists = $false
                    $canmirror = $false
                }
            }

            if ($db.MirroringStatus -ne [Microsoft.SqlServer.Management.Smo.MirroringStatus]::None) {
                Write-Message -Level Verbose -Message "Cannot setup mirroring on database ($dbname) due to its current mirroring state: $($db.MirroringStatus)"
                $canmirror = $false
            }

            if ($db.Status -ne [Microsoft.SqlServer.Management.Smo.DatabaseStatus]::Normal) {
                Write-Message -Level Verbose -Message "Cannot setup mirroring on database ($dbname) due to its current Status: $($db.Status)"
                $canmirror = $false
            }

            if ($db.RecoveryModel -ne 'Full') {
                Write-Message -Level Verbose -Message "Cannot setup mirroring on database ($dbname) due to its current recovery model: $($db.RecoveryModel)"
                $canmirror = $false
            }

            $destdb = Get-DbaDatabase -SqlInstance $dest -Database $db.Name

            if ($destdb.RecoveryModel -ne 'Full') {
                $canmirror = $false
            }

            if ($destdb.Status -ne 'Restoring') {
                $canmirror = $false
            }

            if ($destdb) {
                $destdbexists = $true
            } else {
                Write-Message -Level Verbose -Message "Database ($dbname) does not exist on mirror server"
                $canmirror = $false
                $destdbexists = $false
            }

            if ((Test-Bound -ParameterName SharedPath) -and -not (Test-DbaPath -SqlInstance $dest -Path $SharedPath)) {
                Write-Message -Level Verbose -Message "Cannot access $SharedPath from $($destdb.Parent.Name)"
                $canmirror = $false
                $nexists = $false
            } else {
                $nexists = $true
            }

            if ($server.EngineEdition -ne $dest.EngineEdition) {
                Write-Message -Level Verbose -Message "This mirroring configuration is not supported. Because the principal server instance, $server, is $($server.EngineEdition) Edition, the mirror server instance must also be $($server.EngineEdition) Edition."
                $canmirror = $false
                $edition = $false
            } else {
                $edition = $true
            }

            # There's a better way to do this but I'm sleepy
            if ((Test-Bound -ParameterName Witness)) {
                if ($endpoints.Count -eq 3) {
                    $endpointpass = $true
                } else {
                    $endpointpass = $false
                }
            } else {
                if ($endpoints.Count -eq 2) {
                    $endpointpass = $true
                } else {
                    $endpointpass = $false
                }
            }

            $results = [pscustomobject]@{
                Primary                 = $Primary
                Mirror                  = $Mirror
                Witness                 = $Witness
                Database                = $db.Name
                RecoveryModel           = $db.RecoveryModel
                MirroringStatus         = $db.MirroringStatus
                State                   = $db.Status
                EndPoints               = $endpointpass
                DatabaseExistsOnMirror  = $destdbexists
                DatabaseExistsOnWitness = $witexists
                OnlineWitness           = $wexists
                EditionMatch            = $edition
                AccessibleShare         = $nexists
                DestinationDbStatus     = $destdb.Status
                WitnessDbStatus         = $witdb.Status
                ValidationPassed        = $canmirror
            }

            if ((Test-Bound -ParameterName Witness)) {
                $results | Select-DefaultView -Property Primary, Mirror, Witness, Database, RecoveryModel, MirroringStatus, State, EndPoints, DatabaseExistsOnMirror, OnlineWitness, DatabaseExistsOnWitness, EditionMatch, AccessibleShare, DestinationDbStatus, WitnessDbStatus, ValidationPassed
            } else {
                $results | Select-DefaultView -Property Primary, Mirror, Database, RecoveryModel, MirroringStatus, State, EndPoints, DatabaseExistsOnMirror, EditionMatch, AccessibleShare, DestinationDbStatus, ValidationPassed
            }
        }
    }
}