Professional SQL Server 2005 Administration

Brian Knight,

Ketan Patel,

Wayne Snyder,

Jean-Claude Armand,

Ross LoForte,

Brad McGehee,

Steven Wort,

Joe Salvatore,

Haidong Ji

Published by Wiley Publishing, Inc.

10475 Crosspoint Boulevard Indianapolis, IN 46256.
www.wiley.com

Copyright 2007 by Wiley Publishing, Inc., Indianapolis, Indiana

Published by Wiley Publishing, Inc., Indianapolis, Indiana

Published simultaneously in Canada

ISBN-13:

978-0-470-05520-5

ISBN-10: 0-470-05520-0

Manufactured in the United States of America

10 9 8 7 6 5 4 3 2 1

1B/RW/RR/QW/IN

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, [phone], fax [phone]. Requests to the Publisher for permission should be addressed to the Legal Department, Wiley Publishing, Inc., 10475 Crosspoint Blvd., Indianapolis, IN 46256, [phone], fax [phone], 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 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 or to obtain technical support, please contact our Customer Care Department within the U.S. at [phone], outside the U.S. at [phone] or fax [phone].

Library of Congress Cataloging-in-Publication Data:

 Professional SQL server 2005 administration / Brian Knight … [et al.].      p. cm.   Includes index. 

ISBN-13: 978-0-470-05520-5 (paper/website)

ISBN-10: 0-470-05520-0 (paper/website)

1. SQL server. 2. Database management. 3. Client/server computing. I. Knight, Brian, 1976-

QA76.9.D3P7667 2007

005.75'85-dc22

2006032940

Trademarks: Wiley, the Wiley logo, Wrox, the Wrox logo, 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. SQL Server is a trademark 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.

For my two boys, Colton and Liam, who constantly reset my priorities.

About the Authors

Brian Knight, SQL Server MVP, MCSE, MCDBA, is the co-founder of SQLServerCentral.com and JumpstartTV.com. He runs the local SQL Server users group in Jacksonville (JSSUG) and was recently 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 Web site SQLServerCentral.com, and does regular Web casts at Jumpstart TV. He is the author of Admin911: SQL Server (Osborne/McGrawHill Publishing) and coauthor of Professional SQL Server DTS and Professional SQL Server 2005 Integration Services (Wrox Press). Brian has spoken at conferences like PASS, SQL Connections and TechEd and many Code Camps. His blog can be found at http://www.whiteknighttechnology.com. Brian is an independent consultant at White Knight Technology.

Ketan Patel, B.E. Electronics Engineering, MCSE, MCDBA, is a Development Manager for the Business Intelligence Center of Excellence group at Microsoft. He has worked with SQL Server and other Microsoft technologies for nearly eight years. Ketan has also spoken at TechEd. He spends his spare time with his family and playing cricket, especially in the summer.

Wayne Snyder is recognized worldwide as a SQL Server expert and Microsoft Most Valued Professional (MVP), with over 25 year of experience in project management, database administration, software design, performance measurement, and capacity planning. He is a sought-out 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 on the Board of Directors for PASS (Professional Association for SQL Server), plays keyboard for a cover band named SoundBarrier (www.soundbarrierband.com), and is a managing consultant for Mariner, a Business Intelligence Company.

Jean-Claude Armand is a Senior SQL Server technology specialist with Microsoft Corporation. Jean-Claude has been working with SQL Server since version 4.21a at Dun & Bradstreet. He also worked as a senior consultant for MicroAge and CompuCom Systems prior to coming to Microsoft where he has been working for the past seven years. Jean-Claude has spoken at events such as TechEd, SQL Connection, VSLive, SQL Server Magazine Connections, numerous other partner forums, and various Pass chapters.

Ross LoForte is a SQL technology architect at the Microsoft Technology Center Chicago who specializes in Microsoft SQL Server solutions. Ross has more than 16 years of business development, project management, and architecting SQL solutions on large and mission-critical database platforms. For the past six years, Ross has been working with the Microsoft Technology Centers and has led architecture design and proof-of-concept sessions for Microsoft's largest and most strategic customers to design enterprise, mission-critical SQL Server solutions. Additionally, Ross presents at TechEd, SQL PASS, Gartner, TDWI, and other conferences as well as delivering Microsoft customer presentations around SQL Server. Ross has been active with the Professional Association for SQL Server, with the Chicago SQL Server Users Group, and with the SQL Server community for many years.

Brad McGehee, SQL Server MVP, MCSE, MCSD, is the founder of SQL-Server-Performance.Com. He is a well-known SQL Server industry expert, focusing on writing, speaking, and teaching about SQL Server. He specializes in SQL Server performance tuning and clustering. His personal Web site is at www.sqlHawaii.com.

Steven Wort has been working with SQL Server for the past 12 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 seven years ago, joining Microsoft over five years ago as a SQL Expert in PSS on the SIE team. He spent three years traveling the world working on many very interesting customer issues. Two years ago, he moved over to the SQL Server Product group spending a year working on SQL Server Scalability and took a brief diversion into the SQL Server Best Practices Team before moving onto the SQL Playback team. Earlier this year, Steven moved to the Windows Reliability team and now spends his time writing nasty SQL queries, building SSIS packages, designing SSRS reports, and analyzing Crash and Hang dumps. When he is not involved with technology, Steven is a member of the Pacific Northwest Adventure Racing community and a passionate kayaker, cyclist, runner, inline skater, and downhill and cross-country skier.

Joe Salvatore is a Microsoft Solutions Technical Lead for Idea Integration. He is presently focused on architecting Business Intelligence and Reporting solutions using SQL Server 2005. He has had more than 10 years of programming experience, focused on getting meaningful information out of systems using Crystal Reports, Crystal Enterprise, Visual Basic.NET, T-SQL, Integration Services, Analysis Services, and Reporting Services. Joe has spoken at the Jacksonville SQL Server Users Group (JSSUG), the Jacksonville Developer's User Group (JAXDUG) and at many Microsoft Code Camps in the Southeastern United States.

Haidong Ji, , MCSD and MCDBA, is a developer and senior database administrator. He manages enterprise SQL Server systems, along with Oracle and MySql systems on Unix and Linux. Haidong enjoys sharing his expertise through technical writing, consulting, training, and mentoring. He has coauthored Professional SQL Server 2005 Integration Services (Wrox Press). He maintains his own blog at http://www.HaidongJi.com. He can be reached at [e-mail].

Credits

Executive Editor
Robert Elliott

Development Editor
Brian MacDonald

Technical Editor
G. T. Woody

Production Editor
William A. Barton

Copy Editor
C.M. Jones

Editorial Manager
Mary Beth Wakefield

Production Manager
Tim Tate

Vice President and Executive Group Publisher
Richard Swadley

Vice President and Executive Publisher
Joseph B. Wikert

Project Coordinator
Ryan Steffen
Jennifer Theriot

Graphics and Production Specialists
Carrie A. Foster
Brooke Graczyk
Barbara Moore
Barry Offringa
Alicia B. South

Quality Control Technicians
John Greenough
Jessica Kramer

Media Development Specialists
Angela Denny
Kit Malone
Travis Silvers

Proofreading and Indexing
Techbooks

Acknowledgments

Once again, I must give my eternal gratitude to my wife, Jennifer. Without her, my career of late nights and early mornings would never have been possible. I owe so much to her, I could never possibly start to repay it all. Likewise, my children have had many nights without a "daddy jungle gym" for a few hours while I had to write. -Brian Knight

I would like to thank my parents, Thakorbhai and Induben, for their unwavering and selfless support and inspiration in my life. Whatever I am today is because of them. The word "thanks" cannot express the feeling I have for them. I also would like to thank my sister-in-law and my best friend, Pramakshi, for her altruistic support, encouragement, and belief in me and in everything I do. I would like to thank my wife, Sweety, for her invaluable support when I go into writing mode all those weekends. I would like to acknowledge Brian Knight and JC, coauthors of this book, for providing me with a great opportunity to coauthor this book. Last but not the least, I would like to acknowledge my reviewers, Stefano Stefani, Roger Wolter, Remus Rusanu, Mark Wistrom, and Jakub Kulesza, all from SQL Server team, Brian MacDonald, the editor for this book, and Jennifer Coughlan, my colleague, because without them this book would not exist or contain the level of depth that it has. Finally, I want to say thank you to B.J. Moore and Mark Mincin, both General Managers at Microsoft, for their support. -Ketan Patel

Thank you to my loving wife, Vickie, whose generosity and support make my life better each day. -Wayne Snyder

First and foremost, I would like to thank my wife, Jacqui, who supported me by taking on our son Jacob while she was mourning the loss of her mother. I would also thank my son Jake, who was stuck shopping with his Mom as Daddy was busy with this book. I would also like to thank the good folks with the Microsoft SQL Server Customer Assistance Team (CAT), and Slava Oks who provided guidance and feedback during this process. -Jean-Claude Armand

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. Thanks to Sanjay Mishra for hooking me up with the rest of the team on this book and getting me started on my first attempt at writing. Thanks to my many former colleagues in SQL Release Services, the SQL Server Scalability team, and the SQL Playback Performance team. -Steven Wort

I would like to give thanks to my Lord, Jesus Christ, with whom all things are possible and whose guidance and inspiration are always a blessing. The loving support, sacrifice, and understanding provided by my loving wife, Linda, and son, Andrew, due this November were so very important and rightfully deserve a very big thank you. Professionally, I'd like to thank Brian Knight for his continued friendship, inspiration, and encouragement. My mother, Kathy, father, Richard, and sister, Maria, must also be acknowledged for supporting me during this and all my other adventures. Lastly, I would like to thank all my extended family members, friends, and co-workers for their interest and excitement. -Joe Salvatore

Once again, I'd like to thank Brian Knight for the opportunity to coauthor this book. I also want to express my gratitude to Michael and Susan Merrick and their extended family: thanks so much for your hospitality, friendship, and help over the years. It meant a lot to me. Finally, I'd like to thank Maria and Benjamin for their love and support. -Haidong Ji