SQL Mirroring Status Test

Database mirroring is a solution for increasing the availability of a SQL Server database. Mirroring is implemented on a per-database basis and works only with databases that use the full recovery model.

Database mirroring maintains two copies of a single database that must reside on different server instances of SQL Server Database Engine. Typically, these server instances reside on computers in different locations. Starting database mirroring on a database, initiates a relationship, known as a database mirroring session, between these server instances.

One server instance serves the database to clients (the principal server). The other instance acts as a hot or warm standby server (the mirror server), depending on the configuration and state of the mirroring session. When a database mirroring session is synchronized, database mirroring provides a hot standby server that supports rapid failover without a loss of data from committed transactions. A Witness is an optional instance of SQL Server that enables the mirror server to recognize when to initiate an automatic failover. Unlike the two failover partners, the witness does not serve the database. Supporting automatic failover is the only role of the witness.

When the session is not synchronized, the mirror server is typically available as a warm standby server (with possible data loss).

It is hence evident that to prevent any data loss during failover, a database mirroring session should be in the  synchronized state and a Witness should be up and running. If one or both the aforesaid conditions are not fulfilled, data loss is bound to occur. This is why, administrators should continuously track the state of every database mirroring session and witness on a SQL server instance. This is where the SQL Mirroring Status test helps.

For each database on a SQL server instance on which database mirroring is enabled, this test reports the current status of the mirroring session of that database, reveals what role that database plays in the mirroring session, and the current state of the witness. This way, administrators are promptly alerted when any mirroring session or witness switches to an abnormal state.

Target of the test : A Microsoft SQL server

Agent deploying the test : An internal agent

Outputs of the test : One set of results for each database on the Microsoft SQL server instance being monitored

Configurable parameters for the test

TEST PERIOD - How often should the test be executed Host – The IP address of the Microsoft SQL server. Port - The port number through which the Microsoft SQL server communicates. The default port is 1433. ssl – If the Microsoft SQL server being monitored is an SSL-enabled server, then set the ssl flag to Yes. If not, then set the ssl flag to No. instance - In this text box, enter the name of a specific Microsoft SQL instance that is to be monitored. The default value of this parameter is “default”. To monitor a Microsoft SQL instance named “CFS”, enter this as the value of the INSTANCE parameter. USER – If a Microsoft SQL Server 7.0/2000 is monitored, then provide the name of a SQL user with the Sysadmin role in this text box. While monitoring a Microsoft SQL Server 2005 or above, provide the name of a SQL user with all of the privileges outlined in User Privileges Required for Monitoring Microsoft SQL server. password - The password of the specified user confirm password - Confirm the password by retyping it. domain - By default, none is displayed in the DOMAIN text box. If the ‘SQL server and Windows’ authentication has been enabled for the server being monitored, then the DOMAIN can continue to be none. On the other hand, if ‘Windows only’ authentication has been enabled, then, in the DOMAIN text box, specify the Windows domain in which the managed Microsoft SQL server exists. Also, in such a case, the USER name and PASSWORD that you provide should be that of a user authorized to access the monitored SQL server. isntlmv2 - In some Windows networks, NTLM (NT LAN Manager) may be enabled. NTLM is a suite of Microsoft security protocols that provides authentication, integrity, and confidentiality to users. NTLM version 2 (“NTLMv2”) was concocted to address the security issues present in NTLM. By default, the isntlmv2 flag is set to No, indicating that NTLMv2 is not enabled by default on the target Microsoft SQL host. Set this flag to Yes if NTLMv2 is enabled on the target host. ISPASSIVE – If the value chosen is yes, then the Microsoft SQL server under consideration is a passive server in a SQL cluster. No alerts will be generated if the server is not running. Measures will be reported as “Not applicable" by the agent if the server is not up.

Measurements made by the test

Measurement	Description	Measurement Unit	Interpretation
Mirroring state:	Indicates the mirroring state of this database mirroring session.	Number	The values that this measure can report and their corresponding numeric values are as follows: Measure Value Numeric Value DISCONNECTED 1 SYNCHRONIZED 2 SYNCHRONIZING 3 PENDING_FAILOVER 4 SUSPENDED 5 UNSYNCHRONIZED 6 SYNCHRONIZED 7 Note: By default, this measure reports the Measure Values listed in the table above to indicate the current status of a database mirroring session. In the graph of this measure however, the same is represented using the numeric equivalents only.
Mirroring role state:	Indicates the role that is currently played by this database in the mirroring session.	Number	The values that this measure can report and their corresponding numeric values are as follows: Measure Value Numeric Value PRINCIPAL 1 MIRROR 2 Note: By default, this measure reports the Measure Values listed in the table above to indicate the database role. In the graph of this measure however, the same is represented using the numeric equivalents only.
Mirroring witness sate:	Indicates the current state of the witness in the database mirroring session.	Number	The values that this measure can report and their corresponding numeric values are as follows: Measure Value Numeric Value UNKNOWN 1 CONNECTED 2 DISCONNECTED 3 Note: By default, this measure reports the Measure Values listed in the table above to indicate the state of the witness. In the graph of this measure however, the same is represented using the numeric equivalents only.