SQL AlwaysOn Page Repair Test

The AlwaysOn Availability Groups support automatic page repair. After certain types of errors corrupt a page on a local database, making it unreadable, an availability replica (primary or secondary) attempts to automatically recover the page by resolving those errors that prevent reading the data in the page. If a secondary replica cannot read the page, the replica requests a fresh copy of the page from the primary replica. If the primary replica cannot read the page, the replica broadcasts a request for a fresh copy to all the secondary replicas and gets the page from the first replica that responds. If this request succeeds, the unreadable page is replaced by the copy, which usually resolves the error. If the database encounters too many errors simultaneously corrupting a large volume of pages, then the automatic page repair tries to resolve the errors. Certain types of pages such as File header page, Page 9 (the database boot page), the allocation pages etc cannot be repaired by the automatic page repair. If there are too many pages that cannot be repaired, then it indicates that the database is highly corrupted. If the administrators are warned proactively about the number of pages that cannot be repaired, then remedial action can be taken before serious issues occur! The SQL AlwaysOn Page Repair test helps administrators to proactively take remedial measures!

For each message returned, this test reports the number of pages on which page repair was attempted. Using the detailed diagnosis of this test, administrators may be able to identify the pages on which page repair was attempted and the pages that cannot be repaired. Using this test, administrators can proactively identify the pages that are corrupted and take remedial measures before the availability databases become unavailable.

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 message returned after page repair on the Microsoft SQL Server being monitored

Configurable parameters for the test
  1. TEST PERIOD - How often should the test be executed
  2. Host – The IP address of the Microsoft SQL server.
  3. Port - The port number through which the Microsoft SQL server communicates. The default port is 1433.
  4. 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.
  5. 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.

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

  7. password - The password of the specified user
  8. confirm password - Confirm the password by retyping it.
  9. 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.
  10. 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.

  11. DETAILED DIAGNOSIS – To make diagnosis more efficient and accurate, the eG Enterprise embeds an optional detailed diagnostic capability. With this capability, the eG agents can be configured to run detailed, more elaborate tests as and when specific problems are detected. To enable the detailed diagnosis capability of this test for a particular server, choose the On option. To disable the capability, click on the Off option.

    The option to selectively enable/disable the detailed diagnosis capability will be available only if the following conditions are fulfilled:

    • The eG manager license should allow the detailed diagnosis capability
    • Both the normal and abnormal frequencies configured for the detailed diagnosis measures should not be 0.
Measurements made by the test
Measurement Description Measurement Unit Interpretation

Page count:

Indicates the number of pages that returned this message after page repair was attempted.

Number

The detailed diagnosis of this measure if enabled, lists the DatabaseID, FileID, PageID, Error type and Page modification time.