Transcript
LiteSpeed® Fast Compression Quick Start Guide Using Fast Compression Technology in LiteSpeed 6.1
Rev 47
Written by David Gugick Director, Product Management Quest Software, Inc.
Copyright Quest® Software, Inc. 2010. ALL RIGHTS RESERVED. This guide contains proprietary information, which is protected by copyright. The software described in this guide is furnished under a software license or nondisclosure agreement. This software may be used or copied only in accordance with the terms of the applicable agreement. No part of this guide may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying and recording for any purpose other than the purchaser's personal use without the written permission of Quest Software, Inc.
WARRANTY The information contained in this document is subject to change without notice. Quest Software makes no warranty of any kind with respect to this information. QUEST SOFTWARE SPECIFICALLY DISCLAIMS THE IMPLIED WARRANTY OF THE MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Quest Software shall not be liable for any direct, indirect, incidental, consequential, or other damage alleged in connection with the furnishing or use of this information.
TRADEMARKS LiteSpeed® is a registered trademark of Quest Software, Inc. LiteSpeed ® contains patent-pending technology. All other trademarks and registered trademarks used in this guide are property of their respective owners. World Headquarters 5 Polaris Way Aliso Viejo, CA 92656 www.quest.com email:
[email protected] U.S. and Canada: 949.754.8000 Please refer to our Web site for regional and international office information.
Updated—November 2010
Contents Intro to Fast Compression .............................................................................................................. 1 Fast Compression Example ........................................................................................................... 2 Creating a Fast Compression Database Backup from the Backup Wizard ................................ 3 Setup a Fast Compression Database Backup Task in a Maintenance Plan .............................. 9 First Time Use ............................................................................................................................... 10 Fast Compression Validations and Checks................................................................................. 11 Fast Compression and the LiteSpeed User Interface ................................................................ 12 Fast Compression and the Windows Event Log ......................................................................... 12
3
Intro to Fast Compression Fast Compression (formerly SmartDiff) is a patent-pending, backup technology which minimizes the amount of disk space required to store backups and reduces the time to complete those backups. Fast Compression leverages LiteSpeed for SQL Server compression, the SQL Server differential engine, user-defined thresholds, and a multi-layered validation system to manage the entire database backup process, making what used to be a difficult administration task, easy to implement and manage. Fast Compression solves the inherent side-effects of performing nightly, full database backups when only a small percentage of the actual database has changed. Most databases change at a relatively low rate; usually about 1-5% per day. Yet DBAs will almost always perform nightly full backups on these databases. This means if a database changes only 2% on the first day after a full backup, the DBA backs up 98% of the database that did not change. On the next day, the DBA backs up 96% of unchanged data. On the next, 94%. And so on. This results in unnecessary backup processing and disk space utilization. It also results in reduced maintenance windows. Fast Compression manages the backup process by intelligently deciding whether to perform a full database backup or a differential backup. It does this based on two user-defined thresholds: The Full Backup Interval and the Data Change Threshold. DBAs simply decide the maximum time between full database backups (default is 14 days) and how much data change should trigger a full backup (default is 20%) and Fast Compression automatically performs the right type of backup. Should a problem like a missing backup file in the backup set or a backup validation issue occur, the Fast Compression engine can optionally escalate to a full backup. This multi-layered validation system ensures your backups are in the best possible shape to help guarantee you can recover in the case of disaster.
1
Fast Compression Example The following example illustrates the advantages of moving from a nightly, full backup strategy with LiteSpeed to Fast Compression. In this example, a database of 500GB experiences about 2% data change per day and compresses an average of 80% using LiteSpeed. Over a 14-day period, LiteSpeed Full Backups consume over 1.4 Terabytes of storage and take approximately 615 Minutes to complete (at a backup rate of 100 MB/Sec). Using Fast Compression, this is reduced to only 344 Gigabytes of storage and approximately 125 Minutes of backup time; a recovery of over 1 Terabyte of disk storage and the elimination of over 8 hours of backup processing over that period.
Reduces disk storage, saving valuable disk space Reduces backup time, greatly expanding available maintenance windows Provides insurance against missing or corrupt backups, improving the ability to recover a database
2
Creating a Fast Compression Database Backup from the Backup Wizard 1. Open the Backup Wizard 2. Choose a database to back up and select the “Fast Compression” option as the Backup Type
3. Select the Backup Directory or Directories if Striping
Note: Fast Compression handles the naming of files automatically. Since the database name is incorporated into the backup name, you can safely select the same directory for all databases on an instance.
3
4. Select the Fast Compression Backup Type (Single-File vs Multi-File Backup Sets)
Select whether you prefer to manage a single file for each backup set (Self-contained backup sets option) or you prefer to create a unique file for each backup (Separate backup files option) NOTE: A backup set is composed of the full database backup plus all associated differential backups The Self-Contained Backup Sets option provides the convenience of only having to manage a single file per backup set. Only one file needs to be saved to or pulled from tape or copied from the backup location to a secondary location. The Separate Backup Files option creates a unique file for each backup in the backup set. This option provides the convenience of having to move less data to tape or across the network when copying individual backup files. Using this option means that (separate from the log file backups) up to two physical files may needed to restore the database (full backup plus the associated differential for the day in question). The Self-Contained Backup Sets option automatically verifies the Full backup exists. The Separate Backup Files option performs the same validation by default, but Fast Compression includes an option to not perform this check.
4
5. Select Fast Compression Backup Options
The Force a Full Backup option sets the maximum interval (in days) between full backups. This setting places a hard limit on the number of days between full backups, regardless of how much underlying database data has changed. For example, set this parameter to 14 days to ensure that, at a minimum, every 14 days you run a full backup. Data Change Threshold controls the amount of data change allowed before a new full backup is executed. Fast Compression measures the amount of data change by either querying SQL Server or by comparing the size of the last differential to the last full backup. For example, set this parameter to 20%, and should the database change by 20% or more, Fast Compression will automatically run a Full backup. NOTE: The larger the threshold, the larger the differential backups can grow before Fast Compression triggers the next Full backup NOTE: The option to Query Actual Data Pages provides the most accurate way to determine the amount of data change. If the query fails for any reason, Fast Compression will automatically run a size comparison to the last Differential backup The Backup Escalation option instructs the Fast Compression engine to automatically run a full backup should a problem be encountered. This setting provides insurance against unanticipated errors. For example, if a backup file is missing from the backup set (someone accidentally deleted it), or there is some other type of issue like a Log Sequence Number (LSN) validation error or file corruption, you would not normally be able to restore the database. To correct for this potential issue with backups, Fast Compression automatically runs a full backup to put the database in a restorable state. Errors are still noted in the LiteSpeed user interface and alerts will still be sent via the job. If you uncheck this option and Fast Compression discovers an issue, you will have to correct the problem manually. Correction may require forcing a full backup using the @ForceFull parameter, described in the First Time Use section, or restoring a missing backup file to the Fast Compression backup folder. Any errors 5
should be investigated, regardless of whether or not Fast Compression escalates the backup. These conditions are rare, but Fast Compression protects against them nonetheless. Under normal operating conditions, you should not experience these types of issues as they are normally caused by accidental deletion of files or disk corruption, both of which occur very infrequently. 6. Select Verification and Cleanup Preferences
The optional verify process runs the LiteSpeed equivalent of a RESTORE VERIFYONLY to make sure the backup files in the backup set have integrity. This provides an added level of insurance the backup files can be restored. You have four verify choices: Do not verify any backups (the default), verify only the last backup operation, verify the full plus the last differential backup, or verify the entire backup set. Verification failures appear in the LiteSpeed UI and, optionally, as job failure notifications. A verification failure after a differential backup will trigger the backup escalation process, if selected. Cleanup provides a convenient way to remove old backups from disk without disrupting Fast Compression. You have the option to remove full/differential backups and/or transaction log backups. The cleanup routine (which can be run outside of Fast Compression by calling the xp_slsSmartCleanup procedure) is backup set aware. This is important because the cleanup will never remove a full backup that is needed by a differential backup that is not being deleted. The cleanup leaves backup sets intact. If you use the Separate Backup Files option in Fast Compression, you have the added flexibility of being able to remove differential backups from the active backup set that are no longer needed. Fast Compression is cleanup aware and understands which backups have been removed via cleanup vs those that have been deleted through other means. This prevents Fast Compression from raising errors if it detects a missing backup from a backup set that was removed via the cleanup process. To implement cleanup in the Fast Compression job, select the appropriate options. For example, if you need to recover from a disk-based backup for 14 days, set the 6
Cleanup for full/differential to 14 Days. If you only require disk-based, point-in-time backups from transaction logs for 3 days, then set that option to 3 Days. 7. Set LiteSpeed Options and Schedule
Set the standard LiteSpeed backup options (compression, encryption, advanced options) and then set the backup schedule. Ideally, you would set Fast Compression to run once a day, but custom scheduling options are available. You could, for example, run Fast Compression every 6 Hours if you wanted. Fast Compression provides an option for Full backup exclusion days. This feature guarantees that Fast Compression will never run a Full backup on any of the selected days, even if a threshold is reached. For example, if you have a need to ensure Saturday night maintenance tasks are not fighting for server resources with a running Full backup, you can select Saturday to ensure Fast Compression will never run a Full backup on that particular day.
7
8. Set Job Notification Options
Use the Job Notification feature to be notified when any type of failure occurs, whether that failure was due to a failed backup, a validation issue like a missing Full or Differential backup, an LSN verification issue, or a failed Verify operation. 9. Schedule Transaction Log Backups
You can optionally schedule transaction log backups for the database right from the Fast Compression wizard. This is a convenient option which allows DBAs to schedule a comprehensive backup implementation in one step. Transaction log backups are scheduled as a separate job from Fast Compression. 10. Finish the wizard to create the Fast Compression backup job
8
Setup a Fast Compression Database Backup Task in a Maintenance Plan 1. Create a new maintenance plan 2. Add a Fast Compression task 3. Double-click the Fast Compression Task 4. Select the databases to be backed up and select the Fast Compression backup options (Fast Compression options are described in the previous section)
9
First Time Use Prior to first use, please review this section: 1. When backing up the master database as part of a Fast Compression maintenance plan or job, Fast Compression always executes a full backup. SQL Server does not allow differential backups of the master database. By performing a full backup each time, Fast Compression ensures that you can include the master database in a comprehensive maintenance plan. 2. We recommended that a new folder be used for Fast Compression backups. If you decide to back up to an existing folder, Fast Compression performs some validations to see if a full backup already exists that can be used:
If it is determined the last full backup was not performed by Fast Compression, but a valid, full backup exists in the designated Fast Compression folder, then Fast Compression uses it and begins the process with a differential backup, preventing the need to run an initial full backup on the database.
If it is determined the last full backup was not performed by Fast Compression and that full backup is either missing or has an LSN verification issue, Fast Compression starts off by executing a full backup.
If the last Full backup was performed by Fast Compression and is either missing or has an LSN verification issue, Fast Compression escalates to a full backup (if the escalate option is selected) or continues with a differential backup (if escalation is not selected). If manual correction is required, DBAs should leverage the parameters described in the next item to force a full backup to correct the problem.
3. As a part of the generated job step (T-SQL), we include two commented parameters in the xp_slsFastCompression call: @ForceFull=1 and @ForceDifferential=1. If you ever need to run an ad-hoc full or differential backup, simply copy the SQL call to xp_slsFastCompression, uncomment the desired parameter, and execute from Management Studio or your favorite query tool. 4. If you elect to exclude specific days of the week from Full backups and Fast Compression is set to execute the first time on an excluded day, assuming no full backup exists that can be used as described above, Fast Compression will not execute a full backup. This will continue until Fast Compression runs on a day that is not excluded.
10
Fast Compression Validations and Checks Fast Compression performs some or all of the following validations and checks depending on selected options: 1. Ensures the full backup physically exists. Without a full backup, the backup set is incomplete and will result in an inability to restore the database. 2. Ensures all differential backups in the backup set physically exist. If a differential backup is missing, you are unable to easily restore the database to the day the differential backup was performed. Transaction log backups could be used to restore, but this process could be time-consuming. 3. Ensures the Log Sequence Numbers (LSNs) for the backup set match up properly with each other and match what is logged in the msdb system database. This process ensures that the msdb system database and the backup files are consistent with one another and that there are no logical validation errors. Differential backups cannot be restored if their LSN values do not logically match up with the full backup. 4. Starts the process with a full backup if the most recent full backup was done to a folder different that the Fast Compression backup folder or was executed outside of Fast Compression (not a Fast Compression backup) or is not a LiteSpeed compatible backup. 5. Verifies the backup is readable and can be restored (Verify option). If a backup does not verify, it may not be possible to restore it. 6. Escalates the backup if a problem is detected and the escalation option is selected. The important thing is that the database is never in a state where is cannot be restored. Backup escalation helps to prevent such issues. 7. Logs any issues to the LiteSpeed Activity screen 8. Alerts the user if the Fast Compression job fails for any reason (Notification option)
11
Fast Compression and the LiteSpeed User Interface LiteSpeed includes a new backup type in the LiteSpeed Activity screen called “Fast Compression Backup”. Any Fast Compression validation failures or errors encountered show up here. The actual Full and Differential backups still show up as individual items, separate from the Fast Compression issues. As you see below, there are two entries for the highlighted backup: A differential backup and a Fast Compression backup. The differential backup succeeded, but because of a missing full backup, Fast Compression reports an error as the database cannot be restored without the missing full backup. Double-click on the row in question to see a detailed explanation of the Fast Compression error.
Fast Compression and the Windows Event Log LiteSpeed logs Fast Compression validation failures and errors encountered to the Windows Event Log under the SQL Server Instance or SSIS in the case of maintenance plans.
12
