DSCResources/MSFT_xSQLServerDatabaseDefaultLocation/MSFT_xSQLServerDatabaseDefaultLocation.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
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
Import-Module -Name (Join-Path -Path (Split-Path (Split-Path $PSScriptRoot -Parent) -Parent) `
        -ChildPath 'xSQLServerHelper.psm1') `
    -Force

Import-Module -Name (Join-Path -Path (Split-Path -Path $PSScriptRoot -Parent) `
                               -ChildPath 'CommonResourceHelper.psm1')

$script:localizedData = Get-LocalizedData -ResourceName 'MSFT_xSQLServerDatabaseDefaultLocation'

<#
    .SYNOPSIS
        Returns the current path to the the desired default location for the Data, Log, or Backup files.
 
    .PARAMETER SQLServer
        The host name of the SQL Server to be configured.
 
    .PARAMETER SQLInstanceName
        The name of the SQL instance to be configured.
 
    .PARAMETER Type
        The type of database default location to be configured. { Data | Log | Backup }
 
    .PARAMETER Path
        The path to the default directory to be configured.
        Not used in Get-TargetResource
#>

Function Get-TargetResource
{
    [CmdletBinding()]
    [OutputType([System.Collections.Hashtable])]
    param
    (
        [Parameter(Mandatory = $true)]
        [ValidateNotNullOrEmpty()]
        [System.String]
        $SQLServer,

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

        [Parameter(Mandatory = $true)]
        [ValidateSet('Data', 'Log', 'Backup')]
        [System.String]
        $Type,

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

    Write-Verbose -Message ($script:localizedData.GetCurrentPath -f $Type, $SQLInstanceName)

    # Connect to the instance
    $sqlServerObject = Connect-SQL -SQLServer $SQLServer -SQLInstanceName $SQLInstanceName

    # Is this node actively hosting the SQL instance?
    $isActiveNode = Test-ActiveNode -ServerObject $sqlServerObject

    # Check which default location is being retrieved
    switch ($Type)
    {
        'Data'
        {
            $currentPath = $sqlServerObject.DefaultFile
        }

        'Log'
        {
            $currentPath = $sqlServerObject.DefaultLog
        }

        'Backup'
        {
            $currentPath = $sqlServerObject.BackupDirectory
        }
    }

    return @{
        SqlInstanceName     = $SQLInstanceName
        SqlServer           = $SQLServer
        Type                = $Type
        Path                = $currentPath
        IsActiveNode        = $isActiveNode
    }
}

<#
    .SYNOPSIS
        This function sets the current path for the default SQL Instance location for the Data, Log, or Backups files.
 
    .PARAMETER SQLServer
        The host name of the SQL Server to be configured.
 
    .PARAMETER SQLInstanceName
        The name of the SQL instance to be configured.
 
    .PARAMETER Type
        The type of database default location to be configured. { Data | Log | Backup }
 
    .PARAMETER Path
        The path to the default directory to be configured.
 
    .PARAMETER RestartService
        If set to $true then SQL Server and dependent services will be restarted if a change to the default location
        is made. The default value is $false.
 
    .PARAMETER ProcessOnlyOnActiveNode
        Specifies that the resource will only determine if a change is needed if the target node is the active host of the SQL Server Instance.
        Not used in Set-TargetResource.
#>

Function Set-TargetResource
{
    [CmdletBinding()]
    param
    (
        [Parameter(Mandatory = $true)]
        [ValidateNotNullOrEmpty()]
        [System.String]
        $SQLServer,

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

        [Parameter(Mandatory = $true)]
        [ValidateSet('Data', 'Log', 'Backup')]
        [System.String]
        $Type,

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

        [Parameter()]
        [System.Boolean]
        $RestartService = $false,

        [Parameter()]
        [System.Boolean]
        $ProcessOnlyOnActiveNode
    )

    # Make sure the Path exists, needs to be cluster aware as well for this check
    if (-Not (Test-Path $Path))
    {
        throw ($script:localizedData.InvalidPath -f $Path)
    }
    else
    {
        Write-Verbose -Message ($script:localizedData.SettingDefaultPath -f $Type)
        $sqlServerObject = Connect-SQL -SQLServer $SQLServer -SQLInstanceName $SQLInstanceName

        # Check which default location is being updated
        switch ($Type)
        {
            'Data'
            {
                $currentValuePath = $sqlServerObject.DefaultFile
                $sqlServerObject.DefaultFile = $Path
            }

            'Log'
            {
                $currentValuePath = $sqlServerObject.DefaultLog
                $sqlServerObject.DefaultLog = $Path
            }

            'Backup'
            {
                $currentValuePath = $sqlServerObject.BackupDirectory
                $sqlServerObject.BackupDirectory = $Path
            }
        }

        # Wrap the Alter command in a try-catch in case the update doesn't work
        try
        {
            $originalErrorActionPreference = $ErrorActionPreference
            $ErrorActionPreference = 'Stop'
            $sqlServerObject.Alter()
            Write-Verbose -Message ($script:localizedData.DefaultPathChanged -f $Type, $currentValuePath, $Path)

            if ($RestartService)
            {
                Write-Verbose -Message ($script:localizedData.RestartSqlServer -f $SqlServer, $SQLInstanceName)
                Restart-SqlService -SQLServer $SQLServer -SQLInstanceName $SQLInstanceName
            }
        }
        catch
        {
            $errorMessage = $script:localizedData.ChangingPathFailed
            New-InvalidOperationException -Message $errorMessage -ErrorRecord $_
        }
        finally
        {
            $ErrorActionPreference = $originalErrorActionPreference
        }
    }
}

<#
    .SYNOPSIS
        This function tests the current path to the default database location for the Data, Log, or Backups files.
 
    .PARAMETER SQLServer
        The host name of the SQL Server to be configured.
 
    .PARAMETER SQLInstanceName
        The name of the SQL instance to be configured.
 
    .PARAMETER Type
        The type of database default location to be configured. { Data | Log | Backup }
 
    .PARAMETER Path
        The path to the default directory to be configured.
 
    .PARAMETER RestartService
        If set to $true then SQL Server and dependent services will be restarted if a change to the default location
        is made. The default value is $false.
 
    .PARAMETER ProcessOnlyOnActiveNode
        Specifies that the resource will only determine if a change is needed if the target node is the active host of the SQL Server Instance.
#>

function Test-TargetResource
{
    [CmdletBinding()]
    [OutputType([System.Boolean])]
    param
    (
        [Parameter(Mandatory = $true)]
        [ValidateNotNullOrEmpty()]
        [System.String]
        $SQLServer,

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

        [Parameter(Mandatory = $true)]
        [ValidateSet('Data', 'Log', 'Backup')]
        [System.String]
        $Type,

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

        [Parameter()]
        [System.Boolean]
        $RestartService = $false,

        [Parameter()]
        [System.Boolean]
        $ProcessOnlyOnActiveNode
    )

    Write-Verbose -Message ($script:localizedData.TestingCurrentPath -f $Type)

    $getTargetResourceParameters = @{
        SQLInstanceName     = $SQLInstanceName
        SQLServer           = $SQLServer
        Type                = $Type
        Path                = $Path
    }

    $isDefaultPathInDesiredState = $true

    $getTargetResourceResult = Get-TargetResource @getTargetResourceParameters
    <#
        If this is supposed to process only the active node, and this is not the
        active node, don't bother evaluating the test.
    #>

    if ( $ProcessOnlyOnActiveNode -and -not $getTargetResourceResult.IsActiveNode )
    {
        Write-Verbose -Message ($script:localizedData.NotActiveClusterNode -f $env:COMPUTERNAME,$SQLInstanceName )
    }
    elseif ($getTargetResourceResult.Path -ne $Path)
    {
        Write-Verbose -Message ($script:localizedData.DefaultPathDifference -f $Type, $getTargetResourceResult.Path, $Path)
        $isDefaultPathInDesiredState = $false
    }

    return $isDefaultPathInDesiredState
}