2. Monitoring database mirroring
There
are several tools and techniques for monitoring database mirroring,
including system stored procedures, catalog views, performance
counters, and the GUI-based Database Mirroring Monitor.
Database Mirroring Monitor
You
can access the Database Mirroring Monitor in SQL Server Management
Studio by right-clicking a database and choosing Tasks > Launch
Database Mirroring Monitor. This tool, as you can see in figure 6, displays the mirroring status and related information for all mirrored databases on the SQL Server instance.
Information displayed by the Database Mirroring Monitor includes the following:
Mirroring state
Role of the server instance (principal or mirror)
Status of the witness instance if present
Amount of log in the principal's send queue and the mirror's redo queue
Oldest unsent transaction
Current rate of new transactions entering the principal database (kb/sec)
Current rate at which transactions are being sent to the mirror (kb/sec)
Current rate at which transactions are being processed at the mirror's redo queue (kb/sec)
Average
delay per transaction in waiting for confirmation of transaction
hardening on the mirror (this is specific to high-safety [synchronous]
mirroring only, and indicates the overhead of this mode in comparison
to high performance [asynchronous] mirroring)
Information
displayed by the Database Mirroring Monitor is captured on a regular
basis (1 minute by default) by a SQL Server Agent job that updates
mirroring information stored in msdb tables. If a mirroring session is
created using SQL Server Management Studio, the SQL Server Agent job is
created automatically.
System stored procedures
In
addition to the Database Mirroring Monitor job, several system stored
procedures exist that you can use to view and configure monitoring:
sp_dbmmonitorupdate—This
procedure is called by both the SQL Agent job and Database Mirroring
Monitor to perform the updates on the mirroring status table in the
msdb database. This database is used by both the Database Mirroring
Monitor and the sp_dbmmonitorresults procedure (discussed
shortly). When first executed, this procedure will create the msdb
table to store database mirroring status information. Then it will
insert new status records and purge records older than the retention
period (default: 7 days).
sp_dbmmonitoraddmonitoring—This
procedure creates the SQL Server Agent jobs to periodically update the
msdb tables containing the mirroring status. Running this procedure is
required if database mirroring is established using T-SQL rather than
using Management Studio.
sp_dbmmonitorchangemonitoring—This procedure is used to change the update interval for the SQL Agent job that updates mirroring status information.
sp_dbmmonitorhelpmonitoring—This procedure returns the current value for the update interval, set using sp_dbmmonitorchangemonitoring.
sp_dbmmonitordropmonitoring—This procedure stops and removes the SQL Agent job that updates the mirroring status tables in the msdb database.
sp_dbmmonitorresults—This
procedure can be used as an alternative to the Database Mirroring
Monitor. It returns the same information but in a text-based format. It
takes three parameter values: a value specifying the mirrored database
name to return results for, a value indicating the quantity of rows to
return, and a value indicating whether you want to update mirroring
status as part of the execution.
Catalog views
SQL Server exposes database mirroring metadata through a number of catalog views:
sys.database_mirroring—This
view returns one row for each database on the instance in which the
view is queried. Columns returned include the mirroring status, role,
safety level, and witness status.
sys.database_mirroring_endpoints—Returns information about each database mirroring endpoint enabled on the instance.
sys.database_mirroring_witnesses—Returns
a row for each witness role played by the instance containing
information, such as the safety level, principal, and mirror server
names and synchronization state of the mirroring partners.
Performance counters
On
either the principal or mirror server, Windows System Monitor can be
used to view database mirroring information, including redo and send
queue depth, and log data throughput per second. The counters are
exposed using the SQL Server:Database Mirroring performance object.
Warning thresholds
Finally,
one important aspect of monitoring of any type is being able to specify
threshold values for important metrics and be alerted when such
thresholds are exceeded. For database mirroring, thresholds can be set
for the following metrics:
Oldest unsent transaction—This
metric is used to alert on the existence of old transactions in the
send queue. If transactions exist that exceed the specified age in
minutes, an alert is raised using event ID 32040.
Unsent log—This
metric is used to set the maximum allowable size (in kb) of
transactions in the send queue. This threshold uses event ID 32042.
Unrestored log—Similar
to the unsent log, this metric applies to the allowable size of the
redo queue on the mirror database. This threshold uses event ID 32043.
Mirror commit overhead—
Used for high-safety (synchronous) mirroring, this metric allows an
alert to be generated when the average transaction delay to harden log
records to the mirror log exceeds a specified number of milliseconds.
This threshold uses event ID 32044.
You can set threshold values through the Set Warning Thresholds tab, as shown in figure 7,
accessible by clicking the Set Thresholds button on the Warnings tab of
the Database Mirroring Monitor tool, or by using the system stored
procedures sp_dbmmonitorchangealert, sp_dbmmonitorhelpalert, or sp_dbmmonitordropalert.
