--- PDF PAGE 557 ---
546
T-SQL Configuration
To configure log shipping through T-SQL, we need to run a number of system stored 
procedures. The first of these procedures is sp_add_log_shipping_primary_database, 
which we use to configure the backup job and monitor the primary database. The 
parameters used by this procedure are described in Table 15-1.
Figure 15-10.  The Save Log Shipping Configuration page
Table 15-1.  sp_add_log_shipping_primary_database Parameters
Parameter
Description
@database
The name of the database for which you are configuring log shipping.
@backup_directory
The local path to the backup folder.
@backup_share
The network path to the backup folder.
@backup_job_name
The name to use for the job that backs up the log.
@backup_retention_
period
The duration that log backups should be kept for, specified in minutes.
@monitor_server
The server\instance name of the monitor server.
@monitor_server_
Security_mode
The authentication mode to use to connect to the monitor server. 0 is 
SQL authentication and 1 is Windows authentication.
(continued)
Chapter 15  Implementing Log Shipping

--- PDF PAGE 558 ---
547
Listing 15-2 demonstrates how we can use the sp_add_log_shipping_primary_
database procedure to configure Chapter15 for log shipping. This script uses the  
@backup_job_id output parameter to pass the job’s GUID into the sp_update_job 
stored procedure. It also uses the sp_add_schedule and sp_attach_schedule system 
stored procedures to create the job schedule and attach it to the job. Because configuring 
log shipping involves connecting to multiple instances, we have added a connection to 
the primary instance. This means that we should run the script in SQLCMD mode.
Note  sp_update_job, sp_add_schedule, and sp_attach_schedule are 
system stored procedures used to manipulate SQL Server Agent objects. A full 
discussion of SQL Server Agent can be found in Chapter 22.
Parameter
Description
@monitor_server_login The account used to connect to the monitor server (only use if SQL 
authentication is specified).
@monitor_server_
password
The password of the account used to connect to the monitor server 
(only use if SQL authentication is specified).
@backup_threshold
The amount of time that can elapse, without a log backup being 
taken, before an alert is triggered.
@threshold_alert
The alert to be raised if the backup threshold is exceeded.
@threshold_alert_
enabled
Specifies if an alert should be fired. 0 disables the alert, 1 enables it.
@history_retention_
period
The duration for which the log backup job history will be retained, 
specified in minutes.
@backup_job_id
An OUTPUT parameter that specifies the GUID of the backup job that 
is created by the procedure.
@primary_id
An OUTPUT parameter that specifies the ID of the primary database.
@backup_compression
Specifies if backup compression should be used. 0 means disabled, 1 
means enabled, and 2 means use the instance's default configuration.
Table 15-1.  (continued)
Chapter 15  Implementing Log Shipping

--- PDF PAGE 560 ---
549
                ,@active_end_time = 235900
                ,@schedule_id = @LS_BackUpScheduleID OUTPUT ;
--Attach the job schedule to the job
EXEC msdb.dbo.sp_attach_schedule
                 @job_id = @LS_BackupJobId
                ,@schedule_id = @LS_BackUpScheduleID  ;
--Enable the backup job
EXEC msdb.dbo.sp_update_job
                 @job_id = @LS_BackupJobId
                ,@enabled = 1 ;
We use the sp_add_log_shipping_primary_secondary system stored procedure to 
update the metadata on the primary server in order to add a record for each secondary 
server in the Log Shipping topology. The parameters that it accepts are described in 
Table 15-2.
Listing 15-3 demonstrates how we can use the sp_add_log_shipping_primary_
secondary procedure to add a record of our DRSERVER\PROSQLDR instance to our primary 
server. Again, we specifically connect to the primary server, meaning that the script 
should run in SQLCMS mode.
Listing 15-3.  sp_add_log_shipping_primary_secondary
:connect primaryserver\prosqladmin
EXEC master.dbo.sp_add_log_shipping_primary_secondary
                 @primary_database = N'Chapter15'
                ,@secondary_server = N'drserver\prosqldr'
                ,@secondary_database = N'Chapter15'
Table 15-2.  sp_add_log_shipping_primary_secondary Parameters
Parameter
Description
@primary_database
The name of the primary database
@secondary_server
The server\instance of the secondary server
@secondary_database
The name of the database on the secondary server
Chapter 15  Implementing Log Shipping

--- PDF PAGE 561 ---
550
We now need to configure our DR server. The first task in this process is to run the 
sp_add_log_shipping_secondary_primary system stored procedure. This procedure 
creates the SQL Server Agent jobs that copy the transaction logs to the secondary server 
and restore them. It also configures monitoring. The parameters accepted by this stored 
procedure are detailed in Table 15-3.
Table 15-3.  sp_add_log_shipping_secondary_primary Parameters
Parameter
Description
@primary_server
The server\instance name of the primary server.
@primary_database
The name of the primary database.
@backup_source_directory The folder that the log backups are copied from.
@backup_destination_
directory
The folder that the log backups are copied to.
@copy_job_name
The name that is given to the SQL Server Agent job used to copy 
the transaction logs.
@restore_job_name
The name that is given to the SQL Server Agent job used to restore 
the transaction logs.
@file_retention_period
The duration for which log backup history should be retained, 
specified in minutes.
@monitor_server
The server\instance name of the monitor server.
@monitor_server_
security_mode
The authentication mode to be used to connect to the monitor 
server. 0 is SQL authentication and 1 is Windows authentication.
@monitor_server_login
The account used to connect to the monitor server (only use if SQL 
authentication is specified).
@monitor_server_
password
The password of the account used to connect to the monitor 
server (only use if SQL authentication is specified).
@copy_job_id
OUTPUT parameter that specifies the GUID of the job that has been 
created to copy the transaction logs.
@restore_job_id
OUTPUT parameter that specifies the GUID of the job that has been 
created to restore the transaction logs.
@secondary_id
An OUTPUT parameter that specifies the ID of secondary database.
Chapter 15  Implementing Log Shipping

--- PDF PAGE 564 ---
553
--Attch the schedule to the restore job
EXEC msdb.dbo.sp_attach_schedule
                 @job_id = @LS_Secondary__RestoreJobId
                ,@schedule_id = @LS_SecondaryRestoreJobScheduleID  ;
--Enable the jobs
EXEC msdb.dbo.sp_update_job
                 @job_id = @LS_Secondary__CopyJobId
                ,@enabled = 1 ;
EXEC msdb.dbo.sp_update_job
                 @job_id = @LS_Secondary__RestoreJobId
                ,@enabled = 1 ;
Our next step is to configure the secondary database. We can perform this task 
by using the sp_add_log_shipping_secondary_database stored procedure. The 
parameters accepted by this procedure are detailed in Table 15-4.
Table 15-4.  sp_add_log_shipping_secondary_database Paremeters
Parameter
Description
@secondary_database
The name of the secondary database.
@primary_server
The server\instance of the primary server.
@primary_database
The name of the primary database.
@restore_delay
Specifies the load delay, in minutes.
@restore_all
When set to 1, the restore job restores all available log backups.  
When set to 0, the restore job only applies a single log backup.
@restore_mode
Specifies the backup mode for the restore job to use. 1 means 
STANDBY and 0 means NORECOVERY.
@disconnect_users
Determines if users should be disconnected from the database while 
transaction log backups are being applied. 1 means that they are and  
0 means that they are not. Only applies when restoring logs in 
STANDBY mode.
(continued)
Chapter 15  Implementing Log Shipping

--- PDF PAGE 565 ---
554
Listing 15-5 demonstrates how we can use the sp_add_log_shipping_secondary_
database to configure our secondary database for log shipping. Since we are explicitly 
connecting to the DRSERVER\PROSQLDR instance, the script should run in SQLCMD mode.
Listing 15-5.  sp_add_log_shipping_secondary_database
:connect drserver\prosqldr
EXEC master.dbo.sp_add_log_shipping_secondary_database
                 @secondary_database = N'Chapter15'
                ,@primary_server = N'primaryserver\prosqladmin'
                ,@primary_database = N'Chapter15'
                ,@restore_delay = 10
                ,@restore_mode = 0
                ,@disconnect_users         = 0
                ,@restore_threshold        = 30
                ,@threshold_alert_enabled  = 1
Parameter
Description
@block_size
Specifies the block size for the backup device, in bytes.
@buffer_count
Specifies the total number of memory buffers that can be used by a 
restore operation.
@max_transfer_size
Specifies the maximum size of the request that can be sent to the 
backup device, in bytes.
@restore_threshold
The amount of time that can elapse, without a restore being applied, 
before an alert is generated; specified in minutes.
@threshold_alert
The alert to be raised if the restore threshold is exceeded.
@threshold_alert_
enabled
Specifies if the alert is enabled. 1 means that it is enabled and 0 
means that it is disabled.
@history_retention_
period
The retention period of the restore history, specified in minutes.
@Ignoreremotemonitor An undocumented parameter that partially controls how the internal log 
shipping database journal is updated.
Table 15-4.  (continued)
Chapter 15  Implementing Log Shipping

--- PDF PAGE 566 ---
555
                ,@history_retention_period = 5760
                ,@ignoreremotemonitor = 1
The final task is to synchronize the monitor server and the DR server. We do this by 
using the (surprisingly) undocumented stored procedure sp_processlogshipping 
monitorsecondary. The parameters accepted by this procedure are detailed in Table 15-5.
Table 15-5.  sp_processlogshippingmonitorsecondary
Parameter
Description
@mode
The recovery mode to use for the database. 0 indicates 
NORECOVERY and 1 indicates STANDBY.
@secondary_server
The server\instance of the secondary server.
@secondary_database
The name of the secondary database.
@secondary_id
The ID of the secondary server.
@primary_server
The server\instance of the primary server.
@monitor_server
The server\instance of the monitor server.
@monitor_server_
security_mode
The authentication mode used to connect to the monitor server.
@primary_database
The name of the primary database.
@restore_threshold
The amount of time that can elapse without a restore being 
applied before an alert is triggered; specified in minutes.
@threshold_alert
The alert that fires if the alert restore threshold is exceeded.
@threshold_alert_enabled
Specifies if the alert is enabled or disabled.
@last_copied_file
The file name of the last log backup to be copied to the 
secondary server.
@last_copied_date
The date and time of the last time a log was copied to the 
secondary server.
@last_copied_date_utc
The date and time of the last time a log was copied to the 
secondary server, converted to UTC (Coordinated Universal Time).
@last_restored_file
The file name of the last transaction log backup to be restored on 
the secondary server.
(continued)
Chapter 15  Implementing Log Shipping

--- PDF PAGE 567 ---
556
The script in Listing 15-6 demonstrates how to use the sp_processlogshipping 
monitorsecondary stored procedure to synchronize the information between our DR 
server and our monitor server. We should run the script against the monitor server, and 
since we are connecting explicitly to the MONITORSERVER\PROSQLMONITOR instance, we 
should run the script in SQLCMD mode.
Listing 15-6.  sp_processlogshippingmonitorsecondary
:connect monitorserver\prosqlmonitor
EXEC msdb.dbo.sp_processlogshippingmonitorsecondary
                 @mode = 1
                ,@secondary_server = N'drserver\prosqldr'
                ,@secondary_database = N'Chapter15'
                ,@secondary_id = N''
                ,@primary_server = N'primaryserver\prosqladmin'
                ,@primary_database = N'Chapter15'
                ,@restore_threshold = 30
                ,@threshold_alert = 14420
                ,@threshold_alert_enabled = 1
                ,@history_retention_period        = 5760
                ,@monitor_server = N'monitorserver.prosqladmin.comprosqlmonitor'
                ,@monitor_server_security_mode = 1
Table 15-5.  (continued)
Parameter
Description
@last_restored_date
The date and time of the last time a log was restored on the 
secondary server.
@last_restored_date_utc
The date and time of the last time a log was restored on the 
secondary server, converted to UTC.
@last_restored_latency
The elapsed time between the last log backup on the primary and 
its corresponding restore operation completing on the secondary.
@history_retention_period The duration that the history is retained, specified in minutes.
Chapter 15  Implementing Log Shipping