SQL Server 2005 provides several types of tools for administration. The graphical administration tools are the ones you will use most often. You can access these tools by selecting Start, choosing Programs or All Programs, and then using the Microsoft SQL Server 2005 menu.
SQL Server 2005 has several new graphical tools that replace or combine the features of the graphical tools in previous SQL Server editions. SQL Server Management Studio replaces SQL Server Enterprises Manager, Query Analyzer, and Analysis Manager. This means that you can use SQL Server Management Studio to perform most of your core SQL Server administration tasks.
SQL Server Management Studio provides several different views. When you first start working with this tool, you will see the Registered Servers view, the Object Explorer view, and the Summary view shown in Figure 1-1. If they are not displayed, these and others views can be accessed from the View menu, and the following descriptions explain how to use each view:
Object Explorer. Allows you to view and connect to SQL Server, Analysis Server, Integration Services Server, Report Server, and SQL Mobile. Once connected to a particular server, you view its components as an object tree and can expand nodes to work your way to lower levels of the tree.
Registered Servers. Shows the currently registered servers. The top bar of the view allows you to quickly switch between servers of a particular type (SQL Server, Analysis Server, Integration Services Server, Report Server, SQL Mobile).
Template Explorer. Provides quick access to the default Query Editor templates and any custom templates you have created. Templates can be created in any script language supported by SQL Server Management Studio.
Solutions Explorer. Provides quick access to existing SQL Server, Analysis Server, and SQL Mobile projects. Projects detail connections, queries, and other functions to perform when the project is executed.
If you have worked with previous versions of SQL Server, you will find that SQL Server Management Studio is very different from the tools it replaces. Most of the friendly wizards are gone and have been replaced with nonmodal dialog boxes that provide quick access to configuration elements. As shown in Figure 1-2, Script and Help options are provided on the top bar of these dialog boxes to make it easy to generate a script based on your configuration choices and get help when you need it.
Another important tool is SQL Server Configuration Manager, shown in Figure 1-3. SQL Server Configuration Manager replaces Server Network Utility, Client Network Utility, and Services Manager. This means you can use SQL Server Configuration Manager to perform many essential service setup and network configuration tasks.
When you select a service under the Services node, you can manage the service in the details pane by right-clicking it and then choosing an appropriate option, such as Start, Stop, or Restart. You can also choose Properties to configure the related settings, such as startup mode, login account, and login account password.
SQL Server 2005 is designed for local and remote management. You can use most of the tools to manage local resources as well as remote resources. For example, in SQL Server Management Studio, you can register a new server and then connect to it. Afterward, you can remotely manage the server and all its databases from your system. Table 1-1 provides a summary of the graphical administration tools discussed as well as other useful graphical tools.
Table 1-1. Quick Reference for Key SQL Server 2005 Administration Tools
Administration Tool | Purpose |
|---|---|
Business Intelligence Development Studio | Allows you to develop and manage Business Intelligence objects. Includes SSIS Designer, which you can use to create and maintain SSIS packages. |
Database Tuning Adviser | Helps you tune the performance of SQL Server databases. |
SQL Server Import/Export Wizard | Allows you to define SSIS Services packages for importing and exporting data. |
Report Manager | Allows you to manage reports generated by Reporting Services. |
SQL Server Configuration Manager | Allows you to configure the client and server network libraries and manage SQL Server services. Replaces Server Network Utility, Client Network Utility, and Services Manager. Covered in Chapter 2. |
SQL Profiler | Allows you to analyze user activity and generate audit trails. SQL Profiler is a graphical interface to SQL Trace. Covered in Chapter 13. |
SQL Server Management Studio | The main administration tool for SQL Server 2005. Manages SQL servers, databases, security, and more. Key aspects are discussed in Chapter 4. Replaces SQL Server Enterprises Manager, Query Analyzer, and Analysis Manager. |