SQL Server 2014 Log Shipping Deployment (6): monitoring log shipping

13.3 Monitoring Log shipping

After you deploy log shipping, you need to monitor information about the status of the log shipping server. The history and status of log shipping operations are always saved locally by log shipping jobs. The history and state of the backup operation is stored on the primary server, and the history and state of the copy and restore operations are stored on the secondary server. If a remote monitoring server is used, this information is also stored on the monitoring server.

If a monitoring server is configured, the monitor server will run two alert jobs, one to monitor the primary DB instance and one to monitor the secondary server;

If a monitoring server is not specified, the alert job runs on the primary server instance to monitor the backup operation. The alert job will also run on each secondary server instance to monitor local replication and restore operations.

Tips: Alert jobs that are generated during the deployment log shipping process are written to the event log by default when they fail, and the operator is not set up, notifying the operator that it needs to be manually set after the log shipping is deployed.

13.3.1 Monitoring log shipping by executing pre-defined stored procedures

The monitoring information and history information for log shipping is stored in the table in msdb, which can be accessed by the following preset log shipping stored procedures.

The sp_help_log_shipping_monitor_primary is executed on the primary server or monitoring server. Returns the monitoring record for the specified primary database from the Log_shipping_monitor_primary table.

The sp_help_log_shipping_monitor_secondary is executed on the secondary server or on the monitoring server. Returns the monitoring record for the specified secondary database from the log_shipping_monitor_secondary table.

Sp_help_log_shipping_alert_job is executed on the monitoring server and executed on the secondary server if no monitoring service is available. Returns the job ID of the alert job.

The sp_help_log_shipping_primary_database is executed on the primary server. Retrieves the primary database settings and displays the values in the log_shipping_primary_databases and log_shipping_monitor_primary tables.

The sp_help_log_shipping_primary_secondary is executed on the primary server. Retrieves the secondary database name for the primary database.

The sp_help_log_shipping_secondary_database is executed on the secondary server. From log_shipping_secondary,log_shipping_secondary_databases and log_shipping_monitor_secondary table to retrieve the secondary database settings.

The sp_help_log_shipping_secondary_primary is executed on the secondary server. This stored procedure retrieves the settings for the given primary database on the secondary server.

13.3.2 Monitoring log shipping through standard reports

The monitoring information for log shipping is displayed as a report in Management Studio, which executes the sp_help_log_shipping_monitor stored procedure. Opening this report requires a step: In Object Explorer, right-click the server instance node, and in the report Standard report, transaction log shipping status.

On the primary server, perform the information related to the transaction log backup.

650) this.width=650; "title=" image "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0 px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px; "border=" 0 "alt=" image "src=" http:// S3.51cto.com/wyfs02/m01/57/fc/wkiom1slp--yuysqaacs4jc7lm4013.jpg "height=" 145 "/>

Execution on the secondary server displays information about copying and restoring the transaction log.

650) this.width=650; "title=" image "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0 px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px; "border=" 0 "alt=" image "src=" http:// S3.51cto.com/wyfs02/m02/57/fa/wkiol1slqlgicuxeaacgqyjfcse617.jpg "height=" 138 "/>

Execution on the monitoring server displays information about transaction log backups, replication, and restores.

650) this.width=650; "title=" image "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0 px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px; "border=" 0 "alt=" image "src=" http:// S3.51cto.com/wyfs02/m00/57/fa/wkiol1slql6zjqe4aacspumolcm874.jpg "height=" 149 "/>

This article is from the "Margin with Wish" blog, please be sure to keep this source http://281816327.blog.51cto.com/907015/1598316

SQL Server 2014 Log Shipping Deployment (6): monitoring log shipping