internal/functions/New-DbaLogShippingPrimaryDatabase.ps1
|
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 ThresholdAlert 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) 2018 by dbatools, licensed under MIT 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, ConfirmImpact = "Low")] param ( [parameter(Mandatory)] [Alias("ServerInstance", "SqlServer")] [object]$SqlInstance, [System.Management.Automation.PSCredential]$SqlCredential, [Parameter(Mandatory)] [ValidateNotNullOrEmpty()] [object]$Database, [Parameter(Mandatory)] [ValidateNotNullOrEmpty()] [string]$BackupDirectory, [Parameter(Mandatory)] [ValidateNotNullOrEmpty()] [string]$BackupJob, [Parameter(Mandatory)] [int]$BackupRetention, [Parameter(Mandatory)] [ValidateNotNullOrEmpty()] [string]$BackupShare, [int]$BackupThreshold = 60, [switch]$CompressBackup, [int]$ThresholdAlert = 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 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 (-not $MonitorServer -and $Force) { Write-Message -Message "Setting monitor server to $SqlInstance." -Level Verbose $MonitorServer = $SqlInstance } # 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 ,@backup_threshold = $BackupThreshold ,@history_retention_period = $HistoryRetention ,@backup_job_id = @LS_BackupJobId OUTPUT ,@primary_id = @LS_PrimaryId OUTPUT " if ($SqlInstance.Version.Major -gt 9) { $Query += ",@backup_compression = $BackupCompression" } if ($MonitorServer) { $Query += " ,@monitor_server = N'$MonitorServer' ,@monitor_server_security_mode = $MonitorServerSecurityMode ,@threshold_alert = $ThresholdAlert ,@threshold_alert_enabled = $ThresholdAlertEnabled" #if ($MonitorServer -and ($SqlInstance.Version.Major -ge 16)) { if ($server.Version.Major -ge 12) { # 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' " } } else { $Query += " ,@ignoreremotemonitor = 1" } } if ($Force -or ($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 Verbose Write-Message -Message "Executing query:`n$Query" -Level Verbose $server.Query($Query) # For versions prior to SQL Server 2014, adding a monitor works in a different way. # The next section makes sure the settings are being synchronized with earlier versions if ($MonitorServer -and ($server.Version.Major -lt 12)) { # Get the details of the primary database $query = "SELECT * FROM msdb.dbo.log_shipping_monitor_primary WHERE primary_database = '$Database'" $lsDetails = $server.Query($query) # Setup the procedure script for adding the monitor for the primary $query = "EXEC msdb.dbo.sp_processlogshippingmonitorprimary @mode = $MonitorServerSecurityMode ,@primary_id = '$($lsDetails.primary_id)' ,@primary_server = '$($lsDetails.primary_server)' ,@monitor_server = '$MonitorServer' " # 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' " } $query += " ,@monitor_server_security_mode = 1 ,@primary_database = '$($lsDetails.primary_database)' ,@backup_threshold = $($lsDetails.backup_threshold) ,@threshold_alert = $($lsDetails.threshold_alert) ,@threshold_alert_enabled = $([int]$lsDetails.threshold_alert_enabled) ,@history_retention_period = $($lsDetails.history_retention_period) " Write-Message -Message "Configuring monitor server for primary database $Database." -Level Verbose Write-Message -Message "Executing query:`n$query" -Level Verbose Invoke-DbaQuery -SqlInstance $MonitorServer -SqlCredential $MonitorCredential -Database msdb -Query $query $query = " UPDATE msdb.dbo.log_shipping_primary_databases SET monitor_server = '$MonitorServer', user_specified_monitor = 1 WHERE primary_id = '$($lsDetails.primary_id)' " Write-Message -Message "Updating monitor information for the primary database $Database." -Level Verbose 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 Verbose } |