LiteSpeed for SQL Server 8.6 - User Guide

About Backing Up/Restoring with LiteSpeed LiteSpeed User Interface Configure LiteSpeed for First Use Cloud Back Up Databases Automate Maintenance Tasks Restore Databases Restore Objects View Activity and History Use Command-Line Interface Use Extended Stored Procedures Troubleshoot LiteSpeed Review Additional Resources

Using the Cloud

LiteSpeed is able to:

  • backup to Microsoft Azure and Amazon S3.
  • restore databases and specific objects from Microsoft Azure and Amazon S3.
  • read transaction logs from Microsoft Azure and Amazon S3.

Tip: Set the default compression level to 7 for cloud backups. Using a higher compression level has real savings. Reducing the number of bytes sent to the cloud makes for faster backups and restores and reduces Internet bandwidth.

Using Microsoft Azure

You can get started using Microsoft Azure with LiteSpeed by first setting up a Microsoft Azure account and then registering your account with LiteSpeed.

Setting up Your Microsoft Azure Account

The first step in using Microsoft Azure with LiteSpeed is to create your own account.

To create a Microsoft Azure account

  1. Log in with your existing Microsoft credentials at: https://azure.microsoft.com/en-us/free/ to setup your initial free Microsoft Azure account.

  2. Click Sign up for a free trial.

  3. Complete the Microsoft Azure information page and click Sign up. The Welcome to Microsoft Azure web page is displayed.

  4. Click Start managing my service.

Registering Your Microsoft Azure Account with LiteSpeed

After setting up your Microsoft Azure account, register your account with LiteSpeed before running LiteSpeed backups and restores.

To register a Microsoft Azure account with LiteSpeed

  1. Click .
  2. Click Cloud Accounts.
  3. Click Add.
  4. Select Cloud vendor Azure Blob from the drop-down list.
  5. Enter a display name for this account.
  6. Enter the Storage Account name.
  7. Enter the Access key.
  8. Select Storage type Block Blobs or Page Blobs from the drop-down list.
  9. Enter the Container name or select an existing name from the drop-down list.
  10. Click to deselect or select SSL security.
  11. Click to select or deselect Government account.
  12. Click to deselect or select Automatic Striping. The automatic striping options for block blobs are: auto, 10, 25, 50, 100, and 190 GB. The automatic striping options for page blobs are: auto, 10, 25, 50, 100, 250, 500, and 995 GB.
  13. Click Test Connection to verify that all entered information is working. When successful, the Cloud Accounts screen is displayed with your new account listed.
  14. Click OK to save your entries. Your Microsoft Azure account is now registered with LiteSpeed.

Running LiteSpeed Backups to Microsoft Azure

After first setting up your Microsoft Azure account, and registering your Cloud account with LiteSpeed, you now can run backups to Microsoft Azure using the LiteSpeed Backup Wizard.

To run LiteSpeed backups to Microsoft Azure

Tip: There are three ways to back up to the Microsoft Azure: Backup Wizard, Maintenance Plans, or Backup Templates.

  1. Run the LiteSpeed Backup wizard and make your appropriate selections.
  2. Select Cloud from the destination list and complete the Backup Destination page.

  3. Select the Microsoft Azure account that you created from the Accounts list.

  4. Conclude making the Backup wizard selections and finish by executing the backup.

    Tip: You can review the LiteSpeed cloud backup scripts at the last wizard step before executing the Cloud backup. You can also view an example cloud backup database script here (scroll to the bottom of the page).

Running LiteSpeed Restores from Microsoft Azure

After first setting up your Microsoft Azure account, registering your Cloud account with LiteSpeed, and running backups to Microsoft Azure, you now can run restores from Microsoft Azure using the LiteSpeed Restore Wizard.

To run LiteSpeed restores from Microsoft Azure

  1. Run the LiteSpeed Restore Wizard and make your appropriate selections.
  2. Select Cloud from the Backup Source page and select the Database from which to restore.
  3. Conclude making the Restore wizard selections and finish by executing the restore.

    Tip: You can review the LiteSpeed Cloud restore script at the last wizard step before executing the Cloud restore. You can also view an example Cloud restore database script here (scroll to the bottom of the page).

Using Amazon S3

You can get started using the Amazon S3 with LiteSpeed by first setting up your Amazon S3 account and then registering your Cloud account with LiteSpeed.

Note: LiteSpeed contains all the necessary software and components necessary for using the Amazon S3 and running LiteSpeed backups and restores.

Setting up an Amazon S3 Account

The first step in using the Amazon S3 with LiteSpeed is to create your own Cloud account.

Note: Amazon changes their Cloud account Web UI on a regular basis. Therefore the actual icons, tabs, and selection buttons might be different than what is indicated in the procedures below.

To create an Amazon S3 account

  1. Setup your Amazon account by registering on: http://aws.amazon.com.

  2. Click the IAM icon on the Cloud administration console to create a Cloud user.

  3. The User Access Key IDs and Secret Access Keys are automatically generated.

  4. Write down and save the Access Key IDs and Secret Access Keys. You will use these values later in the LiteSpeed Cloud account registration.

  5. Select the Permissions tab in the Cloud admin console and assign user Administrator Access permissions.

  6. Use the Cloud admin console to create a Bucket Name and Region. This is the location where your backup files will be stored.

  7. Write down and save the Bucket Name and Region. You will use these values later in the LiteSpeed Cloud account registration.

  8. Use the following procedure to register your Amazon S3 Cloud account with LiteSpeed.

Registering an Amazon S3 Account with LiteSpeed

After setting up your Amazon S3 account, then register your Cloud account with LiteSpeed before attempting LiteSpeed backups and restores.

Tip: Make sure to select Cloud Vendor = Amazon S3. Refer to Cloud Account Settings.

To register an Amazon S3 account with LiteSpeed

  1. Click .
  2. Click Cloud Accounts.

  3. Click Add.
  4. Select Cloud vendor Amazon S3 from the drop-down list.
  5. Enter the display name for this account.
  6. Select the authentication type.

    Select Access and Secret Keys to enter the access key and secret key in LiteSpeed.

    1. Enter the Access key saved in Step 4 from the "To create your Amazon S3 account" procedure above.
    2. Enter the Secret key saved in Step 4 from the "To create your Amazon S3 account" procedure above.

    Select IAM Roles to grant permission to the Amazon S3 resource without specifying the Access and Secret key to LiteSpeed. The IAM Role supplies temporary permissions that LiteSpeed can use to make calls to other Amazon Web Services applications. For information on creating and using IAM roles, refer to the Amazon Web Services documentation.

  7. Enter the Region name saved in Step 7 from the same procedure above.
  8. Enter the Storage Class from the drop-down list. The options are standard, standard-infrequent access, and reduced redundancy storage.
  9. Enter the Bucket name saved in Step 7 from the same procedure above.
  10. Click to deselect or select SSL security.
  11. Click to select Use Server Side Encryption (AES-256).
  12. Select 'Use Amazon S3 Transfer Acceleration Speed' to use Amazon's S3 Transfer Acceleration feature which allows for increased upload speed to S3 storage up to 200% in some cases by using local CloudFront endpoints.
  13. Click to select GovCloud (US) Region.
  14. Click to deselect or select Automatic Striping. The automatic striping options are: auto, 10,25, 50, 100, 250, 500, 1000, and 1995 GB.
  15. Click Test Connection to verify that all entered information is working. When successful, the Cloud Accounts screen is displayed with your new account listed.
  16. Click OK to save your entries. Your Amazon S3 account is now registered with LiteSpeed.
  17. Use one of the following procedures and run the LiteSpeed Backup or Restore Wizard to save backups to the Amazon S3 Cloud or restore files from the same.

Running LiteSpeed Backups to Amazon S3

After first setting up your Amazon S3 account, and registering your Cloud account with LiteSpeed, you now can run backups to Amazon S3 using the LiteSpeed Backup Wizard.

Tip: There are three ways to back up to the Amazon S3 Cloud: Backup Wizard, Maintenance Plans, or Backup Templates.

Caution: The Amazon S3 only runs backup to new files. Appending to an existing file is not supported by the Amazon S3 . Existing backups can only be overwritten. The option "Overwrite existing object" is available in the Backup Wizard and MP (Back Up Database task). The option is turned off by default.

To run LiteSpeed backups to Amazon S3

  1. Run the LiteSpeed Backup wizard and make your appropriate selections.
  2. Select Cloud from the destination list and complete the Backup Destination page.

  3. Select the Amazon S3 account that you created from the Accounts list.
  4. Conclude making the Backup wizard selections and finish by executing the backup.

    Tip: You can review the LiteSpeed cloud backup scripts at the last wizard step before executing the Cloud backup. You can also view an example cloud backup database script here (scroll to the bottom of the page).

Running LiteSpeed Restores from Amazon S3

After first setting up your Amazon S3 account, registering your Cloud account with LiteSpeed, and running backups to Amazon S3 , you now can run restores from Amazon S3 using the LiteSpeed Restore Wizard.

To run LiteSpeed restores from Amazon S3

  1. Run the LiteSpeed Restore Wizard and make your appropriate selections.
  2. Select Cloud from the Backup Source page and select the Database from which to restore.
  3. Conclude making the Restore wizard selections and finish by executing the restore.

    Tip: You can review the LiteSpeed Cloud restore script at the last wizard step before executing the Cloud restore. You can also view an example Cloud restore database script here (scroll to the bottom of the page).

 

Related Topics

Cloud Account Settings

Cloud Account Settings

You can setup the Microsoft Azure Blob Cloud and Amazon S3 Cloud account and proxy settings in the Back Up Databases wizard, Restore Databases Using the Restore Wizard, and Create Backup Templates wizard.

NOTE: LiteSpeed supports the following cloud storage: Amazon S3 and Microsoft Azure Blob.

Cloud account

Select the Cloud account from the drop-down list.

Select the following items to add or edit the registered cloud account settings.

  • Add - (For Amazon S3 only), click to add cloud vendor, display name, authentication, region, storage class (standard, infrequent access, reduced redundancy storage), and bucket. Select: use SSL, use server side encryption (AES-256), GovCloud (US) Region, and automatic striping (auto, 10, 25, 50, 100, 500, 1000, 1995) GB. Select 'Use Amazon S3 Transfer Acceleration Speed' to use Amazon's S3 Transfer Acceleration feature which allows for increased upload speed to S3 storage up to 200% in some cases by using local CloudFront endpoints.
  • Add - (For Azure Blob only), click to add cloud vendor, display name, storage account name, access key, storage type (block blobs or page blobs), container, use SSL, government account, and automatic striping . Options for block blobs are: auto, 10, 25, 50, 100, 250, 500, 1000, 2000, 3000, 4000 and 4300 GB. Options for page blobs are: auto, 10, 25, 50, 100, 250, 500, and 995 GB.
  • Edit - (For Amazon S3 only), click to edit display name, authentication, region, storage class (standard, infrequent access, reduced redundancy storage), and bucket. Bucket name must conform to DNS naming requirements and must not contain periods ("."). Select: use SSL, use server side encryption (AES-256), GovCloud (US) Region, and automatic striping. Options for automatic striping are: auto, 10, 25, 50, 100, 250, 500, 1000, and 1995 GB.

    Select 'Use Amazon S3 Transfer Acceleration' to use Amazon's S3 Transfer Acceleration feature which allows for increased upload speed to S3 storage up to 200% in some cases by using local CloudFront endpoints. Additional data transfer charges may apply. See Amazon S3 pricing for more details.

  • Edit - ( For Azure Blob only), click to edit display name, access key, storage type (block blobs or page blobs), container, use SSL, government account, and automatic striping. Options for block blobs are: auto, 10, 25, 50, 100, 250, 500, 1000, 2000, 3000, 4000 and 4300 GB. Options for page blobs are: auto, 10, 25, 50, 100, 250, 500, and 995 GB.
  • Delete - Click to delete the Cloud account from the console.
  • Import - Click to import a saved Cloud account in XML format.
  • Export - Click to export and save a Cloud account in XML format.

Proxy Settings

Select the following items to edit the Cloud account proxy settings.

  • Use LiteSpeed Server proxy settings - Click to use the server proxy setting. This is the default selection. You can also edit the proxy settings from this item.
  • Use LiteSpeed Console proxy settings - Click to use the console proxy setting. You can also edit the proxy settings from this item.
  • Specify custom proxy settings - Click to add your own custom proxy address, port, username and password.

Cloud Automatic Striping

Cloud Automatic Striping

Striping is splitting one backup file into multiple files. The advantage of striping is that it provides higher upload performance when backing up to the Cloud and overcomes some Cloud limitations (e.g. maximum object size on Azure Block Blob storage is 4.75TB). Striping provides a significant performance benefit and time saving when backing up large database files. For example, striping three blocks of one database file up provides three times the upload bandwidth over backing up one large database file to a single cloud container.

Setting up Cloud Automatic Striping

LiteSpeed provides an automatic striping option for backing up databases to the Microsoft Azure and Amazon S3 Clouds. The option can be set to auto which enables LiteSpeed to manage the striped size. Alternately, you can set the striping size in GB. If the database size is larger than the specified stripe size then the file is striped when backed up.

Tip: Quest Software recommends using LiteSpeed defaults.

To enable cloud automatic striping

  1. Click .
  2. Click Cloud Accounts.
  3. Click Edit.
  4. Click to select Automatic Striping. The automatic striping options:

    • Microsoft Azure blob storage (Block blob): auto, 10, 25, 50, 100, 250, 500, 1000, 2000, 3000, 4000 and 4300 GB.

      Note: Maximum size of block blob supported by Microsoft Azure is 4.77 TB.

    • Microsoft Azure blob storage (Page blob): auto, 10, 25, 50, 100, 250, 500 and 995 GB.

      Note: Maximum size of page blob supported by Microsoft Azure is 1000GB.

    • Amazon S3: auto, 10, 25, 50, 100, 250, 500, 1000 and 1995 GB.

      Note: Maximum size of object supported by Amazon S3 is 5000GB. LiteSpeed is limited by 2000GB.

  5. Click Test Connection to verify that all entered information is working. When successful, the Cloud Accounts screen is displayed with your new account listed.
  6. Click OK.

 

Related Topics

Back Up Databases

Test Optimal Backup Settings

Higher compression levels result in smaller backup files, but they also may consume additional CPU. If the server does not have sufficient CPU, then the backup may take longer to complete. The Backup Analyzer evaluates different settings, such as compression level, striping, and backup destinations, to help you determine optimal backup settings.

NOTE: When running the Backup Analyzer, follow these guidelines for the best results:
Minimum: 1 GB sample size.
Recommendation: 10 GB sample size. The combination of compression levels, encryption options, and backup locations should be considered. For example, if you sample 4 GB of data with 10 different tests, LiteSpeed will take the same amount of time as it takes to back up 40 GB. On very large databases, you can speed up the analysis by reducing the number of backup combinations or by reducing the amount of sample data.

Backup Analyzer Wizard

The Backup Analyzer wizard guides you through selecting the backup parameters to test. The wizard tests all of the different combinations of your selected parameters by backing up a portion of the database. It does not interfere with existing backup schedules or sets.

To run the Backup Analyzer wizard

  1. Select the Backup Manager pane (CTRL+1).

  2. Right-click the database and select Backup Analyzer Wizard.

    Tips:

    • To test striped backups, click Add to add several destinations and select the Test backup striping to selected backup locations checkbox on the Backup Location page.
    • If you add more than two destinations, select both checkboxes to test all destination combinations.
    • If you select a large number of backup parameters, you may want to schedule the tests to run at a specified time within the LiteSpeed Analyzer job.
  3. Click Next.
  4. On the Backup Location screen, select Backup to (Disk, Cloud, or TSM Backup) and Backup locations. Add or remove backups from the locations. Additionally select striping options.
  5. Click Next.
  6. On the Data Sample screen, enter the amount of data to sample in megabytes or the percentage of data to test.
  7. Click Next.
  8. On the Compression Level screen, select the compression levels to test. Any combination of compression levels from 0 to 8 are available. You can also specify additional backup advanced options.

    Review the following additional information about the advanced options:

    Compression threads

    Determines the number of threads used for the backup. You will achieve the best results by specifying multiple threads, but the exact value depends on several factors including: processors available, affinity setting, compression level, encryption settings, IO device speed, and SQL Server responsiveness. The default is n-1 threads, where n is the number of processors.

    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.

    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.

    TIP: Before you start tuning the CPU Throttle or Affinity parameters to adjust backup performance, try limiting the number of threads. If you decide to use an affinity value other than default, it is recommended that you limit the threading as well.

    NOTE: LiteSpeed defaults typically result in the best performance. You should only modify advanced options after careful planning and testing. For more information, see Configure LiteSpeed Defaults.

  1. Click Next.
  2. On the Execute Script screen, the list of databases to test is displayed.
  3. Click Next. The Backup Analyzer Wizard test scrip runs and displays the test results summary.
  4. Complete the wizard.

NOTE: After the required number of bytes is received for analysis, the process is intentionally aborted. This generates the VDI error messages in the LiteSpeed log files and the SQL Server error log. Please ignore them. ClosedClick here to see the examples of the error messages.

  • BackupMedium::ReportIoError: write failure on backup device 'VDI_B0F1C91D-201F-48DD-9D4B-5B16A1445D53_0'. Operating system error 995(The I/O operation has been aborted because of either a thread exit or an application request.).
  • BackupVirtualDeviceFile::RequestDurableMedia: Flush failure on backup device 'VDI_B0F1C91D-201F-48DD-9D4B-5B16A1445D53_1'.
  • BackupVirtualDeviceFile::ClearError: failure on backup device 'VDI_B4677313-6D48-4BC0-AC74-B3D0976E6077_1'.
  • SQLVDI: Loc=TriggerAbort. Desc=invoked. ErrorCode=(0).
  • SQLVDI: Loc=SignalAbort. Desc=Client initiates abort. ErrorCode=(0).

Scheduling Backup Analyzer

You can schedule analyzing from within the Backup Analyzer Wizard. An SQL Server job with the name "LiteSpeed Analyzer SERVERNAME.DATABASENAME" is created. View the list of Backup Analyzer jobs directly from the Backup Analyzer tab by clicking "Show Analyzer Jobs." Right-click on the job. You can change schedule, start/stop, disable or even delete the job. To view the script, select the "View in Job Manager" option and open the job properties from the Job Manager module. You can then go to the job step details.

Backup Analyzer Tab

The Backup Analyzer tab consists of the toolbar, graph, and grid. The Backup Analyzer toolbar is used to select the following items:

  • Existing test - View the existing test or use the drop down to select other tests.
  • Purge All - Click to remove all Backup Analyzer tests for the database.
  • Show Analyzer Jobs - Click to view all Backup Analyzer jobs.
  • New Test - Click to run the Backup Analyzer Wizard and begin a new test.
  • - Click to export the grid to Microsoft Excel.
  • - Click to print the grid.

The Backup Analyzer tab presents the test results in a graph and grid format:

  • Graph—Displays the backup duration and compression amount for each test in a bar graph so you can easily compare the results between size and duration. If you hover the mouse over the bar graph, test number, size, and duration are displayed. If you select a test in the grid, LiteSpeed indicates the corresponding test in the bar graph with a yellow border around it. You can view previous tests by changing the Existing test field. 

  • Grid—Displays the details of each test in columnar format. LiteSpeed indicates its recommendation with the number indicated first in the Test Number column and a yellow box around the test bar graph. Review the following for additional information:

    Tip: .Use the Grid horizontal scroll bar to view other table columns.

    Column Description
    Test Number

    The number assigned by the Backup Analyzer.

    Level

    The compression level that is used for a particular database backup test.

    Encryption

    Indicates enabled encryption for the database backup.

    Estimated Duration

    The approximate time allotted to run the database backup.

    Estimated Backup Size (MB)

    The approximate size of the backed up database.

    Database Size (MB)

    The amount of disk space the database occupies.

    Compression (MB)

    The amount of disk space you can save with this compression ratio.

    Compression (%)

    Data compression ratio.

    Read Speed (MB/Sec) Maximum read speed.
    Throughput (MB/Sec) Actual speed of processing data (compression and encryption).
    Backup Speed (MB/Sec) Virtual write speed of the backup. Compare this value to Throughput and Read Speed to determine if you are experiencing a write-bound issue.
    Write Speed (MB/Sec) Actual write speed. It shows how quickly the compressed data stream is written to the destination disk.
    Stripes Number of stripes associated with the backup.
    Destination Backup destination directory location.
    Threads Number of threads associated with the backup.

    You can double-click a row for more information about the test.

    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.

 

Related Topics

Related Documents