Additional Sources of Information about a Mirrored Database
In addition to using the Database Mirroring Monitor and dbmmonitor stored procedures to monitor a mirrored database and set up alerts on monitored performance variables, Microsoft SQL Server 2008 provides the following:
Database mirroring metadata in catalog views. For more information, see "Database Mirroring Metadata," later in this section.
Performance counters for database mirroring. For more information, see "Database Mirroring Performance Counters," later in this section.
Database mirroring event notifications. For more information, see "Database Mirroring Events," later in this section.
Server events for the WMI Provider for Server Events.
Database Mirroring Metadata
Each database mirroring session is described in metadata that is exposed through the following catalog or dynamic management views:
sys.database_mirroring
This view displays the database mirroring metadata for each mirrored database in a server instance. For more information, see sys.database_mirroring (Transact-SQL).
sys.database_mirroring_endpoints
The sys.database_mirroring_endpoints catalog view displays information about the database mirroring endpoint of the server instance. For more information, see sys.database_mirroring_endpoints (Transact-SQL).
sys.database_mirroring_witnesses
This catalog view displays the database mirroring metadata for each session in which a server instance is the witness. For more information, see sys.database_mirroring_witnesses (Transact-SQL).
sys.dm_db_mirroring_connections
This dynamic management view returns a row for each database mirroring network connection.
For more information, see sys.dm_db_mirroring_connections (Transact-SQL).
Database Mirroring Performance Counters
Performance counters let you monitor database mirroring performance. For example, you can examine the Transaction Delay counter to see if database mirroring is impacting performance on the principal server, you can examine the Redo Queue and Log Send Queue counters to see how well the mirror database is keeping up with the principal database. You can examine the Log Bytes Sent/sec counter to monitor the amount of log sent per second.
In Performance Monitor on either partner, performance counters are available in the database mirroring performance object (SQLServer:Database Mirroring). For more information, see SQL Server, Database Mirroring Object.
To start the performance monitor
Database Mirroring Event Notifications
Event notifications are a special kind of database object. Event notifications execute in response to a variety of Transact-SQL data definition language (DDL) statements and SQL Trace events and send information about server and database events to a Service Broker service.
The following events are available for database mirroring:
Database Mirroring State Change event class
This indicates when the mirroring state of a mirrored database changes. For more information, see Database Mirroring State Change Event Class.
Audit Database Mirroring Login event class
This reports audit messages related to database mirroring transport security. For more information, see Audit Database Mirroring Login Event Class.
For more information about event notifications, see Understanding Event Notifications and Monitoring Events.