Azure SQL Database Status Test

If a user complaints of problems while accessing a database, the knowledge of the current state of that database and the mode using which users are allowed to access the database will enable administrators to promptly diagnose the reason for such an occurrence. This test auto-discovers all the instances of the Microsoft Azure SQL database that is configured for rmonitoring, and reports the current state of each database instance and the user access mode, thereby enabling administrators to easily troubleshoot issues related to database access.

Target of the test : A Microsoft Azure SQL service

Agent deploying the test : An external/remote agent

Outputs of the test : One set of results for each instance of the configured Azure SQL database.

Configurable parameters for the test
Parameters	Description
Test Period	How often should the test be executed.
Host	The host for which the test is to be configured.
Port	The port at which the specified Host listens.
Database Name	Specify the name of the Azure SQL database that is to be monitored.
User Name and Password	Against the User Name and Password parameters, specify the credentials of the user who is vested with DBOWNER rights to the configured Database Name.
Confirm Password	Confirm the specified Password by retyping it here.
SSL	If the Azure SQL database service being monitored is SSL-enabled, then set the SSL flag to Yes. If not, then set the SSL flag to No.
Domain	By default, none is displayed in this text box. If the ‘SQL server and Windows’ authentication has been enabled for the Azure SQL database being monitored, then the Domain parameter 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 monitored database exists. Also, in such a case, the User Name and Password that you provide should be that of a 'domain user' with DBOWNER rights to the configured Database Name.
IS NTLMv2	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, this flag is set to No, indicating that NTLMv2 is not enabled by default for the target Microsoft Azure SQL database. Set this flag to Yes if NTLMv2 is enabled for the target database.
Azure AD Authentication	This parameter indicates whether Azure Active Directory (Azure AD) authentication should be used to connect to the Azure SQL Database. If this parameter is set to Yes, the agent will authenticate to the Azure SQL Database using an Azure AD user / domain-based credentials. In this case, the Truststore File Name and Truststore Password parameters must also be configured. If this parameter is set to No, standard SQL authentication will be used. By default, this parameter is set to No.
Truststore File Name	This parameter is applicable only when Azure AD authentication is set to Yes. If not, set this parameter to none. When Azure AD authentication is used, the server certificate presented by the Azure SQL database during TLS connection establishment must be trusted. For this purpose, the agent must have access to the truststore that contains the CA certificates required to authenticate the Azure SQL server. In this text box, specify the name of the truststore file that contains these trusted CA certificate entries. For example: combinedTrustStore.jks. By default, none is displayed against this text box. To know more, refer to How Does eG Enterprise Monitor a Microsoft Azure SQL Database?.
Truststore Password	This parameter is applicable only when Azure AD authentication is set to Yes. If not, set this parameter to none. If a truststore file is provided in the Truststore File Name text box, then specify the password that is required to access the certificate entries stored in that truststore file. This password is used by the agent to open the truststore and retrieve the certificates for validating the Azure SQL server during authentication. By default, this parameter is set to none.

Measurements made by the test

Measurement

Description

Measurement Unit

Interpretation

Status

Indicates the current state of this database instance.

The states that can be reported by this measure, the numeric value that corresponds to each state, and a brief description of the state are provided below:

Numeric Value	State	Description
0	ONLINE	Database is available for access. The primary filegroup is online, although the undo phase of recovery may not have been completed.
1	RESTORING	One or more files of the primary filegroup are being restored, or one or more secondary files are being restored offline. The database is unavailable in this case.
2	RECOVERING	Database is being recovered. The recovering process is a transient state; the database will automatically become online if the recovery succeeds. If the recovery fails, the database will become suspect. The database is unavailable in this case.
3	RECOVERY_PENDING	SQL Server has encountered a resource-related error during recovery. The database is not damaged, but files may be missing or system resource limitations may be preventing it from starting. The database is unavailable. Additional action by the user is required to resolve the error and let the recovery process be completed.
4	SUSPECT	At least the primary filegroup is suspect and may be damaged. The database cannot be recovered during startup of SQL Server. The database is unavailable. Additional action by the user is required to resolve the problem.
5	EMERGENCY	User has changed the database and set the status to EMERGENCY. The database is in single-user mode and may be repaired or restored. The database is marked READ_ONLY, logging is disabled, and access is limited to members of the sysadmin fixed server role. EMERGENCY is primarily used for troubleshooting purposes. For example, a database marked as suspect can be set to the EMERGENCY state. This could permit the system administrator read-only access to the database. Only members of the sysadmin fixed server role can set a database to the EMERGENCY state.
6	OFFLINE	Database is unavailable. A database becomes offline by explicit user action and remains offline until additional user action is taken. For example, the database may be taken offline in order to move a file to a new disk. The database is then brought back online after the move has been completed.

The detailed diagnosis of this measure reports the user access mode of the database, the database recovery model, and the log re-use wait state of the database.

Note:

By default, this measure reports the States listed in the table above to indicate the current status of the database instance. The graph of this measure however, represents the status of the database using the numeric equivalents only i.e., 0 to 6 only.

Is in standby?

Indicates whether/not this database instance is in standby mode.

The values reported by this measure and its numeric equivalents are mentioned in the table below:

Measure Value	Numeric Value
No	0
Yes	1

Note:

By default, this measure reports the Measure Values listed in the table above to indicate whether/not the database instance is in standby mode. The graph of this measure however, is represented using the numeric equivalents only i.e., 0 or 1.

Is read only?

Indicates whether/not this database instance is in READ_ONLY mode.

The values reported by this measure and its numeric equivalents are mentioned in the table below:

Measure Value	Numeric Value
No	0
Yes	1

Note:

By default, this measure reports the Measure Values listed in the table above to indicate whether/not the database instance is in READ_ONLY mode. The graph of this measure however, is represented using the numeric equivalents only i.e., 0 or 1.

User access mode

Indicates the user access mode to this database instance.

The states that can be reported by this measure, the numeric value that corresponds to each state, and a brief description of the state are provided below:

Measure Value	Numeric Value
Multi user	1
Single user	2
Restricted user	3

Note:

By default, this measure reports the Measure Values listed in the table above to indicate the user access mode to this database instance. The graph of this measure however, represents the status of the database using the numeric equivalents only i.e., 1 to 3 only.