Portals eNewsletters Web Seminars dataWarehouse.com DM Review Magazine
DM Review | Covering Business Intelligence, Integration & Analytics
   Covering Business Intelligence, Integration & Analytics Advanced Search

Resource Portals
Business Intelligence
Business Performance Management
Data Integration
Data Quality
Data Warehousing Basics
More Portals...


Information Center
DM Review Home
Conference & Expo
Web Seminars & Archives
Current Magazine Issue
Magazine Archives
Online Columnists
Ask the Experts
Industry News
Search DM Review

General Resources
Industry Events Calendar
Vendor Listings
White Paper Library
Software Demo Lab
Monthly Product Guides
Buyer's Guide

General Resources
About Us
Press Releases
Media Kit
Magazine Subscriptions
Editorial Calendar
Contact Us
Customer Service

The Need for Better SQL Server Backups

  Article published in DM Direct Newsletter
January 21, 2005 Issue
  By Robin Schumacher

There is perhaps no greater responsibility for the database administrator than establishing proper backup and recovery plans. Although the database administrator (DBA) performs other important tasks, nothing approaches the critical nature of ensuring the protection of key corporate data. For this reason, DBAs should take great pains to see that a customized backup and recovery plan is put in place for each critical database and that such plans are practiced on development servers to ensure that each plan and disaster recovery scenario actually works. Not doing so can lead to ugly surprises if a true failure occurs.

Backup and recovery operations are becoming more complex to plan and supervise due to a number of industry trends. Corporate data growth is definitely on the rise with a 2003 study by industry analyst group IDC uncovering an average data growth rate of 42 percent a year for corporations. In addition to explosive existing database volume growth, the proliferation of new databases is also occurring at a rapid rate. Many large customers experience a managed database to DBA ratio of 24 to 1, with that number steadily increasing. Large data volume growth coupled with mushrooming numbers of databases makes it more difficult for the DBA to design and manage backup plans that maintain high availability while minimizing performance and storage impacts.

Nothing personifies the above trends better than Microsoft SQL Server. Another 2003 database study done by IDC shows Microsoft enjoying the largest percentage gains in market share over the other database giants. One noteworthy change regarding Microsoft was the scale at which SQL Server is now being used. Once relegated to departmental applications, SQL Server is now being used more widely for enterprise-class applications. Such systems bring with them larger data volumes and increased visibility. The number of actual SQL Server databases is also on the rise, primarily because it is so easy to create additional Microsoft databases. Today's SQL Server DBA is waking up to backup and recovery issues that used to be experienced only by Oracle and DB2 DBAs like enlarging backup windows, increased storage need (to house backups for quick recovery), security concerns and more.

This article maps out a strategy for DBAs who are currently struggling with old techniques and tools to keep up with their growing SQL Server environment's backup and recovery needs.

Backup/Recovery Checklist

When it comes to data governed by Microsoft SQL Server, a DBA needs to (at a minimum) use the below checklist to ensure that a proper backup and recovery plan is in place to protect key corporate data:

  • Do I have a customized plan in place for my database that addresses its specific data recovery needs
  • Am I intelligently conserving storage space used by backup files?
  • Am I securing backup files to guard sensitive data?
  • Is my backup plan set up in an automated fashion to reliably occur without my intervention?
  • Am I kept informed of backup activities, especially when a backup operation fails?
  • Am I sure that my backup files are valid and can be used for recovery?
  • Am I doing everything possible to minimize the performance impact of backup operations?
  • Can I easily manage my backup/recovery strategy across all SQL Servers?

A DBA obviously wants to be able to answer, "Yes" to each question above. Positive responses to these questions will enable a DBA to reduce the burden of managing their backup plan and allow them to focus on other aspects of their demanding job. Let's look at each question more closely and walk through how a combination of SQL Server built in functionality and support of third party tools makes it easy to create and maintain even the most complex backup and recovery plans.

Choosing the Right Backup for Each Database

Each database has its own particular backup and recovery needs. Some static databases can get by on nightly full or differential backups while a very dynamic and visible database might require a specialized point-in-time recovery plan. When mapping out a backup plan, the DBA needs to ascertain the recovery needs of a database from the application project leads that drive the overall system. Once determined, the DBA can then go about crafting a customized backup plan for the database.

SQL Server offers a number of different backup methods that let a DBA build a plan that exactly matches the recovery needs of a database. The basic backup methods include:

  • Full - a complete, referentially intact, snapshot of the database is taken. This may be done while users continue to work on the system.
  • Differential - a copy of all the pages in a database that have been modified since the last backup is made. Typically smaller than full backups, differential backups usually run quicker and have less performance impact on the system.
  • File/File Group - for very large databases with high availability requirements, the DBA can intelligently split out various objects among certain files and file groups and only back up parts of a database when needed.
  • Transaction Log - for systems requiring point-in-time recovery, backups are made of the transaction log at regular intervals that can later be applied with Full (or other) backups to bring a database back to the last transaction log backup.

If a DBA is choosing a third-party software solution for their backup and recovery operations, they need to make sure that the software supports all the different SQL Server backup methods and can also intelligently perform recovery operations by taking a variety of different backup files into account if need be (Full, Differential, and then a Transaction Log backup for example) to restore a database in one collective task.

Reducing Storage Costs

With data growth doubling every 2-2.5 years, a DBA can be challenged to find ways to reduce the storage costs of accumulating large backup files. Normally, DBAs want to keep recent backup files close at hand should a recovery be necessary, but this can be difficult for very large databases that require generous amounts of storage space. Some SQL Server DBAs have attempted to use native Windows file compression to reduce the amount of storage consumed by backup files. However, because of documented recovery problems that arise from the use of natively compressed files, most DBAs steer clear of using this type of cost-savings technique.

Fortunately, there are ways to reduce the storage space required for SQL Server backup files. Several third party vendors offer a DBA the ability to compress backup files during the actual backup operation. When such files are used for recovery, the compression algorithms are used in reverse during the restore. The end result is, oftentimes, drastic storage savings with complete safety in using backup file compression.

Ensuring Security

Security has become more prominent in recent years with DBAs being one of the lucky people to be placed in the hot seat with respect to ensuring corporate data protection. While passwords, system and object level privileges, auditing and other standard database security practices are the norm for DBAs, the need to protect every aspect of the database is becoming more important. Backup files represent one of the ways database security can be breached. Although created in binary format, backup files can still be programmatically read and interpreted through various "hacker" means. For databases that contain particularly sensitive information, the DBA needs to find a way to ensure that the security of their database is not violated through the interception and decoding of backup files.

Although Microsoft doesn't currently offer specific protection for backup files, a number of third-party tools provide powerful encryption features that protect sensitive backup files. Utilizing encryption, these tools can encrypt backup files to negate any possibility of security being breached through the decoding of a SQL Server backup file.

Automating Backup Operations

It goes without saying that DBAs should schedule backups to occur in an unattended fashion. Reliable scheduling is especially key for databases requiring frequent transaction log backups as log backups may occur as often as every five minutes.

SQL Agent is what many DBAs use today for SQL Server-only scheduling. A few rely on Task Scheduler, although this requires that the workstation remain up. Enterprise SQL Server DBAs are turning more to third-party scheduling packages that allow them to manage all the backup/recovery operations across the many servers in their organization. This especially becomes important for those who have to manage not only SQL Server jobs, but also operating system and other cross-platform database related jobs (Oracle, Sybase, DB2) across different operating system platforms (UNIX, Linux, Windows, etc.)

Staying Informed

A DBA needs to have a solid method in place for staying informed about all their backup operations, be able to quickly review backup history and have the capability to be immediately notified when critical backup or recovery operations fail.

The free tools bundled with SQL Server provide some support in this area but are somewhat lacking in terms of delivering the "big picture" on the status of all backup tasks in the enterprise. As SQL Server continues to grow in visibility, SQL Server DBAs are beginning to find the need for more global monitoring, alerting and reporting of their backup plans and operations.

Validating Backups

It's the horror story no DBA wants to live through. A critical restore is needed, but for some reason errors are encountered with the SQL Server backup files during recovery, with the net result being critical data loss. How can a DBA protect themselves against such a thing?

For starters, DBAs can task the SQL Server engine to read and verify the integrity of a backup operation after it has concluded. However, for critical databases, a DBA may want to go the extra mile to ensure that backup files won't fail in recovery situations. Smart DBAs will take backup files from critical production databases and automate recoveries into development databases that serve as a mirror copy of production. Doing this confirms that all backup files are valid and can be used for actual recovery. It also provides a nice reporting database for ad hoc decision reporting, the activities of which might otherwise impact concurrency on the live production database.

Minimizing the Performance Impact of Backup Activities

One of the nice things about backups performed in SQL Server is that they allow work to continue in the database being backed up. However, even though concurrency is not an issue, the backup operation does impact the overall performance of the system, and a DBA should do whatever is possible to minimize this performance hit.

Of course, the first thing to try and do is run backups in off hours when the system is either not being used or is undergoing the least amount of system activity. Next, backup windows can be shortened by a carefully built plan of running full backups infrequently (once a week), followed by differential backups (backing up only what's changed since the last full backup) and then transaction log backups if granular point-in-time recovery is required. Sometimes, though, for large databases, even this strategy falls short of the desired backup window because the system is either very dynamic and/or very large.

Third-party tools can come to the aid in such situations. When compression is applied to backup files during the backup operation, the amount of actual physical I/O is reduced, with the end result oftentimes being reduced backup runtimes. In addition to overall elapsed time reductions, if backups are executed over a network, the amount of network bandwidth is also greatly reduced resulting in less stress being placed on the overall IT ecosystem.

Reducing Backup Management Time

The proliferation of SQL Server databases normally equates to increased management time for the DBA because each new database brings with it certain responsibilities like space management, monitoring and optimization, as well as backup and recovery tasks. DBAs need a way to more quickly supervise the activities of not only individual databases, but of their entire database farm as well. This can be especially difficult if the DBA has to work cross-platform and support not only SQL Server, but also other databases like Oracle, Sybase, and IBM DB2.

While Microsoft Enterprise Manager is a nice help to small SQL Server installations, it fails to provide the high-powered ammunition needed by DBAs who have to tackle large environments. This is where third party tools can come to the rescue. If a DBA can use the same intuitive console to handle things like space, performance, and capacity, then overall management time can be reduced.

DBAs face a challenging environment of increasing data growth and escalating database numbers. In such environments, backup and recovery responsibilities can be difficult to manage, so the DBA would be wise to utilize the checklist covered in this article to ensure their backup strategy properly addresses all their environment's recovery needs.


For more information on related topics visit the following related portals...
Databases and High Availability/Disaster Recovery.

Robin Schumacher is vice president of Product Management, Embarcadero Technologies, Inc., a leading supplier of database software tools. Schumacher has 16 years of experience with database engines and has been a feature writer and software reviewer for many database related publications. He is the author of Oracle Performance Troubleshooting from Rampant Press, which was recently updated to include Oracle 10g diagnostics. He can be reached at Robin.Schumacher@embarcadero.com.

Solutions Marketplace
Provided by IndustryBrains

Embarcadero ER/Studio 6.6
ER/Studio delivers next-generation data modeling. Multiple, distinct physical models based on a single logical model give you the tools you need to manage complex database environments and critical metadata in an intuitive user interface.

Data Mining: Strategy, Methods & Practice
Learn how experts build and deploy predictive models by attending The Modeling Agency's vendor-neutral courses. Leverage valuable information hidden within your data through predictive analytics. Click through to view upcoming events.

Help Desk Software Co-Winners HelpSTAR and Remedy
Help Desk Technology's HelpSTAR and BMC Remedy have been declared co-winners in Windows IT Pro Readers' Choice Awards for 2004. Discover proven help desk best practices right out of the box.

Dedicated Server Hosting: High Speed, Low Cost
Outsource your web site and application hosting to ServePath, the largest dedicated server specialist on the West Coast. Enjoy better reliability and performance with our screaming-fast network and 99.999% uptime guarantee. Custom built in 24 hours.

Get SAP Technologies Training on DVD
For the first time ever, access SAP Technologies Training at your convenience with the TechEd '04 DVD. Each package includes 100s of hours of SAP training lectures & hands-on workshops.

Click here to advertise in this space

E-mail This Article E-Mail This Article
Printer Friendly Version Printer-Friendly Version
Related Content Related Content
Request Reprints Request Reprints
Site Map Terms of Use Privacy Policy
SourceMedia (c) 2005 DM Review and SourceMedia, Inc. All rights reserved.
Use, duplication, or sale of this service, or data contained herein, is strictly prohibited.