Microsoft SQL Server/Configuring Files

data files contain data and objects (tables, indexes and so on) and log files contain transaction log.

filegroups are grouped datafiles for easier admin.

Data Files

There are primary and secondary Data Files.

The primary has the extension mdf and contains data and all information regarding data (such as information on the secondary data files). For optimal performance do not store data there.

The secondary has the extension ndf. No administrative data is stored here. There is a maximum of 32,766 secondary data files.

Log Files

Extension ldf. Each db needs at least one log file. You can create more than one

Filegroups

Logical structure of data files.

Also there are primary and secondary filegroups. The primary contains the primary data file and every secondary data file not stored in a specific filegroup. There can be up to 32,766 secondary file groups.

Filegroups can be configured as read only.

The filegroups are created with the SSMS or with the CREATE DATABASE command.

Five parameters have to be considered: Name, Filename, Size, Maxsize and Filegrowth

Configuring Raid Systems

There are Raid Levels 0,1,5 and 10. It has to be considered to be used in high performance environments. Configuring Database Files with RAID Systems RAID systems are arrays of disk drives that provide fault tolerance, more storage capacity, and better performance for the disk subsystem, depending on the configuration. Although RAID hardware systems are not part of the SQL Server configuration, they directly affect SQL Server’s performance. There are a variety of RAID levels, each of which uses a different algorithm for fault tolerance. The most common RAID levels used with SQL Server are 0, 1, 5, and 10.

• RAID 0 is also known as disk striping because it creates a disk file system called a stripe set. RAID 0 gives the best performance for read and write operations because it spreads these operations across all the disks in the set. However, RAID 0 does not provide fault tolerance; if one disk fails, you lose access to all the data on the stripe set.
• RAID 1, also known as disk mirroring, provides a redundant copy of the selected disk. RAID 1 improves read performance but can degrade the performance of write operations.
• RAID 5, the most popular RAID level, stripes the data across the disks of the RAID set as does RAID 0, but it also adds parity information to provide fault tolerance. Parity information is distributed among all the disks. RAID 5 provides better performance than RAID 1. However, when a disk fails, read performance decreases. 58 Chapter 2 Configuring SQL Server 2005
• RAID 10, or RAID 1+0, includes both striping without parity and mirroring. RAID 10 offers better availability and performance than RAID 5, especially for write-intensive applications. The RAID configuration that is best for your database files depends on several factors, including performance and recoverability needs. RAID 10 is the recommended RAID system for transaction log, data, and index files. If you have budget restrictions, keep transaction log files in a RAID 10 system, and store data and index files in a RAID 5 system.

Best practices

1. Do not put data files on the same drive as OS files
2. Separate transaction log files from data files
3. database tempdb should be on a separate drive (Raid 10 or RAID 5)