Compaq ProLiant 1000 Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 17

Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 17 the standard Windows NT product17. This driver is not available on the Compaq Software Support Diskette for Windows NT (NT SSD). The Compaq 35/70-GB DLT drive uses a separate driver (DLT7000.SYS) that is available from Quantum corporation18. Some third party backup applications (such as ARCserve for Windows NT) provide their own driver for the DLT family of tape drives; when using one of these applications the Windows NT driver must be removed or disabled. If the DLT is interfaced to a 32-bit Compaq SCSI-2 controller such as the integrated controllers found on Proliant and ProSignia servers, make sure that the Compaq 32-Bit SCSI-2 Controllers driver (CPQ32FS2.SYS) is installed from the Compaq Windows NT SSD. Data compression is typically available via hardware (tape drive or tape drive controller) or software (offline backup application). Speed, effectiveness (compression ratio) and levels of support vary from one solution to another, and from one data set to another. Data compression will reduce the time and storage required, at the expense of adding some processing overhead. Hardware data compression is available with the DLT tape drives when using 10.0, 15.0, 20.0, or 35.0 density formats. In this mode, data is always compressed when written to the tape cartridge; however, the drive will read both compressed and uncompressed tapes. You can choose not to use data compression by manually de-selecting the compression option on the drive, or using tape software that supports data compression control. The nature of the data determines the effectiveness of data compression, and will directly impact backup performance. Highly compressible data, which we will characterize below, will yield substantially higher backup throughput rates than data which is not compressible. Data that compresses well includes: Unallocated space within the database, that has never been used. This includes space on a SQL Server device that has not been allocated to a database or a transaction log, or space in a database or in a transaction log that has not been allocated to an object. Such space, however, DOES NOT include pages that have been used previously but marked by SQL Server as "unused" or "free." Even if your transaction log is empty, i.e. after a transaction log truncate or a dump, its pages may contain previous transaction log entries and are only marked as empty. Any file-based backup utility treats such marked pages as containing data. Naturally, this does not apply to on-line dumps, since SQL Server "knows" which pages contain valid data and which do not, and dumps them accordingly. Repeated patterns within the data set. The compression algorithms will detect repeated patterns in the database (regardless of data type), such as repeated customer names, cities or states, phone number prefixes, credit limits, etc., and compress them at high ratios. Not only database tables or indexes have repeated patterns. Transaction logs can also have highly repeatable patterns. For example, in cases where the transactions modify rows of data with the same or very similar columns, such as date and time stamps, or any repeated values, two or more transaction log entries will have highly compressible data. Chart-1 illustrates the influence of data compression and type of data on backup performance. We obtained these results with off-line backup, using an off-line backup application and 4/16 DAT drives.19 The "Unallocated Space" column represents a backup of an empty database. This database consists of 17 Alternatively, the Quantum DLT2XXX, DLT3XXX, or DLT4XXX tape drive option may be selected from the Windows NT 4.0 product (this is the same driver - DLTTAPE.SYS). 18 This driver should soon be available from Compaq as well. 19 This data was incorporated from the earlier paper 'Compaq Backup and Recovery with Microsoft SQL Server' (1994). Although these tests did not use the same hardware and software that the rest of our document addresses, they still serve to illustrate the effects of compression. 1997 Compaq Computer Corporation, All Rights Reserved Doc No 444A/0797