SQL AlwaysOn Member Status Test

The AlwaysOn feature not only combines the power of clustering and mirroring into one High Availability option, but also allows you to interact with the secondary databases. In addition, AlwaysOn Availability Groups allows you to configure failover for one database, a set of databases or the entire instance. Another important aspect of the AlwaysOn is that you can create multiple failover targets. If the Availability Group is enabled on multiple Microsoft SQL server instances in a cluster, then the administrators are required to monitor each member of the cluster node that is enabled with the AlwaysOn feature. In addition, if the failover concept has to be fool-proof then there arises a need for a file-share witness or a disk witness. The file-share witness or disk witness is most commonly used when shared storage is available to a cluster. The file share witness or disk witness pings the members of a cluster and syncs the data from all the members to keep the database updated. In case of failover, the disk witness or file share witness will render the cluster node with an updated database. Since the file share witness or disk witness stores the updated database by constantly pinging the members of the cluster node on which AlwaysOn feature is enabled, it becomes mandatory to check the status of each member of the cluster node and the disk witness or file share witness. The SQL AlwaysOn Member Status test exactly helps you in this regard.

For each category available in the Microsoft SQL server instance enabled with AlwaysOn feature, this test reports the current status of each member. If the member is online, then this test will report whether/not the member is a primary member and whether the member has failed over.

This test is disabled by default. To enable the test, go to the enable / disable tests page using the menu sequence : Agents -> Tests -> Enable/Disable, pick Microsoft SQL as the desired Component type, set Performance as the Test type, choose the test from the disabled tests list, and click on the < button to move the test to the ENABLED TESTS list. Finally, click the Update button.

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 Category:Member available in the Microsoft SQL server that is 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.

Measurements made by the test

Measurement	Description	Measurement Unit	Interpretation
Member status	Indicates the current state of this member.		The values reported by this measure and their numeric equivalents are available in the table below: Measure Value Numeric Value Offline 0 Online 1 Note: This measure reports the Measure Values listed in the table above to indicate current state of this member. However, in the graph, this measure is indicated using the Numeric Values listed in the above table.
Is primary?	Indicates whether/not this member is the primary member.		The values reported by this measure and their numeric equivalents are available in the table below: Measure Value Numeric Value No 0 Yes 1 Note: This measure reports the Measure Values listed in the table above to indicate whether/not this member is the primary member. However, in the graph, this measure is indicated using the Numeric Values listed in the above table. This measure is not applicable for the DISK_WITNESS descriptor.
Is switch over happened?	Indicates whether/not this member has failed over i.e., this member has switched over from primary to secondary and vice versa.		The values reported by this measure and their numeric equivalents are available in the table below: Measure Value Numeric Value No 0 Yes 1 Note: This measure reports the Measure Values listed in the table above to indicate whether/not this member has failed over. However, in the graph, this measure is indicated using the Numeric Values listed in the above table. This measure is not applicable for the DISK_WITNESS descriptor.
Availability mode	Indicates the current availability mode of this member.		The values reported by this measure and their numeric equivalents are available in the table below: Mode Numeric Value Asynchronous commit 1 Synchronous commit 2 Configuration only 3 Note: This measure reports the Modes listed in the table above to indicate the current availability mode of each member. However, in the graph, this measure is indicated using the Numeric Values listed in the above table.