Beginning Microsoft SQL Server 2008 Administration

www.dbebooks.com - Free Books & magazineswww.it-ebooks.info

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

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

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

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.

Beginning Database Design Solutions

978-0-470-38549-4

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.

Enhance Your Knowledge

Advance Your Career

Get more out of

WROX.com

Programmer to Programmer™

Interact

Take an active role online by participating in

our P2P forums

Wrox Online Library

Hundreds of our books are available online

through Books24x7.com

Wrox Blox

Download short informational pieces and

code to keep you up to date and out of

trouble!

Chapters on Demand

Purchase individual book chapters in pdf

format

Join the Community

Sign up for our free monthly newsletter at

newsletter.wrox.com

Browse

Ready for more Wrox? We have books and

e-books available on .NET, SQL Server, Java,

XML, Visual Basic, C#/ C++, and much more!

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]

spine=1.632"

www.it-ebooks.info

Leiter ffirs.tex V2 - 03/25/2009 1:24pm Page i

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

www.it-ebooks.info

Leiter ffirs.tex V2 - 03/25/2009 1:24pm Page ii

www.it-ebooks.info

Leiter ffirs.tex V2 - 03/25/2009 1:24pm Page iii

Beginning

Microsoft® SQL Server® 2008 Administration

www.it-ebooks.info

Leiter ffirs.tex V2 - 03/25/2009 1:24pm Page iv

www.it-ebooks.info

Leiter ffirs.tex V2 - 03/25/2009 1:24pm Page v

Beginning

Microsoft® SQL Server® 2008 Administration

Chris Leiter

Dan Wood

Albert Boettger

Michael Cierkowski

Wiley Publishing, Inc.

www.it-ebooks.info

Leiter ffirs.tex V2 - 03/25/2009 1:24pm Page vi

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, 1975-

QA76.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 http://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 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.

www.it-ebooks.info

Leiter ffirs.tex V2 - 03/25/2009 1:24pm Page vii

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

www.it-ebooks.info

Leiter ffirs.tex V2 - 03/25/2009 1:24pm Page viii

www.it-ebooks.info

Leiter f02.tex V3 - 03/25/2009 1:26pm Page ix

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 Pro￾fessional 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 archi￾tect, 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.

www.it-ebooks.info

Leiter f02.tex V3 - 03/25/2009 1:26pm Page x

www.it-ebooks.info

Leiter f03.tex V3 - 03/25/2009 1:27pm Page xi

Credits

Executive Editor

Bob Elliott

Development Editor

Maureen Spears

Technical Editor

Jim Adams

Senior Production Editor

Debra Banninger

Copy Editor

Cate Caffrey

Editorial Manager

Mary Beth Wakefield

Production Manager

Tim Tate

Vice President and Executive

Group Publisher

Richard Swadley

Vice President and Executive

Publisher

Barry Pruett

Associate Publisher

Jim Minatel

Project Coordinator, Cover

Lynsey Stanford

Proofreader

Nancy Carrasco

Indexer

J & J Indexing

www.it-ebooks.info

Leiter f03.tex V3 - 03/25/2009 1:27pm Page xii

www.it-ebooks.info

Leiter f04.tex V3 - 03/25/2009 1:30pm Page xiii

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 Publish￾ing. 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 every￾one 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

www.it-ebooks.info