Possible to use APM or NPM to monitor Sync Status of a SQL Mirror?

Hello,

We recently configured ASync DB mirroring for a particular application in our environment. I would like to know if it is possible to use APM or NPM to monitor the status of this SQL mirror. I envision being able to gather stats about the change rate, amount of data transferred, how far behind, and also generate an alert of the mirror fails or goes down. We're using SQL 2008 Enterprise on both servers.

Thanks, iain

Find more posts tagged with

sql_2008

sam

mirroring

application_monitor

mirror

Accepted answers

iain

I was able to figure it out after doing some more research. From my experience the Category: field MUST be SQLServer:Database Mirroring (see example screenshot below).

Use the URL below to learn about and pick the counters you need.

technet.microsoft.com/.../ms189931.aspx

All comments

gryansmith

Yes it is! In fact, I just finished setting this up for our own SQL Server mirrors.

If it continues to work for another week or so, I'll package it up as new Content and upload to the site.

Until then, it's just adding Windows Performance monitors on these entries:

Database Mirroring: Transaction Delay (ms)
Component Monitor status is Up.

Database Mirroring: Log Send Queue KB
Component Monitor status is Up.

Database Mirroring: Send/Receive Ack Time
Component Monitor status is Up.

Database Mirroring: Log Bytes Sent/sec
Component Monitor status is Up.

Database Mirroring: Transactions/sec
Component Monitor status is Up.

Database Mirroring: Log Bytes Flushed/sec
Component Monitor status is Up.

Database Mirroring: Redo Bytes/sec
Component Monitor status is Up.

Database Mirroring: Redo Queue KB

THe instance on all of these is set to _total and the monitor name is dependent on your SQL instance name, but they work really well!

I'll post back when I get around to packing them up for delivery.

iain

Looks great! However, I think I need a little more detail regarding your configuration.

1. This is a Performance Counter Monitor for Windows and NOT a WMI counter, correct?

2. What is the exact naming on the counters?
technet.microsoft.com/.../ms189931.aspx

3. What kind of credentials are you using? Do the credentials require SQL rights through AD?

iain

I was able to figure it out after doing some more research. From my experience the Category: field MUST be SQLServer:Database Mirroring (see example screenshot below).

Use the URL below to learn about and pick the counters you need.

technet.microsoft.com/.../ms189931.aspx