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 and Microsoft Windows
2000 Server Administrator’s Companion (Microsoft Press), Chapter 7, “Planning Fault Tolerance and
Avoidance,” by Charlie Russel and Sharon Crawford, at />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
Lesson 2: Configuring Database Mail 63
Lesson 2: Configuring Database Mail
Database Mail is a new solution for sending messages from the SQL Server 2005
database engine. Applications that are configured to use Database Mail can send e-mail
messages, including HTML messages, query results, and file attachments, to users.
Database Mail uses the Simple Mail Transfer Protocol (SMTP) and does not require
you to install any Extended MAPI client, such as Microsoft Office Outlook, on SQL
Server.
After this lesson, you will be able to:
■ Identify Database Mail prerequisites.
■ Understand the Database Mail architecture.
■ Configure the SQL Server Database Mail subsystem.
Estimated lesson time: 15 minutes
Identifying Database Mail Prerequisites
Before you configure Database Mail, you need to review the following prerequisites:
■ Database Mail must be enabled. Database Mail is not enabled by default; you
need to enable it by using the SQL Server Surface Area Configuration tool, the
Database Mail Configuration Wizard, or the sp_configure stored procedure.
■ Service Broker needs to be enabled in the Database Mail host database. The
default Database Mail host database is msdb, and Service Broker is enabled on
msdb by default.

MORE INFO Service Broker
You can get a full explanation about Service Broker from />default.asp?url=/library/en-us/dnsql90/html/sqlsvcbroker.asp.
■ The Database Mail external executable needs access to the SMTP server. If the
SMTP server requires authentication, the executable accesses the SMTP server
by using the SQL Server service account credentials by default. You should
ensure that the SQL Server service account can access the SMTP server.
C0262271X.fm Page 63 Friday, April 29, 2005 7:29 PM
64 Chapter 2 Configuring SQL Server 2005
Understanding the Database Mail Architecture
Database Mail has four main components: configuration components, messaging
components, the executable, and logging and auditing components.
■ Configuration components There are two configuration components:
❑ A Database Mail account contains the information that SQL Server uses to
send e-mail messages to the SMTP server, such as the SMTP server name,
the authentication type, and the e-mail address.
❑ A Database Mail profile is a collection of Database Mail accounts. Applica-
tions use Database Mail profiles to send e-mail messages so that the infor-
mation about the accounts is transparent for applications, which lets DBAs
change account information without modifying applications’ stored proce-
dures. Database Mail profiles can be private or public. For a private profile,
Database Mail maintains a list of users that can use the profile. For a public
profile, members of the msdb database role DatabaseMailUserRole can use
the profile.
■ Messaging components The main messaging component is the Database Mail
host database, which contains all the Database Mail objects. The Database Mail
host database is msdb.
■ Database Mail executable To minimize the impact on SQL Server, Database Mail
uses an external executable to process e-mail messages. The executable, called
DatabaseMail90.exe, is located in the MSSQL\Binn directory in the SQL Server
installation path. Database Mail uses Service Broker activation to start the exter-

nal program when there are e-mail messages waiting to be processed. The exter-
nal program connects to the database engine by using Microsoft Windows
authentication with the SQL Server service account credentials.
■ Logging and auditing components Database Mail stores log information in
tables in the Database Mail host database. You can see this log information from
the Database Mail Log or by querying the sysmail_event_log system view.
How to Configure Database Mail
SSMS provides the Database Mail Configuration Wizard for configuring your Database
Mail environment. You can set up Database Mail; manage accounts, profiles, and secu-
rity; and change system parameters from the wizard, which is shown in Figure 2-1.
C0262271X.fm Page 64 Friday, April 29, 2005 7:29 PM
Lesson 2: Configuring Database Mail 65
Figure 2-1 Database Mail Configuration Wizard
In the following example, you have an SMTP mail server called mail.adventure-
works.com and an account on that server with an e-mail address of sql@adventure-
works.com. To configure a Database Mail profile account for this e-mail account, follow
these steps:
1. Expand the Management node within Object Explorer in SSMS.
2. Right-click Database Mail and select Configure Database Mail. The Welcome
page of the Database Mail Configuration Wizard appears. Click Next.
3. On the Select Configuration Task page, verify that Set Up Database Mail By Per-
forming The Following Tasks is selected and click Next.
4. A warning message appears: The Database Mail feature Is Not Available. Would
You Like To Enable This Feature? Click Yes.
5. In the Profile Name text box, type TestProfile and click Add to add a new SMTP
account.
6. The New Database Mail Account dialog box appears. Fill in the text boxes as Fig-
ure 2-2 shows. Click OK and then click Next.
C0262271X.fm Page 65 Friday, April 29, 2005 7:29 PM
66 Chapter 2 Configuring SQL Server 2005

Figure 2-2 New Database Mail Account dialog box
7. In the resulting Manage Profile Security page, you configure public and private
profiles. Select the TestProfile check box and click Next.
8. The Configure System Parameters page appears, which enables you to change
system-level configurations. Leave the default options and click Next. The Com-
plete The Wizard page appears. Click Finish.
You can also accomplish these tasks by using the Database Mail stored procedures.
For example, you can change configuration information by using the
sysmail_configure_sp stored procedure.
MORE INFO Database Mail stored procedures
For a list of Database Mail stored procedures and what they do, see the “Database Mail and SQL
Mail Stored Procedures (Transact-SQL)” topic in SQL Server 2005 Books Online.
NOTE Viewing configuration options
You can view information about Database Mail configuration options by running the Database Mail
Wizard or by executing the sysmail_help_configure_sp msdb stored procedure.
C0262271X.fm Page 66 Friday, April 29, 2005 7:29 PM
Lesson 2: Configuring Database Mail 67
PRACTICE Configuring Database Mail
In this practice, you will use the Database Mail stored procedures to configure Data-
base Mail so that you can send e-mail messages from SQL Server. You will create a
Database Mail public profile for an SMTP mail account. The SMTP server is
mail.Adventure-Works.com, and the e-mail address is
NOTE Example server name and e-mail address in this code
SMTP server names and account e-mail addresses used in this code are examples. You should
change them to a valid SMTP server name and e-mail address to run the code.
1. Execute the sysmail_add_account procedure as follows to create a Database Mail
account, using mail.Adventure-works.com as the mail server and sql@adventure-
works.com as the e-mail account:
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'AdventureWorks Mail',

@description = 'Mail account for Database Mail.',
@email_address = '',
@display_name = 'AdventureWorks Automated Mailer',
@mailserver_name = 'mail.Adventure-Works.com'
2. Use the sysmail_add_profile procedure to create a Database Mail profile called
AdventureWorks Mail Profile:
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'AdventureWorks Mail Profile',
@description = 'Profile used for database mail.'
3. Execute the sysmail_add_profileaccount procedure to add the Database Mail
account you created in step 1 to the Database Mail profile you created in step 2:
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'AdventureWorks Mail Profile',
@account_name = 'AdventureWorks Mail',
@sequence_number = 1
4. Use the sysmail_add_principalprofile procedure to grant the Database Mail pro-
file access to the msdb public database role and to make the profile the default
Database Mail profile:
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'AdventureWorks Mail Profile',
@principal_name = 'public',
@is_default = 1 ;
C0262271X.fm Page 67 Friday, April 29, 2005 7:29 PM
68 Chapter 2 Configuring SQL Server 2005
Lesson Summary
■ Database Mail is the SQL Server 2005 subsystem that lets you send e-mail mes-
sages from database applications.
■ Database Mail does not need any Extended MAPI client installed on SQL Server
because the mail subsystem sends messages directly to an SMTP server.
■ You need to have Service Broker enabled to use Database Mail, which uses an

external executable to send messages.
■ You can configure multiple Database Mail accounts and group them into Data-
base Mail profiles.
■ All Database Mail information is stored in the msdb database, the default Data-
base Mail host database.
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 is a prerequisite for Database Mail?
A. Service Broker
B. Database Mirroring
C. Extended MAPI Profile
D. Microsoft Exchange Server
C0262271X.fm Page 68 Friday, April 29, 2005 7:29 PM
Lesson 2: Configuring Database Mail 69
2. Which of the following sentences is true for authentication mechanisms when
the SMTP server is being accessed?
A. Database Mail accesses the SMTP server using the database engine service
credentials by default.
B. Database Mail accesses the SMTP server using the SQL Server Agent service
credentials by default.
C. Database Mail accesses the SMTP server using the SQL Browser service cre-
dentials by default.
D. Database Mail accesses the SMTP server using the SQL Server Active Direc-
tory Helper service credentials by default.
3. Which of the following sentences is true for Database Mail?

A. A Database Mail account is a collection of Database Mail profiles.
B. Each Mail Database Host user account must have a Database Mail profile
associated.
C. A Database Mail profile is a collection of Mail Database Host user accounts.
D. A Database Mail profile is a collection of Database Mail accounts.
C0262271X.fm Page 69 Friday, April 29, 2005 7:29 PM
70 Chapter 2 Configuring SQL Server 2005
Lesson 3: Specifying a Recovery Model
A recovery model is a database configuration option that controls how transactions are
logged, whether the transaction log is backed up, and what restore options are avail-
able for the database. The recovery model you choose for your database has both data-
recovery implications and performance implications, based on the logging the recov-
ery model performs or doesn’t perform.
After this lesson, you will be able to:
■ Explain the differences between the recovery models.
■ Choose the best recovery model for each SQL Server 2005 database.
Estimated lesson time: 10 minutes
Recovery Models Overview
SQL Server 2005 provides three recovery models for databases: Full, Simple, and
Bulk-Logged. These models determine how SQL Server works with the transaction
log and selects the operations that it logs and whether it truncates the log. Truncating
the transaction log is the process of removing committed transactions and leaving log
space to new transactions. The following is a definition of each recovery model:
■ In the Full recovery model, the database engine logs all operations onto the trans-
action log, and the database engine never truncates the log. The Full recovery
model lets you restore a database to the point of failure (or to an earlier point in
time in SQL Server 2005 Enterprise Edition).
■ In the Simple recovery model, the database engine minimally logs most operations
and truncates the transaction log after each checkpoint. In the Simple recovery
model, you cannot back up or restore the transaction log. Furthermore, you can-

not restore individual data pages.
IMPORTANT Simple recovery model scenarios
The Simple recovery model is not appropriate for databases in which the loss of recent
changes is unacceptable.
■ In the Bulk-Logged recovery model, the database engine minimally logs bulk oper-
ations such as SELECT INTO and BULK INSERT. In this recovery model, if a log
backup contains any bulk operation, you can restore the database to the end of
the log backup, not to a point in time. The Bulk-Logged recovery model is
intended to be used only during large bulk operations.
C0262271X.fm Page 70 Friday, April 29, 2005 7:29 PM
Lesson 3: Specifying a Recovery Model 71
How to Configure Recovery Models
You can see the recovery model specified for a given database on the Database Properties
page in SSMS or by querying the sys.databases catalog view, as this basic syntax shows:
SELECT name, recovery_model_desc FROM sys.databases
To configure the recovery model for a database, you can go to the Database Properties
page in SSMS or use the ALTER DATABASE statement.
In SSMS, you can change the recovery model by performing the following steps:
1. Expand the Databases node within Object Explorer in SSMS.
2. Right-click the database for which you want to set the recovery model and then
choose Properties. Select the Options page.
3. You can change the recovery mode from the Recovery model drop-down list, as
Figure 2-3 shows.
Figure 2-3 Changing the recovery model from SSMS
The basic syntax for configuring the recovery model using ALTER DATABASE is as
follows:
ALTER DATABASE <database _name>
SET RECOVERY FULL | SIMPLE | BULK_LOGGED
C0262271X.fm Page 71 Friday, April 29, 2005 7:29 PM
72 Chapter 2 Configuring SQL Server 2005

As noted earlier, Full recovery is the recommended model for a production database
because it provides the most recoverable configuration. If you import data periodi-
cally by using a bulk mechanism, you can temporarily change the recovery model for
your database to Bulk-Logged to get better bulk-load performance. Then, when the
import process ends, return your database to the Full recovery model.
PRACTICE Changing a Database’s Recovery Model
In this practice, you will change the database recovery model to Bulk-Logged to get
good performance for a bulk-logged operation and then revert to the Full recovery
model.
1. Set the database recovery model for the AdventureWorks database to Bulk-
Logged by executing the following ALTER DATABASE statement. (Before chang-
ing the recovery model, do a full backup of the database.)
Note that you should create the C:\Backup folder at Operating System level before
running this backup.
BACKUP DATABASE AdventureWorks TO DISK='C:\Backup\AdventureWorks.Bak'
GO
Change the Recovery Model to Bulk Logged
ALTER DATABASE AdventureWorks
SET RECOVERY BULK_LOGGED
2. Type and then run the following ALTER DATABASE statement to change the
recovery model back to Full after performing the bulk-logged operations; per-
form another full database backup so that you have a backup of the data that
was just loaded:
ALTER DATABASE AdventureWorks
SET RECOVERY FULL
Perform a Full database backup
BACKUP DATABASE AdventureWorks TO DISK='C:\Backup\AdventureWorks.Bak'
GO
Lesson Summary
■ Recovery models let you control how the database engine logs operations and

which restore options are available for a particular database.
■ SQL Server provides three recovery models: Full, Simple, and Bulk-Logged.
■ The Full recovery model is the default and the recommended recovery model,
logging all operations and letting you recover to the point of failure.
C0262271X.fm Page 72 Friday, April 29, 2005 7:29 PM
Lesson 3: Specifying a Recovery Model 73
■ The Simple recovery model minimally logs most operations and doesn’t let you
back up or restore the transaction log.
■ The Bulk-Logged recovery model minimally logs bulk operations and is
intended for temporary use during large bulk operations.
■ You configure a database’s recovery model through the Database Properties win-
dow in SSMS or by using the ALTER DATABASE Transact-SQL statement.
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 sentences is true for recovery models?
A. In the Simple recovery model, most transactions are minimally logged.
B. In the Full recovery model, most transactions are minimally logged.
C. In the Bulk-Logged recovery model, all transactions are logged.
D. In the Simple recovery model, all transactions are logged.
2. Which of the following methods let you change the database recovery model?
(Choose all that apply.)
A. The sp_configure stored procedure
B. Database properties in SSMS
C. ALTER DATABASE
D. CREATE DATABASE

3. Which of the following restore operations are NOT allowed in the Simple recov-
ery model? (Choose all that apply.)
A. Point-in-Time Restore
B. Differential
C. Full
D. Page Restore
C0262271X.fm Page 73 Friday, April 29, 2005 7:29 PM
74 Chapter 2 Configuring SQL Server 2005
Lesson 4: Configuring Server Security Principals
SQL Server 2005 provides a strong security model that helps you prevent unautho-
rized access to your important data resources. This model is based on permissions
that you give principals—the individuals, groups, and processes that can request SQL
Server resources.
SQL Server 2005 authenticates the permissions of all user connections, so all user
connections must specify authentication mode and credentials. You can choose
between two authentication modes—Windows authentication and Mixed Mode authen-
tication—that control how application users connect to SQL Server. And you can create
two types of SQL Server logins—Windows logins and SQL Server logins—that let you
manage access to the SQL Server instance. To help manage the logins of principals
that have administrative privileges to SQL Server, you can arrange these logins in fixed
server roles. Authentication mode and logins are the first security level for SQL Server,
so you should take care to configure the most secure option for your environment.
After this lesson, you will be able to:
■ Choose between authentication modes.
■ Manage SQL Server logins.
■ Manage fixed server roles.
Estimated lesson time: 10 minutes
Choosing Between Authentication Modes
SQL Server 2005 provides two modes for authenticating access to database resources:
Windows authentication and Mixed Mode authentication.

■ Windows authentication When you configure SQL Server 2005 to use Windows
authentication, only authenticated Windows users can gain access to the SQL
Server instance. You need to add a Windows login for each Windows user or
group that needs access to a SQL Server instance. This is the default and recom-
mended authentication mode because you can take advantage of all the central-
ized security policies of your Active Directory domain.
■ Mixed Mode authentication With Mixed Mode authentication, both Windows
logins and SQL Server logins (neither of which are mapped to an operating sys-
tem user) can access the SQL Server instance. You use Mixed Mode authentica-
tion when you need to provide access to non-Windows users—for example, when
users of another client operating system need access to SQL Server.
C0262271X.fm Page 74 Friday, April 29, 2005 7:29 PM
Lesson 4: Configuring Server Security Principals 75
You can change the authentication mode by using Server Properties in SSMS by taking
the following steps:
1. In SSMS, right-click on your server and choose Properties.
2. Select the Security page.
3. Below Server Authentication, select the authentication mode you want to use on
your server. You can select either the Windows authentication mode or the SQL
Server And Windows authentication mode.
4. Click OK to save your changes.
5. Click OK to close the message box stating that your changes will not take effect
until you restart SQL Server.
6. To restart your server, right-click on your server in Object Explorer and choose
Restart.
Quick Check
■ Which authentication mode is the default and recommended mode for
security principals?
Quick Check Answer
■ Windows authentication

How to Configure SQL Server Logins
Logins are the server principals that give users access to SQL Server. You can create
SQL Server logins graphically in SSMS or by using the CREATE LOGIN statement.
The basic CREATE LOGIN syntax to create a Windows login is
CREATE LOGIN [Domain\User ] FROM WINDOWS
The syntax to create a SQL Server login is
CREATE LOGIN login_name WITH PASSWORD='password'
For SQL Server logins, you can specify the following options when creating the login:
■ MUST_CHANGE The login should change the password at the next login.
■ CHECK_EXPIRATION SQL Server will check the Windows expiration policy
for the SQL Server login.
■ CHECK_POLICY SQL Server will apply the local Windows password policy on
SQL Server logins.
C0262271X.fm Page 75 Friday, April 29, 2005 7:29 PM
76 Chapter 2 Configuring SQL Server 2005
BEST PRACTICES Password policies
To get a secure SQL Server environment, you should use the options to check the Windows expi-
ration policy for SQL Server logins and apply the local Windows password policy on them.
In the following example, you create a SQL Server login and force checking of pass-
word expiration and password policy:
CREATE LOGIN secureSQL WITH PASSWORD='Ty%6tsfs$g23', CHECK_EXPIRATION=ON, CHECK_POLICY =ON
If you need to change any login property, you can use the ALTER LOGIN statement.
The following example shows you how to change the password for a SQL Server login:
ALTER LOGIN login_name WITH PASSWORD='password'
You can disable a login by executing the following:
ALTER LOGIN login_name DISABLE
When you need to remove a login, you can use the DROP LOGIN statement:
DROP LOGIN login_name
Or use the following to drop a Windows login:
DROP LOGIN [Domain\User]

To get SQL Server login information such as state or login options, you can query the
sys.sql_logins catalog view.
CAUTION Removing logins
You cannot drop a login that owns any securable, server-level object, or SQL Server Agent job. You
should disable logins before dropping them, and drop logins only when you are sure the action will
not affect your environment.
In addition, if the login is mapped to a database user and you drop the login, SQL Server does not
automatically remove the user, resulting in an orphaned user.
DBAs commonly need to manage exceptions when providing access to a Windows
group. For example, you might need to provide SQL Server access to all the members
of a certain Windows group except for one member. To accomplish this task, you
should create a Windows login for the Windows group and then deny access to the
user who shouldn’t receive access. The following example shows the basic syntax for
accomplishing these steps:
CREATE LOGIN [domain_name\group_name] FROM WINDOWS
DENY CONNECT SQL TO [domain_name\user_name]
C0262271X.fm Page 76 Friday, April 29, 2005 7:29 PM
Lesson 4: Configuring Server Security Principals 77
NOTE Backward compatibility
You can use SQL Server 2000 stored procedures, such as sp_addlogin, sp_droplogin, and so on, to
manage logins. But remember that these stored procedures are in SQL Server 2005 only for back-
ward-compatibility purposes.
Managing Fixed Server Roles
SQL Server provides a set of fixed server roles, such as sysadmin and securityadmin,
which you can use to assign and manage administrative privileges to logins by adding
logins as members of these roles. Table 2-2 describes the fixed server roles for SQL
Server 2005.
To obtain information about logins for a fixed server role, you can query the
sys.server_role_members catalog view, which returns a row for each member of the
server role.

The basic syntax for adding a login to a fixed server role is
EXECUTE sp_addsrvrolemember login_name, fixed_server_role
You can use the sp_dropsrvrolemember stored procedure to remove the login from the
fixed server role.
Table 2-2 SQL Server’s Fixed Server Roles
Fixed Server Role Members Can
sysadmin Perform any activity in SQL Server. The permissions of this
role comprise the permissions of all other fixed server roles.
serveradmin Configure server-wide settings.
setupadmin Add and remove linked servers and execute some system
stored procedures, such as sp_serveroption.
securityadmin Manage server logins.
processadmin Manage processes running in an instance of SQL Server.
dbcreator Create and alter databases.
diskadmin Manage disk files.
bulkadmin Execute the BULK INSERT statement.
C0262271X.fm Page 77 Friday, April 29, 2005 7:29 PM
78 Chapter 2 Configuring SQL Server 2005
Alternatively, you can use SSMS to add and remove logins from fixed server roles.
You can accomplish these tasks by displaying the properties for either a login or a
server role.
MORE INFO Fixed server roles properties
For more information about fixed server roles and their properties, see the “Server-Level Roles”
topic in SQL Server 2005 Books Online.
PRACTICE Selecting an Authentication Mode and Creating a Login
In these practices, you will change your server’s authentication mode to Mixed Mode
and create a SQL Server login. You will enforce the password policy and expiration
policy for that login and add the login to the sysadmin fixed server role.
 Practice 1: Change Authentication Mode
In this practice, you will change authentication mode to Mixed Mode.

1. In SSMS, right-click your server and choose Properties.
2. Select the Security page. Below Server Authentication, select SQL Server And
Windows Authentication mode. Click OK. A warning message appears inform-
ing you that this change will take effect only after you restart SQL Server.
3. Right-click your server and choose Restart so the change will take effect.
 Practice 2: Add a SQL Server Login
In this practice, you will add a new SQL Server login and enforce the expiration and
check policy restrictions. Then you will add the login to the sysadmin fixed server role.
1. Expand the Security node, right-click Logins, and then choose New Login. The
New Login dialog box appears.
2. In the Login Name text box, type sqlLogin.
3. Select the SQL Server Authentication option; in the Password and Confirm Pass-
word text boxes, type the password Pa$$w0rd.
4. Clear the User Must Change Password At Next Login check box.
5. To add the login to the sysadmin fixed server role, select the Server Roles page.
Select the Sysadmin check box and click OK.
C0262271X.fm Page 78 Friday, April 29, 2005 7:29 PM
Lesson 4: Configuring Server Security Principals 79
Lesson Summary
■ Server principals provide a mechanism for controlling how SQL Server authen-
ticates user access to database resources.
■ SQL Server provides two authentication modes: Windows authentication—the
default and recommended mode—and Mixed Mode authentication, which you
use only if you need to give access to non-Windows users.
■ Each user connection should specify a valid login so that the database engine
can authenticate the connection and check the permissions.
■ To help manage administrative privileges to SQL Server, you can assign logins to
fixed server roles, which define ready-made permissions for members of each
role.
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 are valid SQL Server principals? (Choose all that apply.)
A. Database users
B. Fixed server roles
C. Windows logins
D. SQL Server logins
C0262271X.fm Page 79 Friday, April 29, 2005 7:29 PM