Database Layout

3 4

An important part of designing the SQL Server system is laying out the database. This process involves the physical placement of transaction logs, data files, and so forth. This is one of the most important tasks involved in designing a SQL Server system because placement decisions are so difficult to reverse. Chapters 5 and 6 include tips on the physical placement of the transaction log and data files.

Transaction Log

The transaction log is critical to the operation, the stability, and the performance of the database server. Each database has its own transaction log; thus, each transaction log should be properly placed. The transaction log is used to record changes to the database, thus allowing the system to recover in the event of a failure. Because recovery relies on the transaction log, it is important that you use a RAID I/O device to protect this component of the database from possible faults. In the event of the loss of a disk drive, the transaction log should still be available.

In addition to protecting the transaction log from disk failure, you should ensure that the transaction log is on a high-performance device. If the transaction log is too slow, transactions must wait, which drastically affects the performance of the system. The transaction log should also be configured as fault tolerant. These requirements are covered in more detail in the next chapter.

Finally there must be sufficient space within the transaction log so that the system can run uninterrupted for a long period of time. If the transaction log fills up, all transaction processing ceases until space is freed up. Space is freed up by backing up the transaction log. However, backing up the transaction log can affect performance. Some DBAs prefer to create a sufficiently large transaction log so that it is necessary to back it up only once per hour or once per day. The transaction log should be sized to run for at least eight hours without having to be backed up. As you will learn later in this book, this is a simplification of the transaction log process.

Data Files

Data file placement is an entirely different process from transaction log placement. Depending on how the data files are accessed, you should place all of them on as many disks as possible, distributing the I/O load among all of the disk drives. This process is covered in more detail in the next chapter.

You should size data files so that there is enough capacity to handle system growth. You will sometimes be surprised by how fast your database grows. As data grows, so do indexes. Periodically you should check your system and perform a sizing and capacity-planning exercise.

So that you can plan the proper layout for the data files, the space should be calculated, performance needs should be assessed, and the proper number of disk drives should be created using a RAID subsystem. Whether or not fault tolerance is used will depend on your specific needs. Once the I/O subsystem has been determined, the data files should be evenly spread across controllers and disk drives.