spine=1.728"
Wrox Programmer to Programmer TM
Professional
Microsoft
Wrox Programmer to Programmer TM
Knight, Patel, Snyder,
LoForte, Wort
®
SQL Server 2008 Administration
®
SQL Server 2008 represents a significant jump forward in scalability
and performance. Database administrators — and developers as well —
will need to master new methods for effectively managing increasingly
large and complex SQL Server environments.
Professional
Who this book is for
This book is for experienced database administrators who are
interested in learning best pratices for administering SQL Server 2008.
Wrox Professional guides are planned and written by working programmers to meet the real-world needs of programmers,
developers, and IT professionals. Focused and relevant, they address the issues technology professionals face every day. They
provide examples, practical solutions, and expert education in new technologies, all designed to help programmers do a better job.
www.wrox.com
Recommended
Computer Book
Categories
$49.99 USA
$54.99 CAN
Database Management
ISBN: 978-0-470-24796-9
®
Enhance Your Knowledge
Advance Your Career
®
● Best practices for SQL Server 2008 installation
● Tips for troubleshooting and securing the database engine
● How to automate SQL Server
● Ways to index your database
● Methods for monitoring your SQL Server instance
● How to performance tune your SQL Server instance
Microsoft
What you will learn from this book
SQL Server 2008
Administration
This author team of SQL Server experts arms you with unique tips,
tricks, and workarounds, including managing and monitoring SQL
Server, automating administration, clustering, performance tuning,
handling backup and recovery, and more. You’ll learn the various tools
that are available to you with the 2008 release, as well as new features
such as Performance Studio, Change Data Capture, Policy-based
Management, and Query Governor. With a solid understanding of SQL
Server 2008, you’ll be able to confidently handle even the most difficult
SQL Server admin issues.
Professional
Microsoft
®
SQL Server 2008
®
Administration
subtitle
Brian Knight, Ketan Patel, Wayne Snyder, Ross LoForte, Steven Wort
General
Updates, source code, and Wrox technical support at www.wrox.com
spine=1.728"
Programmer to Programmer™
Get more out of
WROX.com
Professional
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
Professional Microsoft SQL Server 2008 Analysis Services shows readers how to
build data warehouses and multidimensional databases, query databases, and
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.
Enhance Your Knowledge
Advance Your Career
Professional Microsoft SQL Server 2008 Administration
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 T-SQL with Microsoft SQL Server 2005 and 2008
978-0-470-25703-6
Beginning T-SQL with Microsoft SQL Server 2005 and 2008 provides a comprehensive introduction to the T-SQL programming language, with
concrete examples showing how T-SQL works with both SQL Server 2005 and SQL Server 2008.
Contact Us.
We always like to get feedback from our readers. Have a book idea?
Need community support? Let us know by e-mailing
[email protected]
Beginning Database Design Solutions
978-0-470-38549-4
Beginning Database Design Solutions 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 it gives good performance while minimizing the chance for error.
Knight
ffirs.tex V1 - 10/01/2008
4:16am
Professional Microsoft® SQL Server® 2008 Administration
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxix
Chapter 1: SQL Server 2008 Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
Chapter 2: SQL Server 2008 Installation Best Practices . . . . . . . . . . . . . . . . . . . . . . 35
Chapter 3: Upgrading SQL Server 2008 Best Practices . . . . . . . . . . . . . . . . . . . . . . . 63
Chapter 4: Managing and Troubleshooting the Database Engine . . . . . . . . . . . . . 97
Chapter 5: Automating SQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129
Chapter 6: Integration Services Administration and Performance Tuning . . . 175
Chapter 7: Analysis Services Administration and Performance Tuning . . . . . 207
Chapter 8: Administering the Development Features . . . . . . . . . . . . . . . . . . . . . . . 237
Chapter 9: Securing the Database Engine . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 329
Chapter 10: Change Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 383
Chapter 11: Configuring the Server for Optimal Performance . . . . . . . . . . . . . . . 413
Chapter 12: Optimizing SQL Server 2008 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 455
Chapter 13: Monitoring Your SQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 497
Chapter 14: Performance Tuning T-SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 555
Chapter 15: Indexing Your Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 605
Chapter 16: Replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 653
Chapter 17: Database Mirroring . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 683
Chapter 18: Backup and Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 731
Chapter 19: SQL Server 2008 Log Shipping . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 779
Chapter 20: Clustering SQL Server 2008 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 807
Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 839
Page i
Knight ffirs.tex V1 - 10/01/2008
4:16am
Page ii
Knight ffirs.tex V1 - 10/01/2008
Professional
Microsoft® SQL Server® 2008
Administration
4:16am
Page iii
Knight ffirs.tex V1 - 10/01/2008
4:16am
Page iv
Knight
ffirs.tex V1 - 10/01/2008
Professional
Microsoft® SQL Server® 2008
Administration
Brian Knight
Ketan Patel
Wayne Snyder
Ross LoForte
Steven Wort
Wiley Publishing, Inc.
4:16am
Page v
Knight ffirs.tex V1 - 10/01/2008
Professional 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-24796-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
Microsoft SQL server 2008 administration / Brian Knight ... [et al.].
p. cm.
Includes index.
ISBN 978-0-470-24796-9 (paper/website)
1. SQL server. 2. Database management. I. Knight, Brian.
QA76.9.D3M57366 2008
005.75’85 — dc22
2008037353
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 Legal Department, Wiley Publishing, Inc., 10475 Crosspoint Blvd., Indianapolis, IN 46256, (317)
572-3447, fax (317) 572-4355, or online at www.wiley.com/go/permissions.
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 Website 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 Website may provide
or recommendations it may make. Further, readers should be aware that Internet Websites 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 (800) 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 SharePoint 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.
4:16am
Page vi
Knight
fabout.tex V1 - 10/01/2008
4:21am
About the Authors
Brian Knight, SQL Server MVP, MCSE, MCDBA, is the co-founder of SQLServerCentral.com and JumpstartTV.com. Brian is a principal consultant and owner of Pragmatic Works. He runs the local SQL
Server users group (JSSUG) in Jacksonville, Florida, and was on the Board of Directors of the Professional Association for SQL Server (PASS). Brian is a contributing columnist for SQL Server Standard,
maintains a regular column for the database website SQLServerCentral.com, and does regular webcasts
at Jumpstart TV. He is the author of nine SQL Server books. Brian is a speaker at numerous conferences, including PASS, SQL Connections, and TechEd, and many code camps. His blog can be found at
www.pragmaticworks.com. Brian spends weekends practicing to be an amateur surgeon and proceeds
from this book will help pay for the mobile CAT scan for his van.
Ketan Patel, B.E., electronics engineering, MCSE, MCDBA, is a senior development manager for the
Business Intelligence Center of Excellence group at Microsoft. He has worked with SQL Server and other
Microsoft technologies for nearly nine years. Ketan has also spoken at TechEd. He spends his spare time
with his family and friends, playing cricket and watching NFL.
Wayne Snyder is recognized worldwide as a SQL Server expert and Microsoft Most Valued Professional
(MVP), with over 25 years of experience in project management, database administration, software
design, performance measurement, and capacity planning. He is a popular consultant, trainer, writer,
and speaker, and produces a series of Web-based seminars on SQL Server 2005. Wayne has edited
many SQL Server books, has SQL Training CDs with Learnkey, is president of PASS, the Professional
Association for SQL Server (www.sqlpass.org), plays keyboard for a cover band named SoundBarrier (www.soundbarrierband.com), and is a managing consultant for Mariner, a Business Intelligence
Company (www.mariner-usa.com).
Ross LoForte is a technical architect at the Microsoft Technology Center Chicago, focused on Microsoft
SQL Server solutions. Ross has more than 16 years of experience in business development, project management, and designing SQL architecture solutions. For the past eight years, Ross has been working with
the Microsoft Technology Centers, and has led architecture design and proof-of-concept engagements
for Microsoft’s largest and most strategic customers to design enterprise, mission-critical SQL Server
solutions. Ross is a SQL Server instructor at DePaul University in Chicago, and regularly presents at
TechEd, SQL PASS, Gartner, TDWI, and other conferences. A published author, he has been active with
the Professional Association for SQL Server, the Chicago SQL Server Users Group, and the SQL Server
community for many years.
Steven Wort has been working with SQL Server for the past 14 years. He spent much of that time working
as a freelance application developer and database architect building VB and Web applications on SQL
Server for many of London’s largest financial institutions. He moved to the United States nine years ago,
joining Microsoft over seven years ago. After three years working in PSS on the SIE team, he moved over
to the SQL Server Product group, spending a year working on SQL Server scalability, followed by a year
working with the SQL Playback team. Two years ago Steven moved to the Windows division, where he
started work on the SQL Server side of the Watson system. When he is not involved with technology,
Steven can be found doing something active somewhere outdoors in the Pacific Northwest.
Geoff Hiten, a Microsoft MVP, is a senior SQL Server consultant based in Atlanta. Geoff began
working with SQL Server in 1992 with version 4.2 and has used every version since. He specializes in
high-availability and high-performance SQL systems. Recent projects include: system upgrades, SQL
Server platform migrations, performance tuning, custom reporting solutions, and database strategy
implementations. Geoff is currently on the leadership team of the Atlanta area Microsoft Database Forum
Page vii
Knight fabout.tex V1 - 10/01/2008
About the Authors
Users group (AtlantaMDF) and can be found lurking in the halls at PASS (Professional Association
for SQL Server) Community Summit events. In addition, Geoff has been a Subject Matter Expert for
Microsoft Training Curriculum materials and has authored articles, white papers, and podcasts on SQL
Server.
K. Brian Kelley (MCSE, Security+, CISA) is a systems and security architect for AgFirst Farm Credit
Bank. At AgFirst he provides infrastructure and security guidance with respect to Windows-based technologies including Active Directory, Internet Information Server, and Microsoft SQL Server. Brian, author
of Start to Finish Guide to SQL Server Performance Monitoring and contributing author for How to Cheat at
Securing SQL Server 2005, is a regular columnist and blogger at SQLServerCentral.com focusing primarily
on SQL Server security. He is also a frequent contributor for SQL Server Standard Magazine. Brian is a
member of the Professional Association of SQL Server (PASS) and the Information Systems Audit and
Control Association (ISACA). He is also active in the Midlands PASS chapter, an official PASS chapter
for South Carolina.
viii
4:21am
Page viii
Knight
fcre.tex V1 - 10/01/2008
Credits
Executive Editor
Robert Elliott
Development Editor
Sara Shlaer
Technical Editor
John Mueller
Production Editor
Kathleen Wisor
Vice President and Executive Group
Publisher
Richard Swadley
Vice President and Executive
Publisher
Joseph B. Wikert
Project Coordinator, Cover
Lynsey Stanford
Copy Editor
Luann Rouff
Proofreader
Nancy Carrasco
Publication Services, Inc.
Editorial Manager
Mary Beth Wakefield
Indexer
Robert Swanson
Production Manager
Tim Tate
8:37am
Page ix
Knight fcre.tex
V1 - 10/01/2008
8:37am
Page x
Knight fack.tex
V1 - 10/01/2008
4:22am
Acknowledgments
As always, I must thank my wife and best friend for supporting me for the past 10 years of marriage.
I’ve been fortunate to have found a woman who doesn’t fall asleep immediately when copyediting my
technical writing. Thanks to my three children, Colton, Liam, and Camille, for allowing their daddy
to be distracted sometimes with this book when they wanted to play. Thanks also to all the wonderful
co-authors, who truly made this book possible. Once again, I must thank the Pepsi-Cola Company for
inventing Mountain Dew, which drove the late night writing. — Brian Knight
I would like to thank my parents, Thakorbhai and Induben, for their unwavering and selfless support and
inspiration in my life, and my wife, Sweety, for her invaluable support and understanding. I would like to
acknowledge Brian Knight, the lead author, for providing me with a great opportunity to co-author this
book. I would also like to acknowledge Sara Shlaer, the development editor, and John Mueller, technical
editor — without them this book would not exist or contain the level of depth that it has. Last but not the
least, I want to thank B. J. Moore and Jim Walch, both general managers at Microsoft, for their invaluable
support and encouragement. — Ketan Patel
Thank-you to my loving wife, Vickie, whose generosity and support make my life better each day.
— Wayne Snyder
I’d like to thank my wife, Anna, and my daughter, Jennifer, for their support while writing this book.
Additionally, I’d like to thank Adam Hecktman and Tony Surma for their support, and for making the
Microsoft Technology Center Chicago a great facility to learn and experience. — Ross LoForte
I have to start by thanking my wife, Tracy, and two daughters, Eleanor and Caitlin, for putting up with
me over the past few months of writing. They have been infinitely patient and understanding while I
have spent many long hours working. I must also thank everyone in the SQL Product Group who has
helped me with information about SQL Server 2008. Finally, I would like to thank everyone at Wrox
Press for their help with this work over the past months. — Steven Wort
I would like to thank my wife Cheryl for being my other, better half, and my children; Victoria, Alexandra, and Katherine, who challenge me to do better simply by being who they are. — Geoff Hiten
Thanks to Kimberly, my beautiful bride and my children, James, Paul, and Faye. I love you all very much.
Thanks also to Brian Knight for opening the door and to Mark Melichar for encouraging me to seize the
opportunity. You guys have been great mentors and I can’t thank you enough. — K. Brian Kelly
Page xi
Knight fack.tex
V1 - 10/01/2008
4:22am
Page xii
Knight ftoc.tex V1 - 10/01/2008
4:36am
Contents
Introduction
Chapter 1: SQL Server 2008 Architecture
The Expanding Role of a DBA
Production DBA
Development DBA
Business Intelligence DBA
Hybrid DBA
New Things You Need to Learn
SQL Server Architecture
Transaction Log and Database Files
SQL Native Client
System Databases
Schemas
Synonyms
Dynamic Management Views
SQL Server 2008 Data Types
Editions of SQL Server
Compact (32-bit only)
SQL Express (32-bit only)
Workgroup Edition (32-bit and 64-bit)
Web Edition (32-bit and 64-bit)
Standard Edition (32-bit and 64-bit)
Enterprise, Evaluation, and Developer Editions (32-bit and 64-bit)
Operating System
Maximum Capacity of SQL Server
Database Features By Edition
Licensing
Summary
Chapter 2: SQL Server 2008 Installation Best Practices
Planning the System
Hardware Options
Software and Install Options
xxix
1
1
1
2
3
3
4
5
5
6
7
9
9
10
10
16
17
17
17
17
17
17
18
18
19
31
34
35
35
36
40
Page xiii
Knight
ftoc.tex V1 - 10/01/2008
Contents
Installing SQL Server
Side-By-Side, Upgrade, and New Installs
Scripted Installation
Remote Installation
Local Installation
Where Are the Sample Databases?
Installing Analysis Services
Burning in the System
Post-Install Configuration
SQL Server Configuration Manager
SQL Server Management Studio
tempdb
Back It Up
Uninstalling SQL Server
Uninstalling Reporting Services
Uninstalling Analysis Services
Uninstalling the SQL Server Database Engine
Common Installation Issues
Installing Client Tools from a Network Share
Admin Tools Not Installed by Default on Remote Cluster Nodes
Minimum Configuration Warning
Troubleshooting a Failed Install
Summary
Chapter 3: Upgrading SQL Server 2008 Best Practices
Why Upgrade to SQL Server 2008?
Risk Mitigation — The Microsoft Contribution
Independent Software Vendors and SQL Community Contributions
Upgrading to SQL Server 2008
In-Place Upgrading
Side-by-Side Upgrade
In-Place Upgrade versus Side-By-Side Upgrade Considerations
Pre-Upgrade Checks
SQL Server Upgrade Advisor
Installing the SQL Server 2008 Upgrade Advisor
Using the Upgrade Advisor
Scripting the Upgrade Advisor
Resolving Upgrade Issues
SQL Server 2008 Upgrade Assistant
Backward Compatibility
Unsupported and Discontinued Features
SQL Server 2008 Deprecated Database Features
Other SQL Server 2008 Changes Affecting Behavior
xiv
45
45
46
47
47
50
51
52
52
53
53
54
56
57
57
58
58
59
59
60
60
60
61
63
63
64
65
65
66
67
67
69
69
69
70
72
73
75
77
77
78
78
4:36am
Page xiv
Knight ftoc.tex V1 - 10/01/2008
4:36am
Contents
SQL Server Component Considerations
Upgrading Full-Text Catalog to SQL Server 2008
Upgrading Reporting Services
Upgrading Analysis Services
Upgrading DTS to SQL Server 2008 Integration Services (SSIS)
Log Shipping
Failover Clustering and Data Mirroring
Upgrading to 64-bit
Post-Upgrade Checks
Poor Query Performance After Upgrade
Updating Usage Counters
SQL Server Configuration Manager
Policy-Based Management
Summary
Chapter 4: Managing and Troubleshooting the Database Engine
Configuration Tools
SQL Server Configuration Manager
Dedicated Administrator Connection
Configuration Servers and Server Groups
Startup Parameters
Startup Stored Procedures
Rebuilding the System Databases
Management Studio
Reports
Configuring SQL Server
Filtering Objects
Error Logs
Activity Monitor
Monitoring Processes in T-SQL
Trace Flags
Getting Help from Support
SQLDumper.exe
SQLDiag.exe
Summary
Chapter 5: Automating SQL Server
Maintenance Plans
Maintenance Plan Wizard
Maintenance Plan Designer
SQL Server Agent
Automation Components
SQL Server Agent Security
79
79
80
80
81
88
88
88
89
89
90
91
91
96
97
97
97
99
100
101
104
105
106
106
108
112
113
114
118
120
124
124
125
127
129
129
130
132
136
137
151
xv
Page xv
Knight
ftoc.tex V1 - 10/01/2008
Contents
Configuring SQL Server Agent
Database Mail
Multiserver Administration
Summary
Chapter 6: Integration Services Administration and Performance Tuning
A Tour of Integration Services
Integration Services Uses
The Four Main Parts of Integration Services
Project Management and Change Control
Administration of the Integration Services Service
An Overview of the Integration Services Service
Configuration
Event Logs
Monitoring Activity
Administration of Integration Services Packages
An Overview of Integration Services Packages
Creating Packages
Management
Deployment
Execution and Scheduling
172
175
175
176
177
179
179
179
180
185
187
187
187
188
191
194
199
Applying Security to Integration Services
203
An Overview of Integration Services Security
Securing Packages
Saving Packages
Running Packages
Package Resources
Digital Signatures
203
203
205
205
205
206
Summary
Chapter 7: Analysis Services Administration and Performance Tuning
Tour of Analysis Services
Unified Dimensional Model Components
Analysis Services Architectural Components
206
207
207
208
209
Administering Analysis Services Server
210
Required Services
Analysis Services Scripting Language
212
212
Administering Analysis Services Databases
Deploying Analysis Services Databases
Processing Analysis Services Objects
Backing Up and Restoring Analysis Services Databases
Synchronizing Analysis Services Databases
xvi
156
160
166
214
214
217
219
222
4:36am
Page xvi
Knight ftoc.tex V1 - 10/01/2008
4:36am
Contents
Analysis Services Performance Monitoring and Tuning
223
Monitoring Analysis Services Events Using SQL Profiler
Creating Traces for Replay
Using Flight Recorder for After-the-Fact Analysis
223
224
226
Management of Analysis Services Storage
Storage Modes
Partition Configuration
Designing Aggregations
Applying Security to Analysis Services
Server Role
Database Role
Database Role Permissions
Summary
Chapter 8: Administering the Development Features
Service Broker
Service Broker Architecture
Service Broker Examples
Activation
Conversation Groups
Security Considerations for Service Broker
Transport Security
Routing
Dialog Security
Conversation Priorities
Setting Conversation Priorities
Reviewing Conversation Priorities
Administering Service Broker
Installing Service Broker Application
Setting Permission on Service Broker Objects
Managing Service Broker Queues
Poison Message Handling
Moving Service Broker Applications
Copying Service Broker Applications
Replacing Expired Certificates
Troubleshooting Service Broker Applications
Performance Tuning Service Broker
Introduction to CLR Integration
SQL Server As .NET Runtime Host
Application Domains
T-SQL versus CLR
Extended Stored Procedures versus CLR
227
227
227
228
233
233
233
234
236
237
237
238
246
260
265
267
267
271
275
279
279
288
288
289
290
292
293
295
296
297
297
307
308
308
309
310
310
xvii
Page xvii
Knight ftoc.tex V1 - 10/01/2008
Contents
Enabling CLR Integration
Creating the CLR Assembly
Deploying the Assembly
Cataloging Objects
Application Domains
Performance Monitoring
Summary
Chapter 9: Securing the Database Engine
Security Principles
311
312
315
321
324
325
327
329
329
The Principle of Least Privilege
The CIA Triad
Defense in Depth
329
330
331
Creating a Secure Configuration
332
New Security Features in SQL Server 2008
Operating System Security
Operating System Security and SQL Server
Surface Area Configuration
Endpoints
Typical Endpoint Configuration
Other Endpoints
Windows Authentication Changes in SQL Server 2008
332
333
337
341
342
342
344
344
Identity and Access Control
344
Server Principals/Logins
Credentials
Users
345
354
355
Schemas
Schema Example
Changing Schemas
User/Schema Separation
Changing the Schema Owner
INFORMATION_SCHEMA and sys Schemas
358
359
362
362
363
364
Securables and Object Permissions
364
Ownership Chaining
Cross-Database Ownership Chaining
Permission Context
EXECUTE AS Command
Troubleshooting Permissions Issues
364
365
366
367
369
Encryption
Setting Up an Encryption Methodology
Encrypting the Data
xviii
370
370
372
4:36am
Page xviii
.PDF 
916 
127 
103 
