SQL Server 2005 Tools
SQL Server 2005 has a number of graphical and command line tools that enable administrators and developers to perform a variety of tasks. The tasks and the supporting tools are described in the following sections.
Installation and Upgrade Tools
This class of tools provides the functionality to install SQL Server 2005 components or to upgrade an existing installation of SQL Server 7.0 or SQL Server 2000. The two major tools are the Upgrade Advisor and the SQL Server Installation Wizard.
SQL Server 2005 Upgrade Advisor is an analysis and reporting tool to prepare installations of SQL Server 7.0 or 2000 to upgrade to SQL Server 2005. The tool has two main components: an analysis wizard and a report viewer. The wizard scans the old installation and verifies its components against a predefined set of rules embedded in the tool. The result of the analysis is stored in a file, which you can view in the Upgrade Advisor report viewer. The report displays the upgrade and backward compatibility issues that need to be addressed before or after upgrade to ensure a smooth upgrade.
Run the Upgrade Advisor before upgrading or migrating to SQL Server 2005 to address upgrade issues before proceeding to the Setup Wizard.
SQL Server Installation Wizard and Command Line Tool
The SQL Server 2005 Setup tool provides the functionality to install SQL Server products. The Setup tool can be launched in two ways: through the Setup Wizard and through the Command line. The Installation Wizard provides a graphical user interface, which assists the user through the installation process. The wizard guides the user through installation steps such as system requirements, product key registration, product selection, instance naming, service account configuration, and collation settings, and shows installation progress.
The Setup command line tool can be executed from a Windows command shell and provides advanced installation options such as remote installation, and can be more suitable for large enterprise installations.
Both the Setup Wizard and command line tool provide functionality to upgrade the existing installations of SQL Server.
SQL Server 2005 provides a number of tools for the configuration and setup of individual products. Using these tools, you can perform a variety of tasks such as controlling the state of services, configuring client and server protocols, and enabling or disabling features.
SQL Server Configuration Manager
The SQL Server Configuration Manager tool provides functionality to control the configuration options associated with all SQL Server 2005 services. In addition, the tool allows configuration of protocols for both the client and server. The SQL Configuration Manager is a graphical user interface tool built as a Microsoft Management Console (MMC) snap-in. The tool can also be accessed from Windows Computer Management to configure a remote computer. SQL Server Configuration Manager does not make a SQL connection; instead, it makes a Distributed Component Object Model (DCOM) connection to use Windows Management Instrumentation (WMI) on the target server to manipulate its services and Registry key.
Surface Area Configuration
To create a secure initial installation, some functionalities of SQL Server 2005 are disabled by default. This reduces the surface area of your installation for potential security attacks and decreases the number of features and components that you need to manage. The surface area functionality includes service states associated with SQL Server 2005 components, some features of the components, and the remote connectivity option for some of the components.
You can use the Surface Area Configuration tool to switch on features after you have installed an instance of SQL Server. The Surface Area Configuration tool provides a graphical user interface to start and stop or enable and disable services and features for both local and remote computers. In addition, the tool provides an advisory help interface to better understand the implications of enabling or disabling a feature. There is also a command line version of the tool, which provides functionality to export and import the surface area settings between computers.
Reporting Services Configuration
The Reporting Services Configuration tool provides a vehicle to configure and control the Reporting Services settings on an installation of SQL Server 2005. This tool can also be used for the deployment of Reporting Services if the user has chosen the option to copy the files during the initial installation (from the SQL Server Installation Wizard). Like SQL Server Configuration Manager, this graphical user interface tool takes advantage of WMI to manipulate the configuration files and Registry keys. The tool can be used to connect to both local and remote installations of SQL Server 2005 Reporting Services.
Usage and Error Reporting
SQL Server 2005 offers you the option to send information about product usage and report errors to Microsoft. The user can use the Usage and Error Reporting tool to opt in or out of this option. The tool provides a matrix where the user has the option to send feature usage information or error reports or both for each of the installed products. The Microsoft SQL Server team uses the error reports to improve the functionality and quality of SQL Server in future releases and service packs.
Management and Administration Tools
SQL Server 2005 provides comprehensive management and administration tools for all products of SQL Server 2005. These tools enable you to use a graphical interface for tasks or give you the option to run queries to manipulate data or perform administration tasks.
SQL Server Management Studio is an integrated and comprehensive environment for management, administration, authoring, and monitoring for all SQL Server 2005 products. The tool has several integrated components that support various classes of tasks. The Object Explorer (the tree view in the left pane) and the new management dialogs provide a rich graphical user interface for management and administration of the server objects on both local and remote installations of SQL Server. Management Studio supports management of all SQL Server 2005 components, as well as Database Engine in SQL Server 2000.
Report Manager is a web-based tool for managing Reporting Services reports via an HTTP connection. The tool can be accessed via Microsoft Internet Explorer and it provides the functionality to manage security and subscriptions of reports and models. In addition, the tool allows managing of schedules for execution and delivery of reports.
OSQL is a command line script execution tool, which provides the functionality to execute Transact-SQL (T-SQL) scripts on a Database Engine. The T-SQL scripts can contain statements in both Data Manipulation (DML) and Data Definition Language (DDL).
OSQL is one of the SQL Server legacy tools and it is officially deprecated in SQL Server 2005. If you are planning to use OSQL, you should consider using SQLCMD instead.
SQLCMD is the next generation of the T-SQL script execution tool. SQLCMD is a replacement for OSQL and ISQL and it enables you to execute T-SQL scripts against a Database Engine. SQLCMD supports all features of OSQL, as well as powerful new features such as support for scripting variables, startup scripts, and better error handling.
Query Editors in Management Studio
SQL Server Management Studio has query editors for editing and execution of scripts against SQL Server components. These include a query editor for Transact-SQL (T-SQL) language against a Database Engine or a SQL Server Everywhere database, a query editor for editing and execution of Multidimensional Expression (MDX) and XML for Analysis (XMLA) against an Analysis Services database, and a query editor for editing and execution of DMX queries against a mining model in Analysis Services. In addition, there is a code editor for editing of XML files and viewing of the XML query results. Management Studio has a Dynamic Help feature, which displays relevant help links in the context of the query being typed.
Management Studio provides a project system for building solutions with Database Engine, Analysis Services, and SQL Server Everywhere scripts. Management Studio can be added to its integrated source control provider so that you can maintain copies of scripts as they change over a development cycle.
The bulk copy command line tool is perhaps one of the oldest tools in Microsoft SQL Server and it is one of a DBA's best friends. The tool provides the capability to import and export data between SQL Server Database Engine and an operating system file in a specified format. The high performance of the BCP makes it an ideal tool to copy large number of rows in and out of tables. BCP can be used in a variety of scenarios, such as copying or transferring all or a subset of rows between two tables on two separate computers, or extracting data to be viewed in an external application such as Microsoft Office Excel. The tool is versatile and requires little knowledge of T-SQL, as long as the user understands the structure of files and tables.
Monitoring and Operations Tools
SQL Server 2005 provides you with several tools for monitoring activities in the Database Engine, for mirroring, and for replication, as well as the capability to schedule and automate tasks.
Activity Monitor in Management Studio
Activity Monitor in SQL Server Management provides information for monitoring activities in SQL Server Database Engine. The activities can be grouped and viewed by current user connections, process numbers, status, locks, commands that applications are running, locked database objects, and types of locks that currently exist in the system. In addition, activities can be filtered based on user-defined criteria and refreshed at specific intervals.
Database Mirroring Monitor in Management Studio
Database Mirroring is a new high availability feature in SQL Server 2005 Database Engine. Database Mirroring maintains two copies of a database on different installations (instances) of SQL Server Database Engine. One server instance acts as the principal server, whereas the other server instance acts as a mirror server. As the data changes on the principal server, Database Mirroring automatically applies the changes to the mirror server. In case of a failure on the principal server, the system fails over to the mirror server, ensuring database availability for the client applications.
The Database Mirroring Monitor in Management Studio provides the functionality to verify the flow of data between the principal and mirror servers. Database Mirroring Monitor is also helpful in troubleshooting causes of data flow interruption between principal and mirror servers.
SQL Server Agent
SQL Server Agent is a SQL Server 2005 Database Engine subsystem that executes scheduled maintenance and operational tasks specified by the system administrator. The tasks are grouped by jobs and each job can have one or more job steps corresponding to each task. SQL Server Agent jobs run manually or automatically based on scheduled intervals. In addition, SQL Server Agent provides a monitoring and alerting mechanism for system events. After an alert is triggered, it can fire a job or notify an operator via email, pager, or network alerts.
New in SQL Server 2005, SQL Server Agent supports execution of job steps. A job contains one or more steps and each job step can be a Database Engine, Analysis Services, or Integration Services task. Also, SQL Server Agent supports alerts based on WMI events. These new features open up a new world of monitoring and alerting opportunities, which you'll find discussed later in this book.
SQL Server Replication Monitor
SQL Server Replication provides technologies for copying and synchronizing data between databases located locally or on a remote server. SQL Server Replication Monitor is a graphical user interface tool for monitoring the overall status and performance of replication between sites. Replication Monitor also provides the functionality to set up warnings, thresholds, and alerts, so the system administrator can be informed about status and performance in a timely manner.
Microsoft Operations Manager
Microsoft Operations Manager (MOM) is a platform for monitoring the operations of server products in a large IT organization. Each product is monitored via a Management Pack, which is a set of configuration settings and alerts designed specifically for the product. SQL Server 2005 has a Management Pack for Microsoft Operations Manager (MOM). The SQL Server Management Pack monitors the performance and availability of all SQL Server 2005 products, including the SQL Server Agent and Replication. In addition, MOM monitors database health, free space, clustering, and the security settings of Database Engine.
Database, Schema, and Report Design Tools
There are a number of database, schema, and report designer tools in SQL Server 2005. The database and schema design tools are hosted in SQL Server Management Studio and Business Intelligence Development Studio. The Reporting Service report design tool is also hosted in Business Intelligence Development Studio. In addition, Reporting Services provides an easy-to-use tool for building reports called Report Builder. Report Builder, with its simple interface, is targeted for information workers.
Management Studio Designers
SQL Server Management Studio has a number of graphical database tool designers that provide functionality to design databases, tables, views, and queries. These designers are also available in Visual Studio 2005.
Database Diagram Designer (see Figure 1-1) is a graphical tool that can be used to design entities and relationships in a database. The tool allows creation, modification, or deletion of database objects such as tables, columns, keys, constraints, indexes, and relationships. The tool also enables you to construct a database diagram by adding or removing the illustrated entities such as tables, columns, keys, and relationships.
Figure 1-1. Database Diagram Designer in SQL Server Management Studio.
Table Designer is another graphical tool that allows creation or modification of tables in a Database Engine. Table designer also makes it possible to design table columns, relationships, primary and unique keys, constraints, indexes, and change scripts. In addition, table designer provides the functionality to view and edit data stored in a table.
The Query and View Designer tool allows graphical creation of Transact-SQL queries and views. The tool supports various types of queries such as SELECT, UPDATE, DELETE, MAKE TABLE, INSERT VALUES, and INSERT RESULT queries. Also, Query and View Designer provide the functionality to modify data in the resultset of executed queries.
SQL Server 2005 provides a rich development tool as well as tight integration with Visual Studio. This integration enables you to utilize both SQL Server 2005 features as well as Visual Studio functionality for developing solutions.
Business Intelligence Development Studio
Business Intelligence Development Studio in an integrated development environment (IDE) for SQL Server 2005 business intelligence components. Business Intelligence Development Studio provides the functionality to build and deploy solutions for Analysis Services, Integration Services, and Reporting Services projects. Under the surface, Business Intelligence Development Studio is Microsoft Visual Studio 2005 IDE with special business intelligence project types. The project types have a rich set of graphical design tools and wizards to develop objects.
Visual Studio 2005
Visual Studio 2005 provides a comprehensive development environment for SQL Server 2005. Visual Studio 2005 provides a number of visual database tools, some of which are shared with SQL Server Management Studio. However, Visual Studio 2005 is the only tool that allows debugging of Database Engine stored procedures, user-defined functions, and triggers. New in Visual Studio 2005 is the functionality to debug managed Common Language Runtime (CLR) assemblies running inside the SQL Server 2005 process.
SQL Server 2005 supports several options for deploying data and metadata to a server. These options include deploying an application from a project file, a source server, or a previous version of Database Engine or Analysis Services.
Generate Script Wizard enables you to generate Transact-SQL script for all databases, one database, or a subset of objects in one database. After the script from the source SQL Server Database Engine is generated, it can be executed on the target server.
Import and Export Data Wizard provides the functionality to copy data from one SQL Server Database Engine to another. In addition, the tool enables you to import data from any Object Linking and Embedding Database (OLE DB) provider, which makes it a powerful tool for creating objects from any data source, such as a Microsoft Office Excel spreadsheet.
Copy Database Wizard
Copy Database Wizard is a component of SQL Server Management Studio, designed for copying the databases and their objects from one SQL Server Database Engine and deploying them on another Database Engine. With the wizard, you can copy all or a subset of database objects, server logins, and SQL Server Agent jobs to the destination server. The Copy Database Wizard is built on top of the SQL Server 2005 Integration Services (SSIS) technologies. The result of the copy or move options in the wizard is saved as an SSIS package that is wrapped in a SQL Server Agent job. The job can run immediately, or it can be scheduled to run at a later time or on a recurring basis. This provides the functionality to copy objects when the source and destination servers are not busy.
Copy Database Wizard provides an excellent tool for migration of SQL Server 7.0 or 2000 to SQL Server 2005.
Analysis Services Deployment Wizard
The Analysis Services project contains definitions of objects in a database such as cubes, dimensions, measure groups, and so on. You can deploy an Analysis Services project on a target server by using the Analysis Services Deployment Wizard. Aside from specifying the source database file in the project and the destination server, the wizard provides additional options for controlling the configuration settings and connection strings. The Deployment Wizard can also run from a command prompt. The user can specify the same deployment switches as the wizard with the option to deploy immediately or generate an XMLA script to be executed later.
Query Tuning and Optimization Tools
SQL Server 2005 includes modifications to some familiar tools that enable you to tune and optimize a broader set of SQL Server products.
SQL Server Profiler
SQL Server Profiler is a graphical user interface that captures events for SQL Server Database Engine or Analysis Services, which can then be used to monitor server events and save trace data to a file or table. The events in the trace can later be analyzed to investigate system performance, troubleshoot a server problem, or audit actions performed on a server.
Profiler also makes it possible to replay the captured trace data from files or tables. This is extremely useful for troubleshooting scenarios where there is a problem on one server instance and you would like to reproduce the problem on another instance and verify a fix.
SQL Server Profiler and Replay provide an excellent tool for verifying two side-by-side systems, such as after an upgrade.
Database Engine Tuning Advisor
The Database Tuning Advisor tool enables you to analyze data and make recommendations to improve the performance of a Database Engine. The tool works in two stages: analysis and recommendation. In analysis stage, the tool measures the effects of performance while a set of Transact-SQL statements are executed on the Database Engine. When analysis is complete, the tool provides recommendations to add, remove, or modify physical design structures such as clustered indexes, nonclustered indexes, indexed views, and partitioning.
The Database Tuning Advisor is great for tuning the performance of a database without requiring an expert understanding of the structure of the database, the workload, or the internals of SQL Server Database Engine.
You can create Database Engine Maintenance Plans by using the Maintenance Plan Designer or Maintenance Plan Wizard. These tools enable you to automate common maintenance tasks and to time these tasks so they make the least impact on your system.
Maintenance Plan Designer and Wizard in Management Studio
The Maintenance Plan designer in SQL Server Management studio provides the functionality to create a workflow required to maintain your system. The maintenance tasks can back up your databases regularly, check for data inconsistencies, or update indexes and table statistics to make sure your system retains its performance. Management Studio also offers a wizard for the creation of maintenance plans. Although the Maintenance Plan Wizard can be used only for creating basic maintenance plans, the designer provides more flexibility in task selection and control flow. Maintenance Plan designer is built on top of Integration Services, and each plan creates a SQL Server Agent job that runs automatically at a scheduled time.
Email Delivery Tools
SQL Server 2005 contains both the previous email solution, as well as a new and more reliable option. These email delivery tools enable you to send emails from the database engine.
The SQL Mail feature of SQL Server 2005 enables you to send or read email messages from the database engine. These email messages could include the result of a query execution as well as file attachments. SQL Mail requires installation of an Extended Messaging Application Programming Interface (Extended MAPI) such as Microsoft Outlook. Extended MAPI is a collection of COM objects that provide an interface between client applications such as SQL Mail and email servers. Although SQL Mail is a powerful and popular feature amongst database administrators and developers, there are limitations in configuration, troubleshooting, and support on 64-bit and clustered systems.
Similar to SQL Mail, the Database Mail feature of SQL Server 2005 provides the functionality to send email messages from the Database Engine. However, Database Mail does not require installation of Microsoft Outlook or any other Extended MAPI clients. Instead, it uses a Simple Mail Transfer Protocol (SMTP) server for sending email messages, which can be installed on the same computer or a remote computer. In SQL Server 2005, Database Mail is designed from the ground up as a high-performance and highly available enterprise mail solution. Unlike SQL Mail, Database Mail is easy to configure and troubleshoot, and it supports 64-bit and clustered systems.
Programming Object Models
Several programming object models are available in SQL Server 2005. The object models enable you to manage SQL Server products programmatically.
SQL Server Management Objects (SMO)
SQL Server 2005 Database Engine has a new object model called SQL Server Management Objects (SMO). SMO provides a complete set of Microsoft.NET Framework objects for use in managed applications, such as those developed in Microsoft Visual Basic .NET or Microsoft C#. SMO replaces the legacy SQL Distributed Management Objects (SQL-DMO) and has a number of performance and functionality improvements over DMO. The goal in the design of SMO has been to support all features of SQL Server 2005 and SQL Server 2000 Database Engine.
The Analysis Management Objects (AMO)
SQL Server 2005 Analysis Service has a new object model, Analysis Management Objects (AMO). AMO provides the functionality to manage SQL Server 2005 Analysis Services objects and perform administrative tasks such as processing cubes and backing up databases. AMO can be used only against SQL Server 2005 Analysis Services.
Replication Management Objects (RMO)
Similar to SMO, Replication Management Objects (RMO) enables you to programmatically configure and manage a replication topology in SQL Server 2005. The goal in the design of RMO has been to support the programming of all aspects of SQL Server 2005 replication. In addition to SQL Server 2005, RMO supports SQL Server 7.0 and SQL Server 2000 replication topologies.
SQL Distributed Management Objects (SQL-DMO)
The SQL Distributed Management Objects (SQL-DMO) is the legacy object model for programming against SQL Server 2000 and SQL Server 2005. SQL-DMO has been replaced by SMO and it is available only for backward compatibility with legacy applications. SQL-DMO supports only features that were available in SQL Server 2000; it does not support the new features of the database engine in SQL Server 2005. To reduce the surface area of SQL Server 2005, SQL-DMO is not installed by default.
SQL Distributed Management Objects (SQL-DMO) is deprecated in SQL Server 2005. You should consider using SQL Server Management Objects (SMO) instead.