SQL Server Management Studio

SQL Server Management Studio (SSMS) is a new integrated application that provides access to most of the graphical tools you can use to perform administrative and development tasks on SQL Server 2005. SSMS is a replacement for the Enterprise Manager, Query Analyzer, and Analysis Manager that were available in SQL Server 2000. Microsoft has consolidated all those tools into one, with a focus on providing a tool that suits the needs of both developers and database administrators (DBAs).

SSMS is a complicated tool that provides an entry point to almost all of SQL Server’s functionality. The functionality that is accessible from SSMS is entirely too much to cover in one chapter. The aim of this chapter is to give a basic overview of SSMS, with a concentration on features that are new to SQL Server 2005. Others chapters in this book focus on the components of SSMS and provide more detailed coverage.

SSMS is an entirely new environment for SQL Server 2005. It encapsulates many of the features previously available in other tools and also offers many new features. The bulk of these new features can be grouped into four major categories: environmental changes, integrated management, enhanced query authoring, and enhanced project management. 

The environmental changes are changes that have occurred to the graphical application. SSMS has a new look and feel, and it offers some significant change to the way that windows are managed within the application. The application was rewritten in .NET and has features that are more like the development environment found in Visual Studio. Many windows in SSMS are dockable, can be pinned, and can be set to Auto Hide. In addition, many of the management dialog boxes are now modal, which means they can stay open while you open other windows within the application.

The new integrated management features stem from a consolidation of management tools. SSMS now contains management functionality that was contained in SQL Server 2000’s Enterprise Manager, Analysis Manager, SQL Server Service Manager, Query Analyzer, and other tools. The functionality from these tools has been integrated into one environment that shares common Help, a summary window that displays useful information, an Object Explorer tree for easy navigation, and a myriad of other tools that can be accessed from one central location.

The changes related to query authoring are also based on a consolidation of functionality that was contained in several different tools in previous versions of SQL Server. Scripts that were previously created with Query Analyzer or Analysis Services can now be authored in SSMS. A new SSMS window named the Query Editor is an editing tool for the creation of SQL Server scripts. It brings with it many of the great features from the prior tools, such as color coding, syntax checks, and performance analysis, along with some new features, such as Dynamic Help, an XML editor, enhanced templates, and the ability to write scripts without being connected to the database.

The last category of changes in SSMS relates to managing the files or scripts you create when working with SQL Server 2005. SSMS provides a tool to organize scripts, connections, and other, related files into projects. These projects can also be grouped to form a solution. Once again, this functionality is based on the Visual Studio application development environment and the way it organizes development files into projects and solutions. As with Visual Studio, these files can also be managed with source control in SSMS. SSMS provides links to Visual SourceSafe, which allows you to secure the files and manage version control.

This chapter further explores the new features in SSMS. It first examines the features at the environmental level, focusing on how SSMS behaves and how to best utilize the environment. Next, it looks at the administrative tools and what changes have been made to help you better manage your SQL Server environment. Finally, this chapter looks at the development tools that are available with SSMS and the changes that have been made to improve your SQL Server development experience.

Those who have been working with SQL Server for a long time may remember the SQL Enterprise Manager that came with SQL Server 6.5. In some respects, with SSMS, Microsoft has moved back to the paradigm that existed then. Like the SQL Server 6.5 Enterprise Manager, SSMS provides an integrated environment where developers and DBAs alike can perform the database tasks they need. Say goodbye to Query Analyzer, Analysis Manager, and a number of other desperate tools and say hello to SSMS, which provides “one-stop shopping” for most of your database needs.

Figure 3.1 shows a sample configuration for the SSMS main display. The environment and the windows that are displayed are completely customizable, with the exception of the document window area. Figure 3.1 shows the document window area displaying the Summary page. The Summary page is the default, but other pages, such as a Query Editor window, can take the focus in this tab-oriented section of the SSMS display.

Figure 3.1.  The SSMS main display.

The dialogs that form the rest of the SSMS display are referred to as components and include the Registered Servers and Object Explorer windows that are shown in Figure 3.1, as well as a number of other components that can be displayed via the View menu found at the top of the SSMS display. You can configure each of the component windows in a number of ways; for example, you can have them float, or you can hide, dock, Auto Hide, or display them as tabbed documents in the document window area.

The configuration that you choose for your SSMS display depends on the type of work you do with SQL Server as well as the type of person you are. The Auto Hide feature causes the component window to shrink to a tab along the left or right side of the display. When you mouse over the tab, the window automatically expands and stays expanded as long as the mouse cursor remains in the component window area. Auto Hide helps maximize the working real estate that is available in the document window for query development and the like. Docking many windows can clutter the screen, but it allows you to view many different types of information all at once. This is a matter of personal preference, and SSMS has made it very easy to change.

TIP

You can reposition the component windows by dragging and dropping them to the desired locations. When you are in the middle of a drag and drop, rectangular icons with arrows are displayed at different locations on the SSMS window surface. If you mouse over one of these arrowed icons to select the window location, you see the window destination highlighted. If you release your mouse button while the destination is highlighted, the window docks in that position.

Some users at first ignore the arrow icons and keep hovering the window over the location where you want the window to go. Hovering the window over the desired location does not allow you to effectively dock it. You should save yourself some time and aggravation and use the arrow icons for drag-and-drop positioning.

The other big changes to the SSMS window environment include non-modal windows that are sizable. The change to non-modal windows allows you to perform multiple tasks at once without needing to open another instance of the SSMS application. Enterprise Manager users of SQL Server 2000 were forced to open another instance of the application during many administrative tasks in order to continue with other work. With SSMS, you can launch a backup with the Back Up Database dialog box and then continue working with the Object Explorer or other components in SSMS while the backup is running. This is a great timesaver and helps improve overall productivity.

The ability to size the dialog boxes is another user-friendly change that may seem minor but is quite handy on certain windows. For example, the SQL Server 2000 Enterprise Manager Restore dialog had a fixed size. Viewing the backup set information in this relatively small (nonsizable) dialog box was a challenge. The Restore dialog in SQL Server 2005’s SSMS can contain a slew of information related to the backup sets available for restore. The ability to size the windows allows for much more information to be displayed.

The tabbed document window area provides some usability improvements as well. This area, as described earlier, is fixed and is always displayed in SSMS. Component windows can be displayed in this area, along with windows for the Query Editor, diagrams, and other design windows. If desired, you can change the environment from a tabbed display to multiple-document interface (MDI) mode. In this mode, each document is opened in its own window within the document window. The MDI mode manages windows like the SQL Server 2000 Query Analyzer and may be more user-friendly for some people. You can change to MDI mode by selecting Tools, Options and then selecting MDI Environment from the General page.

One particularly useful window that can be displayed in the document window is the Summary page. This new window displays information relative to the node that is selected in the Object Explorer and includes options to produce detailed reports and graphs. The Summary page is displayed in the document window by default when SSMS is launched, but you can also display it by pressing F7 or choosing Summary from the View menu.

TIP

In SQL Server 2000, you could select multiple objects for scripting by selecting the items from the Object Explorer tree in Enterprise Manager. You cannot use the Object Explorer tree to do this with SQL Server 2005, and this has generated some confusion. The solution is the Summary page, which provides a means for doing multiple selections of the objects it displays. You can hold down the Ctrl key and click only those items that you want to script. After you have selected the items you want, you simply right-click one of the selected items and choose the preferred scripting method. This also works with scheduled jobs that are displayed in the Summary page. SQL Server 2000 did not offer this capability. 

The reports that are available on the Summary page are often overlooked. Part of the reason for this may be that the reports are not available for every node in the Object Explorer tree. Top-level nodes in the tree are where most of the reports are found. For example, if you select a database in the Object Explorer tree and view the Summary page, you see a Report icon that is enabled on the toolbar at the top of the Summary page. If you click the drop-down arrow next to that icon, you find a list of reports that are available for creation. These reports include Disk Usage, Backup and Restore Events, Top Transactions by Age, and a host of others. Graphs are included with some reports, and you can export or print all these reports. Figure 3.2 shows an example of the Disk Usage report for the AdventureWorks database.

Figure 3.2.  A Disk Usage summary report.

The graphs are easy to read, and some sections of the report can be expanded to provide more detail. Bullets at the bottom of a report are nodes that can be expanded. For example, the bullets Disk Space Used by Data Files and Disk Space Used by Table at the bottom of Figure 3.2 can be expanded.

SSMS offers an expanded set of help facilities as well as improved integration into the application environment. The Help sources have been expanded to include both local and online resources. Local help is similar to the Help resources available in past versions and references files that are installed on your machine during the installation process. Local help includes the local SQL Server Books Online resources. Local help files are static and get updated only if another documentation installation is run on the local machine.

Online help is new to SQL Server 2005 and provides access to content that is not static and can be updated with the very latest changes. Three default online resources are provided by default:

• MSDN Online—MSDN Online contains the latest version of the MSDN documentation, including the latest quarterly releases.
  
• Codezone Community—Codezone Community includes a set of third-party websites that have partnered with Microsoft and provide a wealth of information from sources outside Microsoft.
  
• Questions—The Questions option allows you to search the forum archives for answers to questions that others have already asked. It also allows you to post your own questions.

The help resources you use on your machine are configurable. You can choose to search online resources first, followed by local help, or you can choose an option that searches local help resources first, followed by online resources. You can also choose specific Codezone online resources to search, or you can eliminate the search of all online resources. Figure 3.3 shows the online help Options window, which allows you to configure your Help options. You access this dialog by selecting Tools, Options.

The Help resources you select are used when you search for content within the Help facility. When you use both local and online resources options, you see results from multiple locations in your search results. Figure 3.4 shows a sample Books Online Document Explorer window with results from a search on “Management Studio.” Notice that the panel on the right side of the window lists entries under Local Help, MSDN Online, Codezone Community, and Questions. Each of these sections contains search results that you can access by simply clicking on that area. The number of search results for each section is displayed in parentheses after the section name.

Figure 3.3.  Setting Help options.

Figure 3.4.  A Books Online search.

One other significant change to the help facilities in SSMS is the addition of Dynamic Help. Dynamic Help is a carryover from the Visual Studio environment. It is a help facility that automatically displays topics in a Help window that are related to what you are doing in SSMS. For example, if you are working in a query window and type the word SELECT to start your query, the Dynamic Help window displays several topics related to the SELECT statement. If you are working in the Object Explorer, it displays Help topics related to the Object Explorer.

Dynamic Help is one of the component windows that you can dock or position on the SSMS surface. To use Dynamic Help, you select Help, Dynamic Help. Figure 3.5 shows an example of the SSMS environment with the Dynamic Help window docked on the right side of the window. The Dynamic Help topics in this example are relative to the SELECT keyword that is typed in the Query Editor window in the middle of the screen.

Figure 3.5.  Dynamic Help. 

The tools that are available with SSMS can be broadly categorized into tools that are used for administering SQL Server and tools that are used for developing or authoring new SQL Server objects. As a matter of practice, developers use some of the administrative tools, and administrators use some of the development tools.

SSMS comes with an expanded set of tools to help with SQL Server administrative tasks. It builds on the functionality that was available in the SQL Server 2000 Enterprise Manager and adds some new tools and functionality to help ease the administrative burden.

Registered servers is a new concept in SQL Server 2005 and represents a new division between managing servers and registering servers. With the SQL Server 2000 Enterprise Manager, the Microsoft Management Console (MMC) tree was displayed on the left side of the Enterprise Manager screen, and it contained servers that had been registered via that tree. Any registered servers or groups were listed in the tree, along with any of the associated objects.

With SQL Server 2005, registered servers are managed and displayed in the Registered Servers component window. The objects associated with these registered servers are displayed in the Object Explorer rather than in the Registered Servers window.

Figure 3.6 shows an example of the Registered Servers window, with several server groups and their associated registered servers. You can add new groups any time; this window offers a handy way of organizing the servers you work with.

Figure 3.6.  The Registered Servers window.

The servers listed in Figure 3.6 are all database engine servers. These server types are the conventional SQL Server instances, like those you could register in the SQL Server 2000 Enterprise Manager. You can also register several other types of servers. The icons across the top of the Registered Servers window indicate the types of servers that can be registered. In addition to database engine servers, you can also register servers for Analysis Services, Reporting Services, SQL Server Mobile, and Integration Services. The Registered Servers window gives you one consolidated location to register all the different types of servers that are available in SQL Server 2005. You simply click the icon associated with the appropriate server type, and the registered servers of that type are displayed in the Registered Servers tree.

NOTE

The SQL Server 2005 Registered Servers window enables you to register servers that are running SQL Server 2000 and SQL Server 7.0 as well. You can manage all the features of SQL Server 2000 with SQL Server 2005 tools. You can also have both sets of tools on one machine. The SQL Server 2000 and SQL Server 2005 tools are compatible and function normally together.

The SQL Server 2000 Enterprise Manager and Query Analyzer cannot be used to manage SQL Server 2005. You can connect the Query Analyzer to a SQL Server 2005 instance and run queries, but the Object Explorer and other tools are not compatible with SQL Server 2005.

When a server is registered, you have several options available for managing the server. You can right-click the server in the Registered Servers window to start or stop the related server, open a new Object Explorer window for the server, connect to a new query window, or export the registered servers to an XML file so that they can be imported on another machine.

TIP

The import/export feature can be a real timesaver, especially in environments where many SQL servers are managed. You can export all the servers and groups that are registered on one machine and save the time of registering them all on another machine. For example, you can right-click the Database Engine node, select Export, and then choose a location to store the XML output file. Then, all you need to do to register all the servers and groups on another machine is move the file to that machine and import the file.

Please check back next week for the continuation of this article.