DSCResources/MSFT_xSQLServerAlwaysOnService/MSFT_xSQLServerAlwaysOnService.psm1

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
Import-Module -Name (Join-Path -Path (Split-Path (Split-Path $PSScriptRoot -Parent) -Parent) `
        -ChildPath 'xSQLServerHelper.psm1') `
    -Force

<#
    .SYNOPSIS
    Gets the current value of the SQL Server Always On high availability and
    disaster recovery (HADR) property.
 
    .PARAMETER Ensure
    An enumerated value that describes if the SQL Server should have Always On high
    availability and disaster recovery (HADR) property enabled ('Present') or
    disabled ('Absent').
 
    *** Not used in this function ***
 
    .PARAMETER SQLServer
    The hostname of the SQL Server to be configured.
 
    .PARAMETER SQLInstanceName
    The name of the SQL instance to be configured.
#>

function Get-TargetResource
{
    [CmdletBinding()]
    [OutputType([System.Collections.Hashtable])]
    param
    (
        [Parameter(Mandatory = $true)]
        [ValidateSet('Present', 'Absent')]
        [System.String]
        $Ensure,

        [Parameter(Mandatory = $true)]
        [System.String]
        $SQLServer,

        [Parameter(Mandatory = $true)]
        [System.String]
        $SQLInstanceName
    )

    $sqlServerObject = Connect-SQL -SQLServer $SQLServer -SQLInstanceName $SQLInstanceName

    $isAlwaysOnEnabled = [System.Boolean] $sqlServerObject.IsHadrEnabled
    if ($isAlwaysOnEnabled -eq $true)
    {
        $statusString = 'enabled'
    }
    elseif ($isAlwaysOnEnabled -eq $false)
    {
        $statusString = 'disabled'
    }

    New-VerboseMessage -Message ( 'SQL Always On is {0} on "{1}\{2}".' -f $statusString, $SQLServer, $SQLInstanceName )

    return @{
        IsHadrEnabled = $isAlwaysOnEnabled
    }
}

<#
    .SYNOPSIS
    Sets the current value of the SQL Server Always On high availability and disaster recovery (HADR) property.
 
    .PARAMETER Ensure
    An enumerated value that describes if the SQL Server should have Always On high
    availability and disaster recovery (HADR) property enabled ('Present') or
    disabled ('Absent').
 
    .PARAMETER SQLServer
    The hostname of the SQL Server to be configured.
 
    .PARAMETER SQLInstanceName
    The name of the SQL instance to be configured.
 
    .PARAMETER RestartTimeout
    The length of time, in seconds, to wait for the service to restart. Default is 120 seconds.
#>

function Set-TargetResource
{
    [CmdletBinding()]
    param
    (
        [Parameter(Mandatory = $true)]
        [ValidateSet('Present', 'Absent')]
        [System.String]
        $Ensure,

        [Parameter(Mandatory = $true)]
        [System.String]
        $SQLServer,

        [Parameter(Mandatory = $true)]
        [System.String]
        $SQLInstanceName,

        [Parameter()]
        [System.Uint32]
        $RestartTimeout = 120
    )

    # Build the instance name to allow the Enable/Disable-Always On to connect to the instance
    if ($SQLInstanceName -eq "MSSQLSERVER")
    {
        $serverInstance = $SQLServer
    }
    else
    {
        $serverInstance = "$SQLServer\$SQLInstanceName"
    }

    Import-SQLPSModule

    switch ($Ensure)
    {
        'Absent'
        {
            # Disable Always On without restarting the services.
            New-VerboseMessage -Message "Disabling Always On for the instance $serverInstance"
            Disable-SqlAlwaysOn -ServerInstance $serverInstance -NoServiceRestart
        }
        'Present'
        {
            # Enable Always On without restarting the services.
            New-VerboseMessage -Message "Enabling Always On for the instance $serverInstance"
            Enable-SqlAlwaysOn -ServerInstance $serverInstance -NoServiceRestart
        }
    }

    New-VerboseMessage -Message ( 'SQL Always On has been {0} on "{1}\{2}". Restarting the service.' -f @{Absent = 'disabled'; Present = 'enabled'}[$Ensure], $SQLServer, $SQLInstanceName )

    # Now restart the SQL service so that all dependent services are also returned to their previous state
    Restart-SqlService -SQLServer $SQLServer -SQLInstanceName $SQLInstanceName -Timeout $RestartTimeout

    # Verify always on was set
    if ( -not ( Test-TargetResource @PSBoundParameters ) )
    {
        throw New-TerminatingError -ErrorType AlterAlwaysOnServiceFailed -FormatArgs $Ensure, $serverInstance -ErrorCategory InvalidResult
    }
}

<#
    .SYNOPSIS
    Determines whether the current value of the SQL Server Always On high
    availability and disaster recovery (HADR) property is properly set.
 
    .PARAMETER Ensure
    An enumerated value that describes if the SQL Server should have Always On high
    availability and disaster recovery (HADR) property enabled ('Present') or
    disabled ('Absent').
 
    .PARAMETER SQLServer
    The hostname of the SQL Server to be configured.
 
    .PARAMETER SQLInstanceName
    The name of the SQL instance to be configured.
 
    .PARAMETER RestartTimeout
    The length of time, in seconds, to wait for the service to restart. Default is 120 seconds.
 
    *** Not used in this function ***
#>

function Test-TargetResource
{
    [CmdletBinding()]
    [OutputType([System.Boolean])]
    param
    (
        [Parameter(Mandatory = $true)]
        [ValidateSet('Present', 'Absent')]
        [System.String]
        $Ensure,

        [Parameter(Mandatory = $true)]
        [System.String]
        $SQLServer,

        [Parameter(Mandatory = $true)]
        [System.String]
        $SQLInstanceName,

        [Parameter()]
        [System.Uint32]
        $RestartTimeout = 120
    )

    # Determine the current state of Always On
    $getTargetResourceParameters = @{
        Ensure          = $Ensure
        SQLServer       = $SQLServer
        SQLInstanceName = $SQLInstanceName
    }

    $state = Get-TargetResource @getTargetResourceParameters

    # Determine what the desired state of Always On is
    $hadrDesiredState = @{ 'Present' = $true; 'Absent' = $false }[$Ensure]

    # Determine whether the value matches the desired state
    $desiredStateMet = $state.IsHadrEnabled -eq $hadrDesiredState

    New-VerboseMessage -Message ( 'SQL Always On is in the desired state for "{0}\{1}": {2}.' -f $SQLServer, $SQLInstanceName, $desiredStateMet )

    return $desiredStateMet
}

Export-ModuleMember -Function *-TargetResource