Database Reference
In-Depth Information
The operating system tells SQL Server that all of these hard drives represent a single solitary drive, such as
the C:\ drive. So, when you create a new database, you simply tell SQL to place the database on D:\ drive, and the
operating system, and its RAID hardware, takes care of the rest (Listing 18-1).
Listing 18-1.
Creating a Database on a Specific Drive
USE [master]
GO
CREATE DATABASE [DWPubsSales] ON PRIMARY
( NAME = 'DWPubsSales'
, FILENAME = 'C:\MySQLDataFiles\DWPubsSales.mdf'
)
This simple addition will increase performance, but it can be improved on. For instance, you can buy more
hard drives, create an additional RAID array, and move the database to a different drive than the one hosting
the operating system. The idea is that the OS has one set of hard drives, and the database has another set of hard
drives, and they are not contending for resources.
This concept can be expanded. Remember that a SQL Server database is a collection of two or more files.
At a minimum there will always be a data file with an
.mdf
extension and a log file with an
.ldf
extension. The
data file holds the database data, and the log file records any changes to that data. If your database is used for
transactional operations, like a website's ordering application, then your log file will be very active. You gain
performance by placing the log file on a RAID array separate from either the OS or the MDF file, because the
Figure 18-6.
A common RAID array stategy
■
The log file is unlikely to require a RAid 1+0 array, because it does not benefit from a stripe as much as the
os and data files. This is because of the nature of the sQl server logging algorithm, which has the log file to write
sequentially to the file. Therefore, we have depicted the log file as being on a RAid 1 array, because it benefits from
the fault tolerance aspect of RAid.
Tip
