SQL Server Backup and Recovery

The motive for backing up SQL Server data and systems hasn't changed much over the years: Protect the assets. The threats to those assets have diversified, as have specific recovery requirements. Today, the assets you're protecting often include application availability, not just data.While physical loss of data is always a concern, theft or other misappropriation is climbing up the list. Needs to maintain application availability, to stash copies of data in safe, remote locations, and to be able to readily recover data have given rise to new SQL Server 2005 features, as well as new backup and recovery strategies. Let's take a look at some of these.

Old and New Backup Options

As you might expect, enhancements to SQL Server 2005 are driving updates to SQL Server backup software—and driving users to look at new ways to address their varying requirements for data protection and rapid restoration. Database mirroring is new to SQL Server 2005. The feature streams SQL Server transaction logs to a mirror server, maintaining a second database and allowing connections to the mirror server when the principal server fails. Native encryption of both database storage and client-server communications is also new to SQL Server 2005.

There are several ways to back up SQL Server data. Traditional backup software vendors have always supported backing up to tape. For a number of reasons, backing up to disk has become a viable, popular option both as a primary storage medium and as a means of staging data for backup to offline or near-line media.

Several vendors of continuous data protection (CDP) products now offer specific support for SQL Server. (See below for more information about CDP.) Backup appliances and data mirroring appliances are other niche markets. Small businesses and users with desktop database applications can opt for one of the online Web-based backup services.With so many technologies available to support SQL Server data protection and recovery, you can mix and match technologies to achieve the level of data and application protection and recoverability that meets your business needs.

Backup Appliances

Tandberg Data's InteliStor is an example of a NAS appliance with integrated tape backup. The various InteliStor models are configured with Microsoft Windows Storage Server 2003, Symantec Backup Exec Quick Start, varying amounts of storage, and tape cartridge backup capabilities sufficient to back up the storage. Appliance solutions are intended to reduce the technical skill level required to implement and manage a particular IT function. However, as Adam Carheden indicated in his review of backup appliances, these aren't install-and-forget solutions.Although backup appliances simplify acquisition and installation, configuring backups and restoring data still takes a knowledgeable person. Add SQL Server to the mix ( supported by the SQL Server module for the appliance's backup software), and backup appliances aren't that easy to use.

Online Backup Services

A growing number of services allow you to back up files over the Internet to their storage farms—a convenient way to safeguard relatively small volumes of data—and some now support SQL Server databases. Small businesses and desktop database users should consider using one of these Webbased backup services.

NovaStor offers an online service called NovaNet-WEB. Enhancements are planned for July to specifically support SQL Server 2005 and SQL Server 2000 backup in two ways: using Microsoft Volume Shadow Copy Service (VSS) for open file backup and using the SQL Server backup API.VSS quiesces SQL Server activity long enough to map a snapshot of current database file structures, after which the snapshot data is encrypted for transmission to NovaStor. The SQL Server backup API allows the NovaNet-WEB agent on the server to obtain updates to protected tables. The agent compresses changed data, encrypts it, and sends it to NovaNet-WEB backup servers, where it's stored in an encrypted format. This type of backup archives changed data in a way that allows point-in-time recovery.

Restoring normal SQL Server backups requires that you understand the correct full, incremental, and log backups to apply.With NovaNet-WEB, you need not be concerned with the physical structure of the backups in order to restore a table. "Being able to restore from a synthesized full backup—simply selecting a database without worrying about reassembling the correct full and incremental backups—saves administrators both time and potentially their jobs," according to Chris Harvey, NovaStor CTO.

Support for 64-bit versions of Windows Server 2003,Windows XP, and SQL Server 2005

Speed and ease of recovering system and user data structures backed up in different ways

The ability to encrypt backup copies of data to protect them from unauthorized use, and the means for long-term management of the encryption keys vital to your use of the backup

Compression capabilities and strategies to reduce the amount of disk and tape needed to store backup data

Support for CDP

The ability to encrypt data as it's transmitted to the target backup device and as it resides on that device is often an option—an important one, especially if you contract for safe offsite storage of sensitive private or corporate information.

Earlier versions of SQL Server supported three primary backup types. A full backup allows you to restore a complete database to the point in time of the backup. Differential backups build on previous backups by copying only data pages that have changed since the last full or differential backup. Transaction log backups copy transaction log data and in the case of a bulk log backup, data pages changed by bulk operations. To these standard full, differential, and transaction log backup types, SQL Server 2005 adds some new twists.All data and differential backups now include log records.A new partial backup option that allows you to skip backing up read-only file groups is available.

Continuing the trend toward 64-bit support, many backup software vendors have added x64 support to their products. However, if you plan to run under an x64 OS, don't assume your backup solution vendor has x64 support—ask. While the list of x64-supporting vendors is growing rapidly, support isn't yet a given.

CDP

SQL Server–ware CDP is available from several vendors, although at this writing, Microsoft System Center Data Protection Manager (DPM) lacks this feature. CDP technology fully replicates a database, then monitors the database for changes and writes them to storage media as they occur. This creates a log of changes and allows point-in-time recovery of files and tables.

One CDP vendor, XOsoft, offers products that bridge the gap between data backup and high availability, with some unique capabilities thrown in. XOsoft's core functions for SQL Server are found in Enterprise Rewinder SQL. Rewinder is a file replication product that operates at the level of the file system stack rather than using SQL Server's backup VDI. Rewinder can recognize the various kinds of SQL database activity, including checkpoints, in order to maintain an accurate consistent replica. Because Rewinder also logs all database change activity, it can "unapply" changes (i.e., restore a database to an earlier point in time before some kind of corruption occurred). Rewinder supports x64 OSs.

A separate XOsoft module, Assured Recovery, makes a replica available for normal use—for example, for testing recovery plans or to create a VSS snapshot for a traditional point-in-time backup. While the replica is open for use, Assured Recovery maintains two activity logs: one that logs all the activity against the open replica and another that logs the activity for the primary production copy of the database.These two logs allow you to test your recovery procedures against the replica, then return the replica to a state consistent with the primary production copy of the database. XOsoft's WANSyncHA adds monitoring and application failover to the replica server.

SAN vendors also offer snapshot backup features with support for SQL Server. While each SAN vendor has its own proprietary technology to implement snapshot backup, the solutions often combine the following features and capabilities:

SAN firmware to quickly capture a map of the volume's data blocks that are in use at the moment of the snapshot and preserve the data as further updates occur.

Server applications to ensure logically complete and consistent snapshots for applications such as SQL Server.

Other server applications to make a snapshot copy of data available for use.

Trends

I uncovered three key backup and recovery trends while writing this article.The first is the move away from tape in favor of disk as a primary backup medium. Driving this trend is a perfect storm in the form of reduced cost of disk storage, reduced cost of the bandwidth needed to transmit copies of data to a remote site, and software improvements to more easily create, manage, and work with disk-based backups and replicas. Disk backup is a most welcome trend for anyone who has worked with tapes or had to budget for their use.

The second trend follows naturally from the first—the increased use of tiered backup and recovery solutions.The use of snapshots, replication, and mirroring lets you minimize or effectively eliminate application downtime for backing up data. Backing up and archiving data isn't an isolated activity anymore—it's part of a larger business-continuation, high-availability strategy.

The third trend stems from the use of database technology in more desktop applications. The enhanced capabilities of SQL Server 2005 Express Edition (over Microsoft SQL Server Desktop Engine—MSDE) combined with Microsoft's promotion of Visual Studio 2005 Express editions (students, your first apps are free!) will drive this trend. WinFS (Microsoft's long-promised database-oriented file system) might have been stripped from the next Microsoft OS you'll use, but your users' next desktop application might very well employ database technology, and you'd better have a strategy to protect and recover that data.