SQL Server Database Mirroring – Performance Metrics and Warning Thresholds

Hi Friends,

This is an old writing of mine from my old blog – just wanted to re post this here.

Management by exception – don’t be too sure that things are running fine. Probably, that’s the time you need to worry when everything is running smoothly, especially your Database Mirroring session .

When your SQL Server Database Mirroring session is established and running smoothly, you can use the Mirroring Monitor to monitor the session.

You can see many performance metrics including the sent rate, restore rate, unsent log, etc…

But more importantly, what do you when you want to be alerted when one these performance metrics cross their limits. So what do I mean?

Lets take an example: Imagine a scenario where the mirror server is down and there are continuous transactions occurring on the principal server. In such a case, there will be a back log on the principal server which is displayed as unsent log. But you certainly don’t want the unsent log to get huge and you should by notified, rightly so.

Therefore, DB mirroring in SQL Server offers you threshold warnings.  You can define threshold warnings in Unsent Log, Unrestored log, Oldest unsent transaction & Mirror commit overhead.

Unsent log – how many kilobytes KBs of unsent log will generate a warning?

Unrestored log – how many KBs of unrestored log will generate a warning?

Oldest unsent transaction – Do you want a warning when the oldest unsent transaction crosses 2 mins or 4 mins ??

Mirror commit overhead – This is relevant only in high-safety mode. Specifies the number of milliseconds of average delay per transaction that are tolerated before a warning is generated on the principal server.

You can choose to be alerted via mail, NET SEND msg or pager by configuring the relevant alerts for these warning thresholds.

How do you do it?

1. First specify the thresholds that are applicable to your environment.

2. You should know the error number / event ID for these warnings, which is as follows: Unsent log-32042, Unrestored log-32043, Oldest unsent transaction-32040, Mirror commit overhead-32044.

3. Next, configure Alerts under SQL Server Agent and specify the above error numbers in the configuration dialog box.

4. You need to choose, how you want to be notified. If you choose mail, make sure DB mail is configured appropriately.