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

Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 27 A SQL Server data device (.DAT file) can be created on each controller, then the database built so that portions of it alternate across each of the devices. For example, a CREATE DATABASE statement for a 40 GB database can be structured to create: 5 GB on dev1, 5 GB on dev2, 5 GB on dev1 again, etc. (8 sections total); where dev1 and dev2 are on separate controllers (there is a limit of 32 such 'sections' for any one database). This method has the benefit of 'spreading the data out' amongst the controllers more effectively than does extending a previously created database. A SQL Server data device (.DAT file) can be created on each controller, and one or more segments can be created per device or across the devices. This method requires that the data layout be done out at the object (table, index, etc.) level. A Windows NT stripe set can be created across the controllers. The stripe set effectively 'combines' the arrays on the two controllers into a single volume, and distributes data evenly across all of the drives. With the exception of the Windows NT stripe set, the above methods will not cause an increase in read performance during a database dump, due to the sequential nature of the process (the process will read from only one controller at a time). Finally, remember that database layout affects more than just backup performance. While the hints in this section should be kept in mind when planning your database, the method and structure of the database layout can ultimately depend upon a number of things including the performance of the system under its normal production use. This section covers the use of a third party software program in conjunction with Microsoft SQL Server for the purpose of implementing database backup. As the SQL Server product matures and its use in the industry increases, many software vendors have designed utilities for use with SQL Server and some existing applications have been enhanced to become "SQL Server aware". In the area of backup and recovery, one widely used product that now has the capability to back up SQL Server databases online is ARCserve for Windows NT from Cheyenne Software35. The ARCserve family of products is available directly from Compaq to help form a storage management solution, and has been thoroughly tested on Compaq hardware. Thus, this section will focus specifically on ARCserve 6.0 for Windows NT, Enterprise Edition, and its 'add-on' option software. Subsequent chapters will then analyze the performance aspects of using the ARCserve products to carry out online backup of SQL Server databases. ARCserve 6.0 Enterprise is a powerful storage management product that offers a rich set of features, especially when combined with its option software. Some of these features serve to provide ARCserve with capabilities beyond what is available with the native SQL Server backup, and so can be considered as advantages of using ARCserve over a straight SQL Server 'dump'. It is of course, up to the customer to evaluate both backup methods and to determine which type of backup solution best meets their needs. The following is a brief look at some of the capabilities of the ARCserve products: Database Agent - The Backup Agent for Microsoft SQL Server (formerly known as DBagent) is available as an option, and must be installed on the same system where the SQL Server is located in order to perform online backups there. Otherwise, ARCserve must perform off-line backups of the database device files. The agent functions as a Windows NT service, and allows backup at the database or table level. Tape Spanning - ARCserve 6.0 allows the creation of 'groups' of media, should multiple tape drives be available. A single backup job can continue uninterrupted across all the tape drives in that group. ARCserve supports up to 8 tape drives per server. 35 Cheyenne Software, Inc. is now a wholly owned subsidiary of Computer Associates International, Inc. 1997 Compaq Computer Corporation, All Rights Reserved Doc No 444A/0797