Microsoft Press microsoft sql server 2005 PHẦN 2 pdf

Lesson 1: Configuring Log and Data Files 55

If your database has an access-intensive table—for example, Order Detail—you could

create multiple secondary data files for the database, store the files on different disk

drives, and group these files in a filegroup. Then, you could store the Order Detail

table in this filegroup so that queries against the table would be spread across the

disks.

BEST PRACTICES Filegroup design

Create at least one user-defined filegroup to hold secondary data files and database objects. Con￾figure this filegroup as the default filegroup so that SQL Server will store all objects you create in

this filegroup.

How to Configure Data Files and Log Files

You can configure data files and log files when you’re creating them by using the CRE￾ATE DATABASE Transact-SQL statement, and you can modify a configuration by

using the ALTER DATABASE statement. Alternatively, you can configure the files from

the Database Properties page in SSMS. Table 2-1 describes the options that you can

configure for each file.

Table 2-1 File Configuration Options

Option Description

Name The logical name for the file.

Filename The operating system full path and file name.

Size The size for the file. When you do not specify a size for the primary

file, the database engine uses the size of the primary file on the

model database. If you specify a secondary or log file without the

size option, the database engine creates files that are 1 MB in size.

Maxsize The maximum size for the file. If you do not specify maxsize or you

specify the UNLIMITED value, the file grows until the drive is full.

In SQL Server 2005, a log file has a maximum size of 2 terabytes,

and data files have a maximum size of 16 terabytes.

Filegrowth Specifies the automatic growth allowed for the file. You can specify

the value in kilobytes, megabytes, gigabytes, or terabytes; or as a

percentage of the actual file size. If you specify a value of 0, the file

will not grow.

C0262271X.fm Page 55 Friday, April 29, 2005 7:29 PM

56 Chapter 2 Configuring SQL Server 2005

As a rule, you should create database files as large as possible, based on the maximum

amount of data you estimate the database will contain, to accommodate future

growth. By creating large files, you can avoid file fragmentation and get better data￾base performance. In many cases, you can let data files grow automatically; just be

sure to limit autogrowth by specifying a maximum growth size that leaves some hard

disk space available. By putting different filegroups on different disks, you can also

help eliminate physical fragmentation of your files as they grow.

The following example creates a database with several files and filegroups, specifying

explicit values for each file property:

NOTE Volumes necessary to run this sample

To run this sample, you need three additional volumes—D, E, and F—with a folder called

\Projects_Data on each volume.

CREATE DATABASE Projects

ON

PRIMARY

(NAME = ProjectPrimary,

FILENAME = 'D:\Projects_Data\ProjectPrimary.mdf',

SIZE = 100MB,

MAXSIZE = 200,

FILEGROWTH = 20),

FILEGROUP ProjectsFG

( NAME = ProjectData1,

FILENAME = 'E:\Projects_Data\ProjectData1.ndf',

SIZE = 200MB,

MAXSIZE = 1200,

FILEGROWTH = 100),

( NAME = ProjectData2,

FILENAME = 'E:\Projects_Data\ProjectData2.ndf',

SIZE = 200MB,

MAXSIZE = 1200,

FILEGROWTH = 100),

FILEGROUP ProjectsHistoryFG

( NAME = ProjectHistory1,

FILENAME = 'E:\Projects_Data\ProjectHistory1.ndf',

SIZE = 100MB,

MAXSIZE = 500,

FILEGROWTH = 50)

LOG ON

(NAME = Archlog1,

FILENAME = 'F:\Projects_Data\ProjectLog.ldf',

SIZE = 300MB,

MAXSIZE = 800,

FILEGROWTH = 100)

C0262271X.fm Page 56 Friday, April 29, 2005 7:29 PM

Lesson 1: Configuring Log and Data Files 57

You can add, remove, and modify file properties by using the ALTER DATABASE state￾ment. The following example adds a new file to the Projects database:

ALTER DATABASE Projects

ADD FILE

(NAME=ProjectsData4,

FILENAME='E:\Projects_Data\ProjectData4.ndf',

SIZE=100MB,

MAXSIZE=500MB,

FILEGROWTH=75MB) TO FILEGROUP ProjectsFG

You can also configure these file options from SSMS.

MORE INFO CREATE DATABASE

For more information about the CREATE DATABASE and ALTER DATABASE syntax, see the topics

“CREATE DATABASE (Transact-SQL)” and “ALTER DATABASE (Transact-SQL)” in SQL Server Books

Online. SQL Server 2005 Books Online is installed as part of SQL Server 2005. Updates for SQL

Server 2005 Books Online are available for download at www.microsoft.com/technet/prodtechnol/sql/

2005/downloads/books.mspx.

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 configu￾ration. Although RAID hardware systems are not part of the SQL Server configura￾tion, 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 tol￾erance. Parity information is distributed among all the disks. RAID 5 provides

better performance than RAID 1. However, when a disk fails, read performance

decreases.

C0262271X.fm Page 57 Friday, April 29, 2005 7:29 PM

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.

MORE INFO RAID levels and SQL Server

Selecting the appropriate RAID levels for database files generates a lot of angst in the DBA commu￾nity, and full coverage of this topic is beyond this lesson. For more information about RAID, see

“RAID Levels and SQL Server” at http://msdn2.microsoft.com/ms190764.aspx and Microsoft Windows

2000 Server Administrator’s Companion (Microsoft Press), Chapter 7, “Planning Fault Tolerance and

Avoidance,” by Charlie Russel and Sharon Crawford, at http://www.microsoft.com/technet/prodtechnol/

windows2000serv/plan/planning.mspx.

Best Practices

To configure data and log files for best performance, follow these best practices:

■ To avoid disk contention, do not put data files on the same drive that contains

the operating system files.

■ Put transaction log files on a separate drive from data files. This split gives you

the best performance by reducing disk contention between data and transaction

log files.

■ Put the tempdb database on a separate drive if possible, preferably on a RAID 10

or RAID 5 system. In environments in which there is intensive use of tempdb

databases, you can get better performance by putting tempdb on a separate drive,

which lets SQL Server perform tempdb operations in parallel with database oper￾ations.

PRACTICE Configuring Database Files and Filegroups

In this practice, you will create a database that contains several files and filegroups

and then configure one filegroup as the default filegroup and another as a read-only

filegroup.

C0262271X.fm Page 58 Friday, April 29, 2005 7:29 PM

Lesson 1: Configuring Log and Data Files 59

NOTE Volumes necessary to run this example

To run this sample properly, you need three volumes—D, E, and F—with a Sales_Data folder on

each of them. Also, you need the free space specified to create each file.

1. Open SSMS.

2. Connect to the SQL Server instance using Microsoft Windows authentication by

clicking OK in the Connect To Server dialog box.

3. Click New Query.

4. Build the first part of a CREATE DATABASE statement that creates a database

called Sales; this database will have three filegroups:

CREATE DATABASE Sales

ON

5. Build the first part of the code, which creates the primary filegroup to contain

the SalesPrimary file, as follows:

PRIMARY

(NAME = SalesPrimary,

FILENAME = 'D:\Sales_Data\SalesPrimary.mdf',

SIZE = 50MB,

MAXSIZE = 200,

FILEGROWTH = 20),

6. Create the part of the code that defines the second filegroup, SalesFG, which will

store current data contained in files SalesData1 and SalesData2:

FILEGROUP SalesFG

( NAME = SalesData1,

FILENAME = 'E:\Sales_Data\SalesData1.ndf',

SIZE = 200MB,

MAXSIZE = 800,

FILEGROWTH = 100),

( NAME = SalesData2,

FILENAME = 'E:\Sales_Data\SalesData2.ndf',

SIZE = 400MB,

MAXSIZE = 1200,

FILEGROWTH = 300),

7. Add the following statement to create the third filegroup, SalesHistoryFG, which

will store historical information in the SalesHistory1 file:

FILEGROUP SalesHistoryFG

( NAME = SalesHistory1,

FILENAME = 'E:\Sales_Data\SalesHistory1.ndf',

SIZE = 100MB,

MAXSIZE = 500,

FILEGROWTH = 50)

C0262271X.fm Page 59 Friday, April 29, 2005 7:29 PM

60 Chapter 2 Configuring SQL Server 2005

8. Add the code to create a log file called SalesLog:

LOG ON

(NAME = Archlog1,

FILENAME = 'F:\Sales_Data\SalesLog.ldf',

SIZE = 300MB,

MAXSIZE = 800,

FILEGROWTH = 100)

9. Execute the complete CREATE DATABASE statement, as shown here:

CREATE DATABASE Sales

ON

PRIMARY

(NAME = SalesPrimary,

FILENAME = 'D:\Sales_Data\SalesPrimary.mdf',

SIZE = 50MB,

MAXSIZE = 200,

FILEGROWTH = 20),

FILEGROUP SalesFG

( NAME = SalesData1,

FILENAME = 'E:\Sales_Data\SalesData1.ndf',

SIZE = 200MB,

MAXSIZE = 800,

FILEGROWTH = 100),

( NAME = SalesData2,

FILENAME = 'E:\Sales_Data\SalesData2.ndf',

SIZE = 400MB,

MAXSIZE = 1200,

FILEGROWTH = 300),

FILEGROUP SalesHistoryFG

( NAME = SalesHistory1,

FILENAME = 'E:\Sales_Data\SalesHistory1.ndf',

SIZE = 100MB,

MAXSIZE = 500,

FILEGROWTH = 50)

LOG ON

(NAME = Archlog1,

FILENAME = 'F:\Sales_Data\SalesLog.ldf',

SIZE = 300MB,

MAXSIZE = 800,

FILEGROWTH = 100)

10. Use the following ALTER DATABASE statement to configure the SalesFG file￾group as the default filegroup for the Sales database. All database objects created

after this change will be stored in SalesFG by default:

ALTER DATABASE Sales

MODIFY FILEGROUP SalesFG DEFAULT

C0262271X.fm Page 60 Friday, April 29, 2005 7:29 PM

Lesson 1: Configuring Log and Data Files 61

Lesson Summary

■ A SQL Server 2005 database contains three file types: primary data files, second￾ary data files, and transaction log files.

■ You can group data files into filegroups to facilitate administration, such as

backup and restore operations, and to provide top performance.

■ You can improve your system’s performance by using the best RAID level and

file configuration for your environment.

Lesson Review

The following questions are intended to reinforce key information presented in this

lesson. The questions are also available on the companion CD if you prefer to review

them in electronic form.

NOTE Answers

Answers to these questions and explanations of why each answer choice is right or wrong are

located in the “Answers” section at the end of the book.

1. Which of the following statements can you use to create a filegroup?

A. ALTER DATABASE … ADD FILE

B. .ALTER DATABASE … MODIFY FILEGROUP

C. ALTER DATABASE … ADD FILEGROUP

D. ALTER DATABASE … REMOVE FILEGROUP

C0262271X.fm Page 61 Friday, April 29, 2005 7:29 PM

62 Chapter 2 Configuring SQL Server 2005

2. You are in charge of designing the physical structure for your company’s new

server running SQL Server 2005. The server has the following characteristics:

two disks in RAID 1, five disks in RAID 5, and another ten disks in RAID 5.

Where should you store database files for the best performance?

A. Use RAID 1 to install the operating system. Use the first RAID 5 disk set to

install SQL Server executable files and the second RAID 5 disk set to store

database files.

B. Use RAID 1 to install the operating system. Use the first RAID 5 system to

install SQL Server executable files and data and transaction log files. Use

the second RAID 5 system to store database backups.

C. Use RAID 1 to install the operating system and SQL Server executable files.

Use the first RAID 5 system to store transaction log files. Use the second

RAID 5 system to store data files.

D. Use the first RAID 5 system to install the operating system and SQL Server

executable files. Store data files in the second RAID 5 system and log files

in the RAID 1 system.

3. Which of the following are valid filegroup types? (Choose all that apply.)

A. Read-only

B. Write-only

C. Default

D. Primary

C0262271X.fm Page 62 Friday, April 29, 2005 7:29 PM