Additional Sources of Information about a Mirrored Database

  • Article

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 2005 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. For more information, see DATABASE_MIRRORING_STATE_CHANGE.

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.

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:

For more information about event notifications, see Understanding Event Notifications and Monitoring Events.

See Also

Other Resources

Monitoring Database Mirroring

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

14 April 2006
New content:
  • Added reference to DATABASE_MIRRORING_STATE_CHANGE WMI event.
Changed content:
  • Moved the information in this topic from the "Monitoring Database Mirroring" topic.
  • Removed information about the Database Mirroring Connection event class, which does not exist.