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 ("Disk-Based Backup Appliances Go Head-to-Head," July 2005, InstantDoc ID 46613), 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.