Tools and Utilities Overview

With SQL Server 2000, tools are kind of disjointed and sometimes have overlapping capabilities. For instance, you have to use Enterprise Manager for common administration and DTS-related tasks; Query Analyzer as a query and authoring tool; separate client network, server network, and service management tools; Analysis Manager to administer and develop Analysis Services objects; and so on. SQL Server 2000 offers minimal tool integration by providing main menu options to launch one tool from another.

When the SQL Server 2005 team started building the new toolset, it devised two primary guiding principles: integration and "no secrets." Integration refers to tools consolidation so that administrators do not have to deal with multiplicity of tools in order to get things done. The new SQL Server Management Studio, Business Intelligence Development Studio, and SQL Server Configuration Manager are examples of this:

• SQL Server Management Studio This is an integrated management and authoring graphical tool that combines the functionality of Enterprise Manager, Query Analyzer, and the administration part of Analysis Manager.

• Business Intelligence Development Studio This is a new integrated graphical tool to design and develop business intelligence applications using SQL Server Integration Services (SSIS; formerly DTS), Analysis Service, and Reporting Services.

• SQL Server Configuration Manager This is a graphical tool that consolidates Client and Server Network Utilities and Service Manager into a single interface.

With SQL Server 2000, you can find developers and administrators running SQL Profiler to find out about what Enterprise Manager is doing behind the scenes to perform a particular function. The "no secrets" design guideline for SQL Server 2005 tools development was devised to fix this. The scriptable dialogs in Management Studio and the new management objects APIs (SMO, RMO, and AMO) are examples of this. Every management dialog in SQL Server Management Studio provides a Script toolbar button that can be used to generate a script for the task performed by that dialog. Everything that you can do using graphical tools can be automated by using new .NET-based management object libraries such as SQL Management Objects (SMO), Replication Management Objects (RMO), and Analysis Management Objects (AMO).

The SQL Server 2005 tools and utilities can be grouped into four categories:

• Management and authoring This category includes SQL Server Management Studio, Business Intelligence Development Studio, and Express Manager.

• Performance monitoring, tuning, and optimization This category includes Profiler, Database Engine Tuning Advisor, and several new performance monitor counters.

• Operations and configuration This category includes the new SQLCMD command-prompt tool, SQL Server Configuration Manager, Database Mail, SQL Agent and maintenance wizard enhancements, other command-prompt utilities and enhancements (such as XML format file for bcp.exe), and the Microsoft Operations Manager (MOM) management pack for SQL Server 2005.

• Management application development APIs This category includes the enhanced WMI support and the new managed object libraries, such as SMO, RMO, and AMO.

Table 5.1 maps SQL Server 2000 tools to their equivalents in SQL Server 2005.

Table 5.1. SQL Server 2000 Tools and Their SQL Server 2005 Equivalents

SQL Server 2000 Tool	SQL Server 2005 Tool
Enterprise Manager SQL	Server Management Studio
Query Analyzer	Query Editor in SQL Server Management Studio
Index Tuning Wizard	Database Engine Tuning Advisor
Service Manager	SQL Server Configuration Manager
Server Network Utility	SQL Server Configuration Manager
Client Network Utility	SQL Server Configuration Manager
DTS Designer in Enterprise Manager	SSIS in Business Intelligence Development Studio
Analysis Manager	Administer using SQL Server Management Studio and design/develop using Analysis Server Project in Business Intelligence Development Studio
MDX Sample Application	Query Editor in SQL Server Management Studio
Reporting Services Report authoring using Visual Studio .NET	Report Project in Business Intelligence Development Studio or Report Builder
Command-line tools to manage	Notification Services folder in the Object
Notification Services applications	Explorer tree in SQL Server Management Studio