13.5 Monitoring Database Mirroring

13.5 Monitoring Database Mirroring

13.5.1 Database Mirroring Monitor

Database Mirroring Monitor is a graphical user interface tool that system administrators can use to view and update status and configure warning thresholds for multiple key performance indicators.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/6D/B4/wKiom1VpoRGCzbqHAAQA62mV0IQ972.jpg "title=" Start monitor. png "alt=" wkiom1vporgczbqhaaqa62mv0iq972.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6D/B4/wKiom1VpoP2gsF6jAALATvASgiw798.jpg "style=" float: none; "title=" mirror Monitor. png "alt=" wkiom1vpop2gsf6jaalatvasgiw798.jpg "/>

13.5.2 Database Mirroring Monitoring Job

The database Mirroring monitoring job (Database Mirroring Monitor Job) runs independently of the database Mirroring Monitor. The Database Mirroring Monitor Jobis created automatically only when you start a mirroring session by using SSMS.

If you always start mirroring with the ALTER DATABASE database_name SET PARTNER command, the job exists only if the system administrator runs the sp_dbmmonitoraddmonitoring stored procedure.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/6D/B4/wKiom1VporGipFFaAARo4_uLhOQ723.jpg "title=" Monitor job. png "alt=" wkiom1vporgipffaaaro4_ulhoq723.jpg "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/6D/B4/wKiom1VppJ2AaX3QAASIlQ81qFQ828.jpg "title=" Job history. png "alt=" wkiom1vppj2aax3qaasilq81qfq828.jpg "/>

13.5.3 Using Stored Procedures

　　The database mirroring state is stored in an internal, non-logged Database mirroring state table within the msdb database. This state table is automatically created when the mirroring state is first updated on the server instance.

　　The status table can be updated automatically through the database Mirroring monitor and the Database Mirroring Monitor job (if it is running).

After you create a Database Mirroring Monitor Job , if SQL Server agent is running, the job is called every minute by default. The job then calls the sp_dbmmonitorupdate system stored procedure to update the status table. System administrators can use the sp_dbmmonitorchangemonitoring system stored procedure to change the duration of the update (specified as a value between 1-120 minutes), or you can use the sp_dbmmonitorchangemonitoring The system stored procedure views the current update duration.

Instead, the database Mirroring Monitor automatically updates the status table every 30 seconds.

　　You can retrieve or update the current state by running the sp_dbmmonitorresults system stored procedure.

Use msdb; GO Sp_dbmmonitorresults ' SQLDB01 '; GO

This article from "SQLServer2014 series" blog, declined reprint!

13.5 Monitoring Database Mirroring