internal/functions/New-DbaLogShippingPrimaryDatabase.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
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
function New-DbaLogShippingPrimaryDatabase {
    <#
        .SYNOPSIS
            New-DbaLogShippingPrimaryDatabase add the primary database to log shipping
 
        .DESCRIPTION
            New-DbaLogShippingPrimaryDatabase will add the primary database to log shipping.
            This is executed on the primary server.
 
        .PARAMETER SqlInstance
            SQL Server instance. You must have sysadmin access and server version must be SQL Server version 2000 or greater.
 
        .PARAMETER SqlCredential
            Login to the target instance using alternative credentials. Windows and SQL Authentication supported. Accepts credential objects (Get-Credential)
 
        .PARAMETER Database
            Database to set up log shipping for.
 
        .PARAMETER BackupDirectory
            Is the path to the backup folder on the primary server.
 
        .PARAMETER BackupJob
            Is the name of the SQL Server Agent job on the primary server that copies the backup into the backup folder.
 
        .PARAMETER BackupJobID
            The SQL Server Agent job ID associated with the backup job on the primary server.
 
        .PARAMETER BackupRetention
            Is the length of time, in minutes, to retain the log backup file in the backup directory on the primary server.
 
        .PARAMETER BackupShare
            Is the network path to the backup directory on the primary server.
 
        .PARAMETER BackupThreshold
            Is the length of time, in minutes, after the last backup before a threshold_alert error is raised.
            The default is 60.
 
        .PARAMETER CompressBackup
            Enables the use of backup compression
 
        .PARAMETER ThressAlert
            Is the length of time, in minutes, when the alert is to be raised when the backup threshold is exceeded.
            The default is 14,420.
 
        .PARAMETER HistoryRetention
            Is the length of time in minutes in which the history will be retained.
            The default is 14420.
 
        .PARAMETER MonitorServer
            Is the name of the monitor server.
            The default is the name of the primary server.
 
        .PARAMETER MonitorCredential
            Allows you to login to enter a secure credential.
            This is only needed in combination with MonitorServerSecurityMode having either a 0 or 'sqlserver' value.
            To use: $scred = Get-Credential, then pass $scred object to the -MonitorCredential parameter.
 
        .PARAMETER MonitorServerSecurityMode
            The security mode used to connect to the monitor server. Allowed values are 0, "sqlserver", 1, "windows"
            The default is 1 or Windows.
 
        .PARAMETER ThresholdAlertEnabled
            Specifies whether an alert will be raised when backup threshold is exceeded.
            The default is 0.
 
        .PARAMETER WhatIf
            Shows what would happen if the command were to run. No actions are actually performed.
 
        .PARAMETER Confirm
            Prompts you for confirmation before executing any changing operations within the command.
 
        .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.
 
        .PARAMETER Force
            The force parameter will ignore some errors in the parameters and assume defaults.
            It will also remove the any present schedules with the same name for the specific job.
 
        .NOTES
            Author: Sander Stad (@sqlstad, sqlstad.nl)
            Website: https://dbatools.io
            Copyright: (C) Chrissy LeMaire, clemaire@gmail.com
            License: MIT https://opensource.org/licenses/MIT
 
        .EXAMPLE
            New-DbaLogShippingPrimaryDatabase -SqlInstance sql1 -Database DB1 -BackupDirectory D:\data\logshipping -BackupJob LSBackup_DB1 -BackupRetention 4320 -BackupShare "\\sql1\logshipping" -BackupThreshold 60 -CompressBackup -HistoryRetention 14420 -MonitorServer sql1 -ThresholdAlertEnabled
 
    #>

    [CmdletBinding(SupportsShouldProcess = $true, ConfirmImpact = "Low")]

    param (
        [parameter(Mandatory = $true)]
        [Alias("ServerInstance", "SqlServer")]
        [object]$SqlInstance,

        [System.Management.Automation.PSCredential]
        $SqlCredential,

        [Parameter(Mandatory = $true)]
        [ValidateNotNullOrEmpty()]
        [object]$Database,

        [Parameter(Mandatory = $true)]
        [ValidateNotNullOrEmpty()]
        [string]$BackupDirectory,

        [Parameter(Mandatory = $true)]
        [ValidateNotNullOrEmpty()]
        [string]$BackupJob,

        [Parameter(Mandatory = $true)]
        [int]$BackupRetention,

        [Parameter(Mandatory = $true)]
        [ValidateNotNullOrEmpty()]
        [string]$BackupShare,

        [int]$BackupThreshold = 60,

        [switch]$CompressBackup,

        [int]$ThressAlert = 14420,

        [int]$HistoryRetention = 14420,

        [string]$MonitorServer,

        [ValidateSet(0, "sqlserver", 1, "windows")]
        [object]$MonitorServerSecurityMode = 1,

        [System.Management.Automation.PSCredential]
        $MonitorCredential,

        [switch]$ThresholdAlertEnabled,

        [Alias('Silent')]
        [switch]$EnableException,

        [switch]$Force
    )

    # Try connecting to the instance
    Write-Message -Message "Connecting to $SqlInstance" -Level Verbose
    try {
        $server = Connect-SqlInstance -SqlInstance $SqlInstance -SqlCredential $SqlCredential
    }
    catch {
        Stop-Function -Message "Could not connect to Sql Server instance" -Target $SqlInstance -Continue
    }

    # Check if the backup UNC path is correct and reachable
    if ([bool]([uri]$BackupShare).IsUnc -and $BackupShare -notmatch '^\\(?:\\[^<>:`"/\\|?*]+)+$') {
        Stop-Function -Message "The backup share path $BackupShare should be formatted in the form \\server\share." -Target $SqlInstance
        return
    }
    else {
        if (-not ((Test-Path $BackupShare -PathType Container -IsValid) -and ((Get-Item $BackupShare).PSProvider.Name -eq 'FileSystem'))) {
            Stop-Function -Message "The backup share path $BackupShare is not valid or can't be reached." -Target $SqlInstance
            return
        }
    }

    # Check the backup compression
    if ($CompressBackup -eq $true) {
        Write-Message -Message "Setting backup compression to 1." -Level Verbose
        $BackupCompression = 1
    }
    elseif ($CompressBackup -eq $false) {
        Write-Message -Message "Setting backup compression to 0." -Level Verbose
        $BackupCompression = 0
    }
    elseif (-not $CompressBackup) {
        $defaultCompression = (Get-DbaSpConfigure -SqlInstance $SqlInstance -ConfigName DefaultBackupCompression).ConfiguredValue
        Write-Message -Message "Setting backup compression to default value $defaultCompression." -Level Verbose
        $BackupCompression = $defaultCompression

    }

    # Check of the MonitorServerSecurityMode value is of type string and set the integer value
    if ($MonitorServerSecurityMode -notin 0, 1) {
        $MonitorServerSecurityMode = switch ($MonitorServerSecurityMode) {"WINDOWS" { 1 } "SQLSERVER" { 0 } }
        Write-Message -Message "Setting monitor server security mode to $MonitorServerSecurityMode." -Level Verbose
    }

    # Check the MonitorServer
    if ($Force -and -not $MonitorServer) {
        $MonitorServer = $SqlInstance
        Write-Message -Message "Setting monitor server to $MonitorServer." -Level Verbose
    }

    # Check the MonitorServerSecurityMode if it's SQL Server authentication
    if ($MonitorServerSecurityMode -eq 0 -and -not $MonitorCredential) {
        Stop-Function -Message "The MonitorServerCredential cannot be empty when using SQL Server authentication." -Target $SqlInstance
        return
    }
    elseif ($MonitorServerSecurityMode -eq 0 -and $MonitorCredential) {
        # Get the username and password from the credential
        $MonitorLogin = $MonitorCredential.UserName
        $MonitorPassword = $MonitorCredential.GetNetworkCredential().Password

        # Check if the user is in the database
        if ($server.Databases['master'].Users.Name -notcontains $MonitorLogin) {
            Stop-Function -Message "User $MonitorLogin for monitor login must be in the master database." -Target $SqlInstance
            return
        }
    }

    # Check if the database is present on the source sql server
    if ($server.Databases.Name -notcontains $Database) {
        Stop-Function -Message "Database $Database is not available on instance $SqlInstance" -Target $SqlInstance
        return
    }

    # Check the if Threshold alert needs to be enabled
    if ($ThresholdAlertEnabled) {
        [int]$ThresholdAlertEnabled = 1
        Write-Message -Message "Setting Threshold alert to $ThresholdAlertEnabled." -Level Verbose
    }
    else {
        [int]$ThresholdAlertEnabled = 0
        Write-Message -Message "Setting Threshold alert to $ThresholdAlertEnabled." -Level Verbose
    }

    # Set the log shipping primary
    $Query = "
        DECLARE @LS_BackupJobId AS uniqueidentifier;
        DECLARE @LS_PrimaryId AS uniqueidentifier;
        EXEC master.sys.sp_add_log_shipping_primary_database
            @database = N'$Database'
            ,@backup_directory = N'$BackupDirectory'
            ,@backup_share = N'$BackupShare'
            ,@backup_job_name = N'$BackupJob'
            ,@backup_retention_period = $BackupRetention"


    if ($SqlInstance.Version.Major -gt 9) {
        $Query += ",@backup_compression = $BackupCompression"
    }

    if ($MonitorServer) {
        $Query += ",@monitor_server = N'$MonitorServer'
            ,@monitor_server_security_mode = $MonitorServerSecurityMode
            ,@threshold_alert = $ThressAlert
            ,@threshold_alert_enabled = $ThresholdAlertEnabled"

    }

    $Query += ",@backup_threshold = $BackupThreshold
            ,@history_retention_period = $HistoryRetention
            ,@backup_job_id = @LS_BackupJobId OUTPUT
            ,@primary_id = @LS_PrimaryId OUTPUT "


    # Check the MonitorServerSecurityMode if it's SQL Server authentication
    if ($MonitorServer -and $MonitorServerSecurityMode -eq 0 ) {
        $Query += ",@monitor_server_login = N'$MonitorLogin'
            ,@monitor_server_password = N'$MonitorPassword' "

    }

    if ($server.Version.Major -gt 9) {
        $Query += ",@overwrite = 1;"
    }
    else {
        $Query += ";"
    }

    # Execute the query to add the log shipping primary
    if ($PSCmdlet.ShouldProcess($SqlServer, ("Configuring logshipping for primary database $Database on $SqlInstance"))) {
        try {
            Write-Message -Message "Configuring logshipping for primary database $Database." -Level Output
            Write-Message -Message "Executing query:`n$Query" -Level Verbose
            $server.Query($Query)
        }
        catch {
            Write-Message -Message "$($_.Exception.InnerException.InnerException.InnerException.InnerException.Message)" -Level Warning
            Stop-Function -Message "Error executing the query.`n$($_.Exception.Message)`n$($Query)" -ErrorRecord $_ -Target $SqlInstance -Continue
        }
    }

    Write-Message -Message "Finished adding the primary database $Database to log shipping." -Level Output

}