internal/functions/New-DbaLogShippingSecondaryDatabase.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
function New-DbaLogShippingSecondaryDatabase {
    <#
        .SYNOPSIS
            New-DbaLogShippingSecondaryDatabase sets up a secondary databases for log shipping.
 
        .DESCRIPTION
            New-DbaLogShippingSecondaryDatabase sets up a secondary databases for log shipping.
            This is executed on the secondary 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 BufferCount
            The total number of buffers used by the backup or restore operation.
            The default is -1.
 
        .PARAMETER BlockSize
            The size, in bytes, that is used as the block size for the backup device.
            The default is -1.
 
        .PARAMETER DisconnectUsers
            If set to 1, users are disconnected from the secondary database when a restore operation is performed.
            Te default is 0.
 
        .PARAMETER HistoryRetention
            Is the length of time in minutes in which the history is retained.
            The default is 14420.
 
        .PARAMETER MaxTransferSize
            The size, in bytes, of the maximum input or output request which is issued by SQL Server to the backup device.
 
        .PARAMETER PrimaryServer
            The name of the primary instance of the Microsoft SQL Server Database Engine in the log shipping configuration.
 
        .PARAMETER PrimaryDatabase
            Is the name of the database on the primary server.
 
        .PARAMETER RestoreAll
            If set to 1, the secondary server restores all available transaction log backups when the restore job runs.
            The default is 1.
 
        .PARAMETER RestoreDelay
            The amount of time, in minutes, that the secondary server waits before restoring a given backup file.
            The default is 0.
 
        .PARAMETER RestoreMode
            The restore mode for the secondary database. The default is 0.
            0 = Restore log with NORECOVERY.
            1 = Restore log with STANDBY.
 
        .PARAMETER RestoreThreshold
            The number of minutes allowed to elapse between restore operations before an alert is generated.
 
        .PARAMETER SecondaryDatabase
            Is the name of the secondary database.
 
        .PARAMETER ThresholdAlert
            Is the alert to be raised when the backup threshold is exceeded.
            The default is 14420.
 
        .PARAMETER ThresholdAlertEnabled
            Specifies whether an alert is raised when backup_threshold is exceeded.
 
        .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-DbaLogShippingSecondaryDatabase -SqlInstance sql2 -SecondaryDatabase DB1_DR -PrimaryServer sql1 -PrimaryDatabase DB1 -RestoreDelay 0 -RestoreMode standby -DisconnectUsers -RestoreThreshold 45 -ThresholdAlertEnabled -HistoryRetention 14420
    #>


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

    param (
        [parameter(Mandatory)]
        [Alias("ServerInstance", "SqlServer")]
        [DbaInstanceParameter]$SqlInstance,
        [PSCredential]$SqlCredential,
        [int]$BufferCount = -1,
        [int]$BlockSize = -1,
        [switch]$DisconnectUsers,
        [int]$HistoryRetention = 14420,
        [int]$MaxTransferSize,
        [Parameter(Mandatory)]
        [ValidateNotNullOrEmpty()]
        [DbaInstanceParameter]$PrimaryServer,
        [PSCredential]$PrimarySqlCredential,
        [Parameter(Mandatory)]
        [ValidateNotNullOrEmpty()]
        [object]$PrimaryDatabase,
        [int]$RestoreAll = 1,
        [int]$RestoreDelay = 0,
        [ValidateSet(0, 'NoRecovery', 1, 'Standby')]
        [object]$RestoreMode = 0,
        [Parameter(Mandatory)]
        [ValidateNotNullOrEmpty()]
        [int]$RestoreThreshold,
        [Parameter(Mandatory)]
        [ValidateNotNullOrEmpty()]
        [object]$SecondaryDatabase,
        [int]$ThresholdAlert = 14420,
        [switch]$ThresholdAlertEnabled,
        [Alias('Silent')]
        [switch]$EnableException,
        [switch]$Force
    )

    # Try connecting to the instance
    Write-Message -Message "Connecting to $SqlInstance" -Level Verbose
    try {
        $ServerSecondary = Connect-SqlInstance -SqlInstance $SqlInstance -SqlCredential $SqlCredential
    }
    catch {
        Stop-Function -Message "Failure" -Category ConnectionError -Target $SqlInstance -ErrorRecord $_ -Continue
    }

    # Try connecting to the instance
    Write-Message -Message "Connecting to $PrimaryServer" -Level Verbose
    try {
        $ServerPrimary = Connect-SqlInstance -SqlInstance $PrimaryServer -SqlCredential $PrimarySqlCredential
    }
    catch {
        Stop-Function -Message "Failure" -Category ConnectionError -Target $PrimaryServer -ErrorRecord $_ -Continue
    }

    # Check if the database is present on the primary sql server
    if ($ServerPrimary.Databases.Name -notcontains $PrimaryDatabase) {
        Stop-Function -Message "Database $PrimaryDatabase is not available on instance $PrimaryServer" -Target $PrimaryServer -Continue
    }

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

    # Check the restore mode
    if ($RestoreMode -notin 0, 1) {
        $RestoreMode = switch ($RestoreMode) { "NoRecovery" { 0}  "Standby" { 1 } }
        Write-Message -Message "Setting restore mode to $RestoreMode." -Level Verbose
    }

    # 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
    }

    # Checking the option to disconnect users
    if ($DisconnectUsers) {
        [int]$DisconnectUsers = 1
        Write-Message -Message "Setting disconnect users to $DisconnectUsers." -Level Verbose
    }
    else {
        [int]$DisconnectUsers = 0
        Write-Message -Message "Setting disconnect users to $DisconnectUsers." -Level Verbose
    }

    # Check hte combination of the restore mode with the option to disconnect users
    if ($RestoreMode -eq 0 -and $DisconnectUsers -ne 0) {
        if ($Force) {
            [int]$DisconnectUsers = 0
            Write-Message -Message "Illegal combination of database restore mode $RestoreMode and disconnect users $DisconnectUsers. Setting it to $DisconnectUsers." -Level Warning
        }
        else {
            Stop-Function -Message "Illegal combination of database restore mode $RestoreMode and disconnect users $DisconnectUsers." -Target $SqlInstance -Continue
        }
    }

    # Set up the query
    $Query = "EXEC master.sys.sp_add_log_shipping_secondary_database
        @secondary_database = '$SecondaryDatabase'
        ,@primary_server = '$PrimaryServer'
        ,@primary_database = '$PrimaryDatabase'
        ,@restore_delay = $RestoreDelay
        ,@restore_all = $RestoreAll
        ,@restore_mode = $RestoreMode
        ,@disconnect_users = $DisconnectUsers
        ,@restore_threshold = $RestoreThreshold
        ,@threshold_alert = $ThresholdAlert
        ,@threshold_alert_enabled = $ThresholdAlertEnabled
        ,@history_retention_period = $HistoryRetention "


    # Addinf extra options to the query when needed
    if ($BlockSize -ne -1) {
        $Query += ",@block_size = $BlockSize"
    }

    if ($BufferCount -ne -1) {
        $Query += ",@buffer_count = $BufferCount"
    }

    if ($MaxTransferSize -ge 1) {
        $Query += ",@max_transfer_size = $MaxTransferSize"
    }

    if ($ServerSecondary.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 secondary database $SecondaryDatabase on $SqlInstance"))) {
        try {
            Write-Message -Message "Configuring logshipping for secondary database $SecondaryDatabase on $SqlInstance." -Level Verbose
            Write-Message -Message "Executing query:`n$Query" -Level Verbose
            $ServerSecondary.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 secondary database $SecondaryDatabase to log shipping." -Level Verbose

}