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