SQL AlwaysOn Availability Test

The AlwaysOn Availability Groups feature is a high-availability and disaster-recovery solution that provides an enterprise-level alternative to database mirroring. Introduced in SQL Server 2012, AlwaysOn Availability Groups maximizes the availability of a set of user databases for an enterprise. An availability group supports a failover environment for a discrete set of user databases, known as availability databases, that failover together. An availability group supports a set of read-write primary databases and one to eight sets of corresponding secondary databases. Optionally, secondary databases can be made available for read-only access and/or some backup operations. An availability group fails over at the level of an availability replica. Failovers are not caused by database issues such as a database becoming suspect due to a loss of a data file, deletion of a database, or corruption of a transaction log. In environments where critical data is stored, it is important for the database to be highly available. The AlwaysOn Availability Groups if enabled, will help administrators in maintaining such high availability. Therefore, it is important to monitor the status of the AlwaysOn Availability Groups. The SQL AlwaysOn Availability test exactly helps in this regard.

This test reports whether the AlwaysOn Availability Group feature is enabled and the current state of the AlwaysOn Availability Groups manager.

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 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.

Measurements made by the test

Measurement	Description	Measurement Unit	Interpretation
Is always-on enabled?:	Indicates whether/not the Always on Availability Groups feature is enabled.		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 the Always on Availability Groups concept was enabled. However, in the graph, this measure is indicated using the Numeric Values listed in the above table.
Alwayson manager status:	Indicates the current state of the Always on Availability Groups manager.		The values reported by this measure and their numeric equivalents are available in the table below: Measure Value Numeric Value Failed 0 Pending Communication 1 Running 2 Note: This measure reports the Measure Values listed in the table above to indicate the current status of the Always on Availability Groups manager. However, in the graph, this measure is indicated using the Numeric Values listed in the above table.