SQL Server Monitoring - Log shipping configuration

This blog is part of the blog series named SQL Server Monitoring.

Purpose

To list the log shipping configuration when the server is configured as a primary, standby, or monitoring server.

Report

The report has 6 tables, 2 for monitoring configuration, 2 for primary databases configuration and 2 for secondary databases configuration.

History – drill-through page

The Log shipping configuration history page list all the configuration changes made on the log shipping configuration.

Sources

The feature queries the msdb DMV’s log_shipping_monitor_primary, log_shipping_monitor_secondary, log_shipping_primary_databases, log_shipping_primary_secondaries, log_shipping_secondary and log_shipping_secondary_databases.

Model

The model for the report uses the facts [Log shipping monitor primary database state], [Log shipping monitor secondary database state], [Log shipping primary database state], [Log shipping primary secondary database state], [Log shipping secondary database state], [Log shipping secondary primary database state] and [Log shipping state history], and the dimensions [Database] and [Server].

Service setup

The feature uses the service ‘fhsmLogShipping’ which can be configured using the stored procedure fhsmSPControl.

Parameter

There is no parameter to be configured for this service.

Schedule

The default schedule for the feature is every 2 minutes (120 seconds) and configured as schedule type 1.

EXEC fhsmSPControl
	@Type = 'Schedule',
	@Command = 'list',
	@Task = 'fhsmSPLogShipping',
	@Name = 'Log shipping',
	@Wildcard = 0;

EXEC fhsmSPControl
	@Type = 'Schedule',
	@Command = 'set',
	@Task = 'fhsmSPLogShipping',
	@Name = 'Log shipping',
	@ScheduleType = 1,
	@Enabled = 1,
	@ExecutionDelaySec = 120,
	@FromTime = '00:00:00',
	@ToTime = '23:59:59',
	@Monday = 1,
	@Tuesday = 1,
	@Wednesday = 1,
	@Thursday = 1,
	@Friday = 1,
	@Saturday = 1,
	@Sunday = 1;

Retention

The default retention for the tables are:

dbo.fhsmLogShippingMonitorErrorDetail – 90 days
dbo.fhsmLogShippingMonitorHistoryDetail – 90 days
dbo.fhsmLogShippingMonitorPrimary – 40 days
dbo.fhsmLogShippingMonitorSecondary – 40 days
dbo.fhsmLogShippingPrimaryDatabases – 40 days
dbo.fhsmLogShippingSecondary – 40 days
dbo.fhsmLogShippingSecondaryDatabases – 40 days
dbo.fhsmLogShippingState – 1.825 days (5 years).

EXEC fhsmSPControl
	@Type = 'Retention',
	@Command = 'list',
	@TableName = 'fhsmLogShipping',
	@Wildcard = 1;

EXEC fhsmSPControl
	@Type = 'Retention',
	@Command = 'set',
	@TableName = 'fhsmLogShippingMonitorErrorDetail',
	@Enabled = 1,
	@Sequence = 1,
	@Days = 90;

EXEC fhsmSPControl
	@Type = 'Retention',
	@Command = 'set',
	@TableName = 'fhsmLogShippingMonitorHistoryDetail',
	@Enabled = 1,
	@Sequence = 1,
	@Days = 90;

EXEC fhsmSPControl
	@Type = 'Retention',
	@Command = 'set',
	@TableName = 'fhsmLogShippingMonitorPrimary',
	@Enabled = 1,
	@Sequence = 1,
	@Days = 40;

EXEC fhsmSPControl
	@Type = 'Retention',
	@Command = 'set',
	@TableName = 'fhsmLogShippingMonitorSecondary',
	@Enabled = 1,
	@Sequence = 1,
	@Days = 40;

EXEC fhsmSPControl
	@Type = 'Retention',
	@Command = 'set',
	@TableName = 'fhsmLogShippingPrimaryDatabases',
	@Enabled = 1,
	@Sequence = 1,
	@Days = 40;

EXEC fhsmSPControl
	@Type = 'Retention',
	@Command = 'set',
	@TableName = 'fhsmLogShippingSecondary',
	@Enabled = 1,
	@Sequence = 1,
	@Days = 40;

EXEC fhsmSPControl
	@Type = 'Retention',
	@Command = 'set',
	@TableName = 'fhsmLogShippingSecondaryDatabases',
	@Enabled = 1,
	@Sequence = 1,
	@Days = 40;

EXEC fhsmSPControl
	@Type = 'Retention',
	@Command = 'set',
	@TableName = 'fhsmLogShippingState',
	@Enabled = 1,
	@Sequence = 1,
	@Days = 1825

SQL Server Monitoring – Log shipping configuration