Uh-oh, it looks like your Internet Explorer is out of date.
For a better shopping experience, please upgrade now.
Inside SQL Server 2005 Tools: Authoritative Guides for Microsoft Windows and Server Professionals available in Multimedia Set
- ISBN-10:
- 0321397967
- ISBN-13:
- 9780321397966
- Pub. Date:
- 11/13/2006
- Publisher:
- Addison-Wesley
Inside SQL Server 2005 Tools: Authoritative Guides for Microsoft Windows and Server Professionals
Multimedia Set
Buy New
$45.29Buy Used
$39.62This item is available online through Marketplace sellers.
Temporarily Out of Stock Online
Please check back later for updated availability.
This item is available online through Marketplace sellers.
Overview
- 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.
ADVERTISEMENT
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
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
Preface
Foreword
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
Preface
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.