Pub. Date:
Inside SQL Server 2005 Tools: Authoritative Guides for Microsoft Windows and Server Professionals

Inside SQL Server 2005 Tools: Authoritative Guides for Microsoft Windows and Server Professionals

Multimedia Set

Current price is , Original price is $59.99. You

Temporarily Out of Stock Online

Please check back later for updated availability.

This item is available online through Marketplace sellers.


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.

Product Details

ISBN-13: 9780321397966
Publisher: Addison-Wesley
Publication date: 11/13/2006
Series: Microsoft Windows Server System Series
Pages: 816
Product dimensions: 7.00(w) x 9.25(h) x 1.58(d)

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 HyperionFrance. 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.



Table of 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




SQL Server has always distinguished itself by, among other things, its management tools. One of the earliest design principles for the team was "It just works." We accomplished this by replacing the usual database knobs and levers with intelligent defaults and adaptive behavior. We never felt that we could or should remove all administrator control. Instead we delivered Enterprise Manager in SQL Server 7.0 and SQL Server 2000. Enterprise Manager grew a huge fan base over the years. So it was with some trepidation that we undertook a massive rewrite, yielding SQL Server Management Studio (SSMS) in SQL Server 2005.

The most nerve-wracking experience for software developers is receiving the user initial feedback on a new product or feature. We released an early version of SSMS in SQL Server 2005 Beta 1. You might recall this as Yukon Beta 1. When we received the summary of an early user survey, we were delighted to find SSMS as the most liked new feature in the beta release. At the same time, we were mortified to also find it as the most disliked feature!

Getting past our bewilderment, we realized that we were getting incredible feedback from our users. In fact, we found out that we had two distinct user populations. We had taken our management tools in the direction of supporting DBAs as developers in addition to administrators. The feedback told us we were on the right track, but that we had serious work to do. The users that felt like developers—that is, those that wrote and maintained scripts, schemas, stored procedures and other objects—loved the new paradigms in the product. This affirmed ourassumptions and research. The users that felt like administrators—that is, they worried about users and user access, backup and restore and other operations—hated the new paradigms. We had assumed that most DBAs were both developers and administrators. With the amazing growth of SQL Server, it is not surprising that DBAs have specialized.

Armed with user feedback, we made substantial changes to SQL Server Management Studio. Your authors were in the thick of the action. They shaped the feature set and the user interface you use when you manage your SQL Server. They know the tool set inside and out. I had the privilege of working with them for five years. I’m delighted to see them write this book and deliver their insights, their advice, and a behind the scenes perspective.

Several chapters stand out. Chapter 4 covers administration, vital to keeping your server running optimally and securely. DBAs receive constant requests to move data for users. SQL Server 2005 features Integration Services, the successor to Data Transformation Services. Chapter 16 covers this new and improved tool. And as the guy who drove Business Intelligence in SQL Server, I of course recommend the coverage of this topic in Chapter 13. BI is growing rapidly in our industry. Even if you are not engaged in BI, this chapter is worth your time. You will be getting requests for Business Intelligence in the future.

Enjoy this book, and enjoy SQL Server 2005!

Bill Baker
General Manager, SQL Server Business Intelligence
Redmond, WA
June 2006


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

Who Should Read This Book?

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.

How to Use This Book

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.

What This Book Covers

Here's a brief overview of each chapter and appendix.

Chapter 1: Inside the Fundamentals

This chapter covers the history of SQL Server, as well as background on the SQL Server tools. The rest of the chapter provides an overview of the new toolset in SQL Server 2005, as well as a mapping between tools from previous releases and the current version. In addition, there is a brief summary of each tool.

Chapter 2: Inside the Installation and Upgrade Tools

The features included with each edition of SQL Server 2005 are highlighted in this chapter. It also provides brief descriptions of the target audience for each edition. You can find step-by-step instructions on installations, upgrade, gotchas to avoid during the install process, and information on backward compatibility issues. The new Upgrade Advisor is also covered in detail.

Chapter 3: Inside the Configuration Tools

Confused by the variety of configuration tools available in SQL Server 2005? This chapter will help you understand which tool to use for which task. You can also find descriptions and the architecture of each configuration tool. SQL Server Configuration Manager, SQL Server Surface Area Configuration, and Usage and Error Reporting tools are all covered in this chapter, as well as common configuration tasks. Because security is such a hot button issue, this chapter also covers surface area reduction.

Chapter 4: Inside Management and Administration Tools

This chapter is a must-read for all SQL Server users. You will find information on everything from how the new toolset is laid out, how to register servers, using the management dialogs, and understanding Object Explorer. There is also a discussion of management and administration scenarios that describes how to leverage Management Studio to accomplish common tasks.

Chapter 5: Inside Data Authoring Tools

Running and editing queries are two of the most common tasks you are likely to perform in SQL Server. This chapter describes the new querying tool (Query Editor) available in SQL Server Management Studio. This chapter is a must-read for anyone interested in modifying their editing environment or learning inside tricks for using Query Editor. Bulk Copy Utility can also be used when moving external data to SQL Server, and a full review of Bulk Copy Utility architecture and scenarios is covered.

Chapter 6: Inside SQLCMD Query Tools

Wondering what happened to your old friend OSQL? SQLCMD is the replacement tool, where you will find backward compatibility for much of OSQL as well as many enhancements. This chapter gets you up to speed on working with the new tool. You can also find descriptions of all the command line options and insider tips and tricks.

Chapter 7: Inside Tuning and Optimization Tools

As soon as you understand the new query editing environment, you may want to enhance query performance. This chapter reviews the SQL Server Management Studio query optimizations and takes a comprehensive look at SQL Server Profiler. The SQL Server Profiler user interface has undergone a makeover since the last release, so this chapter may be helpful for even experienced users. You can also find information on the trace API, which enables users to automate creation of the trace and perform trace data reading and manipulations programmatically. Finally, this chapter covers the Database Tuning Advisor that replaces the Index Tuning Wizard in this release of SQL Server. We cover detailed architecture of the tool and its advanced options.

Chapter 8: Inside Scheduling Tools

Task scheduling through SQL Server Agent is covered in detail in this chapter. You can read about everything from creating job steps to job scheduling and execution. Essential information on SQL Server Agent security is included, to ensure that you understand the changes from previous releases as well as how to refine permissions as much as possible. If your environment requires running jobs on multiple machines at the same time, the information on multi-server administration will be very helpful.

Chapter 9: Inside Monitoring Tools

One of the most essential administration tasks is monitoring the server for potential or immediate problems. This chapter describes the monitoring tools, such as Activity Monitor, SQL Server Agent, and SQL Server Profiler, as well as reviews some of the core monitoring scenarios.

Chapter 10: Inside Email Tools

If you are considering using a mailing solution with SQL Server, you will want to fully understand the new email tools. In this chapter you will learn tips and tricks for working with the legacy tool (SQL Mail), as well as in-depth configuration information on Database Mail, new for SQL Server 2005. A brief overview of SQL Agent mail is also included.

Chapter 11: Inside Programming Object Models

You will accomplish many core administration tasks by using a programming model rather than the user interface. Given the importance of understanding the programming models and how they work, this chapter is a must-read. SQL Server 2005 introduces an entirely new programming model (SMO), and this chapter covers basic SMO concepts, usage scenarios, and internal SMO architecture with advanced performance and optimization topics.

SQL Server Management Objects (SMO) is a brand-new API. SMO is implemented as a .NET library and replaces SQL-DMO (COM server with multiple automation interfaces) that shipped as part of earlier releases and is deprecated with SQL Server 2005.

Chapter 12: Inside Replication Tools

Today's data increasingly needs to be in multiple locations at the same time and the data must be kept synchronized. Database developers must consider scale-out requirements for performance and growth and roll-up requirements for reporting and data warehousing when planning and building applications. Disconnected users, such as sales or service personnel, need to take data with them when they enter the field. Keeping this disparate data synchronized on an ongoing basis is a difficult task.

Fortunately, SQL Server offers powerful technology for replicating and synchronizing data. Database replication was introduced in SQL Server version 6.5, but SQL Server 2005 brings a new level of power, performance, and ease of use to this complex functionality. This chapter provides an insightful look at the tools, as well as tips and tricks for accomplishing common tasks.

Chapter 13: Inside Analysis Services OLAP Tools

This chapter covers all aspects of designing, deploying, and managing OLAP databases. In addition, this chapter describes over 20 business scenario solutions and techniques that take full advantage of the Business Intelligence development studio. Also included on the CD are three full samples for some of these scenarios (that is, a full SQL Server Integration Services and SQL Server Analysis Services self documentation tool and two KPI samples). These samples are not only great examples of design best practices, but are also immediately useful in your day-to-day activities.

Chapter 14: Inside Analysis Services Data Mining Tools

This section covers all the Data Mining tools from designing to training a model, to defining prediction, to actually embedding data mining techniques in your ETL job or your reporting environment.

This is not an exhaustive list of scenarios, and you may find many other tips, techniques, and best practices through your own experience. These tools are extremely rich and will surely provide great satisfaction to any data analyst aspiring to gain knowledge from and insight into large data sets.

Chapter 15: Inside Notification Services Tools

Are you new to Notification Services? Or did you use Notification Services 2.0, but want to understand the improvements made in SQL Server 2005? This chapter briefly describes Notification Services, and then introduces the Notification Services tools, including the command prompt utility that has been carried forward from Notification Services 2.0, the new SQL Server Management Studio interface, the built-in stored procedures and views, and the new management API. This chapter then walks you through the use of Management Studio and the command prompt utility to deploy and administer instances of Notification Services.

Chapter 16: Inside Integration Services Tools

Even if you were familiar with DTS (Data Transformation Services) in SQL Server 2000, you will want to review this chapter to learn about its replacement in SQL Server 2005 (Integration Services). The extract, transform, load (ETL) component of SQL Server was redesigned from the ground up in SQL Server 2005.

Integration Services introduces a rich set of tools to support the development, deployment, and administration of ETL solutions. The tools support the simplest solutions in which you just want to perform tasks such as copying data from one location to another to enterprise-level solutions in which you develop a large number of complex packages in a team environment. This chapter describes the Integration Services tools and service in the context of the life cycle of the ETL solution: development and testing, deployment to the test or production environment, and administration in the production environment.

Chapter 17: Inside the Reporting Services Tools

This section describes and illustrates with many business scenarios the various aspects of the reporting cycle. From upgrading, to designing, to managing a report infrastructure, to scheduling, to integrating reporting with other BI components such as SQL Server Integration Services and SQL Server Analysis Services, this section helps you make the most of the Reporting Services capability.

Appendix A: Sample KPI Client Code—Retrieving and Exposing Your First KPI

This sample shows the basics of how to query and retrieve KPI information such as value, goal, trend, and status with ADOMD.

Appendix B: KPI Utilities—Code for Parsing Display Folders and Getting Image Indexes

This sample is a little bit more sophisticated than Appendix A. It navigates through the list of KPIs, retrieves the Display folder, and categorizes the KPIs using their display folders.

Appendix C: KPI Viewer

This sample contains the entire code for building an ASP application that connects to a cube, retrieves and categorizes KPIs using their display folders, and lets the user select a dimension to drill through on selected KPIs.

Appendix D: Complete List of Data Mining Stored Procedures

The full list of available data mining stored procedures, as well as their definitions, can be found in this appendix.

Preparing to Use This Book

Before starting to use this book you should have access to SQL Server 2005. For further information on how to get a copy of SQL Server 2005 and the supported Windows operating system, see site, under "Download Trial Software."

What's on the CD-ROM

The enclosed CD-ROM can be used to view sample code from the book as well as any corresponding Visual Studio project files.

© Copyright Pearson Education. All rights reserved.

Customer Reviews

Most Helpful Customer Reviews

See All Customer Reviews