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:
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 CodeRetrieving 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 UtilitiesCode 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 www.microsoft.com/sql. You don't need to purchase the full-blown version of SQL Server 2005 if you just want to check it out. The evaluation version of SQL Server 2005 and SQL Server Express are free. It would be good to have SQL Server 2005 installed so you can follow along with the steps. Both SQL Server Express and the evaluation version are currently available for download on the www.microsoft.com/sql 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.