Log Shipping Monitoring Improvement

[DavidWiseman]

If you are log shipping a large number of DBs it would be useful to know if you have all the databases protected from the primary server on your secondary server. DBA Dash will only alert you if the latency of any of the restoring databases hits a certain threshold. It won't tell you if you are missing any databases. This is a useful validation check - particularly if you are log shipping a larger number of databases and databases are been added frequently to the primary.

Until something is built into the app this can be used a workaround.

• Create a new database on the same SQL Instance as the DBA Dash repository database.
• Run this script in the context of the new database:

CREATE TABLE dbo.LogShippingConfig(
	PrimaryInstance SYSNAME NOT NULL,
	SecondaryInstance SYSNAME NOT NULL,
	NewDatabaseGracePeriodMins INT NOT NULL,
	CONSTRAINT PK_LogShippingConfig PRIMARY KEY(PrimaryInstance,SecondaryInstance)
)
CREATE TABLE dbo.ExcludedDBs(
	PrimaryInstance SYSNAME NOT NULL,
	ExcludedDatabase SYSNAME NOT NULL,
	CONSTRAINT PK_ExcludedDBs PRIMARY KEY (PrimaryInstance,ExcludedDatabase)
)
CREATE OR ALTER PROC dbo.LogShippingStatus_Get
AS
SET NOCOUNT ON
SELECT	LS.PrimaryInstance,
		LS.SecondaryInstance,
		D1.name,
		D1.create_date,
		CASE WHEN D2.name IS NULL THEN CAST(0 AS BIT) ELSE CAST(1 AS BIT) END AS IsLogShipped,
		CASE WHEN DATEDIFF(mi,d1.create_date,GETUTCDATE()) < LS.NewDatabaseGracePeriodMins THEN CAST(1 AS BIT) ELSE 0 END as IsGracePeriodForNewDB,
		CASE WHEN X.ExcludedDatabase IS NOT NULL THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END as IsExcluded,
		LSS.last_file,
		LSS.LatencyOfLast,
		LSS.TimeSinceLast,
		LSS.TotalTimeBehind,
		LSS.backup_start_date,
		LSS.backup_start_date_utc,	
		LSS.restore_date,
		LSS.restore_date_utc,
		LSS.StatusDescription,
		LSS.Status
INTO #LogShippingStatus
FROM dbo.LogShippingConfig LS
JOIN dbo.Instances I1 ON LS.PrimaryInstance = I1.ConnectionID AND I1.IsActive=1
JOIN dbo.Databases D1 ON I1.InstanceID = D1.InstanceID AND D1.IsActive=1
LEFT JOIN dbo.Instances I2 ON LS.SecondaryInstance = I2.ConnectionID AND I2.IsActive=1
LEFT JOIN dbo.Databases D2 ON D2.InstanceID = I2.InstanceID AND D2.name = D1.name AND(D2.state=1 OR D2.is_in_standby=1) AND D2.IsActive=1
LEFT JOIN dbo.LogShippingStatus LSS ON LSS.DatabaseID = D2.DatabaseID
LEFT JOIN dbo.ExcludedDBs X ON D1.name = X.ExcludedDatabase AND LS.PrimaryInstance = X.PrimaryInstance
WHERE  D1.name NOT IN('master','model','tempdb','msdb') 

SELECT	LS.PrimaryInstance,
		LS.SecondaryInstance,
		D.name,
		D.create_date,
		CAST(1 AS BIT) AS IsLogShipped,
		NULL AS IsGracePeriodForNewDB,
		CASE WHEN X.ExcludedDatabase IS NOT NULL THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END as IsExcluded,
		LSS.last_file,
		LSS.LatencyOfLast,
		LSS.TimeSinceLast,
		LSS.TotalTimeBehind,
		LSS.backup_start_date,
		LSS.backup_start_date_utc,	
		LSS.restore_date,
		LSS.restore_date_utc,
		LSS.StatusDescription,
		LSS.Status
INTO #Extra
FROM dbo.LogShippingConfig LS 
JOIN dbo.Instances I ON LS.SecondaryInstance=I.Instance
JOIN dbo.Databases D ON I.InstanceID = D.InstanceID AND D.IsActive=1 AND (D.state =1 OR D.is_in_standby=1)
LEFT JOIN dbo.LogShippingStatus LSS ON LSS.DatabaseID = D.DatabaseID
LEFT JOIN dbo.ExcludedDBs X ON D.name = X.ExcludedDatabase AND LS.PrimaryInstance = X.PrimaryInstance
WHERE D.name NOT IN('master','model','tempdb') 
AND NOT EXISTS(SELECT 1 
				FROM #LogShippingStatus LSS
				WHERE LSS.SecondaryInstance = LS.SecondaryInstance
				AND LSS.name = D.name)

SELECT PrimaryInstance,
	SecondaryInstance,
	SUM(CASE WHEN IsLogShipped=0 AND IsExcluded=0 THEN 1 ELSE 0 END) AS [Missing Count],
	SUM(CASE WHEN IsLogShipped=1 THEN 1 ELSE 0 END) AS [LogShipped Count],
	SUM(CASE WHEN IsExcluded=1  THEN 1 ELSE 0 END) AS [Excluded Count],
	SUM(CASE WHEN Status=1 THEN 1 ELSE 0 END) as [Critical Count],
	SUM(CASE WHEN Status=2 THEN 1 ELSE 0 END) AS [Warning Count],
	SUM(CASE WHEN Status=3 THEN 1 ELSE 0 END) AS [N\A Count],
	SUM(CASE WHEN Status=4 THEN 1 ELSE 0 END) AS [OK Count],
	MIN(backup_start_date_utc) AS [Oldest Backup Restored Utc],
	MAX(backup_start_date_utc) AS [Most Recent Backup Restored Utc],
	MAX(TimeSinceLast) AS [Max Time Since Last (min)],
	MAX(LatencyOfLast) AS [Max Latency of Last (min)],
	MAX(TotalTimeBehind) AS [Max Total Time Behind (min)]
FROM #LogShippingStatus
GROUP BY PrimaryInstance,
		 SecondaryInstance


SELECT'Missing' as Issue,CASE WHEN IsGracePeriodForNewDB=1 THEN 100 ELSE 1 END as Priority,* 
INTO #Issues
FROM #LogShippingStatus
WHERE IsExcluded=0 
AND IsLogShipped=0
UNION ALL
SELECT 'Unhealthy' as Issue,2 AS Priority,*
FROM #LogShippingStatus
WHERE IsExcluded=0
AND (Status IN(1,2))
UNION ALL
SELECT 'Extra database on secondary' as Issue,999 AS Priority,*
FROM #Extra
UNION ALL
SELECT 'Excluded (for review purposes)' AS Issue,1000 AS Priority, *
FROM #LogShippingStatus
WHERE IsExcluded=1

IF EXISTS(SELECT 1
	FROM #Issues
	)
BEGIN
	SELECT * 
	FROM #Issues
	ORDER BY Priority, 
		backup_start_date_utc
END

IF EXISTS(SELECT 1
	FROM #Issues
	WHERE Priority<100
)
BEGIN
	RAISERROR('Log shipping status is not healthy',11,1)
END


DROP TABLE #LogShippingStatus
DROP TABLE #Issues
DROP TABLE #Extra

• Configure
  We need to insert the names of the primary and secondary instances in the LogShippingConfig. e.g.

INSERT INTO dbo.LogShippingConfig(PrimaryInstance,SecondaryInstance,NewDatabaseGracePeriodMins)
SELECT 'MyPrimaryServer1','MySecondaryServer1',1440
UNION ALL
SELECT 'MyPrimaryServer2','MySecondaryServer2',1440

The exclusion period means we don't throw an error when the SP is run for new databases created within the last day. They are still reported in the missing tab, but we don't throw an exception.

• Add any exclusions if required (system DBs are excluded automatically)

INSERT INTO dbo.ExcludedDBs(PrimaryInstance,ExcludedDatabase)
SELECT 'MyPrimaryServer1','DontLogShipMeDB'
UNION ALL
SELECT 'MyPrimaryServer2','DontLogShipMeDB'

• Schedule a job to run 'dbo.LogShippingStatus_Get'

The job will fail if there are issues. This allows the issue to be visible in DBA Dash as a failed SQL Agent job.

• Run 'dbo.LogShippingStatus_Get' in SSMS to get a summary of your log shipping as well as details of any issues.