Data Protection for SQL Server backup methods

Two types of backups are available: Legacy backups and VSS backups.

Legacy backup

A Legacy backup is a specialized API backup that functions with the Microsoft SQL Server storage engine, as show in Figure 1-7. This is the type of backup provided by previous releases of Data Protection for SQL Server.

Figure 1-7 Data Protection for SQL Legacy backup communications

A Legacy backup creates a copy of all or part of a SQL Server database on Tivoli Storage Manager storage media. Data Protection for SQL Server provides selection mechanisms and the logic required to back up and restore SQL Server data.

When you initiate a Legacy backup operation, Data Protection for SQL executes the following steps:

1. Starts a session with the server using the IBM Tivoli Storage Manager API and information contained in a client options file.

2. Starts a session with the SQL server using an internal Microsoft interface, known as SQL-DMO (Distributed Management Objects).

3. Instructs the SQL server using the Microsoft SQL-VDI (Virtual Device Interface) to begin a backup of the selected database objects.

4. Receives data from the SQL server and sends it to the Tivoli Storage Manager server.

5. Ends the Tivoli Storage Manager server and Microsoft SQL Server sessions.

SQL

Server API SQL Data

Protection for SQL

API TSM SQL Database and Data Protection for SQL

Tivoli Storage Manager Server

The following characteristics are true of Legacy backups:

򐂰 Full, copy, incremental, differential, and database copy types are supported.

򐂰 Backup granularity is at the database level.

򐂰 Backups are stored on IBM Tivoli Storage Manager server storage.

򐂰 Backups are managed through IBM Tivoli Storage Manager server policy.

򐂰 Backups can be performed in a Microsoft Cluster Server (MSCS) environment.

򐂰 Backups provide Microsoft SQL Server database integrity check functionality.

VSS backups

A VSS backup uses Microsoft Volume Shadow Copy Service technology to produce an online snapshot (point-in-time consistent copy) of SQL Server data that can be stored on local shadow volumes or on Tivoli Storage Manager server storage. During a VSS backup, the SQL server is not in “backup mode” for an extended period of time because the length of time required to perform the snapshot is usually measured in seconds and not hours. In addition, a VSS backup makes it possible to take a snapshot of large amounts of data at the same time because the snapshot works at the volume level. VSS backups require IBM Tivoli Storage Manager for Copy Services to be installed, in addition to Data Protection for SQL and the IBM Tivoli Storage Manager backup-archive client. You can optionally use an alternate machine to move data to the IBM Tivoli Storage Manager server, an offloaded backup.

Note: When a backup is performed, Data Protection for SQL Server records information about the Microsoft SQL Server and database. This information is available for query and restore operations. The information about the names and sizes of the database filegroups and files is stored along with the database data, as a sub-object. This sub-object is referred to as metadata. You need this sub-object when you require information about individual database filegroups and files.

Chapter 1. Introduction 25 The VSS architecture is depicted in Figure 1-8.

Figure 1-8 General VSS architecture

Optionally, VSS backups can be stored locally on VSS shadow volumes that are directly accessible by the SQL system, as long as sufficient space is available for the snapshot. Local VSS shadow backups are fast because data is not

transferred to Tivoli Storage Manager server storage. Restoring these backups is also fast because the SQL Server data is not transferred from Tivoli Storage Manager server storage over the network.

After the snapshot is complete, it can be accessed directly through standard operating system functions or other applications - for example, you can back up and manage the VSS snapshot on an external storage manager (for example, IBM Tivoli Storage Manager). When this function is selected and after the snapshot is made, IBM Tivoli Storage Manager is automatically invoked to store the snapshot.

In this way, we can establish two uses for VSS backup. First, VSS backup is a backup in itself, so that multiple shadow copy generations can potentially be kept online for rapid restore (backup to local). Second, VSS backup is a means for providing extra backups to an external media (backing up to IBM Tivoli Storage Manager), so that the database can be restored even if the disk system containing the original database and the shadow copies fails. When a Data Protection for SQL backup is started, you must specify the backup destination: to local, IBM Tivoli Storage Manager, or both.

MIcrosoft

Primary application server Offload Offload

backup server

The following characteristics are true of VSS backups:

򐂰 Full VSS snapshot backups and full VSS offloaded snapshot backups are supported. Incremental, differential, and transaction log backup types are not supported. (See 1.6.2, “Microsoft SQL Server backup types” on page 26 for definitions of the types of backups.)

򐂰 Backup granularity is at the database level only.

򐂰 Backups are managed through Tivoli Storage Manager policy.

򐂰 Backups can be stored on local shadow volumes, Tivoli Storage Manager server storage, or both locations.

򐂰 Different policy settings can be defined for each storage location and backup method.

򐂰 Backups to Tivoli Storage Manager server storage can be offloaded to an alternate machine, to reduce the workload on the production servers.

򐂰 Backups can be performed in a Microsoft Cluster Server (MSCS) or Veritas Cluster Server (VCS) environment.