SQL Server 2000 Configuration Sample

Configuring a SQL Server 2000 Database to be Resistant to Disaster

Below is a disk layout designed with backup and recovery in mind. Note that we are using RAID 1 for each of the following files: Transaction Logs, Data Files, OS Files. All are stored on separate disks from each other with separate controllers.

Picture of a disk layout - OS Files are separated from Data Files and Transaction Logs. Each of these three groups is configured using RAID 1

OS (Mirrored disks 1 and 2)

  • You should be able to store your OS files and page file on a single mirrored disk
  • Never move the page file to a disk containing transaction log files or data files

Data Files (Mirrored disks 3 and 4)

  • Data files should be located on separate physical disks with separate controllers than transaction log files and OS files
  • If you have tables that are accessed frequently, consider putting them on separate physical drives
  • If you can't afford to install all of the disks required for mirroring, you could also do data striping with striped parity (RAID 5)
  • You will often times have multiple disks to store the data files

Transaction Log Files (Mirrored disks 5 and 6)

  • Transaction log files should be located on separate physical disks with separate controllers than data files and OS files
  • Transaction logs are written sequentially and will generally do just fine on one single mirrored drive

Tempdb (contained with data files on mirrored disks 3 and 4 in this example)

  • Usually can go on the same physical disks as your data files
  • If you have a large database with a lot of activity, may wish to break tempdb out on to its own separate disk