Home

	Expand Search

What's New in SYBASE SQL Server Release 10.0?

Chapter 1: New Features in SQL Server Release 10.0

Backup/Space Management

Backup/Space Management

Backup Server

The Open Server-based Backup Server utility now handles all dumps and loads for SQL Server. Dumps and loads now work more quickly, with significantly less effect on other SQL Server activities. New dump and load syntax provides more flexibility and more options:

• Dump striping allows you to use up to 32 dump devices in parallel to dump or load a single database or transaction log.

• Multi-file/multi-volume dumps allow one dump to span multiple tapes, or allow multiple dumps to be made to a single tape.

• Network dumps allow dumping and loading over the network to or from a device on another machine.

• Backup Server complies with ANSI standard tape labelling and handling.

• Platform-specific tape handling options support dump/load command syntax specification for volume naming, dismount and load control, tape density, block size, tape capacity, days to retain, initialization, file naming for multi-dump volumes, and listing header or file information instead of loading the files.

• The system procedure sp_volchanged signals volume changes during backups. (OpenVMS users do this with the REPLY command.) It replaces the console utility program. sp_volchanged can be submitted from any ordinary SYBASE client to signal volume changes to the Backup Server.

• Multiple dumps and loads can be managed from one or more local or remote servers.

Installing a Backup Server

If you do not want to use the new functionality immediately, you can continue to use your existing dump and load routines. Pre-10.0 syntax is compatible with 10.0 syntax. The only exception is that dump database and dump transaction commands cannot be included in a user-defined transaction in Release 10.0.

Dump and load commands must use Backup Server. It must be installed and running, and SQL Server must be configured to connect to Backup Server.

Warning! You cannot load dumps made with earlier releases using 10.0 dump and load commands: pre-10.0 dumps are not compatible with 10.0 SQL Server.

The SYBASE SQL Server Installation Guide contains information about installing Backup Servers, and the System Administration Guide Supplement for your platform contains information on supported dump devices and information about platform-specific issues.

Additional Sources of Information

The new syntax for the dump database , load database , dump transaction , and load transaction commands is documented in Volume 1 of the SQL Server Reference Manual . sp_volchanged is documented in Volume 2 of the SQL Server Reference Manual .

Information about starting and stopping Backup Servers during routine operations can be found in startserver in the SQL Server Utility Programs for your operating system, shutdown in Volume 1 of the SQL Server Reference Manual , and your System Administration Guide Supplement . See "backupserver" in the SQL Server Utility Programs for information about flags for the backupserver executable.

See Chapter 7, "Developing a Backup and Recovery Plan" , in the System Administration Guide , for a complete discussion of Backup Server.

Threshold Manager

Thresholds monitor how much free space remains on a particular segment. When the amount of free space falls below a threshold, SQL Server automatically executes the associated stored procedure. For example:

• You can create a threshold and stored procedure to dump the transaction log when space runs low.

• You can create a threshold and a stored procedure that detects when space for the data segment of a database runs low, so that messages in the error log warn you before space runs out.

The system procedures sp_addthreshold , sp_dropthreshold , sp_modifythreshold and sp_helpthreshold create, drop, change and monitor free space thresholds in a database. See Volume 2 of the SQL Server Reference Manual for specific syntax information. Chapter 10, "Managing Free Space with Thresholds" , in the System Administration Guide provides additional examples.

In databases that store data and logs on separate segments, SQL Server now installs a last-chance threshold on the log segment. This threshold calls a procedure named sp_thresholdaction . You can use sp_modifythreshold to call a different stored procedure instead of sp_thresholdaction .

Since user requirements for threshold procedures vary so widely, Sybase does not provide sp_thresholdaction ; you must write it yourself. A special system function, lct_admin , creates last-chance thresholds on databases created under pre-10.0 SQL Servers.

The new abort tran on log full option for sp_dboption allows you to choose whether to suspend or kill processes that attempt to write to the log when there is not enough log space.

See sp_addthreshold , sp_dboption , and Chapter 10, "Managing Free Space with Thresholds" , of the System Administration Guide for examples.

Variables Allowed in Dump and Load Commands

As part of the Release 10.0 threshold improvements, you can now use parameters or variables in dump and load commands for database and device names. This allows a single stored procedure (such as sp_thresholdaction ) to dump multiple databases or transaction logs. See "dump database" , "dump transaction" , "load database" and "load transaction" in Volume 1 of the SQL Server Reference Manual .