SQL Server Management Studio Overview

One of the biggest theme changes for SQL Server 2005 Business Intelligence applications was the deliberate separation between design time and management features. As you know by now, the Business Intelligence Development Studio (BIDS) now provides the designer features once found in Enterprise Manager. Management Studio is the replacement tool for the management functions SQL Server 2000 Enterprise Manager provided.

Management Studio is based on the Visual Studio platform infrastructure, so it looks similar in many ways to Visual Studio. By using the Visual Studio platform, Management Studio is consistent with the designer in behavior and also shares some of the tools, such as the Object Explorer, Solution Explorer, Source Control, and many others. However, Management Studio is not capable of hosting other tools, such as programming languages, Integration Services, or Reporting Services, the way BIDS can.

This short introduction will be helpful in getting you up to speed on how to move around the Management Studio environment, especially for those of you who are accustomed to working in SQL Server 2000 and have been confused initially by the separation between the designer and Management Studio. Here, a few of the more commonly used features of Management Studio are pointed out just to get you started, and the rest of the chapter covers the topics in more detail.

Figure 17.1 shows a screenshot of Management Studio.

Figure 17.1. Management Studio is for managing your SQL Server environment

Object Explorer

The Object Explorer is where you connect to all the servers, including Integration Services, SQL Server, Analysis Services, and Reporting Services. This window is essentially the home base from which you start almost all operations. In the upper-left portion of the Object Explorer docked window is the Connect button, shown in Figure 17.2.

Figure 17.2. Use the Connect button to connect to servers

To create a new query, use the New Query button on the Standard toolbar, located under the main menu by default and shown in Figure 17.3.

Figure 17.3. Use the Standard toolbar to create new queries and show windows

The default location of the Standard toolbar is shown in Figure 17.1. However, because the toolbar might have been moved elsewhere or hidden, it might not be visible or in the same location on your screen. To show the Standard toolbar, right-click on the main window header right below the window title bar. The toolbar context menu shown in Figure 17.4 appears.

Figure 17.4. Use the toolbar context menu to show or hide toolbars

The Standard toolbar has a number of query buttons for Analysis Services queries to the right of the New Query button. These are useful for quickly loading a new query window. The next few buttons are useful for loading and saving files. Using those buttons, you can open some useful windows, as follows.

Registered Servers

The first button (fifth from the right) opens the Registered Servers window where you can do things like preserve connection information, determine if a registered server is running, view the properties of a server, or connect to the server in the Object Explorer.

To see the properties of a server, right-click on a server node and select Properties. The Edit Server Registration Properties dialog box opens, as shown in Figure 17.5.

Figure 17.5. View the properties of the server

The Summary Window

The second button (fourth from the right) opens the Summary window. The Summary window presents a polymorphic report on the object currently selected in the Object Explorer. Unfortunately, Integration Services does not fully support the Summary window, so very little information appears for packages. Figure 17.6 shows the Summary window with the column schema for a table in the AdventureWorks database.

Figure 17.6. The Summary window shows information about the selected object

Template Explorer

The third of the five buttons opens the Object Explorer, and the fourth button (second from the right) on the Standard toolbar opens the Template Explorer. This is a nifty tool that provides what are essentially cookbook T-SQL scripts for accomplishing a wide variety of tasks, such as creating endpoints, creating a login, or attaching a database. Figure 17.7 shows the Template Explorer with a few of the templates showing.

Figure 17.7. The Template Explorer contains T-SQL scripts for common tasks

Viewing the Windows Event Log

One more nifty feature that you should know about is the log viewer. If you right-click on the Integration Services node and select View Logs, the Log File Viewer appears with the Windows Event Log for recently executed packages. This is a helpful feature for getting an overall picture of how well your packages are executing, allowing you to easily and quickly find problem packages.

There's much more to Management Studio than can possibly be covered here. This short introduction should be enough for you to become familiar with the basics. You can find more information in Books Online and MSDN. The remainder of this chapter drills down into the Integration Servicesspecific features of Management Studio.