Online Tutorials & Training Materials |
Register Login

Monitoring SQL Server Database Mirroring

|| || 0

Monitoring SQL Server Database Mirroring

The process of monitoring database mirroring along with performance monitor counters delivers critical data on the assessment of evaluating possible bottlenecks present in the system. It is possible for the user to monitor counters of the principal and the mirror side from both sides of the instances.

In the asynchronous configuration, the most critical counter is observing the 'Log Send Queue', which displays how many KB of data has been queued up in the transaction log of the principal side. All these changes are committed on the principal side, however have not been transferred as of now to the mirror side.

It is not uncommon for the counter to have value 0 at all time, and sometimes it can also have a few KB lined up. Additionally, it is interesting to check the 'Bytes Sent/sec'.  This provides an indication, for the performance or bandwidth of the network connection. 'Log Bytes Sent/sec' is the third counter for monitoring the principal. The shorter the transactions, the bigger is the difference between 'Bytes Sent/sec' and 'Log Bytes Sent/sec', which is mainly due to the fact that the overhead has every submitted package experiences.

On the contrary, it is indeed interesting to monitor the receiving side, which is the mirror server. The less critical queue in all configurations of database mirroring, is the queue which can build up in the transaction log of the mirror server. Which means that the data has been transmitted to the mirror, but the changes have not yet been applied to the data itself. This queue is less critical as the data already has been transferred the mirror side. Therefore, no changes are lost during the case of the principal server failing like a queue only further delays the recovery time and fails a little bit (either automatic or manual). The performance counter is utilized for monitoring this queue is 'Redo Queue KB'. It is common for this value not to be 0, but is a few KB.

For synchronous mirroring configurations, there are two important counters for monitoring those providing information on the delay of the transactions with the synchronous mirroring configuration. These counters are namely the 'Transaction Delay' out of the performance object 'SQLServer:Database Mirroring' and 'transactions/sec' of Counter object 'SQLServer:Databases'. The first counter displays the total accumulated time of delay of all the transactions which are waiting for acknowledgements from the mirror side.  For setting this number in perspective the user has to divide it by the number the second counter delivers. The result will therefore be the average delay of every transaction which has been introduced by Synchronous Database Mirroring.

Related Articles