Using Sp_dbmmonitorresults to monitor mirroring

Using Sp_dbmmonitorresults to monitor mirroring

Configuration

First, execute the stored procedure sp_dbmmonitoraddmonitoring in the msdb database. This system stored procedure creates a new job "Database Mirroring Monitor Job" in SQL Server Agent that periodically updates the mirror database information.

Use the following script to execute on each mirror server:

Use msdb; Goexec sp_dbmmonitoraddmonitoring; GO

You can specify the update interval (in minutes). This value can be a value between 1-120 minutes. The default value is 1 minutes.

Once done, you can use sp_dbmmonitorresults to quickly pull the mirror state data. You can specify the number of rows returned by the result. The default is the last 1 rows. You can also specify whether to update the status before returning the results.

You can use the following script to return the mirroring state data.

EXEC sys.sp_dbmmonitorresults@database_name = ' insertdatabasenamehere ',--sysname@mode = 0,--int@update_table = 0–int

Status of the current mirror

In order to get the status of the current mirror of 1 databases, you can query the value of the Mirror_state column. You can configure this value to alert you if a state is abnormal. The query is as follows:

DECLARE @MonitorResults as TABLE (database_name VARCHAR (255), role int,mirror_state Tinyint,witness_status Tinyint,log_ Generat_rate int,unsent_log int,sent_rate int,unrestored_log int,recovery_rate int,transaction_delay INT,transaction _per_sec int,average_delay int,time_recorded datetime,time_behind datetime,local_time DATETIME); INSERT into @ Monitorresultsexec sp_dbmmonitorresults@database_name = ' Insertdatabasenamehere ', @mode = 0, @update_table = 0; SELECT Mirror_statefrom @MonitorResults;

The values that can be returned are:

• 0=suspended

• 1=disconnected

• 2=synchronizing

• 3=pending Failover

• 4=synchronized

If you configure an alarm to check for values that are less than 2, you can know that the mirror is offline.

Database Current Role

After mirroring the configuration, you can track the roles of a given database. If you always want a server to be the main one, you can modify the following query to check the role:

DECLARE @MonitorResults as TABLE (database_name VARCHAR (255), role int,mirror_state Tinyint,witness_status Tinyint,log_ Generat_rate int,unsent_log int,sent_rate int,unrestored_log int,recovery_rate int,transaction_delay INT,transaction _per_sec int,average_delay int,time_recorded datetime,time_behind datetime,local_time DATETIME); INSERT into @ Monitorresultsexec sp_dbmmonitorresults@database_name = ' Insertdatabasenamehere ', @mode = 0, @update_table = 0; SELECT Rolefrom @MonitorResults;

The query returns a value of:

• 1=principal

• 2=mirror

Depending on your monitoring plan, you can track when a database is switched from the master.

Status of the witness

If you configure a witness server, you also need to monitor the status of the witness server at all times, and if the witness server is offline, the availability of the mirrored database is affected. You can modify the following query:

DECLARE @MonitorResults as TABLE (database_name VARCHAR (255), role int,mirror_state Tinyint,witness_status Tinyint,log_ Generat_rate int,unsent_log int,sent_rate int,unrestored_log int,recovery_rate int,transaction_delay INT,transaction _per_sec int,average_delay int,time_recorded datetime,time_behind datetime,local_time DATETIME); INSERT into @ Monitorresultsexec sp_dbmmonitorresults@database_name = ' Insertdatabasenamehere ', @mode = 0, @update_table = 0; SELECT Witness_statusfrom @MonitorResults;

The returned results are as follows:

• 0=unknown

• 1=connected

• 2=disconnected

The author uses an asynchronous mirror, with no configuration witness, so the value is 0. If a witness is configured, you can track its status, configure the alarm, and know when the value is not 1.

The data has not yet been sent to the mirror

You can track the amount of data waiting to be sent to the mirror. is often referred to as the Send queue. You can modify the following script:

DECLARE @MonitorResults as TABLE (database_name VARCHAR (255), role int,mirror_state Tinyint,witness_status Tinyint,log_ Generat_rate int,unsent_log int,sent_rate int,unrestored_log int,recovery_rate int,transaction_delay INT,transaction _per_sec int,average_delay int,time_recorded datetime,time_behind datetime,local_time DATETIME); INSERT into @ Monitorresultsexec sp_dbmmonitorresults@database_name = ' Insertdatabasenamehere ', @mode = 0, @update_table = 0; SELECT Unsent_logfrom @MonitorResults;

Reference:

Sp_dbmmonitoraddmonitoring

Https://msdn.microsoft.com/zh-cn/library/ms403582.aspx

Sp_dbmmonitorresults

Https://msdn.microsoft.com/zh-cn/library/ms366320.aspx

This article is from the SQL Server deep Dive blog, so be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1907216

Using Sp_dbmmonitorresults to monitor mirroring