Microsoft SQL Server 7.0 System Administration Training Kit
If your database implementation uses multiple disks, you may be able to use RAID to increase fault tolerance, achieve better performance, and accommodate database growth more easily.
You can also use named collections of database files (called filegroups) to save data and log files on separate disks in order to simplify administrative tasks, eliminate disk drive contention, and reduce the time it takes to backup critical files.
NOTE
Regardless of the other fault tolerance measures you take to protect your database, remember to back up your files frequently.
After this lesson, you will be able to
- Describe strategies for managing databases on multiple disks
- Create filegroups
- Identify ways to improve database performance using multiple disks
Estimated lesson time: 30 minutes
Leveraging Windows NT Server Fault Tolerance
The RAID storage system uses disks that are configured in an array and managed as if they were all one large disk. RAID configurations are not only easy to manage, but also provide increased performance (due to simultaneous disk activity) and reliability (if one disk fails, others keep working). Fault tolerance is the ability of the operating system to continue functioning without data loss when part of the system fails. Windows NT supports software implementations of RAID at three levels, 0, 1, and 5. The levels are ranked numerically according to their ability to provide fault tolerance. RAID 0 provides no fault tolerance, whereas RAID 5 provides the best fault tolerance. Windows NT Server implements RAID levels 0, 1, and 5, which can be used with SQL Server.
Using Disk Striping (RAID 0)
Disk striping writes data evenly across multiple disks.
- Microsoft Windows NT Server-based disk striping implements RAID level 0. This is the highest-performance RAID level but offers no fault tolerance.
- If one disk fails, all data on the stripe set is inaccessible.
Using Disk Mirroring (RAID 1)
Disk mirroring protects against media failure by maintaining a fully redundant copy of a partition on another disk.
- Windows NT Server-based disk mirroring is a form of continual backup and implements RAID level 1.
- Windows NT Server-based disk mirroring requires a lot of disk space. Fifty percent of your disk space is redundant.
- Disk mirroring minimizes downtime and other expenses involved in recovering and restoring data.
NOTE
Previous versions of SQL Server supported device mirroring; however, SQL Server 7 does not. Use hardware-based or software-based RAID instead.
Using Disk Striping with Parity (RAID 5)
Disk striping with parity writes data evenly across multiple disks and includes redundant parity data.
- Windows NT Server-based disk striping with parity implements RAID level 5. This RAID level offers excellent performance and fault tolerance.
- You can achieve fault tolerance protection that equals disk mirroring but uses less disk space. All told, the size of one disk in the set is redundant. For example, if you have 5 disks, each 1 GB in size, 1 GB of space would be redundant, making the capacity of the stripe set with parity 4 GB.
- If any one disk fails, lost data is regenerated from the remaining disks of a striped set. In this case, disk striping with parity loses its performance advantage because data recovery takes longer.
- Disk striping with parity requires more system memory (RAM) than disk mirroring.
NOTE
Hardware-based RAID solutions perform better than the software implementation in Windows NT Server, and may support advanced features such as replacing a drive while the system is operating. The main disadvantage of hardware-based solutions is that they are expensive.
Using RAID with SQL Server
The following table lists and compares RAID solutions typically used with SQL Server to show the varying levels of redundancy and fault tolerance.
| RAID implementation | Advantages | Disadvantage |
|---|---|---|
| Hardware-based RAID level 3 or 5 | Has excellent performance Does not compete for processor cycles | Cost |
| Hardware-based RAID level 1 | Has best redundancy Does not compete for processor cycles | Cost |
| Windows NT-based RAID level 1 | Has good redundancy Is low in cost | Uses system processing resources |
| Windows NT-based RAID level 5 | Has excellent read performance Is low in cost | Uses system processing resources |
Creating Filegroups
If your hardware setup includes multiple disk drives and you are not using RAID, you can place database files on different disks, as shown in Figure 5.3.
Figure 5.3 Placing database files on different disks
This implementation allows for the building of very large databases that can improve performance because the disks operate simultaneously. To simplify the management of multiple database files, SQL Server provides filegroups. Filegroups are named collections of files. Every database has one default filegroup and you can create additional filegroups as needed.
You can assign specific tables, indexes, or the text, ntext, and image data from a table to a specific filegroup. In Figure 5.3, the Ordhist1.ndf and Ordhist2.ndf files are placed on a separate disk to keep files that are heavily queried separate from those that are heavily modified and to reduce disk drive contention.
System administrators can back up and restore individual files or filegroups instead of backing up or restoring an entire database.
NOTE
Log files are not part of a filegroup. Log space is managed separately from data space. Filegroups are used for managing data files only.
Considerations When Using Filegroups
Using filegroups is an advanced database design technique. You must understand your database structure, data, transactions, and queries in order to determine the best way to place tables and indexes in specific filegroups. In many cases, using the striping capabilities of RAID systems provides much of the same performance gain that you might achieve by using filegroups, without the added administrative burden of defining and managing filegroups.
Types of Filegroups
SQL Server offers the following three types of filegroups:
- The primary filegroup contains the primary data file and any secondary data files that are not part of another filegroup. All system tables are placed in the primary filegroup. For this reason, it is very important that the primary filegroup does not run out of space.
- User-defined filegroups, which are specified using the FILEGROUP keyword in a CREATE DATABASE or an ALTER DATABASE statement.
- The default filegroup, which can be any filegroup in the database. Initially the primary filegroup is the default filegroup, but members of the db_owner fixed database role can change the default filegroup at any time. All tables and indexes for which a filegroup was not specified upon creation are placed in the default filegroup.
Sizing the Default Filegroup
It is important to size the primary filegroup correctly. The primary filegroup must be large enough to hold all system tables and, if it remains the default filegroup, large enough to hold any tables not allocated to a user-defined filegroup.
If the primary filegroup runs out of space, new information cannot be added to the system tables. If a user-defined filegroup runs out of space, only the user files that are specifically allocated to that filegroup are affected. The primary filegroup will fill only if the automatic growth option is turned off or if the disk holding the primary filegroup runs out of space. To allow the primary filegroup to grow, turn the automatic growth option back on or free more disk space. For more information on automatic growth, search for "automatic growth" in Books Online.
Example: Creating a User-Defined Filegroup
The following example creates a user-defined filegroup in the Northwind database and adds a secondary data file to the user-defined filegroup.
| ALTER DATABASE northwind ADD FILEGROUP orderhistorygroup GO ALTER DATABASE northwind ADD FILE (NAME = 'ordhistyear1', FILENAME = 'c:\mssql7\data\ordhist1.ndf', SIZE = 5MB) TO FILEGROUP orderhistorygroup GO |
Viewing Filegroup Information
The following table lists system stored procedures that display information about database files and filegroups.
| System stored procedure | Description |
|---|---|
| sp_helpfile 'logical_file_name' | Returns the physical names and attributes of all files or a specified file associated with the current database. |
| sp_helpfilegroup 'filegroup_name' | Returns the names and attributes of filegroups associated with the current database. If a filegroup name is specified, sp_helpfilegroup returns a list of the files in the group. |
Performance Considerations
If you want to achieve the best performance from your database, consider the following guidelines.
Use RAID to Improve Performance or Fault Tolerance
You can use RAID either to gain faster access to data or to increase the safety of your data. Use the appropriate RAID level to achieve the performance gains you want while still maintaining the fault tolerance levels you require.
When you simply want to increase performance, choose RAID disk striping over filegroups.
Eliminate Disk Drive Contention
Place data files and transaction log files on separate physical disks with separate input/output (I/O) controllers so that concurrent writes to the transaction log do not compete with INSERT, UPDATE, or DELETE actions to the database tables.
Appropriate use of filegroups can eliminate disk drive contention. SQL Server 7 uses a proportional space allocation algorithm. For example, if a filegroup has two files, one of which has twice the free space of the other, two pages will be allocated from the file with more empty space for every one page allocated from the other file. This means that every file in a filegroup should have a similar percentage of space used.
Use Filegroups to Simplify Backups
Use filegroups to place database objects on separate disks. This allows you to use individual backup strategies based on how often data is revised. If you have a group of files that change often, you can back up those tables or objects frequently.
Lesson Summary
SQL Server 7 can take advantage of features of Windows NT to more safely and quickly store and retrieve data. In this lesson, you learned how to use Windows NT fault tolerance features to reduce the risk of data loss and eliminate disk drive contention. It is considered best practice to avoid the use of filegroups in SQL Server, instead using RAID for faster access and better protection.