System mirroring

Title description author ms.author ms.date ms.service ms.subservice ms.topic helpviewer_keywords Monitoring Database Mirroring (SQL Server) Learn about the Database Mirroring Monitor, system stored procedures, and how database mirroring monitoring works, including the Database Mirroring Monitor Job. MikeRayMSFT mikeray 03/14/2017 sql database-mirroring how-to monitoring [SQL Server], database mirroring database mirroring [SQL Server], monitoring [!INCLUDE SQL Server]This section introduces Database Mirroring Monitor and the sp_dbmmonitor system stored procedures, explains how database mirroring monitoring works (including the Database Mirroring Monitor Job), and summarizes the information that you can monitor about database mirroring sessions. Additionally, this section introduces how to define warning thresholds for a set of predefined database mirroring events and how to set up alerts on any database mirroring event.You can monitor a mirrored database during a mirroring session to verify whether and how well data is flowing. To set up and manage monitoring for one or more of the mirrored databases on a server instance, you can use either Database Mirroring Monitor or the sp_dbmmonitor system stored procedures.A database mirroring monitoring job, Database Mirroring Monitor Job, operates in the background, independently of Database Mirroring Monitor. [!INCLUDEssNoVersion] Agent calls Database Mirroring Monitor Job at regular intervals, the default is once a minute, and the job calls a stored procedure that updates mirroring status. If you use [!INCLUDEssManStudioFull] to start a mirroring session, Database Mirroring Monitor Job is created automatically. However, if you only use ALTER DATABASE SET PARTNER to start mirroring, you must create the job by running a stored procedure.In this Topic:Monitoring Mirroring StatusAdditional Sources of Information About a Mirrored DatabaseRelated Tasks Monitoring Mirroring StatusTo set up and manage monitoring for one or more of the mirrored databases on a server instance, you can use either Database Mirroring Monitor or the dbmmonitor system stored procedures. You can monitor a mirrored database during a mirroring session to verify whether and how well data is flowing.Specifically, monitoring a mirrored database enables you to:Verify that mirroring is functioning.Basic status includes knowing if the two server instances are up, that the servers are connected, and that the log is being moved from the principal to the

The monitor, how users can monitor database mirroring status, and how the monitoring job can be dropped.Database Mirroring Status TableDatabase mirroring status is stored in an internal, undocumented database mirroring status table in the msdb database. This status table is automatically created the first time the mirroring status is updated on the server instance.The status table may be updated either automatically or manually by a system administrator, with a minimum update interval of 15 seconds. The 15 second minimum prevents server instances from being overloaded with status requests.The status table is updated automatically by both Database Mirroring Monitor and the database mirroring monitor job, if running. Database Mirroring Monitor Job updates the table once a minute by default (a system administrator can specify an update period of 1 to 120 minutes). Database Mirroring Monitor, in contrast, updates the table automatically every 30 seconds. For these updates, Database Mirroring Monitor Job and Database Mirroring Monitor call sp_dbmmonitorupdate.The first time sp_dbmmonitorupdate runs, it creates the database mirroring status table and the dbm_monitor fixed database role in the msdb database. sp_dbmmonitorupdate usually updates the mirroring status by inserting a new row into the status table for every mirrored database on the server instance; for more information, see "Database Mirroring Status Table," later in this topic. This procedure also evaluates the performance metrics in the new rows and truncates rows older than the current retention period (the default is 7 days). For more information, see sp_dbmmonitorupdate (Transact-SQL).NoteUnless Database Mirroring Monitor is currently being used by a member of the sysadmin fixed server role, the status table is automatically updated only if the Database Mirroring Monitor Job exists and [!INCLUDEssNoVersion] Agent is running.Database Mirroring Monitor JobThe database mirroring monitoring job, Database Mirroring Monitor Job, operates independently of Database Mirroring Monitor. Database Mirroring Monitor Job is created automatically only if [!INCLUDEssManStudioFull] is used to start a mirroring session. If ALTER DATABASE database_name SET PARTNER commands are always used to start mirroring, the job exists only if the system administrator runs the sp_dbmmonitoraddmonitoring stored procedure.After Database Mirroring Monitor Job is created, assuming that [!INCLUDEssNoVersion] Agent is running,

The job is called once a minute, by default. The job then calls the sp_dbmmonitorupdate system stored procedure.[!INCLUDEssNoVersion] Agent calls Database Mirroring Monitor Job once a minute, by default, and the job calls sp_dbmmonitorupdate to update the status table. System administrators can change the update period by using the sp_dbmmonitorchangemonitoring system stored procedure, and they can view the current update period by using the sp_dbmmonitorchangemonitoring system stored procedure. For more information, see sp_dbmmonitoraddmonitoring (Transact-SQL) and sp_dbmmonitorchangemonitoring (Transact-SQL).Monitoring Database Mirroring Status (by System Administrators)Members of the sysadmin fixed server role can view and update the status tableUsing Database Mirroring MonitorWhen using Database Mirroring Monitor, a system administrator can manually refresh the Status page, navigation tree, or History page. This also updates the status table, unless it has already been updated within the previous 15 seconds.To view the history of mirroring status on a given server instance, the system administrator can also click the History button for a server instance (on the Status page). The history is displayed in the Database Mirroring History dialog box. There, the system administrator can view some or all of the rows in the status table of the server instance.For information about the Status page metrics, see Performance Metrics Displayed by the "Database Mirroring Monitor," later in this topic.Using sp_dbmmonitorresultsSystem administrators can use the sp_dbmmonitorresults system stored procedure to view and, optionally, to update the status table, if it has not been updated within the previous 15 seconds. This procedure calls the sp_dbmmonitorupdate procedure and returns one or more history rows, depending on the amount requested in the procedure call. For information about the status in its results set, see sp_dbmmonitorresults (Transact-SQL).Monitoring Database Mirroring Status (by dbm_monitor Members)As mentioned, the first time sp_dbmmonitorupdate runs, it creates the dbm_monitor fixed database role in the msdb database. Members of the dbm_monitor fixed database role can view the existing mirroring status by using either Database Mirroring Monitor or the sp_dbmmonitorresults stored procedure. But these users cannot update the status table. To learn the age of the displayed status a user can look at the times in the Principal log ()

To configure warning thresholds on several key performance metrics. Database Mirroring Monitor can also be used by members of the dbm_monitor fixed database role to view the most recent row of the mirroring status table, though they cannot update the status table.The monitor displays the status, including performance metrics, for a selected database on the Status tabbed page. The content of this page comes from both the principal and mirror server instances. The page is filled asynchronously as status is gathered through separate connections to the principal and mirror server instances. The monitor tries to update the status table at 30-second intervals. The update succeeds only if the table has not been updated within 15 seconds and the user is a member of the sysadmin fixed server role. For a summary of the information reported on the Status page, see Status Displayed by the Database Mirroring Monitor, later in this topic.For an introduction to the Database Mirroring Monitor interface, see Database Mirroring Monitor Overview. For information on launching Database Mirroring Monitor, see Start Database Mirroring Monitor (SQL Server Management Studio).System stored proceduresYou can also retrieve or update the current status by running the sp_dbmmonitorresults system stored procedure. Other dbmmonitor stored procedures enable you to set up monitoring, change monitoring parameters, view the current update period, and drop monitoring on the server instance.The following table introduces the stored procedures for managing and using database mirroring monitoring independently of the Database Mirroring Monitor.ProcedureDescriptionsp_dbmmonitoraddmonitoringCreates a job that periodically updates the status information for every mirrored database on the server instance.sp_dbmmonitorchangemonitoringChanges the value of a database mirroring monitoring parameter.sp_dbmmonitorhelpmonitoringReturns the current update period.sp_dbmmonitorresultsReturns status rows for a monitored database and allows you to choose whether the procedure obtains the latest status beforehand.sp_dbmmonitordropmonitoringStops and deletes the mirroring monitor job for all the databases on the server instance.The dbmmonitor system stored procedures can be used as an adjunct to the Database Mirroring Monitor. For example, even if monitoring was configured using sp_dbmmonitoraddmonitoring, Database Mirroring Monitor can be used to view the status.How Monitoring WorksThis section introduces the database mirroring status table, database mirroring monitor job and