Inside SQL Server 2005 Tools

Sunil Agarwal is a Program Manager in the SQL Server Storage Engine Group. Prior to joining Microsoft, Sunil had worked in companies like DEC, Sybase, BMC Software, and DigitalThink, with a primary focus on core database engine technology and related applications. Sunil holds a BS in Electrical Engineering from IIT, Kanpur, India, and has done graduate work in Computer Science at the University of Rhode Island and at Brown University   Thierry D’Hers is a Lead Program Manager in the SQL BI Analysis Services team. Thierry has over 12 years experience in the BI industry. For the last five years, Thierry has been responsible for designing and specifying many of the functionalities of the Analysis Services tools. Beyond participating in the product design, Thierry is also a technical evangelist for the SQL BI product line. In this role, Thierry spends a large amount of time educating customers and partners on the various benefits of the SQL BI components. Before joining Microsoft, Thierry spent seven years at Hyperion where he occupied various positions. He began as a consultant, and then moved into technical Sales for Hyperion France. Next he was asked to join Hyperion Corp as a Product Manager, and then he became the Group Product Manager on Hyperion OLAP (renamed Hyperion MBA), Hyperion Business Rules, and a few other Hyperion products initiatives.   Phillip Garding is a Senior Program Manager in the SQL Server Database Replication team. Phillip has driven the design for the administration and monitoring tools for database replication in SQL Server versions 7.0, 2000, and 2005 and helped define the overall user interface experience in SQL Server 2005. In addition, he has contributed more broadly to developing the wizard and error messaging designs at Microsoft. Phillip is a 17-year veteran at Microsoft, and he worked on Microsoft Access and Microsoft Word prior to joining the SQL Server team in 1995.   Diane Larsen is a technical writing lead on the SQL Server User Education team. She has written documentation and articles about several SQL Server 2000 and SQL Server 2005 features, including Notification Services, DTS, Upgrade Advisor, and SQL Server Surface Area Configuration. She worked with the incredible Notification Services development team for over four years, and enjoyed (almost) every minute of it. The times she didn’t enjoy it were due to lack of sleep.   Kami LeMonds is a Documentation Manager for the SQL Server User Education team at Microsoft and manages a team of technical writers. In the past eight years at Microsoft, she has been a technical writer and lead, primarily on SQL Server Management Tools. In addition, she has contributed to the Answers from Microsoft column in SQL Server Magazine, as well as the SQL Server 2000 Resource Kit. Kami recently completed a Technology Management MBA from the University of Washington.   Michael Raheem is a Senior Product Manager in the SQL Server Marketing team at Microsoft. Michael currently leads the SQL Server enterprise marketing efforts, including High Availability, Scalability, Performance, and SQL Server Always On Technologies. Prior to joining the marketing team, he led the design and implementation of several SQL Server 2005 tools such as Management Studio, Upgrade Advisor, Database Mail, and Surface Area Configuration. Michael has spoken at several conferences, including TechEd, TechReady, PASS, and SQL Connections. Additionally, he has contributed to the Answers from Microsoft column in SQL Server Magazine and has over 13 years of experience in designing and developing solutions with Microsoft SQL Server.   Dima Sonkin is a Software Design Engineer Lead at Microsoft Corporation working in SQL Server Management Tools product group. He has been with Microsoft for over seven years. His development team owns such components as SQL Server Agent, SMO/DMO, SQL Profiler, Database Tuning Advisor, Database Mail/SQLMail, SqlCmd/OSQL, and many others. Dima has been through entire shipping cycles of SQL Server 2000 and SQL Server 2005. He has filed over a dozen patents in database management tools space. He holds multiple Microsoft certifications, including MCSD and MCDBA. He has spoken at several professional conferences. Prior to Microsoft, Dima held the position of Staff Software Engineer with IBM Corporation. His formal education includes a master’s degree in Engineering from St. Petersburg Technical University and an MSEE from the University of South Florida.   Brian Welcker is Group Program Manager for SQL Server Reporting Services, part of the SQL Server product group. His team is focused on delivering the premier managed and ad-hoc enterprise reporting platform as part of SQL Server 2005, as well as delivering developer reporting components for Visual Studio 2005. A 10-year Microsoft veteran, Brian is one of the founding members of the Reporting Services team; he previously worked as the lead program manager for SQL Server Meta Data Services, as well as a technical evangelist in Microsoft’s Developer Relations Group. He joined Microsoft after working for a company in his home town of Raleigh, North Carolina building software for hospitals and doctors’ offices. Brian resides in Seattle, Washington with his wife and two children.   Marianne Willumsen is a documentation manager on the SQL Server user education team. Before she joined the management team, she was the lead writer for the Integration Services user education team. In that role, she developed the table of contents and wrote reams of Integration Services documentation for SQL Server Books Online, as well as many samples. Writing is what she does, and prior to joining the SQL Server user education team she was a writer on the Exchange SDK team.    

Contents

Foreword . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii

Preface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix

Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . xxvi

About the Authors . . . . . . . . . . . . . . . . . . . . . . . xxvii

Chapter 1 Inside the Fundamentals. . . . . . . . . . . . . . . . . . . . . 1

Chapter 2 Inside the Installation and Upgrade Tools. . . . . . . 23

Chapter 3 Inside the Configuration Tools . . . . . . . . . . . . . . . 73

Chapter 4 Inside Management and Administration Tools . . 129

Chapter 5 Inside Data Authoring Tools . . . . . . . . . . . . . . . . 181

Chapter 6 Inside SQLCMD Query Tools . . . . . . . . . . . . . . . . 199

Chapter 7 Inside Tuning and Optimization Tools . . . . . . . . . 237

Chapter 8 Inside Scheduling Tools . . . . . . . . . . . . . . . . . . . 273

Chapter 9 Inside Monitoring Tools. . . . . . . . . . . . . . . . . . . . 305

Chapter 10 Inside Email Tools . . . . . . . . . . . . . . . . . . . . . . . . 337

Chapter 11 Inside Programming Object Models . . . . . . . . . . 357

Chapter 12 Inside Replication Tools . . . . . . . . . . . . . . . . . . . 385

Chapter 13 Inside Analysis Services OLAP Tools . . . . . . . . . 429

Chapter 14 Inside Analysis Services Data

Mining Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . 519

Chapter 15 Inside Notification Services Tools . . . . . . . . . . . 543

Chapter 16 Inside Integration Services Tools . . . . . . . . . . . . 601

Chapter 17 Inside the Reporting Services Tools . . . . . . . . . . 679

Appendix A Sample KPI Client Code— Retrieving and

Exposing Your First KPI. . . . . . . . . . . . . . . . . . . . 711

Appendix B KPI Utilities—Code for Parsing Display

Folders and Getting Image Indexes . . . . . . . . . . 713

Appendix C KPI Viewer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 721

Appendix D Complete List of Data Mining Stored

Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 723

Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 733