SQL Log Shipping Status Test

SQL Server Log shipping allows you to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances. The transaction log backups are applied to each of the secondary databases individually.

The following are the benefits of the SQL Server Log shipping:

  • Provides a disaster-recovery solution for a single primary database and one or more secondary databases, each on a separate instance of SQL Server.
  • Supports limited read-only access to secondary databases (during the interval between restore jobs).
  • Allows a user-specified delay between when the primary server backs up the log of the primary database and when the secondary servers must restore (apply) the log backup. A longer delay can be useful, for example, if data is accidentally changed on the primary database. If the accidental change is noticed quickly, a delay can let you retrieve still unchanged data from a secondary database before the change is reflected there.

Log shipping involves applying the transaction log from every insertion, update, or deletion made on the primary database onto the secondary database. Log shipping can be used in conjunction with replication, with the following behavior:

  • Replication does not continue after a log shipping failover. If a failover occurs, replication agents do not connect to the secondary, so transactions are not replicated to Subscribers. If a failback to the primary occurs, replication resumes. All transactions that log shipping copies from the secondary back to the primary are replicated to Subscribers.
  • If the primary is permanently lost, the secondary can be renamed so that replication can continue.

When log shipping feature is enabled on the target database, the transaction logs are applied to the secondary database quickly without loss of data from committed transactions. If the secondary database is not reachable or is not able to accept the transaction logs from the primary database, data may not be up to-date in the secondary database when a primary database goes down even for a short duration. This may result in data loss and eventually unhappy users! To avoid such unpleastaries, it is necessary to monitor the time duration that elapsed while applying the transaction logs from the primary to the secondary database. The SQL Log Shipping Status test helps administrators in this regard!

This test auto-discovers the secondary databases and for each secondary database, reports the lag time noticed while the data is transferred from the target database. By comparing the value of this test across the secondary databases, administrators can figure out the secondary database on which the transaction logs took too long to be applied. This way, administrators can figure out the secondary database that is currently not up to-date.

Note:

This test is applicable only to Microsoft SQL Server 2005 (and above).

Target of the test : A Secondary Microsoft SQL server with DR setup

Agent deploying the test : An internal agent / external agent

Outputs of the test : One set of results for each 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. 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. 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

T-log lagging duration

Indicates the time lag that was noticed between the target database and this secondary database.

Minutes

A sudden / gradual increase in the value indicates a longer than usual transaction gap between the primary and secondary database. This may lead to the data loss in the secondary database if the primary primary database is down.

The detailed diagnosis lists the Last restored time, Last restored file and the current server time

By comparing the value of this measure across the secondary databases, administrators can figure out the secondary database on which the transaction logs took too long to be applied. This way, administrators can figure out the secondary database that is currently not up to-date.