When you dump database/transaction to dump devices known by a remote Backup Server (through sp_addumpdevice), the local Backup Server reads the database devices and sends the data over the network to the remote Backup Server, which stores it on the dump devices. When you use the load command, the local Backup Server sends instructions over the network to the remote Backup Server. The remote Backup Server reads the data from the dump devices and sends it back to the local Backup Server, which writes the data to the database devices. A network dump performs only as well as does the network supporting it, leading in many cases to significant performance decreases.
Backup Server contains several major features to allow users to implement an unattended backup policy to improve dump and load performance. SQL Server is able to detect remaining space on database segments and allows automatic dumping and truncation of the transaction log when free space falls below particular thresholds. Backup Server:
- Senses dump device type and density automatically
- Supports writing multiple dumps to the same volume
- Supports dump striping (interleaving of dump data across several volumes)
See SYBASE SQL Server Utility Programs for UNIX for more information on the Backup Server's configurable parameters:
- -C specifies the number of server connections
- -N specifies the number of network connections
- -c specifies the Backup Server dump device configuration file (SQL Server 11.0 and later releases). For more information on the dump device configuration file, see the Backing Up and Restoring User Databases chapter of the SQL Server System Administration Guide.
See "Developing a Backup and Recovery Plan" in the System Administration Guide for more information on the capabilities of the Backup Server.
Deferred Shutdown The syntax for deferred shutdown is as follows:
1> shutdown backup_server_nameThis shutdown permits dumps and loads in progress to complete (including volume change notifications for these sessions), but will not permit initiation of new dumps and loads. When the last dump or load has completed, Backup Server disconnects and exits from all SQL Servers.
Immediate Shutdown The syntax for immediate shutdown is as follows:
1> shutdown backup_server_name with nowaitThis causes Backup Server to drop all connections immediately and to exit.
The shutdown command must be typed while you are logged into SQL Server. In the syntax examples above, backup_server_name is the local/remote Backup Server name known internally to SQL Server (that is, it is not the network name). If you are using only one Backup Server, the backup_server_name should be SYB_BACKUP.
The definition for the remote Backup Server must exist in the interfaces file on both the local and the remote system.
Additionally, if the Backup Server start-up option -S specifies a particular server name, all interfaces files must use that name, not another. In other words, you cannot use an alias for your Backup Server in the interfaces file as you can for other servers. For example:
The local Backup Server entry in sysservers must have an accurate network name. Run sp_helpserver to verify this. If entries are inaccurate, SQL Server may contact the wrong Backup Server and write to or read from the wrong devices.
- If you have started a Backup Server with the -SSYB_BACKUP option, and
- You have BS2 aliased to SYB_BACKUP in your interfaces file, and
- You attempt to do a dump to dumpdev at BS2,
Backup Server: 126.96.36.199: DB-Library error, error number 20011, severity 8:
Maximum number of DBPROCESSes already allocated.
Backup Server: 188.8.131.52: Cannot open a connection to the slave site 'REM_BACKUP_SERVER'
Backup Server: 184.108.40.206: DB-Library error, error number 20018, severity 5:
General SQL Server error: Check messages from the SQL Server.
Backup Server: 220.127.116.11: RPC ('as_arch_device') execution failed.
- There is one session handler for each dump or load currently in progress.
- There is one stripe service task for each stripe being used by the dump or load. Local Stripe
- The Backup Server process creates and attaches to one shared memory segment. The size of this memory segment is 2,048 bytes * the number of the local stripes.
- Each local stripe also creates a shared memory segment. The Backup Server process does not attach to this segment. Each local stripe may allocate up to 110K of shared memory (164K on HP-UX). sybmultbuf is a process forked by the Backup Server which reads and writes the data from the database devices. Only the two sybmultbuf processes associated with each local stripe attach to this segment. Remote Backup Server
- The remote Backup Server allocates up to 54K of shared memory per stripe.
- The Backup Server process attaches to one shared memory segment per remote stripe. For example, if a local Backup Server dumps to a remote Backup Server with two stripes, the remote Backup Server creates two shared memory segments and attaches to both. Shared Memory Calculations
- Local dump
- Backup Server process:
2048 bytes * number of local stripes + (Backup Server process)
- sybmultbuf, UNIX except HP:
108K * number of local stripes
- sybmultbuf, HP-UX:
164K * number of local stripes
- Remote site
- UNIX except HP:
54K * number of remote stripes (active at one time)
size = 54K * number of remote stripes (active at one time) + 64K
System Defaults The default number of shared memory segments a process can attach to varies from platform to platform. Refer to the System Administration Guide Supplement for your platform for this figure. If more than six stripes are to be dumped, the SHMSEG operating system configuration parameter needs to be increased.
Because Digital OpenVMS has true asynchronous I/O built in, the Backup Server itself does all I/O and there is no sybmultbuf equivalent. There is no shared memory because there are no subprocesses ("child" processes) communicating with each other.
$SYBASE/backup_tape.cfgWhen Backup Server encounters a dump device with which it is unfamiliar, it issues calls to the operating system in an attempt to get about the tape dump device. Backup Server writes this information to the configuration file for future use.
The configuration file is a standard ASCII file.
There are three main categories of devices:
- Single-file, single-volume. These types of devices can contain, at most, one Sybase dump and that the dump resides entirely within the file or partition.
- Single-file, multi-volume. A dump on these media may span multiple volumes, but a set of volumes may collectively contain, at most, one dump. These devices do not allow backspacing, and attempts to append multiple files will prompt you for overwrite confirmation and will overwrite the previous contents if you enter "PROCEED."
- Multi-file, multi-volume. A given volume of one of these media types may contain more than one dump, and the last dump on a volume may continue onto other volumes.
SQL Server 10.x is more restrictive about supported devices. The following table summarizes hardware requirements for dump and load commands in SQL Server 10.x.
|HP9000||4-mm (/SCSI and /HPIB)||/dev/rmt/Nmn|
|9-track (/SCSI and /HPIB)||/dev/rmt/Nmn|
|AT&T||8-mm/SCSI (5GB and 2.2GB)||/dev/nrmtN|
|8-mm DAT/SCSI (5GB and 2.2GB)||/dev/rmtN.[0- 127]|
|QIC/SCSI (1/4-inch cartridge)||/dev/rmtN.[0- 127]|
|SunOS 4.x (BSD)||9-track/SCSI||/dev/nrmtN|
|QIC/SCSI (1/4-inch cartridge)||/dev/nrarN|
|8-mm/SCSI (5GB and 2.2GB)||/dev/nrstN|
|Sun Solaris 2.x (SPARC)||9-track||See "Sun Solaris 2.x"<z- Ignore> on page -12.|
|8-mm/SCSI (5GB and 2.2GB)|
|Digital OpenVMS VAX||9-track (/HSC and /DSSI)||See "Digital OpenVMS"<z -Ignore> on page -13.|
|8-mm (/HSC 5GB and 2GB and /DSSI)|
|TK50 (/HSC and /DSSI)|
|Digital OpenVMS Alpha||8-mm/SCSI||See "Digital OpenVMS"<z -Ignore> on page -13.|
|Digital UNIX||4-mm DAT/SCSI||See "Digital UNIX"<z- Ignore> on page -12.|
|Novell Netware||1/4inch cartridge||N (from the list device command)|
|8-mm||N (from the list device command)|
|Windows NT||1/4-inch cartridge||.TAPEN|
|SCO UNIX||1/4-inch cartridge QIC||/dev/ct0 or /dev/Stp0|
|1/4-inch SCSI QIC||/dev/Stp0|
|4mm SCSI||/dev/Stpdevic e#|
|8mm SCSI||/dev/Stpdevic e#|
SQL Server 10.x requires non-rewinding devices so that Backup Server can control the positioning of the tape device.
QIC devices are single-file devices. They do not allow backspacing and attempts to append multiple files to such a device will prompt you for overwrite confirmation.
/dev/rmt/ unit_number density [BSD behavior] no rewind
where density is "l" (low), "m" (medium), "h" (high), or "c" (compressed); and unit_number is a logical number that uniquely identifies the tape drive or unit.
Tape device names indicating BSD behavior are used during installation, but should not be used with Backup Server.
where density is "a" or "l" (low), "m" (medium), or "h" (high); [n] indicates "no rewind"; and unit_number is a logical number that uniquely identifies the tape drive or unit.
type spec unit
- type is the controller type
- spec is the controller specifier in the backplane
- unit is the unit number on the controller
/dev/rmt/controller_number device number density [compression]no rewind
where density is "l" (low), "m" (medium), or "h" (high).
Identical sets of files are located in each of the character set and language directories:
libsdna.loc is required for Digital OpenVMS only; libtli.loc is required for Sun Solaris 2.x only.
locales/us_english/cp437/Only U.S. English is provided with the basic product. If you have purchased one of the language modules, the same set of locales files is provided for each character set supported for the language.
Following are the locations of files needed for some other languages:
locales/german/cp437/Individual locales file names are the same for Digital OpenVMS, with paths like the following:
[SYBASE.LOCALES.US_ENGLISH.ISO_1]BSLIB.DOCAdditionally, Backup Server requires the file charset.loc in at least the default character set subdirectory (charsets/iso_1/charset.loc) and at least the default sort-order file (charsets/iso_1/roman8.srt for HP; charsets/iso_1/binary.srt for other platforms).
By default, Backup Server uses the $SYBASE/locales/locales.dat file's entry for platform default as the locale. You can override this with the LANG shell variable or any of the Open Server language variables. With SQL Server release 10.0.1, you can use the backupserver command's -J option (the option is /character_set for Digital OpenVMS) to define the Backup Server's character set. See the backupserver entry in the SQL Server Utility Programs manual for your platform for more details.
Backup Server may issue a message informing you that SQL Server's character set is different from Backup Server's, which may lead you to worry about database corruption if you are doing local or remote backups to Backup Servers with different character sets. However, there is no need for concern. This is only an informational message.
Backup Server does not convert the data pages from the Server database device when performing dumps or loads. Localization changes are made only for messages passed back to the client. The rules are:
1. Backup Server sends messages to SQL Server in SQL Server's character set but in the client's language.
2. Backup Server reads pages from the database and writes them to an archive device, and vice versa. It does not interpret the data. It only copies it. No pages are ever changed while they are being dumped or loaded.
- SQL Server must be configured for remote access. You may need to restart SQL Server if you have changed from this default behavior.
- On UNIX systems, the user (usually "sybase") who starts the Backup Server process must have write permission for the /tmp directory.
- The user who starts the Backup Server process must have write permission for the dump devices and read permission for the database devices.
- Sybase does not officially support dumping on one platform and loading to another.
- You can load a 10.x dump into an 11.0 or later SQL Server.
- You cannot load a pre-10.x dump into a 10.x or later SQL Server.
- You cannot load a 10.x or later dump into a pre-10.x SQL Server.
- To make your pre-11.0 load scripts work in 11.0 or later SQL
Server, add the following command after your load command:
1> online database database_name