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