About the Book
This is the eBook version of the printed book. If the print book includes a CD-ROM, this content is not included within the eBook version.
Direct from Microsoft Insiders: the Complete Hands-On Guide to
SQL Server 2005’s Powerful Tools!
Microsoft SQL Server 2005’s high-powered management tools can dramatically
improve DBA productivity and effectiveness. Now there’s a comprehensive guide
to SQL Server 2005’s toolset, straight from the Microsoft team that created it.
This book covers the entire toolset in unprecedented depth, guides database
professionals in choosing the right tools, and shows them how to use various
tools collectively to solve real-world problems. The authors present “how-to”
solutions and never-before-published tips for SQL Server 2005 installation,
upgrades, configuration, authoring, schema design, development, deployment,
optimization, troubleshooting, and more. Coverage includes
Understanding how SQL Server 2005 tools differ from their predecessors
Leveraging the new Business Intelligence Development Studio and
Management Studio to accomplish dozens of common tasks
Improving security by reducing the “surface area” you expose
Executing queries with the new Query Editor and SQLCMD query tools
Optimizing with the revamped SQL Server Profiler and Database
Engine Tuning Advisor
Scheduling maintenance tasks with the SQL Server Agent
Monitoring with the Activity Monitor, SQL Server Agent, and other tools
Moving data efficiently and reliably with Integration Services
Using SQL Server’s new e-mail tool, Database Mail
Managing replication in complex distributed environments
Mastering SQL Server’s Analysis Services’ OLAP and Data Mining tools
Using Notification Services 2.0, Reporting Services, and more
Extending functionality using underlying APIs and the included
CD-ROM’s sample code.
MICHAEL RAHEEM is the senior product manager of the Microsoft SQL Server
team and led the design and implementation of several SQL Server tools, such
as SQL Server Management Studio, Database Mail, Upgrade Advisors, and
Surface Area Configuration. He currently leads the SQL Server enterprise
marketing efforts including high availability, scalability, and performance.
DIMA SONKIN is development lead in the SQL Server Tools group responsible
for SQL Server Agent, SMO, DMO, SQL Profiler, Database Engine Tuning Advisor,
Database Mail, SQLMail, SqlCmd, and OSQL.
THIERRY D’HERS is the lead program manager on Microsoft’s SQL BI Analysis
Services team and has spent five years designing and specifying Analysis
Services tools.
KAMI LEMONDS is the documentation manager for Microsoft’s SQL Server User
Education team and contributed to the SQL Server 2000 Resource Kit.
CD-ROM contains all sample code from the book and corresponding
Visual Studio project files.
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
Table of Contents:
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
About the Author :
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.
