How to Achieve 40:1 Backup Compression with LiteSpeed® for SQL Server’s SmartDiff Written by Brent Ozar, SQL Server Domain Expert, Quest Software, Inc. Technical Brief © 2009 Quest Software, Inc. ALL RIGHTS RESERVED. This document contains proprietary information, protected by copyright. No part of this document may be reproduced or transmitted for any purpose other than the reader'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 All 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 e-mail: [email protected] Please refer to our Web site (www.quest.com) for regional and international office information. Updated—July 2009 CONTENTS INTRODUCTION .......................................................................................... 1 IN THEORY, DIFFERENTIALS ARE A HELPFUL SOLUTION .............................................. 2 IN PRACTICE, DIFFERENTIALS ARE A PAINFUL PROBLEM ............................................. 2 LITESPEED FOR SQL SERVER SMARTDIFF TECHNOLOGY TAKES THE PAIN OUT OF DIFFERENTIALS 3 CONFIGURATION OPTIONS ............................................................................... 4 DATA WAREHOUSES CAN SET IT AND FORGET IT WITH SMARTDIFF ............................... 5 SMARTDIFF EVEN COMPRESSES SHAREPOINT AND TDE-ENCRYPTED DATABASES ............... 5 GETTING STARTED NOW WITH SMARTDIFF ............................................................ 5 ABOUT LITESPEED FOR SQL SERVER WITH SMARTDIFF .............................. 6 FEATURES AND BENEFITS ................................................................................. 6 ABOUT THE AUTHOR ................................................................................... 7 ABOUT QUEST SOFTWARE, INC. .................................................................. 8 CONTACTING QUEST SOFTWARE ......................................................................... 8 CONTACTING QUEST SUPPORT ........................................................................... 8 i How to Achieve 40:1 Backup Compression with LiteSpeed for SQL Server's SmartDiff INTRODUCTION SQL Server can do differential backups, which capture only the parts of the database that have changed since the last full backup. Differential backups can be significantly smaller than full backups, which means less network traffic and shorter maintenance windows. However, differential backups are not always appropriate. If your database has a high rate of change, or its indexes are rebuilt each day, then a differential backup can be nearly as large as a full backup, and take nearly as long. In that case, using differential backups actually adds to your space requirements as well as the time and complexity of restores. But deciding when to perform full backups and when to use differentials—and ensuring that the right combination of backups is ready in case a restore is needed—often becomes so complicated that database administrators (DBAs) simply abandon the feature of differential backups altogether, losing all the potential benefits. Quest’s LiteSpeed® for SQL Server can help address this issue. Its SmartDiff technology lets you easily configure rules that enable it to decide when to do a full backup and when to do a differential—automatically. It’s as if you examined every database, every night, and made the best backup decision based on change rates, day of the week, retention needs, and more. LiteSpeed SmartDiff enables you to enjoy the benefits of differential backups, without the hassles. DBAs can achieve the same level of bulletproof recoverability they’ve come to expect from LiteSpeed, yet save company resources in the process. It’s a win-win for everyone. 1 White Paper In Theory, Differentials Are a Helpful Solution Consider the following backup schedule: • Sunday night – Perform a full backup. • Monday night – Perform a differential backup. If only 10% of the database has changed, then the differential will be only 10% of the full backup’s size, and will only take 10% of the time. • Tuesday night – Perform a differential backup. At this point, 20% of the database has changed since Sunday, and the differential will be 20% of the full backup’s size and take 20% of the time. Assuming we do differential backups all week long, and then switch back to full backups on the weekends, differentials would save a lot of space and time. When disaster strikes, a restore from backup would proceed as follows: 1. First, restore the most recent full backup. 2. Then restore the most recent differential backup. 3. Then restore all of the transaction log backups since the last differential. The differentials do add a step to recovery: step #2 would not be necessary if we weren’t doing differentials. In return for that slightly longer recovery time, however, differentials can give us smaller, faster backups when measured over spans of 7 to 30 days. In Practice, Differentials Are a Painful Problem The reality of differential backups, however, is that they’re painful to manage, difficult to predict, and don’t always pay off on their promises. After implementing a differential backup plan, DBAs have to continuously circle back to make sure the differentials are indeed smaller and faster than nightly full backups. For instance, in the case of OLTP databases with a high rate of change, differential backups can be just as large as full backups, thereby increasing the amount of storage required instead of decreasing it. Since the DBA needs to keep both the full backup and the most recent differential backup available, using differential backups in this case actually requires more storage than simply making a full backup each night. And if the differential is of significant size, then the backup storage actually got worse instead of better. Something as harmless as a nightly index rebuild job can also wreak havoc with differential backups. Consider this backup schedule: 2 • Saturday night – Full backup • Sunday through Friday – Differential backups How to Achieve 40:1 Backup Compression with LiteSpeed for SQL Server's SmartDiff At first, this implementation works great. But suppose the DBA adds a new task for Sunday: • Saturday night – Full backup • Sunday – Maintenance plan to rebuild indexes • Sunday through Friday – Differential backups The Sunday differential backup will now be as large as the entire full backup because so many of the pages change. In fact, all week long, the differential backups will be huge, and will take as long as full backups. Moreover, restores will take twice as long, since the Saturday full backup must be restored, followed by a large differential restore. Management is also more complex. To prepare for disasters, the DBAs not only have to make sure the full backup is always kept available. It’s no longer enough just to verify each night’s differential backup; DBAs have to verify that the last full backup is available and good as well. A differential backup by itself is worse than useless: if the full backup is accidentally deleted, then a restore is not possible, regardless of the success of the subsequent differential backup jobs. To avoid these kinds of problems, DBAs have to vigilantly patrol their differential backup schedules, make sure full backups aren’t accidentally deleted, monitor activity versus backup sizes for every single database, and adjust the schedule accordingly. Because this is so much work, DBAs often avoid differential backups altogether and just use nightly full backups instead. LiteSpeed for SQL Server SmartDiff Technology Takes the Pain Out of Differentials LiteSpeed SmartDiff backups use easy-to-configure rules to solve the challenges of differentials thereby bringing even more speed, compression and time savings to LiteSpeed’s already high-performance backup solution. To get started, simply replace your current full backups with LiteSpeed SmartDiff backups. LiteSpeed uses your guidelines to decide when to do a full backup and when to do a differential. It’s as if you examined every database, every night, and made the best backup decision based on change rates, day of the week, retention needs, and more. 3 White Paper Configuration Options Some of the configuration options for SmartDiff are shown below: Use the “Force a full backup” option to specify how long you’re willing to go without a full backup. Longer time intervals mean more backup savings, but the full backup must be kept for a longer period of time. LiteSpeed helps make sure the backup is available by offering several verification options, including the one selected in the sample screen, “Verify full and last differential.” Choosing this option protects you from disaster. The “Data change threshold” option provides flexibility on when a differential versus full backup is triggered. On small OLTP systems, the DBA may prefer to run with a low (20 to 30%) change threshold, since the size savings aren’t as large. With large data warehouses, on the other hand, DBAs may want to go with a much higher threshold, such as 50 to 60%. With multi-terabyte databases, saving 50 to 60% of the backup space is a significant achievement. LiteSpeed SmartDiff backups save more than just disk space: they send less data over the network, take less space on tapes, and maintenance windows take less time each night. DBAs can achieve the same level of bulletproof recoverability they’ve come to expect from Quest LiteSpeed, yet save company resources in the process. It’s a win-win for everyone. 4 How to Achieve 40:1 Backup Compression with LiteSpeed for SQL Server's SmartDiff Data Warehouses Can Set It and Forget It with SmartDiff Data warehouse nightly maintenance windows are constantly restricted. The business intelligence team wants the DBAs to spend less time backing up because the business needs to load more data. Data warehouses are the perfect candidates for SmartDiff because a fairly low percentage of the data changes on a daily basis. Data warehouse administrators may already be doing full backups once per week and differentials during the week, but LiteSpeed provides even more flexibility. Consider this combination of settings: • Avoid full backups during the week (Monday through Friday) • Wait for 50% data change before doing a full backup • Force a full backup every 30 days With these settings, DBAs may be able to go more than one week without a full backup. This presents an opportunity to do other types of maintenance during the weekend, such as DBCCs and index defrags, which are normally tough to achieve with very large databases. SmartDiff Even Compresses SharePoint and TDE-encrypted Databases Users love SharePoint because they can upload Word documents, Excel spreadsheets, PDFs, and other binary files to share with their team members. DBAs hate SharePoint because these binary files clog up the database with gigs of uncompressible data. Conventional backup compression operations don’t help shrink the backup sizes or shorten backup windows. SQL Server 2008’s new Transparent Data Encryption (TDE) has also been a thorn in the side of DBAs: it helps with privacy regulations, but it makes backups uncompressible. Until now, DBAs have had to choose between security and compression. SmartDiff solves these problems by only backing up the parts of the database that have changed. In the case of SharePoint content databases and TDE-encrypted databases, that’s a very small portion of the total database. Other vendors can’t deliver this functionality, because LiteSpeed’s patent-pending SmartDiff technology is like nothing else on the market. Only LiteSpeed can reduce your management time, backup sizes, and maintenance windows and do it all with one product: the new SmartDiff. Getting Started Now with SmartDiff Install the latest LiteSpeed and replace your current full backups with SmartDiff backups. We’re confident you’ll see impressive disk, time and tape savings within the very first week—all without introducing risk into your infrastructure. 5 White Paper ABOUT LITESPEED FOR SQL SERVER WITH SMARTDIFF LiteSpeed for SQL Server is a fast and flexible backup and recovery solution that allows DBAs to easily maintain complete control over the backup and recovery process. Its low-impact, high-performance compression technology enables users to reduce storage costs, as well as SQL Server backup and recovery windows. With its latest SmartDiff technology, LiteSpeed for SQL Server‘s compression ratio jumps from 20:1 to 40:1, compared to native backups. Its powerful SQL backup compression is comparable to storing more than a terabyte of data on a single USB thumb drive. The result is a unique backup and recovery approach, unmatched in flexibility, that greatly reduces backup times, creates a significantly smaller storage footprint, and maintains a high level of recoverability. LiteSpeed provides an enterprise view of the backup and recovery environment through an intuitive user interface. DBAs can script backup and recovery commands via T-SQL or a fully functional command-line executable. LiteSpeed offers flexible backup and recovery options, including restoration of individual database objects, and simple data migration to any SQL Server. And its remote installation capability allows for enterprise-wide deployment and upgrades. Features and Benefits SmartDiff Backup: Reduces backup size substantially and decreases backup times from hours to minutes while maintaining a high level of recovery without compromising security. Backup Analyzer: Allows users to test LiteSpeed parameters and backup locations. Every environment has unique challenges and the Backup Analyzer lets users expand the tuning capabilities with just a few clicks. Encryption: Offers nine embedded encryption levels that are applied to data during the backup process. Database Categorization: Categorizes databases according to user specifications (for example, production, development, and test databases). It also allows users to choose desired global views. Centralized Reporting: Supplies convenient, consolidated reports. Users can avoid checking each server one by one by creating a single report for failures, throughput, and compression rates across multiple servers. Double-Click Restore (exclusive): Allows users to create a database backup file that can be restored simply by double-clicking the backup file. Users can create portable backup files and further reduce restore times in a disaster recovery scenario. LiteSpeed is the only SQL backup solution to offer this feature. 6 How to Achieve 40:1 Backup Compression with LiteSpeed for SQL Server's SmartDiff Log Shipping Management: Allows users to easily configure and monitor both LiteSpeed and native log shipping plans, including all relevant latency alerting. Users can easily add and remove subscribers on demand. Log Reader: Recovers data stored in transaction log files by rolling back any operation and reconstructing transactions. Users can view and browse the online transaction log as well as LiteSpeed and native T-Log backups. Object-Level Recovery: Gives users the flexibility to restore individual objects from LiteSpeed and native backup files. Users can restore tables, schema and rows. Attached File Backup (exclusive): Enables users to attach relevant documentation to their database archives. Attached files can be restored individually or along with the database as part of the recovery operation. LiteSpeed is the only SQL backup solution to offer this feature. Backup Mirroring: Allows users to write a single backup to multiple locations simultaneously, eliminating the need to make individual copies. LiteSpeed’s backup mirroring is available on both SQL Server Standard and Enterprise Editions. LiteSpeed Maintenance Plans: Lets users craft maintenance plans with all the options of the native tool, regardless of how simple or complex the plans are. LiteSpeed adds compression, encryption, attached files, mirrored backups, TSM integration, and SmartDiff backup management. Tivoli Storage Manager (TSM) Integration: Schedules jobs and builds maintenance plans that can write directly to TSM without the need for an additional agent. ABOUT THE AUTHOR Brent Ozar is Quest Software’s SQL Server domain expert. Brent has a decade of broad IT experience that includes systems administration, project management, and database administration. In his current role, Brent specializes in database performance tuning, SANs, and data warehousing. Previously, Brent spent six years at UniFocus, a Dallas-based hospitality metrics company. Brent conducts training sessions, has written several technical articles, and blogs at http://www.brentozar.com/. 7 White Paper ABOUT QUEST SOFTWARE, INC. Quest Software, Inc., a leading enterprise systems management vendor, delivers innovative products that help organizations get more performance and productivity from their applications, databases, Windows infrastructure and virtual environments. Quest also provides customers with client management through its ScriptLogic subsidiary and server virtualization management through its Vizioncore subsidiary. Through a deep expertise in IT operations and a continued focus on what works best, Quest helps more than 100,000 customers worldwide meet higher expectations for enterprise IT. Quest Software can be found in offices around the globe and at www.quest.com. Contacting Quest Software Phone: 949.754.8000 (United States and Canada) E-mail: [email protected] Mail: Quest Software, Inc. World Headquarters 5 Polaris Way Aliso Viejo, CA 92656 USA Web site: www.quest.com Please refer to our Web site for regional and international office information. Contacting Quest Support Quest Support is available to customers who have a trial version of a Quest product or who have purchased a commercial version and have a valid maintenance contract. Quest Support provides around the clock coverage with SupportLink, our web self-service. Visit SupportLink at http://support.quest.com From SupportLink, you can do the following: • Quickly find thousands of solutions (Knowledgebase articles/documents). • Download patches and upgrades. • Seek help from a Support engineer. • Log and update your case, and check its status. View the Global Support Guide for a detailed explanation of support programs, online services, contact information, and policy and procedures. The guide is available at: http://support.quest.com/pdfs/Global Support Guide.pdf 8
© Copyright 2024