Chat now with support
Chat with Support

LiteSpeed for SQL Server 8.8 - Configure Log Shipping Guide

LiteSpeed Log Shipping

Log shipping is a critical feature for disaster recovery. It allows you to automate backing up a database (the publisher) and restoring its transaction logs on one or more standby databases (the subscribers). The process runs automatically throughout the day at the interval you specify, which creates synchronized databases.

LiteSpeed for SQL Server provides flexible backup options for log shipping and allows you to create and modify LiteSpeed log shipping plans, monitor shipping progress and troubleshoot plan problems.

About Log Shipping Plans

In case you want to have a copy of your database on another server, you can configure a plan that contains only a publisher and one subscriber.

If you require more than one destination database, you can have multiple subscribers. This allows you to distribute your data at different rolling points in time for reporting and to cushion the impact of invalid data entry into the main system.

Log Shipping Jobs

The Backup job is created on publisher server. The Copy and Restore jobs are created on subscriber server(s).

Shared Folder

In all cases, the publishing server will backup to the specified shared folder where the subscriber server will copy backups from. Any server involved in the log shipping process can act as a file server. You can also specify the local path on the publisher server.

Destination Folder

The subscriber server copies the transaction log backups from the shared folder to the specified destination folder and then restores them. It is recommended that you select a local destination folder on the Subscriber. The destination folder is also used for undo files to track incomplete transactions.

Initializing Log Shipping

A log shipping plan will not work unless it is initialized by restoring a full backup of publisher database to the subscriber database. The backup is written to and restored from the shared folder.

LiteSpeed Log Shipping Architecture

The diagram below shows the relationship between the LiteSpeed and SQL Server components involved in log shipping.


Log Shipping Requirements

Requirements

NOTE: This section only focuses on requirements specific to log shipping and implies you have reviewed the general LiteSpeed requirements. For more information, please refer to the "Privileges and Grant Requirements" section of the LiteSpeed Installation Guide.For more information, see Privilege and Grant Requirements.

General

  • LiteSpeed for SQL Server installed on all SQL Server instances involved in log shipping.

  • Microsoft SQL Server version 2005/2008/2012/2014/2016/2017.

  • Publisher and subscriber must have the same SQL Server version.

  • Publisher and subscriber must have the same LiteSpeed version.

  • SQL Server Agent must be running for all SQL server instances involved in log shipping.

NOTE: Backup log shipping is not supported in the Express edition of SQL Server.

Database

  • All databases involved in log shipping must use a full or bulk-logged recovery model.

  • A database can only have one log shipping publication (LiteSpeed or native).

  • A local repository must exist on both the publisher and subscriber.

Permissions

  • (On publisher) SQL Server Agent Service Account must have access and read/write permissions to the shared directory to write transaction log backups to. The same permissions are required for SQL Server Service Account used to initialize and reinitialize log shipping. You only need write permissions if you do not specify a local folder for the publisher.

  • (On subscriber) SQL Server Agent Service Account must have access and read permissions to the shared directory to copy transaction log backups from. The same permissions are required for SQL Server Service Account used to initialize and reinitialize log shipping.

NOTE: You need to register all SQL Server instances involved in log shipping in the LiteSpeed UI Console to retrieve log shipping data for them. Please refer to the "Register Server Instances" section of the LiteSpeed Installation Guide for more information.

 

Related Topics

Create Log Shipping Plans

The Create LiteSpeed Log Shipping Plan wizard helps you set up a publisher database and one or more subscriber databases.

Scenario

Your company has a web site that customers use to query financial information. It is required that customers do not query the database that receives live updates by applications. You need to create a copy of that database on another server for customers to query it online.

ClosedClick here to see additional information for this scenario.

NOTES:

To create a log shipping plan

  1. Select the Log Shipping pane (CTRL+2).
  2. Right-click the server instance and select Create New Log Shipping Plan Wizard.

  3. Review the following additional information about the Backup Options page:

    UNC path to share folder where backup files are available for subscribers

    Enter the network path or click to navigate to it. You should always complete this field. 

    Note: UNC paths have the following format: \\<computer_name>\<shared_folder_name>.

    Specify local path

    Specify the local path if your SQL Server Service Agent is running on a local system account. If you enter a local path, the local stored procedures and jobs on publisher will use it instead of the UNC path. Otherwise, this field is not required.

    Perform full backup and then restore to initialize subscriber(s)

    Select this option to create a full backup and restore it on the subscribers when creating the plan. If you do not select this checkbox, you must perform a full backup of the publisher and restore it on the subscribers before finishing the wizard.

    Note: Log shipping will not work unless you perform a full backup when setting up the plan.

    Backup interval (min)

    The execution frequency of the backup job. By default, this job runs every 15 minutes.

    Retain backup files for (hrs) The amount of time to store transaction log files. The default is 1440 (24 hours).
    Alert threshold (min)

    If no transaction log backups have occurred during this amount of time, LiteSpeed displays an alert on the monitoring tab. See View Log Shipping Activity and History for more information.

    The default alert threshold is 45 minutes.

  4. Click the advanced options link to set advanced backup options.

    NOTE: LiteSpeed defaults typically result in the best performance. You should only modify advanced options after careful planning and testing.

    ClosedClick here for more information

  5. Add one or more subscribers on the Subscribers page.

    Recovery mode

    Loading prevents users from querying the database.

    Read-only allows users to query the database. If you select Read-only, you should select Disconnect users when restoring backup to ensure that LiteSpeed can perform the restore; otherwise, you will have to manually disconnect them.

    Copy interval (min) The execution frequency of the copy job. By default, this job runs every 15 minutes.
    Copy offset (min)

    The amount of time by which you want to offset the start time of a job. Use this option to avoid overlap of backup, copy and restore operations. The default copy offset is 100 (1 minute) and the restore offset is 200 (2 minutes).

    Restore interval (min)

    The execution frequency of the restore job. By default, this job runs every 15 minutes.

    Restore offset (min)

    The amount of time by which you want to offset the start time of a job. Use this option to avoid overlap of backup, copy and restore operations. The default copy offset is 100 (1 minute) and the restore offset is 200 (2 minutes).

    Database load delay (min)

    The amount of time you want the subscriber database to wait before it restores the transaction log backup.

    Using this option you can specify how far you want a subscriber to be behind the publisher.For more information, see About Log Shipping Plans..

    By default, load delay is set to 0 minutes, and backups are restored immediately when the restore job runs.

    Retain backup files for (hrs) The amount of time to store transaction log files on the subscriber. The default is 1440 (24 hours).
    Restore alert threshold (min) If no transaction log restores have occurred during this amount of time, LiteSpeed displays an alert on the monitoring tab. See View Log Shipping Activity and History for more information.The default alert threshold is 30 minutes.

    Tip: The timeline represents the recurring [B]ackup, [C]opy and [R]estore job start times and helps you identify the optimal frequency of the log shipping jobs and avoid overlap of backup, copy and restore operations.

    Destination folder for copied files The folder assigned to contain the destination copied files.
    Network Resilience

    If LiteSpeed fails to write disk backups or reads from disk, it waits and retries the operation. You can enable and disable and control the number of times to retry and the amount of time to wait before retrying.

    • Number of times to retry any given read/write attempt—The default is 4 retries. The maximum allowed setting is 1000 retries.
    • Wait period before each retry attempt (in seconds)—The default period to wait before retry is 15 seconds The maximum allowed setting is 300 seconds.

    For more information, see Network Resilience.

  6. Complete the wizard.

To edit a log shipping plan (for example, to add a new Subscriber)

Right-click the plan in the Server tree and select Edit Log Shipping Plan Wizard...

Note: You cannot edit the encryption password, because doing so would prevent the existing transaction logs from restoring.

To delete a log shipping plan 

Right-click the plan in the Server tree and select Delete Log Shipping Plan...

 

Related Topics

View Activity and History

The LiteSpeed UI Console provides information about native and LiteSpeed log shipping plans in the Log Shipping tabs. Depending on what level of the navigation pane tree you select, you can view the plans for a server, log shipping jobs, and activity history. You can view information about a category, subcategory, server instance, server group, all of your server instances, or a specific log shipping plan by selecting the appropriate level in the navigation pane tree.

NOTE: You need to register all SQL Server instances involved in log shipping in the LiteSpeed UI Console to retrieve log shipping data for them.

Tab Level Description
Overview All

Displays information about the backup volume savings, successful jobs, and failed jobs. You can view information about different dates by changing the Period field or clicking to select the dates from a calendar.

Log Shipping Plans Server instance

Displays all log shipping plans for the selected level with their publishing database, number of subscribers, and status. Double-click a plan to navigate to its Monitoring tab. Right-click to view the plan's jobs, properties, or refresh.

Tip: To view current status of all log shipping plans when connecting to the server instance and when refreshing the tab, select Tools | Options | Log Shipping and enable the Load log shipping plan statuses... option.

History Server instance and plan

Activity log: The History tab lists log shipping activity for the selected parameters in a grid.

You can change the parameters in the following fields:

  • Period—Select the time span, or select dates from a calendar.
  • Operation—Select the type of activity to display, such as backup or restore.
  • Status—Select the status of the activity, such as running or success.

Errors: If the activity had errors, double-click the row in the bottom of the pane to view the errors details.

Tip: Export the grid to Microsoft Excel or print the grid.

Monitoring Plan

Monitor shipping progress: The Monitoring tab represents the plan in a diagram with its shipping progress from the publisher to subscribers. The progress lines represent the amount of time until LiteSpeed generates an alert.

You can hover over the databases and progress lines for additional information.

The Monitoring tab has the following options:

  • Refresh—Refresh the diagram and current problems.
  • Properties—View the plan properties.
  • Display Jobs—View the plan jobs (the backup job created on publisher and copy and restore jobs created on subscriber).
  • Edit Log Shipping Plan—Launch the Edit Log Shipping Plan wizard.

NOTE: If you need to synchronize the publisher and subscriber databases or reinitialize log shipping, right-click the subscriber database and select the appropriate option. For more information, see Troubleshoot Log Shipping.

View suggested solutions: If there are any problems with the log shipping plan, LiteSpeed displays the problem and suggested solution in the bottom of the pane.

Tip: For panes that have grids, you can sort, group, move, and remove the columns:

  • To sort and group the records, right-click a column header and select the appropriate options.
  • To sort records against multiple columns, click column headers while holding the SHIFT key. For example, to sort by type and then by name, click the Type column header and then SHIFT+click the Name column header.
  • To add or remove columns, right-click a column header and select Column Chooser. Add a column by dragging it from the list into the column headers. Remove a column by dragging its column header into the list.
  • To move a column, drag the column header to the new location.

NOTE: You can group server instances in the navigation pane tree based on their category or server group. Categories are similar to server groups, but they offer different features. For more information, see Change Server Instance Grouping Methods.

 

Related Topics

Convert Native Log Shipping Plans

Convert Native SQL Server Plans

LiteSpeed for SQL Server lets you monitor both the LiteSpeed and native SQL Server log shipping plans. However, flexible backup options and ability to create, modify and reinitialize LiteSpeed log shipping plans in the LiteSpeed UI Console are only available for LiteSpeed log shipping plans.

The following table lists options available (•) for native SQL Server and LiteSpeed log shipping plans from the LiteSpeed UI Console:

Option Native Plan LiteSpeed Plan
Create, edit or delete log shipping plan  

Monitor log shipping progress

View history

Synchronize subscribers

Reinitialize log shipping

 

View properties for the plan components

Convert to LiteSpeed
(only available for SQL Server 2005 and higher)
 

Using LiteSpeed UI Console 5.1 or higher, you can convert native log shipping plans to LiteSpeed on the servers where you have LiteSpeed 5 or higher installed. This option is only available for SQL Server 2005 and higher.

To convert a plan to LiteSpeed

Right-click a native SQL Server log shipping plan in the Server tree and select Convert Native Log Shipping Plan wizard.

NOTE: A native SQL Server log shipping plan cannot be recovered after it is converted to LiteSpeed.

NOTE: For more information about native SQL Server log shipping, see the following:

 

Related Topic

Self Service Tools
Knowledge Base
Notifications & Alerts
Product Support
Software Downloads
Technical Documentation
User Forums
Video Tutorials
RSS Feed
Contact Us
Licensing Assistance
Technical Support
View All
Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating