[Top] [Prev] [Next]

Chapter 11

Backup and Recovery

Backup and Recovery Features

SQL Server delivers high application availability by providing on-line maintenance, rapid recovery, and software-based fault tolerance¯24 hours a day, seven days a week. This continuous availability:

This chapter discusses:

Features that Provide High Availability

 Requirements for high availability are met by the capability for on-line maintenance, rapid recovery, and software-based fault tolerance:

Transactions and Recovery

A transaction is a mechanism for ensuring that a set of one or more SQL statements is treated as a single unit of work. SQL Server automatically manages all data modification commands, including single-step change requests, as transactions. In addition, users can group a set of SQL statements into a user-defined transaction with the begin transaction and commit transaction commands. Transactions allow SQL Server to guarantee:

Transactions and Consistency

In a multiuser environment, SQL Server must prevent simultaneous queries and data modification requests from interfering with each other. This is important because if the data being processed by a query could be changed by another user's update while the query is running, the results of the query would be ambiguous.

SQL Server automatically sets the appropriate level of locking for each transaction. Users can make shared locks more restrictive on a query-by-query basis by including the holdlock keyword in a select statement or by changing the transaction isolation level for a session with the set command.

The begin transaction and commit transaction commands allow users to instruct SQL Server to process any number of Transact-SQL statements as a single unit. rollback transaction allows the user to undo the transaction back to its beginning or back to a savepoint inside the transaction that has been defined with the save transaction command.

In addition to giving the user control over transaction management, user-defined transactions also improve performance, because system overhead is incurred only once for a set of transactions, rather than once for each individual command.

Transactions and Automatic Recovery

A transaction is both a unit of work and a unit of recovery. The fact that SQL Server handles single-step change requests as transactions means that the database can be recovered completely in case of system or media failures.

System failures are handled by SQL Server's automatic recovery mechanisms, which run every time SQL Server is restarted. Automatic recovery ensures that all transactions completed before a system crash or shutdown are written out to the database device, and that all transactions not completed before a crash are removed (rolled back).

The non-automatic recovery mechanisms that protect you from media failure involve backing up your databases and transaction logs. Because these mechanisms are not automatic, you are responsible for making backups and for loading them in case of media failure.

SQL Server's automatic recovery time is measured in seconds and minutes, rather than hours or days, and users can specify the maximum acceptable recovery time.

The Transaction Log

Every change to the database, whether it is the result of a single SQL update statement (a system-defined transaction) or a grouped set of SQL statements (a user-defined transaction), is automatically recorded in the system table syslogs. This table is called the transaction log.

The transaction log records data modification requests (update, insert, or delete statements) on a moment-to-moment basis. When a transaction begins, a begin transaction event is recorded in the log. As each data modification statement is received, it is recorded in the log.

The change is always recorded in the log before any change is made in the database itself. This type of log, called a write-ahead log, ensures that the database can be recovered completely in case of a failure.

To recover from a failure, transactions that were in progress but not yet committed at the time of the failure must be undone; a partial transaction is not an accurate change. Completed transactions must be redone if there is no guarantee that they have been written to the database device.

syslogshold Table

You can query the new syslogshold system table to determine the oldest active transaction in each database. syslogshold exists in the master database, and each row in the table represents:

A database may have no rows in syslogshold, a row representing one of the above, or two rows representing each of the above. For information about how Replication Sever truncation points affect the truncation of a database's transaction log, see your Replication Server documentation.

Querying syslogshold can help you when the transaction log becomes too full, even with frequent log dumps. The dump transaction command truncates the log by removing all pages from the beginning of the log up to the page that precedes the page containing an uncommitted transaction record (the oldest active transaction). The longer this active transaction remains uncommitted, the less space is available in the transaction log, since dump transaction cannot truncate additional pages.

Backup and Restore

SQL Server's dynamic dump allows the database and transaction log to be backed up while use of the database continues. Making the backup process quick and easy encourages frequent backups¯an important consideration, since their frequency determines the amount of work that could be lost if a failure should occur.

In case of media failure, you can recover your databases if¯and only if¯you have been making regular backups of your databases and their transaction logs. These non-automatic recovery mechanisms depend completely on the regular use of the dump commands.

The owner of each database, a System Administrator, or a user with the Operator role is responsible for backing up the database and its transaction log with the dump commands. Only a database owner or a System Administrator can reload a database. Once the appropriate load commands are issued, SQL Server handles all aspects of restoring the database.

Backup Server

Backup Server is dedicated to performing backups and restores for the SQL Server. It creates a physical image of the all the used pages in entire database or of a transaction log on an archive device when the dump command is used. One of the greatest benefits Backup Server offers is the ability to perform backups at any time without bringing the SQL Server system down.

Like SQL Server, Backup Server is an operating system process. In a multiprocessor architecture, the operating system schedules the Backup Server process onto an available physical processor. In a single processor architecture, the operating system schedules the Backup Server process when the physical processor is available.

Server has the ability to use archive devices on the same machine as SQL Server (local) or on another machine (remote). Backup Server is implemented as an auxiliary server built on Open Server. It supports SQL Server through the use of remote procedure calls (RPCs).

Dump and load options include:

Properly configured, backup performance can exceed 65GB per hour. Impact on applications running on SQL Server is minimal, since all I/O is performed by the Backup Server as a separate process, with minimal work performed by SQL Server itself.

SQL Server database backups are complete almost to the instant when the dump finishes, rather than the start of the dump process. SQL Server first dumps the entire database and then tracks all page changes during the dump. When the first phase completes, all of the changed pages are appended to the dump.

Relationship Between SQL Server and Backup Servers

Figure 11-1 shows two users performing backup activities simultaneously on two databases:

Each user issues the appropriate dump or load command from a SQL Server session. SQL Server interprets the command and sends remote procedure calls (RPCs) to the Backup Server. The calls indicate which database pages to dump or load, which dump devices to use, and other options.

While the dumps and loads execute, SQL Server and Backup Server use RPCs to exchange instructions and status messages. Backup Server¯not SQL Server¯performs all data transfer for the dump and load commands.

The system procedure sp_volchanged signals volume changes during backups. (OpenVMS users do this with the REPLY command.) sp_volchanged can be submitted from any ordinary Sybase client to signal volume changes to the Backup Server.

Figure 11-1: SQL Server/Backup Server installation with a remote Backup Server

When the local Backup Server receives user1's dump instructions, it reads the specified pages from the database devices and sends them to the remote Backup Server. The remote Backup Server saves the data to offline media. Simultaneously, the local Backup Server performs user2's load command by reading data from local dump devices and writing it to the database device.

Incremental Backups with Transaction Log Dumps

SQL Server supports full database dumps with the dump database command, and incremental dumps with the dump transaction command. The dump database command makes a complete, page-by-page copy of the entire database, including the transaction log. The dump transaction command dumps only the transaction log.

If you need to restore or move your database by loading from backups, you first use the load database command to load the full database dump, and then use the load transaction command to load each of the incremental transaction log dumps. These transaction log dumps reapply the changes performed in the transactions to the copy of the database.

If your database and its transaction log are stored on separate physical devices, SQL Server can recover transactions from the log device even when the database device is damaged. This provides up-to-the-minute recoverability from disk failure.


[Top] [Prev] [Next] [Bottom]