SQL Backup Details Test

To prevent the data loss that may occur due to the sudden failure of a SQL database, administrators are often advised to schedule the automatic backup of the databases on the Microsoft SQL server. It is recommended that these backup jobs are scheduled to occur frequently (say, once a day), so that the backup is always in sync with the data backed up. Failed or unusually fast backup jobs should also be detected quickly and marked for closer scrutiny, as this can cause data non-sync and increase the risk of data loss when disaster strikes. This is why, administrators will find the SQL Backup Details test very helpful! This test monitors the backup jobs configured for every SQL database. In the process, the test reports the type of backup that is configured, when the last backup job ran, and how long it took. This way, administrators can quickly identify databases that are not backed up as frequently as they would like them to be, rapidly detect backup jobs that may have failed to run as per schedule, and can pinpoint those databases where the last backup was suspiciously fast. With the help of these inferences, administrators can fine-tune backup schedules and can troubleshoot backup failures.

Target of the test : A Microsoft SQL server

Agent deploying the test : An internal agent

Outputs of the test : One set of results for every database on the Microsoft SQL server 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. 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.

  6. password - The password of the specified user
  7. confirm password - Confirm the password by retyping it.
  8. 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.
  9. excludepattern - Provide a comma-separated list of programs/processes on the SQL server that need to be excluded from monitoring. The default value is none, indicating that all processes are monitored by default. To make sure that the test ignores a few processes, specify the process names as a comma-separated list. For example: SQL_Query_Analyzer,jTDS. You can also use wild card patterns in your specification - for instance, SQL*,*TDS,Microsoft*.
  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. ISPASSIVE – If the value chosen is yes, then the Microsoft SQL server under consideration is a passive server in a SQL cluster. No alerts will be generated if the server is not running. Measures will be reported as “Not applicable" by the agent if the server is not up.
  12. dd frequency - Refers to the frequency with which detailed diagnosis measures are to be generated for this test. The default is 2:1. This indicates that, by default, detailed measures will be generated every time this test runs, and also every time the test detects a problem. You can modify this frequency, if you so desire. Also, if you intend to disable the detailed diagnosis capability for this test, you can do so by specifying none against dd frequency.

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

Backup type:

Indicates the type of backup that is configured for this database.

 

The values that this measure can report and their corresponding numeric values are listed below:

Measure Value Numeric Value

Full

 1

Diff-Database

 2

T-Log

 3

File-File Group

 4

Diff-File

 5

Partial

 6

Diff-Partial

 7

Note:

By default, this measure will report one of the Measure Values listed in the table above to indicate the backup type of a database. However, in the graph of this measure, the same is represented using the numeric equivalents only.

You can use the detailed diagnosis of this measure to know the start time and end time of the backup operation, the time taken for the last backup operation, the user who has initiated the backup operation and name of the server in which the backup operation is performed.

Time taken for last backup operation:

Indicates the time it took for the last backup operation on this database to complete. 

Minutes

If the value of this measure is very low for a database, it warrants a probe, as it could be owing to the failure of the backup job.

Interval since last backup:

Indicates the number of days that have elapsed since this database was last backed up.

Minutes

If the value of this measure is over 1 day, it could imply one of the following:

  • The database is not been backed up regularly, or;
  • The last backup job on the database failed

Either way, further investigation may be required to determine the real reasons.