/*============================================================================
	File: Monitoring DBM.sql 

	SQL Server Versions:2008 R2
------------------------------------------------------------------------------
	Copyright (C) 2012 Joe Sack, SQLskills.com
	All rights reserved. 

	For more scripts and sample code, check out
		http://www.sqlskills.com/ 

	You may alter this code for your own *non-commercial* purposes. You may
	republish altered code as long as you give due credit. 

	THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
	ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED
	TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
	PARTICULAR PURPOSE.
============================================================================*/ 

/*

SELECT @@SERVERNAME;

USE [msdb]
GO

/****** Object:  Job [Database Mirroring Monitor Job]    Script Date: 03/13/2012 12:11:44 ******/
IF  EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'Database Mirroring Monitor Job')
EXEC msdb.dbo.sp_delete_job @job_id=N'0fc051c0-ceff-4890-af51-5b607b2b4526', @delete_unused_schedule=1
GO

USE [msdb]
GO

/****** Object:  Alert [Oldest Unsent Transaction]    Script Date: 03/13/2012 12:14:10 ******/
IF  EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Oldest Unsent Transaction')
EXEC msdb.dbo.sp_delete_alert @name=N'Oldest Unsent Transaction'
GO

USE [msdb]
GO

/****** Object:  Alert [Oldest Unsent Transaction]    Script Date: 4/16/2013 10:00:29 AM ******/
EXEC msdb.dbo.sp_delete_alert @name=N'Oldest Unsent Transaction'
GO



*/

-- **
-- Connect to principal
--(Although in production you'll want to define warning thresholds and 
-- alerts at BOTH partner server instances)
-- **

SELECT @@SERVERNAME;

-- 1) Execute "2 - Launch Updates.cmd" 
--    SQL Server Agent running?

-- 2) Launch Database Mirroring Monitor

-- 3) Expore Database Mirroring Monitor
--    Status, Warnings, and Application Event Log (set 1KB warning thresholds).

-- 4) Launch Perfmon - watching principal/mirror

-- 5) Investigate sp_dbmmonitor* procs
--    For use independently of Database Mirroring Monitor

-- Create the database mirroring monitor job (if it doesn't exist)
EXEC sp_dbmmonitoraddmonitoring 1;

-- Manual execution of the mirroring monitor status table 
-- Default retention period is 7 days (168 hours). 
EXEC msdb.sys.sp_dbmmonitorupdate;

-- Look at configured alerts
-- 1 = Oldest unsent transaction
-- 2 = Unsent log
-- 3 = Unrestored log
-- 4 = Mirror commit overhead
-- 5 = Retention period
EXEC msdb.sys.sp_dbmmonitorhelpalert 'CreditReporting';

-- Change a warning threshold
-- Unsent log 2,000 KB
EXEC sp_dbmmonitorchangealert 'CreditReporting'
        , 2 -- Alert ID
        , 2000 -- Value
        , 1 -- Enabled

EXEC msdb.sys.sp_dbmmonitorhelpalert 'CreditReporting';

-- Drop a performance metric warning
EXEC msdb.sys.sp_dbmmonitordropalert 'CreditReporting', 2;

EXEC msdb.sys.sp_dbmmonitorhelpalert 'CreditReporting';

-- Return status rows
EXEC msdb.sys.sp_dbmmonitorresults 'CreditReporting',
	1, -- last two hours
	1 -- Update status

/* Performance Alerts
Event ID 32042 - Unsent Log [on principal server]
	Warn if the unsent log exceeds the threshold 
	High-performance -> relevant
	High-safety -> applies if mirroring is suspended

Event ID 32043 - Unrestored Log [on mirror server]
	Warn if the unrestored log exceeds the threshold 

Event ID 32040 - Oldest Unsent Transaction [on principal server]
	Warn if the age of the oldest unsent transaction	exceeds the threshold 
		High-performance -> relevant
	High-safety -> applies suspended mirroring

Event ID 32044 - Mirror Commit Overhead [On principal server]
	Warn if the mirror commit overhead exceeds the		threshold (milliesconds)
	Relevant to high-safety mode only
*/

-- Defining the alert

SELECT * FROM sys.messages 
WHERE language_id = 1033 AND 
message_id=32042;

EXEC sp_altermessage 32042, 'WITH_LOG', TRUE;

SELECT * FROM sys.messages 
WHERE language_id = 1033 AND 
message_id=32042;

-- Closed with "Won't Fix"
-- https://connect.microsoft.com/SQLServer/feedback/details/657230/alerting-on-database-mirroring-events
-- !! Need to define on <all databases> rather than single named database

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Oldest Unsent Transaction', 
		@message_id=32040, 
		@severity=0, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=0;
GO

-- Removing the database mirroring monitoring job
EXEC msdb.sys.sp_dbmmonitordropmonitoring;

