spine=1.632"

Programmer to Programmer™

Get more out of
WROX.com

Beginning
Microsoft

®

SQL Server 2008 Administration
®

Professional Microsoft SQL Server 2008 Integration
Services
978-0-470-24795-2
This book shows developers how to master the 2008 release of SSIS, covering topics
including data warehousing with SSIS, new methods of managing the SSIS platform,
and improved techniques for ETL operations.

Professional SQL Server 2008 Reporting Services
978-0-470-24201-8
This book teaches solutions architects, designers, and developers how to use
Microsoft’s reporting platform to create reporting and business intelligence solutions.

Professional Microsoft SQL Server 2008 Analysis Services
978-0-470-24798-3

Interact

Chapters on Demand

Take an active role online by participating in
our P2P forums

Purchase individual book chapters in pdf
format

This shows readers how to build data warehouses and multidimensional databases,
query databases, and how to use Analysis Services and other components of SQL
Server to provide end-to-end solutions.

Professional Microsoft SQL Server 2008 Programming
978-0-470-25702-9

Wrox Online Library

Join the Community

Hundreds of our books are available online
through Books24x7.com

Sign up for our free monthly newsletter at
newsletter.wrox.com

Wrox Blox

Browse


Download short informational pieces and
code to keep you up to date and out of
trouble!

Ready for more Wrox? We have books and
e-books available on .NET, SQL Server, Java,
XML, Visual Basic, C#/ C++, and much more!

This updated new edition of Wrox’s best-selling SQL Server book has been expanded
to include coverage of SQL Server 2008’s new datatypes, new indexing structures,
manageability features, and advanced time-zone handling.

Professional Microsoft SQL Server 2008 Administration

Enhance Your Knowledge
Advance Your Career

978-0-470-24796-9
A how-to guide for experienced database administrators, this book is loaded with
unique tips, tricks, and workarounds for handling the most difficult SQL Server
administration issues. The authors discuss data capture, performance studio, Query
Governor, and new techniques for monitoring and policy management.

Beginning Microsoft SQL Server 2008 Programming
978-0-470-25701-2
This comprehensive introduction to SQL Server covers the fundamentals and moves on to discuss how to create and change tables, manage
keys, write scripts, work with stored procedures, and much more.

Beginning Microsoft SQL Server 2008 Administration

978-0-470-44091-9
This book teaches both novice and experienced database administrators how to leverage all of the features of SQL Server to deliver solid,
reliable performance. All features and techniques are illustrated with real-world examples and step-by-step instructions. With this book, you’ll
develop the skills required to successfully administer a SQL Server 2008 database, regardless of your experience level.

Contact Us.

Beginning Database Design Solutions

We always like to get feedback from our readers. Have a book idea?
Need community support? Let us know by e-mailing

This introduces IT professionals—both DBAs and database developers—to database design. It explains what databases are, their goals,
and why proper design is necessary to achieve those goals. It tells how to decide what should be in a database to meet the application’s
requirements. It tells how to structure the database so the database performs well while minimizing the chance for error.

978-0-470-38549-4


Leiter ffirs.tex V2 - 03/25/2009

1:24pm

Beginning Microsoft® SQL Server® 2008 Administration
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxvii
Chapter 1: Introducing SQL Server 2008 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
Chapter 2: Installing SQL Server 2008 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
Chapter 3: SQL Server 2008 Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51
Chapter 4: SQL Server 2008 Storage Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111
Chapter 5: SQL Server 2008 Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129

Chapter 6: SQL Server 2008 Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 201
Chapter 7: Configuring SQL Server Network Communication . . . . . . . . . . . . . . . . . . . . . . . . . . . 261
Chapter 8: Automating Administrative Tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 285
Chapter 9: Disaster Prevention and Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 363
Chapter 10: Monitoring SQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 401
Chapter 11: Optimizing SQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 473
Chapter 12: SQL Server High Availability . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 553
Chapter 13: Introduction to Replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 589
Chapter 14: Introduction to the Common Language Runtime . . . . . . . . . . . . . . . . . . . . . . . . . . 607
Chapter 15: An Administrator’s Guide to Business Intelligence . . . . . . . . . . . . . . . . . . . . . . . . 639
Chapter 16: Introduction to SQL Server Integration Services . . . . . . . . . . . . . . . . . . . . . . . . . . 645
Chapter 17: Introduction to SQL Server Analysis Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 677
Chapter 18: Introduction to SQL Server Reporting Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . 707
Chapter 19: Introduction to Service Broker . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 733
Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 755

Page i


Leiter ffirs.tex

V2 - 03/25/2009

1:24pm

Page ii


Leiter


ffirs.tex V2 - 03/25/2009

1:24pm

Beginning

Microsoft® SQL Server® 2008 Administration

Page iii


Leiter ffirs.tex

V2 - 03/25/2009

1:24pm

Page iv


Leiter ffirs.tex V2 - 03/25/2009

1:24pm

Beginning

Microsoft® SQL Server® 2008 Administration
Chris Leiter
Dan Wood
Albert Boettger

Michael Cierkowski

Wiley Publishing, Inc.

Page v


Leiter ffirs.tex

V2 - 03/25/2009

Beginning Microsoft® SQL Server® 2008 Administration
Published by
Wiley Publishing, Inc.
10475 Crosspoint Boulevard
Indianapolis, IN 46256

www.wiley.com
Copyright © 2009 by Wiley Publishing, Inc., Indianapolis, Indiana
Published simultaneously in Canada
ISBN: 978-0-470-44091-9
Manufactured in the United States of America
10 9 8 7 6 5 4 3 2 1
Library of Congress Cataloging-in-Publication Data
Beginning Microsoft SQL server 2008 administration / Chris Leiter ... [et al.].
p. cm.
Includes index.
ISBN 978-0-470-44091-9 (paper/website)
1. SQL server. 2. Database management. 3. Relational databases. I. Leiter,
Chris, 1975QA76.9.D3B4465 2009

005.4’476--dc22
2009004135
No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any
means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections
107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or
authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood
Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 646-8600. Requests to the Publisher for permission should be
addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, (201)
748-6011, fax (201) 748-6008, or online at />Limit of Liability/Disclaimer of Warranty: The publisher and the author make no representations or warranties
with respect to the accuracy or completeness of the contents of this work and specifically disclaim all warranties,
including without limitation warranties of fitness for a particular purpose. No warranty may be created or extended
by sales or promotional materials. The advice and strategies contained herein may not be suitable for every
situation. This work is sold with the understanding that the publisher is not engaged in rendering legal, accounting,
or other professional services. If professional assistance is required, the services of a competent professional person
should be sought. Neither the publisher nor the author shall be liable for damages arising herefrom. The fact that an
organization or Web site is referred to in this work as a citation and/or a potential source of further information
does not mean that the author or the publisher endorses the information the organization or Web site may provide
or recommendations it may make. Further, readers should be aware that Internet Web sites listed in this work may
have changed or disappeared between when this work was written and when it is read.
For general information on our other products and services please contact our Customer Care Department within the
United States at (877) 762-2974, outside the United States at (317) 572-3993 or fax (317) 572-4002.
Trademarks: Wiley, the Wiley logo, Wrox, the Wrox logo, Wrox Programmer to Programmer, and related trade dress
are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates, in the United States and other
countries, and may not be used without written permission. Microsoft and SQL Server are registered trademarks of
Microsoft Corporation in the United States and/or other countries. All other trademarks are the property of their
respective owners. Wiley Publishing, Inc., is not associated with any product or vendor mentioned in this book.
Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be
available in electronic books.

1:24pm


Page vi


Leiter

ffirs.tex V2 - 03/25/2009

1:24pm

For my wife, Bridget Your patience, love, and support have made everything I have, and everything I am, possible.
Thanks for believing in me
— Chris Leiter
I dedicate my contribution of this book to my dad, Reginald Kaaikaula Wood, who lost his battle with cancer while
I was writing this book. He was a great encouragement and proud that his son was a published author even though
he said, ‘‘I don’t understand a darn word of it.’’ My dad left an amazing legacy and he will be missed.
— Dan Wood
I dedicate this book to my daughter, Rachel. Watching you grow and re-experiencing the beauty and wonder of the
world through your eyes, is and has been the greatest joy in my life. So few years to give you wings to fly.
I love you.
— Albert Boettger
I would like to dedicate this accomplishment to my daughter, Alina. You are the best thing that has ever happened
to me and I love you very much.
— Michael Cierkowski

Page vii


Leiter ffirs.tex V2 - 03/25/2009


1:24pm

Page viii


Leiter

f02.tex

V3 - 03/25/2009

1:26pm

About the Authors
Chris Leiter (Auburn, WA) is a Senior Consultant for Hitachi Consulting. His primary focus is Microsoft’s
Business Intelligence and Performance Management products. Chris has been a Microsoft Certified Professional since 1997 and a Microsoft Certified Trainer since 2001. He currently holds the MCSE: Security,
MCITP: Database Administrator, and ITIL: Foundation certifications. Chris is also co-author of Beginning
SQL Server 2005 Administration by Dan Wood, Chris Leiter, and Paul Turley from Wrox Press 2006. When
not writing about or working with Microsoft SQL Server, he enjoys watching movies from his extensive
DVD collection with his wife, Bridget, and their cat, Cosmo. Chris contributed Chapters 1, 2, 3, 6, 7, 8, 12,
13, 15, 16, 17, and 19.
Dan Wood (Silverdale, WA) is the senior database administrator for Avalara, a sales tax compliance
company where he both administers and develops database solutions for several enterprise applications
that handle global address validation, tax rate calculation, and sales tax remittance for e-commerce and
ERP clients. He has been working with SQL Server as a DBA, consultant, and trainer since 1999. Dan
was a co-author on Beginning Transact-SQL with SQL Server 2000 and 2005 by Paul Turley and Dan Wood
(2005) and Beginning T-SQL with Microsoft SQL Server 2005 and 2008 by Paul Turley and Dan Wood (2008)
and the lead author of Beginning SQL Server 2005 Administration, all from WROX press. Dan contributed
Chapters 4 and 9.
Albert Boettger (Federal Way, WA) is the Senior Software Engineer and Database Administrator for

Sagem Morpho, Inc. Albert has more than 20 years of experience as a solution developer, database architect, and software engineer. Albert contributed Chapters 10 and 11.
Michael Cierkowski (Maple Valley, WA) currently works as an instructor for Netdesk Corporation,
with a primary focus on SQL Server Administration. Michael has been a Microsoft Certified Professional
and Trainer since 2000. He currently holds his MCSD, MCDBA, MCAD, MCSA, MCPD: (Windows,
Web, and Enterprise), and MCITP: (Database Administrator, Database Developer, BI Developer, Server
Administrator, and Enterprise Administrator). Michael contributed Chapters 5, 14, and 18.

Page ix


Leiter f02.tex

V3 - 03/25/2009

1:26pm

Page x


Leiter

f03.tex

Credits
Executive Editor
Bob Elliott
Development Editor
Maureen Spears
Technical Editor
Jim Adams

Senior Production Editor
Debra Banninger
Copy Editor
Cate Caffrey

Vice President and Executive
Group Publisher
Richard Swadley
Vice President and Executive
Publisher
Barry Pruett
Associate Publisher
Jim Minatel
Project Coordinator, Cover
Lynsey Stanford

Editorial Manager
Mary Beth Wakefield

Proofreader
Nancy Carrasco

Production Manager
Tim Tate

Indexer
J & J Indexing

V3 - 03/25/2009


1:27pm

Page xi


Leiter f03.tex

V3 - 03/25/2009

1:27pm

Page xii


Leiter f04.tex

V3 - 03/25/2009

1:30pm

Acknowledgments
First and foremost, I thank my wife, Bridget, for once again supporting and encouraging me through this
process. It’ll be nice to have our evenings back. Thanks also to Dan Wood, for letting me take the reins
on this one. I’m really glad that you were able to stay on as a Contributing Author. Michael Cierkowski
and Albert Boettger also deserve my gratitude for stepping up to the plate and co-authoring this book.
Both of you are absolutely brilliant, and I’m lucky to know you. I also thank Lance Baldwin, one of the
best people I’ve had the privilege of working for (twice!), and Paul Turley, who helped Dan and me
get introduced to Wiley. And speaking of Wiley, I must also thank Bob Elliott for his support on this
project and faith that I could pull it all together; Maureen Spears for having the patience of a saint; and
Jim Adams, who never let anything get by him (and provided a huge contribution to Chapter 17!). There

are several other people whom I would like to thank for helping me in one way or another during the
process of creating this book. They include (in no particular order) Jeff Sparks, for constantly feeding my
ego; Rick Kinglsan, for setting the bar and letting me raise it; D.J. Norton, for being as much of a gadget
geek as I am; Stephanie Gulick, for being so supportive; everyone at Hitachi Consulting; and, of course,
the Banz and Leiter families, who put up with me working through yet another holiday season.
— Chris Leiter
A great deal of thanks to Chris Leiter for taking over this book and being an outstanding Project Lead.
Special thanks to all the wonderful people at Wrox for their patience for missed deadlines and support
when my dad was ill. Lastly, but most importantly, my gratitude and undying love goes to my beautiful
wife, Sarah, who supported me through yet another book project and expressed her pride and love while
spending many nights and weekends without me. Thank you, my love.
— Dan Wood
A special thanks to Chris Leiter for convincing me to join the team and introducing me to Wiley Publishing. You were right. Thank you to Jeff Sparks for being a friend and mentor, and for always pushing me
to explore and master new technologies. Your opinions and insights were invaluable. Thanks to everyone at Wiley Publishing who helped to make this book a reality, and especially to Bob Elliot for all his
hard work. Thanks, Maureen, for keeping us all on schedule and answering all of our questions (kind of
like herding cats), and to Jim for his excellent technical editing. To my loving wife, Elise, and beautiful
daughter, Rachel, thank you for your love, patience, and understanding. You mean more to me than
words can convey.
— Albert C. Boettger
First, I thank both Dan and Chris for considering me for this project. It has been a wonderful experience
working with you, and I hope we can do it again sometime. I also thank everyone at Wrox for making the
entire process a fairly painless affair. And finally, I thank my wife, Stacy, for dealing with many nights
of neglect while I worked on my many projects. I love you more each and every day. A task that I didn’t
think was possible.
— Michael Cierkowski

Page xiii


Leiter


f04.tex

V3 - 03/25/2009

1:30pm

Page xiv


Leiter

ftoc.tex V3 - 03/25/2009

1:31pm Page xv

Contents

Introduction
Chapter 1: Introducing SQL Server 2008
A Condensed History of SQL Server
In the Beginning
The Evolution of a Database
Microsoft Goes It Alone
BI for the Masses
2008 . . . and Beyond!

What Is SQL Server 2008?
Database Engine
Integration Services

Analysis Services
Reporting Services
Service Broker
Data Tier Web Services
Replication Services
Multiple Instances
Database Mail
A Note about Notification Services

SQL Server 2008 Editions
SQL Server Compact 3.5 SP1
SQL Server 2008 Express Edition
SQL Server 2008 Web Edition
SQL Server 2008 Workgroup Edition
SQL Server 2008 Standard Edition
SQL Server 2008 Enterprise Edition

SQL Server 2008 Architecture

xxvii
1
1
1
1
2
2
3

3
3

5
5
6
6
6
6
6
7
7

7
8
9
9
10
10
10

11

SQL Server 2008 Communication
SQL Server 2008 Services

11
13

SQL Server 2008 Database Objects

15


Server
Database
Schema
Object Names

15
16
16
16


Leiter ftoc.tex V3 - 03/25/2009

Contents
SQL Server 2008 Databases
System Databases
User Databases
Distribution Databases

SQL Server 2008 Database Storage
Data Files and Filegroups
Log Files

SQL Server Security
Windows Authentication Mode
SQL Server and Windows Authentication Mode (Mixed Mode)

Summary

Chapter 2: Installing SQL Server 2008

SQL Server Installation Planning

18
18
20
20

20
21
21

22
22
22

23

25
25

Hardware Considerations
Processor Considerations
Memory Considerations
Storage Considerations
Virtualization Considerations
Software Prerequisites

26
27
27

28
32
32

SQL Server Installation Center

34

Setup Support Rules (for Setup Support Files)
Setup Support Rules (for Installation)
Feature Selection
Installing to a Windows Cluster
Configuring the Virtual Server Name
Sample Databases

Installation Review
Summary

34
36
37
45
46
49

50
50

Chapter 3: SQL Server 2008 Tools


51

SQL Server Management Studio

52

Tool Windows
Toolbars
SQL Server Management Studio Configuration

Log File Viewer
SQL Server Business Intelligence Development Studio
SQL Server Profiler
SQL Server Trace
Trace Properties

xvi

53
65
82

90
91
93
93
94

1:31pm


Page xvi


Leiter ftoc.tex V3 - 03/25/2009

1:31pm

Contents
Database Engine Tuning Advisor
General Tab
Tuning Options Tab

SQL Server Configuration Manager
Reporting Services Configuration Manager
Command-Line Tools
SQLCMD
Bulk Copy Program (BCP)
PowerShell

Summary

Chapter 4: SQL Server 2008 Storage Architecture
The Resource Database
The sys Schema
SQL Server Database Physical Structure
Physical Storage Data Types
FILESTREAM Data
Other Data Types
SQL Server Database Files
Data Files

Transaction Log

Summary

Chapter 5: SQL Server 2008 Databases
System Databases
User Databases
Database Planning

97
98
99

100
100
102
102
104
106

109

111
112
112

113
114
118
119

119
120
123

127

129
129
129
129

Capacity Planning

130

Creating Databases

131

Getting Started
Creating a New Database
Schemas
Tables
Indexes
Enforcing Data Integrity

Database Diagrams
Views
System Views


Synonyms
Programming Objects

132
132
152
155
165
181

190
191
191

192
193

xvii

Page xvii


Leiter

ftoc.tex V3 - 03/25/2009

Contents
Stored Procedures
Functions
Triggers

Assemblies
Types
Defaults
Rules

Summary

Chapter 6: SQL Server 2008 Security
SQL Server Authentication Modes
Changing the Authentication Mode from Management Studio
Using the xp_instance_regwrite Extended Stored Procedure

Principals
Logins
Credentials
Server Roles
Database Users
Fixed Database Roles

Permissions
Server Permissions
Database Scope Permissions
Schema Scope Permissions
Using SQL Server Management Studio for Managing Permissions

SQL Server Encryption
Extensible Key Management (EKM)
Encryption Tools

Best Practices

Summary

Chapter 7: Configuring SQL Server Network Communication
SQL Server 2008 Network Protocols

193
193
194
196
196
199
200

200

201
201
202
202

204
205
210
212
214
219

225
229
235

238
240

243
246
246

257
259

261
261

Shared Memory
Named Pipes
TCP/IP
Virtual Interface Adapter (VIA)

262
262
262
264

SQL Native Client Configuration
SQL Server Endpoints

264
265

Default TSQL Endpoints

TSQL TCP Endpoints
Database Mirroring Endpoints

266
269
270

xviii

1:31pm

Page xviii


Leiter ftoc.tex V3 - 03/25/2009

1:31pm

Contents
SOAP Endpoints
Service Broker Endpoints
Securing Endpoints

Summary

Chapter 8: Automating Administrative Tasks
Policy-Based Management
Targets
Facets
Conditions

Policies
Policy Categories
Effective Policies

Central Management Servers
Database Mail
How It Works
How to Configure Database Mail
Configuring Database Mail Options
Managing Profiles and Accounts
Guidelines for Deleting Mail Objects
Sending Mail
Managing Messages

Event Notifications
SQL Server Agent
Configuring the SQL Server Agent Service
SQL Server Agent Security
Creating Jobs
Creating Schedules
Creating Operators
Creating Alerts
Creating Proxies
Multi-Server Jobs

Maintenance Plans
Maintenance Plan Wizard
Maintenance Plan Designer

Best Practices

Summary

Chapter 9: Disaster Prevention and Recovery
Chapter Preparation
Database Recovery Models

272
278
278

284

285
286
286
287
287
288
289
289

292
294
294
295
300
301
309
310
314


315
316
316
321
323
335
342
345
353
356

358
358
358

360
361

363
363
365

xix

Page xix


Leiter


ftoc.tex V3 - 03/25/2009

Contents
Full Recovery Model
Bulk-Logged Recovery Model
Simple Recovery Model

SQL Server 2008 Database Backup
Backup Devices

SQL Server 2008 Backup Types
Full Backup
Differential Backup
File/Filegroup Backup
Transaction Log Backup
Partial Backup
Copy Only Backup

Backup Options
Backup Stripe
Mirrored Backup
Compressed Backup
WITH Options

Backup Strategies
Full Backup Only
Full Backup with Differential
Full Backup with Transaction Log
Full and Differential Backup with Transaction Log
File and Filegroup Backup

Filegroup with Differential
Partial Backup
Backup Summary

367
367

369
369
370
370
371
371
372

372
372
372
373
373

375
375
376
376
377
377
378
378
378


Restoring Databases

379

Restore Process
Delaying Recovery

379
380

RESTORE Command
RESTORE DATABASE database_name
FROM Options
WITH Clause
Database Restore Preparation
Restoring User Databases
Recovering System Databases
Database Restore Summary

Database Snapshots
Database Snapshot Limitations
Disaster Recovery and Database Snapshots

Summary

xx

365
366

366

380
381
382
382
385
387
393
395

396
398
398

400

1:31pm Page xx


Leiter ftoc.tex V3 - 03/25/2009

1:31pm

Contents
Chapter 10: Monitoring SQL Server
Performance Monitoring
Performance Monitoring Strategy
Creating a Performance Baseline


Tools and Techniques for Monitoring
Log File Viewer
Activity Monitor
System Stored Procedures
Using Profiler
Monitoring Files

Auditing
SQL Server Audit
Login Auditing
C2 Audit Mode
Security Audit Event Category
SQL Trace

Tracking Changes
Change Data Capture
Change Tracking

Data Collection
Terminology
Architecture and Processing
Configuring Data Collection
Data Collector Types
Data Collection Sets
Error Handling
Reporting
Management Data Warehouse

Monitoring Database Modifications
Data Definition Language (DDL) Triggers


Summary

Chapter 11: Optimizing SQL Server

401
401
402
403

409
410
411
413
420
427

430
430
438
440
441
442

444
444
452

455
456

456
458
461
461
465
466
466

468
469

472

473

Hardware Optimization

474

CPU Selection
Hyperthreading
Memory
Storage Options
Network Design
Virtualizing SQL Server

475
475
475
476

477
478

xxi

Page xxi


Leiter ftoc.tex V3 - 03/25/2009

Contents
Design Considerations
Database Recovery Model
Designing Efficient Tables
Declarative Referential Integrity (DRI)
Constraints versus Triggers
Deciding What to Index
Indexed Views and Filtered Indexes
Minimizing Blocking
Hidden Dangers of Time-Outs

Query Optimization
Execution Plans
Updating Statistics
Managing Indexes
Query Optimizer Hints
Plan Guides
Database Engine Tuning Advisor

T-SQL Optimization Tips

Limiting Result Sets
ANSI-Style Join Syntax
Dealing with Null Values
Alternatives to Cursors
Merge Joins
Grouping Sets
Distinct Aggregation
How Many Records Are in That Table?
Temp Tables versus Table Variables

Resource Governor
Configuring the Resource Governor
Monitoring the Resource Governor

Summary

Chapter 12: SQL Server High Availability
Introduction to High Availability
Failover Clustering
Windows Clustering — A Quick Primer
Clustering Components
Active/Passive Clustering
Active/Active Clustering
Considering Clustering

Log Shipping
Preparing for Log Shipping
Configuring Log Shipping with SQL Server Management Studio

xxii


478
479
480
485
488
488
494
497
498

499
500
504
504
510
512
517

526
527
530
531
533
534
536
537
538
539


540
541
545

551

553
553
554
555
556
556
557
558

558
558
558

1:31pm

Page xxii


Leiter

ftoc.tex V3 - 03/25/2009

1:31pm


Contents
Configuring Log Shipping with Transact-SQL
Configuring Failover

Database Mirroring
Client Redirection
Database Mirroring Modes
Configuring Database Mirroring
Monitoring Database Mirroring
Managing Database Mirroring

Summary

Chapter 13: Introduction to Replication
Replication Overview
SQL Server Replication Agents
Snapshot Agent
Log Reader Agent
Distribution Agent
Merge Agent
Queue Reader Agent

SQL Server Replication Types
Distributed Transactions
Transactional Replication
Snapshot Replication
Merge Replication
Oracle Replication

SQL Server Replication Models

Single Publisher/Multiple Subscribers
Multiple Publishers/Single Subscriber
Multiple Publishers/Multiple Subscribers

Replication Tools
Filtering
Replicating Partitioned Tables and Indexes
New Publication Wizard
New Subscription Wizard
Replication Monitor

Summary

Chapter 14: Introduction to the Common Language Runtime
Databases and Programming
Is Transact-SQL Going Away?
.NET and the CLR

563
571

572
574
574
576
581
584

587


589
589
590
591
591
591
591
591

591
592
593
594
594
595

595
595
596
596

596
596
598
598
601
602

605


607
607
608
609

xxiii

Page xxiii