Basic Database Mirroring Monitoring

Published On: 2015-10-07By:

If you have database mirroring setup and running, you’ll notice that it has a lack of built in monitoring. And if you have a lot of databases having monitoring for each potential state that each database can be in can flood you with email if the network between sites goes down and comes back up. Especially if it happens a few times in a minute (the record that I’ve gotten using this sort of configuration is about 700 emails in one night of network maintenance.

So I’ve put together a slightly more simplified database mirroring monitor. It’ll still tell you if the network goes down and comes back up, but it’ll do so if a slightly nicer way. You’ll want to change the @operator_name value to a valid operator on your system before you run this, otherwise you’ll get an error as the operator doesn’t exist. I’ve set the delay between retries at 5 minutes, so even if the network is going nuts you’ll get at most 12 emails from each alert per hour. Depending on how stable your network is you may want to adjust this as needed.

Never fear, this works on SQL Server 2005 – SQL Server 2016 (CTP 2 at least).

USE [msdb]
GO
DECLARE @name sysname
SET @name = @@SERVERNAME + N' - DBM State: Mirror Connection Lost'
EXEC msdb.dbo.sp_add_alert @name=@name,
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=0,
@category_name=N'Database Mirroring',
@wmi_namespace=N'\.rootMicrosoftSqlServerServerEventsMSSQLSERVER',
@wmi_query=N'SELECT * from DATABASE_MIRRORING_STATE_CHANGE
WHERE State = 6',
@job_id=N'00000000-0000-0000-0000-000000000000'
EXEC msdb.dbo.sp_add_notification @alert_name=@name, @operator_name=N'SQLAdmins', @notification_method = 1
GO

USE [msdb]
GO

DECLARE @name sysname
SET @name = @@SERVERNAME + N' - DBM Perf: Oldest Unsent Transaction Threshold'
EXEC msdb.dbo.sp_add_alert @name=@name,
@message_id=32040,
@severity=0,
@enabled=1,
@delay_between_responses=1800,
@include_event_description_in=0,
@category_name=N'Database Mirroring',
@job_id=N'00000000-0000-0000-0000-000000000000'
EXEC msdb.dbo.sp_add_notification @alert_name=@name, @operator_name=N'SQLAdmins', @notification_method = 1
GO

USE [msdb]
GO

DECLARE @name sysname
SET @name = @@SERVERNAME + N' - DBM State: Mirroring Suspended'
EXEC msdb.dbo.sp_add_alert @name=@name,
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=0,
@category_name=N'Database Mirroring',
@wmi_namespace=N'\.rootMicrosoftSqlServerServerEventsMSSQLSERVER',
@wmi_query=N'SELECT * from DATABASE_MIRRORING_STATE_CHANGE
WHERE State = 9',
@job_id=N'00000000-0000-0000-0000-000000000000'
EXEC msdb.dbo.sp_add_notification @alert_name=@name, @operator_name=N'SQLAdmins', @notification_method = 1
GO

USE [msdb]
GO

DECLARE @name sysname
SET @name = @@SERVERNAME + N' - DBM State: Principal Connection Lost'
EXEC msdb.dbo.sp_add_alert @name=@name,
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=0,
@category_name=N'Database Mirroring',
@wmi_namespace=N'\.rootMicrosoftSqlServerServerEventsMSSQLSERVER',
@wmi_query=N'SELECT * from DATABASE_MIRRORING_STATE_CHANGE
WHERE State = 5',
@job_id=N'00000000-0000-0000-0000-000000000000'
EXEC msdb.dbo.sp_add_notification @alert_name=@name, @operator_name=N'SQLAdmins', @notification_method = 1
GO

Happy monitoring,
Denny

Contact the Author | Contact DCAC

Now Is The Time To Review Disaster Recovery Plans

Published On: 2014-05-16By:

As we watch the fires which are raging in the San Diego area we should be reminded that now is the time to review our disaster recovery plans. Even if your home or company isn’t located in the San Diego area, the speed at which these fires came up and are spreading through the area should remind us that we should have backup plans for running anything critical in our lives, both our work and our personal lives.

As I sit here in the Houston Airport coming home from TechEd North America the news is my primary source of information about the destruction which is happen around my own home. Thankfully in our case we’ve already got the pets out at a friend’s house, several changes of clothing with us, and our laptops with us. Our data is all backed up to the cloud and is accessible if needed. Not all companies are so lucky. Some of my clients have offices located within the evacuation zone for the current fires and I know what their disaster recovery plans currently look like for the systems in those offices. Some have full disaster recovery plans, while some don’t. Hopefully this will show those companies that do not have disaster recovery plans that it is time to change that policy.

Just because a system isn’t a customer facing system doesn’t mean that it isn’t a critical system. An example would be your email servers. If your internal Exchange system goes offline for several days, how much of a problem is this? Can your remote users continue to work and function without email? Can your company keep ordering supplies at other offices or continue to pay vendors if the finance system isn’t available? I’m guessing that the answer to these questions is “a big one”, “no” and “no”.

Every system that a company has to run its day to day business is a critical system to someone within the company and keeping those systems up and running when the office isn’t available for days at a time is critical to keeping the business running smoothly.

Contact the Author | Contact DCAC

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner   Denny Cherry & Associates Consulting LLC BBB Business Review    Microsoft MVP    Microsoft Certified Master VMWare vExpert
INC 5000 Award for 2020    American Business Awards People's Choice    American Business Awards Gold Award    American Business Awards Silver Award    FT Americas’ Fastest Growing Companies 2020   
Best Full-Service Cloud Technology Consulting Company       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers