ptg
Microsoft SQL Server 2008 R2 Unleashed
4
SQL Server high availability provide some expert advice in these areas. Task schedul-
ing and notification using SQL Server Agent and using the new Policy Based
Management feature are also discussed in this part.
. Part IV, “SQL Server Database Administration”—This part delves into the admin-
istrative tasks associated with creating and managing a SQL Server 2008 database,
including the creation and management of database objects, such as tables, indexes,
views, stored procedures, functions, and triggers. It also provides coverage of the
Database Snapshots and an overview of database maintenance tasks and responsibil-
ities.
. Part V, “SQL Server Performance and Optimization”—This part provides informa-
tion to help you get the best performance out of SQL Server. It begins with a discus-
sion of data structures, indexes, and performance, key items to understand to help
ensure good database performance. It then builds on that information with chapters
on query optimization and analysis, locking, database design and performance, and
ways to manage workloads using the new Resource Governor; then it finishes with a
methodology for monitoring and optimizing SQL Server performance.
. Part VI, “SQL Server Application Development”—This part includes a comprehen-
sive overview of what’s new in T-SQL in SQL Server 2008 and SQL Server 2008 R, T-
SQL programming guidelines, tips, and tricks, and advanced stored procedure
programming and optimization. In addition, chapters in this Part provide an
overview of .NET integration with SQL Server and creating .NET CLR objects infor-
mation, working with XML in SQL Server, and working with additional SQL Server
components that are not part of the core database engine such as Web Services,
Service Broker, and Full-Text Search.
. Part VII, “SQL Server Business Intelligence Features”—This Part includes a
comprehensive overview of SQL Server 2008 R2’s built-in business intelligence
features: Analysis Services, Integration Services, and Reporting Services, with a
specific focus on the enhancements to Reporting Services introduced with the R2

computer typeface. We have tried to be consistent in our use of uppercase and lowercase
for keywords and object names. However, because the default installation of SQL Server
doesn’t make a distinction between upper- and lowercase for SQL keywords or object
names and data, you might find some of the examples presented in either upper- or
lowercase.
Code and output examples are presented separately from regular paragraphs and are also
in a monospaced computer typeface. The following is an example:
select object_id, name, type_desc
from sys.objects
where type = ‘SQ’
object_id name type_desc

1977058079 QueryNotificationErrorsQueue SERVICE_QUEUE
2009058193 EventNotificationErrorsQueue SERVICE_QUEUE
2041058307 ServiceBrokerQueue SERVICE_QUEUE
When syntax is provided for a command, we have followed these conventions:
Download from www.wowebook.com
ptg
Microsoft SQL Server 2008 R2 Unleashed
6
Consider the following syntax example:
grant {all |
permission_list
} on
object
[(
column_list
)]
to {public |
user_or_group_name

. Shift
. TransactionHistor y
If any of the examples use any of these tables, you may see different results depend-
ing on whether you run them in AdventureWorks2008 or AdventureWorks2008R2.
When necessary, it will be stated in the chapter which version of the AdventureWorks
database was used to generate the results displayed.
Although it is not necessary to install both versions of the AdventureWorks database,
it is possible to install both versions in the same SQL Server instance if you wish.
For many of the examples presented in Part V, larger tables than what are available in the
AdventureWorks database were needed to demonstrate many of the concepts with more
meaningful examples. For many of the chapters in this part, as well as some other chap-
ters throughout the book, the examples come from the bigpubs2008 database. A copy of
the database, along with an Entity-Relationship (ER) diagram and table descriptions, is
also on the CD.
Download from www.wowebook.com
ptg
Introduction
7
To install the bigpubs2008 database on your system so you can try the various examples,
do the following:
1. Copy the bigpubs2008.mdf file into the SQL Server data folder where you want it to
reside.
2. After copying the file to the destination folder, ensure that the Read-Only property
of the bigpubs2008.mdf file is not enabled (this can happen when the file is copied
from the CD). Right-click the file in Windows Explorer and select Properties to bring
up the Properties dialog. Click the Read-Only check box to remove the check mark.
Click OK to save the changes to the file attributes.
3. Attach the bigpubs2008 database by using a command similar to the following:
sp_attach_single_file_db ‘bigpubs2008’,
N’D:\MSSQL\DATA\MSSQL.1\MSSQL\Data\bigpubs2008.mdf

bility and availability at a reasonable cost. With this book, you now have the information
you need to make the best of it.
Many of us who worked on this book have been using SQL Server since it was first
released. Writing about each new version challenges us to reassess our understanding of
SQL Server and the way it works. It’s an interesting and enjoyable process, and we learn a
lot writing each of these books. We hope you get as much enjoyment and knowledge
from reading this book as we have from writing it.
Download from www.wowebook.com
ptg
CHAPTER 1
SQL Server 2008
Overview
IN THIS CHAPTER
. SQL Server Components and
Features
. SQL Server 2008 R2 Editions
. SQL Server Licensing Models
Exactly what is SQL Server 2008? When you first install
the product, what are all the pieces you get, what do they
do, and which of them do you need?
At its core, SQL Server 2008 is an enterprise-class database
management system (DBMS) that is capable of running
anything from a personal database only a few megabytes in
size on a handheld Windows Mobile device up to a multi-
server database system managing terabytes of information.
However, SQL Server 2008 is much more than just a data-
base engine.
The SQL Server product is made up of a number of differ-
ent components. This chapter describes each of the pieces
that make up the SQL Server product and what role each

Reliable Storage
Reliable storage starts at the hardware level. This isn’t the responsibility of the Database
Engine, but it’s a necessary part of a well-built database. Although you can put an entire
SQL database on a single IDE or SATA drive (or even burn a read-only copy on a CD), it is
preferable to maintain the data on RAID arrays. The most common RAID arrays can
survive hardware failures at the disk level without loss of data.
NOTE
For more information on the reliability characteristics and performance implications of
the various RAID configurations and guidelines for implementing RAID configurations
with SQL Server, see Chapter 38, “Database Design and Performance.”
Using whatever hardware you have decided to make available, the Database Engine
manages all the data structures necessary to ensure reliable storage of your data. Rows of
data are stored in pages, and each page is 8KB in size. Eight pages make up an extent, and
the Database Engine keeps track of which extents are allocated to which tables and
indexes.
NOTE
A page is an 8KB chunk of a data file, the smallest unit of storage available in the
database. An extent is a collection of eight 8KB pages.
Another key feature the Database Engine provides to ensure reliable storage is the transac-
tion log. The transaction log makes a record of every change that is made to the database.
Download from www.wowebook.com
ptg
11
SQL Server Components and Features
For more information on the transaction log and how it’s managed, see Chapter 31,
“Transaction Management and the Transaction Log.”
NOTE
It is not strictly true that the transaction log records all changes to the database; some
exceptions exist. Operations on binary large objects—data of type image and text—
can be excepted from logging, and bulk copy loads into tables can be minimally logged

the server, database, schema, and object levels. Server-level access is enforced either by
1
Download from www.wowebook.com
ptg
12
CHAPTER 1 SQL Server 2008 Overview
using a SQL Server username and password or through integrated network security, which
uses the client’s network login credentials to establish identity.
SQL Server security is examined in greater detail in Chapter 11, “Security and User
Administration.”
Data Integrity
Some databases have to serve the needs of more than a single application. A corporate
database that contains valuable information might have a dozen different departments
wanting to access portions of the database for different needs.
In this kind of environment, it is impractical to expect the developers of each application
to agree on an identical set of standards for maintaining data integrity. For example, one
department might allow phone numbers to have extensions, whereas another department
may not need that capability. One department might find it critical to maintain a relation-
ship between a customer record and a salesperson record, whereas another might care
only about the customer information.
The best way to keep everybody sane in this environment—and to ensure that the data
stays consistent and usable by everyone—is to enforce a set of data integrity rules within
the database itself. This is accomplished through data integrity constraints and other data
integrity mechanisms, such as triggers. See Chapter 26, “Implementing Data Integrity,” for
details.
SQL Server 2008 Administration and Management Tools
SQL Server 2008 and SQL Server 2008 R2 provide a suite of tools for managing and admin-
istering the SQL Server Database Engine and other components. The following sections
provide an overview of the primary tools for day-to-day administration, management, and
monitoring of your SQL Server environments.

. Configure and manage replication
. Create, edit, execute, and debug Transact-SQL (T-SQL) scripts
. Define, implement, manage, and invoke SQL Server Policies
1
Download from www.wowebook.com