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.

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.

Closed Click here to see additional information for this scenario.

NOTES:

You should review log shipping requirements before creating a plan. For more information, see Log Shipping Requirements.
Performing an individual transaction log backup on the publisher will break the log shipping plan.

To create a log shipping plan

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

Scenario: Select the database you want to copy to another server.

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. Scenario: For all time intervals, use the default values. Scheduling the backup, copy and restore jobs with the same frequency keeps the subscriber as close to the publisher as possible.
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.

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.

Closed Click here for more information

Adaptive Compression	LiteSpeed automatically selects the optimal compression based on throughput and CPU usage and optimize backups either for size or for speed (default). NOTE: Adaptive Compression is only available with LiteSpeed 6.5 or later; Enterprise license.
Compression level	Select 0 for no compression or 1-8 to compress the file. Higher compression levels result in smaller backup files, but they also take longer to complete. Note: LiteSpeed supports backing up, restoring and shipping transaction logs of the databases encrypted with transparent data encryption (TDE). If you want to compress the backup, you should choose compression level 1 to minimize CPU, since using a higher level of compression will only cause CPU to increase without any real benefit on the backup file size. If you choose compression level 0, LiteSpeed will not attempt to compress the backup. See the "Compression Methods" section of the LiteSpeed User Guide for more information. For more information, see Compression Methods.
Encrypt backup	Select this checkbox to encrypt the backup. Then, select the encryption level and enter the encryption password. CAUTION: When encrypting data, take care not to lose the encryption password; a backup cannot be restored or recovered without the original encryption password.
Set the following parameters manually:
Compression threads	Enter the number of threads used for the backup. The default is n-1 threads, where n is the number of CPU cores.
Max transfer size	Enter the maximum backup file size in bytes. . The possible values are multiples of 65536 bytes (64 KB) ranging up to 4,194,304 bytes (4 MB). The default is 1048576.
Buffer count	Enter the number of SQL Server buffers available for a LiteSpeed operation. The default is set by SQL Server.
CPU throttle	Enter the maximum percentage of CPU that LiteSpeed can use for the process. The default is 100.
Processor affinity	Click to select which processors LiteSpeed can use. The default is 0, which allows LiteSpeed to use all available system processors. For more information, see Processor Affinity.
Processor priority	Select the priority of the backup over other transactions or processes running on the same server. The default is Normal.
Logging level	Select one of the following options: None—LiteSpeed does not write a log file for the backup or restore operation. Verbose—LiteSpeed writes a log file for the backup or restore operation. Verbose. Log file is removed on success—LiteSpeed only saves log files if the backup or restore operation fails. If it succeeds, LiteSpeed does not save the log.
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.

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. Scenario: Select both the Read-only and Disconnect users when restoring backup options.
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.

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

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.

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:

Please select your product:

To serve you better, please complete the Purpose of your Chat:

Recommended Solutions for Your Problem

LiteSpeed for SQL Server 8.8 - Configure Log Shipping Guide