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.
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.
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.
|
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.
- 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.
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.
Scenario: If you need to encrypt backups, then select the encryption level and enter the encryption password. Otherwise, skip this step.
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.
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.
Processor priority
Select the priority of the backup over other transactions or processes running on the same server. The default is Normal.
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.
-
-
Add one or more subscribers on the Subscribers page.
Scenario: Specify one subscriber. Select the server where you want to have a copy of the publisher database.
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.
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. 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.
- 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 change roles in a existing log shipping plan
- Select an existing log shipping plan in tree and activate Monitor tab.
- Right-click the publisher or any of subscribers and select Change Role or Select as a New Subscriber menu items.
- Select new publisher and define new settings for subscribers in opened Change Role LiteSpeed Log Shipping Wizard.
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 |
| 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:
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:
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. |
to select the dates from a calendar.|
Tip: For panes that have grids, you can sort, group, move, and remove the columns:
|
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: