SQL AlwaysOn Replica Database Status Test

An availability database is a database that belongs to an availability group. For each availability database, the availability group maintains a single read-write copy (the primary database) and one to eight read-only copies (secondary databases). Whenever a failover is detected, the administrators may want the secondary database to take over quickly from the primary database. If too much of data is not transferred to the secondary database from the primary database, then the users have to wait for a longer period to access the databases during failover. In order to avoid such delays, administrators are required to continuously monitor the synchronization status and synchronization health of the availability databases. For each availability database, this test reports the current state, synchronization state and synchronization health. In addition, administrators can be alerted to whether the availability database is suspended from the availability replica.

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 availability database on the target Microsoft SQL server that is 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

Is suspended?:

Indicates whether/not this availability database is suspended from the availability replica.

 

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 this availability database is suspended or not. However, in the graph, this measure is indicated using the Numeric Values listed in the above table.

The detailed diagnosis of this measure if enabled, lists the IsLocal and the reason for suspension of the availability database.

Synchronization state:

Indicates the current synchronization state of this availability database.

 

The values reported by this measure and their numeric equivalents are available in the table below:

Measure Value

Numeric Value

NOT SYNCHRONIZING

 0

INITIALIZING

 1

REVERTING

 2

SYNCHRONIZING

 3

SYNCHRONIZED

 4

Note:

This measure reports the Measure Values listed in the table above to indicate the synchronization state of the database. However, in the graph, this measure is indicated using the Numeric Values listed in the above table.

Synchronization health state:

Indicates the health of this availability database during synchronization.

 

The values reported by this measure and their numeric equivalents are available in the table below:

Measure Value

Numeric Value

NOT_HEALTHY

 0

PARTIALLY_HEALTHY

 1

HEALTHY

 2

Note:

This measure reports the Measure Values listed in the table above to indicate the health status of the availability database during synchronization. However, in the graph, this measure is indicated using the Numeric Values listed in the above table.

Database state:

Indicates the current status of this availability database.

 

The values reported by this measure and their numeric equivalents are available in the table below:

Measure Value

Numeric Value

EMERGENCY

 0

SUSPECT

 1

RECOVERY_PENDING

 2

RECOVERING

 3

RESTORING

 4

OFFLINE

 5

ONLINE

 6

Note:

This measure reports the Measure Values listed in the table above to indicate the current status of this availability database. However, in the graph, this measure is indicated using the Numeric Values listed in the above table.