Inside SQL Server 2005 Tools (Microsoft Windows Server System Series)

This book reveals the power of the SQL Server 2005 tools to database management system professionals, enabling you to maximize productivity. The authors of the book have been working on the SQL Server 2005 team since its inception, they share the philosophy behind the design of the tools, and they are familiar with insider tips and tricks.The SQL Server 2005 family of products consists of Database Engine, Analysis Services, Reporting Services, Integration Services, Notification Services, and SQL Server Mobile Edition (SQL Server Everywhere Edition). This book describes the core functionality of all the SQL Server 2005 tools, and closely examines the Database Engine tools. The book provides solutions for installation, upgrade, configuration, schema design, management, authoring, development, optimization, deployment, operation, and troubleshooting. It covers the Installation Wizard, Upgrade Advisor, SQL Server Configuration Manager, SQL Server Management Studio, Database Tuning Advisor, SQLCMD, Database Mail, SQL Server Agent, and SQL Server Profiler. It also touches on technologies that these tools are built upon, such as SQL Server Management Objects (SMO), the Windows Management Instrumentation (WMI) Provider, and ADO.NET.The book describes individual features and how they work together to create end-to-end scenarios. For the beginning user, we have screenshots and code samples. We provide examples with code snippets where applicable. When we introduce particular aspects of tool behavior or scenarios, we start first by describing them and relating them to other previously described tasks. Screen shots show how these things look inside the tools. For the more experienced users, we provide tips and insight. In addition, we provide code snippets to illustrate how particularly interesting functionalities can be implemented with underlying APIs.In summary, the book will provide: * Hundreds of time-saving solutions * Tips, tricks, and workarounds * Solutions for working smarter * A vehicle through which you can expand your existing knowledge of SQL Server tools The book is targeted at both new and experienced database professionals. New professionals who have never used the SQL Server tools will learn how to efficiently use each tool to perform specific tasks. More experienced database professionals can update their existing knowledge of the tools, learn about new features, and learn tips and tricks of the SQL Server 2005 toolset.The purpose of this book is not to introduce you to the basic concepts of databases. We assume that you have been working with databases for at least one year. This book will show you how the tools can be used to maximize productivity while performing common SQL Server 2005 tasks. After reading this book you will be very comfortable using the tools for day-to-day tasks.The book is organized by SQL Server tools and scenarios. Each chapter presents a tool or set of tools and also includes a number of relevant scenarios that illustrate the use of the tools. If you are interested in specific tools or scenarios, you can move straight to those chapters, or you can read through the book in a linear fashion for a more complete understanding of all the tools and features. Direct from Microsoft the Complete Hands-On Guide to SQL Server 2005s Powerful Tools! Microsoft SQL Server 2005s high-powered management tools can dramatically improve DBA productivity and effectiveness. Now theres a comprehensive guide to SQL Server 2005s 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 Servers new e-mail tool, Database Mail Managing replication in complex distributed environments Mastering SQL Servers 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-ROMs 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 DHERS is the lead program manager on Microsofts SQL BI Analysis Services team and has spent five years designing and specifying Analysis Services tools. KAMI LEMONDS is the documentation manager for Microsofts 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 UtilitiesCode 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 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.