Inside SQL Server 2005 Management and Administration Tools

This chapter is from the book 

The Microsoft SQL Server Team had two main goals for the design of the new SQL Server 2005 management tools. The first goal was to provide a consistent management experience for all components of SQL Server. The second goal was to provide an innovative tool to make database professionals more productive at querying, managing, and administrating SQL Server. To achieve these goals, the team had to choose an environment that was easily extensible, user-friendly, and familiar to the database professionals. While we were studying different hosting environments, or as we called them "shells," we soon concluded that the Microsoft Management Console (MMC) shell was not suitable to host the new tools. The MMC model was old, monolithic, and it did not provide the extensibility needed. For example, the MMC model stipulated that the management dialogs had to be modal with a fixed size optimized for a desktop size of 640 x 480 pixels. However, as the high-resolution computer monitors became more popular and the average size of the desktop increased to 1024 x 768, the legacy Enterprise Manager users hated this restriction. They consistently asked us why they could not resize the management dialogs to take advantage of the full size of their screens. However, deviating from the MMC model was not that easy. A new shell would definitely solve some of the issues, but it would also break a long-term trend. For two generations of SQL Server, database professionals had used the Enterprise Manager or Analysis Manager, both of which were MMC snap-ins. The innovative new shell had to eliminate some of the legacy restrictions but also had to be familiar to users.

One of the environments that looked very attractive was the Integrated Development Environment (IDE) upon which Visual Studio was built. The IDE had provided extensibility and a proven, user-friendly environment for developers for several years. It also offered several engineering advantages, such as a ready-to-use editor, a debugger, and a powerful help model, and it could host some of the components that were built by the Visual Studio team. After studying several prototypes, the team decided to use the IDE for the new shell. The tool was called SQL Server Workbench and it was distributed to a number of early adopter customers and SQL Server Most Valued Professionals (MVPs). By the Beta 1 release, SQL Server Workbench created a lot of controversy in the community. The feedback was very bipolar; customers either loved or hated it. While the developers found the environment extremely friendly, the DBAs found the user interface confusing. Some DBAs thought the shift to a development-like environment unnecessary, and it required a steep learning curve. The engineering team faced a tough task addressing these issues. The decision was to cut down some of the features and use the resources to address these important usability issues. The team carefully collected all the feedback and redesigned the environment to bridge the gap between the legacy tools and the new innovative tool. We also made the environment simpler and friendlier for the DBAs. By the Beta 2 release, the tool was called SQL Server Management Studio, and it received positive feedback from both developers and DBAs. The results were successful, but it came at the price of cutting some features such as the T-SQL debugger and Object Search functionality.

With almost six months since the release of SQL Server 2005 at the time of this writing, I (Michael) can see that the team made the right decision to achieve its goals. SQL Server Management Studio provides the ultimate integrated management and authoring experience for all components of SQL Server and it is a productive and user-friendly environment. You may not find all the functionality of Enterprise Manager or Query Analyzer in the new tool, but it certainly increases your productivity. As for the missing features, you can find them in other tools—as with the T-SQL debugger now found in Visual Studio—or look for them in the future releases of SQL Server. Remember, the SQL Server team is always looking forward to your feedback: http://connect.microsoft.com.

SQL Server Management Studio

Management Studio is a comprehensive integrated environment to manage, administer, and author queries for all components of SQL Server, namely Database Engine (including Replication, SQL Server Agent, and Full-Text Search), Analysis Services, Reporting Services, Integration Services, Notification Services, and SQL Server Mobile. Management Studio is a collection of rich tools in the form of dialogs, wizards, tool windows, designers, and editors (see Figure 4-1).

The Management Studio environment or "shell" leverages the same technologies as the Visual Studio Integrated Development Environment (IDE). However, Management Studio and Visual Studio are two completely different products. Management Studio is specifically designed for management and authoring of SQL Server products, whereas Visual Studio is an integrated development environment for all types of development such as Windows, Office, web, and mobile applications.

One of the main productivity gains in Management Studio is the integration of authoring and management in a single environment. For those of you who are familiar with SQL Server 2000/7.0 tools, Management Studio combines the functionality of Enterprise Manager, Query Analyzer, MDX Sample Application, and Analysis Manager in a single environment. In addition to SQL Server 2005, SQL Server Management Studio can manage SQL Server 2000 Database Engine.

Installing and Launching SQL Server Management Studio

SQL Server Management Studio can connect locally or remotely to a server. To install Management Studio, run the SQL Server Setup program and proceed with the setup screens until you see the Components to Install screen. On this screen, select Workstation components, Books Online and development tools (see Figure 4-2), and continue with the rest of the setup screens.

To launch Management Studio from Windows Start menu, point to All Programs/Microsoft SQL Server 2005, and select SQL Server Management Studio.

Management Studio Components

Management Studio hosts a number of components for the authoring, administration, and management of SQL Server products. The following sections present a description of the main components in Management Studio along with their functionality.

Connection Dialog

Connection dialog provides the functionality to specify the necessary parameters to connect to a server (see Figure 4-3).

Using the fields Server type and Server name, you can connect Management Studio to any of the SQL Server products on a given server. These products could be Database Engine, Analysis Services, Reporting Services, Integration Services, or SQL Server Mobile. As described in Chapter 2, you can install named instances of Database Engine, Analysis Services, and Reporting Services. To connect to a default instance of any server type, you can enter the name of the server in Server name. To connect to a named instance, enter the name of the server followed by the \ character and then the name of the instance. For example, in Figure 4-3 the user has entered MICHAEL1\YUKON1 in the Server name field to connect to a database engine instance named YUKON1 on server MICHAEL1.

On the connection dialog, you can optionally click on the Options button to access the advanced connection options (see Figure 4-4). You can find a description of these options in SQL Server Books Online. For most scenarios, you do not need to change any default values.

Upon clicking on Connect, the Connection dialog tries to establish a connection with the specified server. While the connection is being established, the narrow orange band on top of the dialog moves from left to right and you can click on Cancel to cancel the connection. During the connection cancellation, the narrow orange band changes its movement to the opposite direction, moving from right to left.

Registered Servers

Registered Servers (see Figure 4-5) is a Management Studio tool window that provides the functionality to keep links to your frequently used servers. You can use these links to quickly view the status of the server, connect to the server to manage its objects, or to execute a query. Each user has a separate list of Registered Servers stored in a file on the local computer (the same computer that is running Management Studio).

You can organize the Registered Servers in server groups (similar to the files and folders) to match the logical or physical grouping of the servers in your organization. For example, in Figure 4-5 the registered servers are grouped by physical location of the servers—in other words, by continent, country, region, state, and so on. You can create registered servers for all server types. The toolbar on top of the Registered Servers window allows quick switching between different server types: Database Engine, Analysis Server, Reporting Services, SQL Server Mobile, and Integration Services. Alternatively, you can use View, Registered Server Types from the main menu in SQL Server Management Studio to switch between server types.

By default, the Registered Server window appears on the top-left corner of Management Studio. You can easily move, hide, or autohide the window to allocate more space to other components. To quickly view the window, choose Ctrl+Alt+G or View, Registered Servers from the main menu.

The Registered Servers window enables you to view and change the state of a server from the registered server. To view the status of a server, look at the icon to the left of to the registered server and match it to Table 4-1.

Table 4-1. Status of Servers in the Registered Servers Window

Icon	Status	Description
	Started	Service is up and running.
	Stopped	Service is stopped.
	Paused	Service is paused.
	Unknown	The state of the service is unknown. Registered Server cannot access the service possibly because of network or WMI security issues—see "Troubleshooting Registered Servers to Show Server Status" in this chapter.

The context menu on a registered server (see Figure 4-6) provides the capability to change the state of a service, along with a variety of management options that are covered later in this chapter.

Object Explorer

Object Explorer (see Figure 4-7) is one of the main components of SQL Server Management Studio. It provides a hierarchical tree view of the objects on a server. The tree displays nodes in folders representing the server's logical structure. Object Explorer is the manifest for performing management operations on the server objects for Database Engine (including SQL Server Agent, Replication, and Full-Text Search), Analysis Services and Data Mining, Reporting Services, Integration Services, and Notification Services. Each folder or a node in Object Explorer has a context menu with a variety of options to perform an action on the server. For those of you who are familiar with SQL Server 2000 tools, Object Explorer is the replacement for the left tree in Enterprise Manager and Analysis Services, and Object Browser in Query Analyzer.

By default, the Object Explorer window appears on the left side of Management Studio, docked under the Registered Server window. You can move, hide, or autohide the window to allocate more space to other components. There are several ways to quickly show the Object Explorer window. You can press F8 on the keyboard, select View, Object Explorer from the main menu, or click on the Object Explorer button on the main toolbar in Management Studio.

To connect Object Explorer to a server, you can right-click on a registered server and choose the option Connect, Object Explorer. You can also click on Connect in the toolbar of Object Explorer and select one of the server types. Alternatively, you can choose File, Connect Object Explorer from the Management Studio main menu. All these connection options, with the exception of the registered server, pop up the connection dialog, where you can enter the server name and other connection options. After you are connected, you can expand and collapse the nodes to navigate through the objects and use the context-menu to perform operations on the objects. To disconnect, click on Disconnect in the toolbar or right-click on the server node and choose the option Disconnect. The context menu of the server node has an option to register the server. This is particularly useful for situations in which you have connected to a server and you want to quickly add it to your registered servers for subsequent connections.

You can use Object Explorer to connect to multiple servers in the same window. The servers could be of the same type or different types, installed locally or remotely. This is a great productivity feature because you can manage all servers in your organization without launching a separate management tool or opening multiple instances of the same tool for each of the servers. In addition, Object Explorer supports connections to the SQL Server 2000 database engine. This is a useful option during upgrade and migration processes, where you can use Object Explorer to manage both SQL Server 2000 and SQL Server 2005 environments and remove the old tools altogether.

In addition to managing objects, Object Explorer can assist you in programming and writing queries. As shown in Figure 4-8, you can right-click on an object (such as a table) and choose the option to create a DML (INSERT, UPDATE, DELETE), or a DDL (CREATE, ALTER, DROP) statement. You can optionally send the generated script to a new query window, a file, or the clipboard to subsequently paste into other editors. In addition, you can drag an object from Object Explorer to a query window rather than typing it. This is extremely useful in a situation in which you have objects with long names or names that are too difficult to spell.

Object Explorer has several productivity features such as filtering. If you plan to work with a large number of objects, consider creating a filter. Filters enable you to view and work with only a subset of objects at a time. The filtering is applied just like a WHERE clause; it restricts the object list to only those objects that meet the filtering criteria. Let's go through a filtering example and see how it works:

Figure 4-9 shows a snapshot of tables in the AdventureWorks database prior to filtering.

Imagine that the objective is to create a filter to display only tables whose names contains the word "Transaction" and that belong to the "Production" schema. To create the filter, right-click on the Tables folder in Object Explorer and select Filter, Filter Settings in the context menu.

In the Object Explorer Filter Settings dialog, enter the filtering criteria to match the objective. For the Name property, pick the operator Contains and enter Transaction for its value. For Schema, pick the operator Equals and enter Production for the value. The finished filter settings are shown in Figure 4-10.

After you click OK, the Object Explorer displays only two tables that matched the filtering criteria. Notice the word "filtered" is now appended to Tables, in parentheses, indicating that filtering is in effect (see Figure 4-11). To remove the filtering, you can right-click and select the option Filter, Remove Filter.

Under the hood, Object Explorer uses a component called Enumerator to query the meta-data of objects on a server and displays them in a tree control. As the user clicks on each node of the server, Object Explorer sends a query that looks very much like an XML Path Language (XPath) query to the enumerator. Enumerator interprets the XPath-like query into a metadata query, which is understood by the particular server type. The enumerator fetches the query result from the server and passes it to Object Explorer in XML format. Object Explorer transforms the XML into the graphic format and appends it to the appropriate node in the tree.

Summary Window

The Summary window (see Figure 4-12) works in conjunction with Object Explorer to display additional information about the selected object in Object Explorer. In addition, the Summary window provides an alternative method to Object Explorer for navigating through the objects in a server. You can find the title and relative location of the current object in the area just under the toolbar. The grid displays the down-level objects relative to the current object. You can double-click on the objects in the grid to further navigate their down-level objects. Double-clicking an object with no down-level objects opens a dialog where you can view/change properties of the object. To view the up-level node, click on the Up button in the Summary Window toolbar. Navigating through Object Explorer automatically changes the current node in the Summary window, but not vice versa. To change the current node in Object Explorer to the same node in the Summary window, you need to click on the Synchronize button on in the Summary window toolbar. Just as you can with Object Explorer, you can use filtering by clicking on the Filter button in the Summary window toolbar.

The Summary window provides two views, List and Details, that can be selected from the List button on the toolbar. In Details mode, the Summary window grid displays additional metadata information for the down-level objects. You can sort the metadata information in the grid in ascending or descending order by clicking on the column headers. This metadata information is useful for the times when you want to find the creation date of an object quickly or you need to find objects with the same name in different schemas.

Management Dialogs

Management Dialogs are components of SQL Server Management Studio that provide the functionality to manage objects or perform actions on a server through a graphical user interface. There are over 200 management dialogs in Management Studio, which support all SQL Server 2005 products. Some of these dialogs are very simple and some are complex. However, they all follow a consistent layout and flow to ensure a low learning curve for the user. Figure 4-13 displays a typical management dialog and its various components.

You launch most of the management dialogs by selecting an entry on the Object Explorer context menu. The Object Explorer context menu is carefully designed to provide a link to dialogs and actions that are relevant to the currently selected node. For example, from the Databases folder in Object Explorer you can launch the following management dialogs that are relevant to databases:

• New Database—Allows creation of a new database.
• Attach—Creates a database from files that were previously used to store a database.
• Restore Database—Restores a database from a full or transaction log backup.
• Restore File and Filegroups—Restores specific files or file groups of a database.

The concept of management dialogs is not new in SQL Server 2005, and they have been around since SQL Server 6.0. However, a few innovations in SQL Server 2005 management dialogs could significantly improve your productivity. These innovations provide a new generation of dialogs that are resizable, non-modal, scriptable, and schedulable.

Resizability

One of the strong bits of feedback from SQL Server 2000 customers was related to the shortcomings of management dialogs in Enterprise Manager. The dialogs were too small and not resizable. New in SQL Server 2005, almost all management dialogs are resizable. You can discover the resizability of a dialog from the size-grip displayed on the bottom-right corner of the dialog.

Non-Modality

Another problem with SQL Server 2000 Enterprise Manager dialogs was their modality: Only one dialog could be opened at any given time. This was extremely frustrating in situations where the user was in the middle of creating an object and wanted to look up properties of another object. To do this, the user had to cancel the first dialog, which meant losing all the data already entered. Next, the user had to open up the second dialog, look up the information, close the second dialog, and open the first dialog again and re-enter the information. This was a big hindrance to productivity. New in SQL Server 2005, multiple instances of management dialogs can be opened without any stipulation to close the previous ones.

Scriptability

Most management dialogs provide several scripting options that can be accessed from the management dialog toolbar (see Figure 4-14).

The scripting options provide a means of generating a script for the action that the dialog is about to perform on the server. The language of the generated script depends on the type of server to which the management dialog is connected. Table 4-2 describes the script type for each of the server types.

Table 4-2. Server Types and Script Languages Generated by the Management Dialogs

Server Type	Script Language and Execution Environment
Database Engine (including SQL Server Agent and Full-Text Search)	Transact-SQL; the generated T-SQL script can be executed in Management Studio or other command line T-SQL execution tools such as SQLCMD, OSQL, etc.
Analysis Services	XMLA; the generated XMLA script can be executed in Management Studio.
Reporting Services	VBScript; the generated script cannot be executed in Management Studio, but you can use any script runtime such as CScript or Visual Studio to run and debug the script.
Integration Services	None; Integration Services dialogs are not scriptable.
Notification Services	None; Integration Services dialogs are not scriptable.
SQL Server Mobile	None; SQL Server Mobile dialogs are not scriptable.

The scripting options are useful for situations in which you want to review and perhaps tweak the script that the management dialog is about to execute on the server prior to its execution. Another usage scenario would be to use the scripting option of the dialogs as a template generator for scripts with long and difficult-to-remember syntax, such as the Analysis Services XMLA.

Schedulable

The action of the management dialogs can be scripted as SQL Server Agent jobs to run at a later time or on a recurring schedule. This is particularly useful, for example, in situations in which you want to create a recurring job to back up a database or reorganize an index on the production server at a later time, when the server is not so busy. To create a job for the dialog action, click on the Script button on the dialog toolbar and select Script Action to Job. On the New Job dialog (See Figure 4-15), switch to the Schedules page and select or create a new schedule for the job.

Architecturally, all management dialogs are inherited from a base class. This ensures consistency in the layout, formatting, and behavior of the dialogs. In addition, almost all management dialogs use an object model to create/manipulate and perform actions on the server (see Figure 4-16). As a matter of fact, the Microsoft SQL Server team enforced a development policy that all management dialogs must use the object models rather than make a direct connection and embed metadata queries in the dialogs for creation of or performing actions on the server. Enforcing this level of abstraction has been a huge win for SQL Server because it ensured quality in the management tools and reinforced the versatility and flexibility of SQL Server management object models.

Wizards

A wizard is a graphical user interface element similar to a management dialog that guides you step by step through a set of complex tasks. Management Studio has a number of wizards to assist the user with management tasks. Similar to the management dialogs, all wizards are inherited from the same class to provide a consistent user experience. Table 4-3 provides a list of the SQL Server Database Engine wizards and a brief explanation of their functionality.

Table 4-3. SQL Server Database Engine Wizards.

Wizard Name	Functionality
Generate SQL Server Scripts Wizard	Creates T-SQL scripts for all or a subset of objects in a database.
Import and Export Data Wizard	Allows copying, transformation, and loading of data between supported data sources and the Database Engine.
Copy Database Wizard	Provides the functionality to move or copy one or more databases from a source to a target instance.
Maintenance Plan Wizard	Creates scheduled jobs to perform routine database tasks such as back up, update statistics, check integrity, and so on.
Database Mail Wizard	Allows management and configuration of Database Mail.
Configure Distribution Wizard	Configures a server to be a replication distributor.
New Publication Wizard	Provides the functionality to choose objects to replicate.
New Subscription Wizard	Create one or more subscriptions to a publication for replication purposes.

Query Editors

SQL Server Management Studio hosts a number of rich code editors that provide the functionality to create, edit, and execute script-language queries against SQL Server Database Engine, Analysis Services (including Data Mining), and SQL Server Mobile. Table 4-4 shows which editors work with which server type and through which script language. In addition, Management Studio hosts an XML editor for editing XML files and viewing XML data in query resultsets.

Table 4-4. Query Editors in Management Studio by Server Type and Script Language

Server Type	Script Language	Query Editor in Management Studio
Database Engine	Transact-SQL (T-SQL)	T-SQL Query Editor
Analysis Services	Multidimensional Expression (MDX)	MDX Query Editor
	Data Mining Extensions (DMX)	DMX Query Editor
	XML for Analysis (XMLA)	XMLA Query Editor
SQL Server Mobile	Transact-SQL (T-SQL)	.SQL Server Mobile Query Editor

Figure 4-17 illustrates various components of a query window. The query pane is where you type a query. The database drop-down in the toolbar provides the functionality to specify the database where you want to execute the query. To execute the query, you can click on the Execute button on the query tool bar, select Query, Execute from Management Studio main menu, or press F5, Ctrl+E, or Alt+X on the keyboard. You also have the option to execute part of the query by selecting the script and clicking on the Execute button (or pressing any of the aforementioned keyboard shortcuts). The result of the query execution appears in the Results pane. The Results pane has tabs for displaying various components of a query result: Data appears in the Results tab, messages are displayed in Messages tab, query execution plan is displayed in Execution plan tab, and client statistics appear in the Client Statistics tab. The data for query results can be displayed in a grid or as text, or it can be redirected to a file. These options can be selected from Query, Result to in the main menu of Management Studio, or you can press Ctrl+T, Ctrl+D, and Ctrl+Shift+F for Results to Grid, Results to Text, and Results to File. You can also access these options from buttons on the query tool bar. You can hide and show the Results pane by selecting Window, Hide Result Pane from Management Studio main menu or by pressing Ctrl+R on the keyboard to give you more screen space for editing the query.

As shown in Figure 4-17, the query window has a status bar on the bottom of the window that displays useful information about the status of the last executed query.

Help and Dynamic Help Window

The Dynamic Help window in Management Studio displays links to relevant help topics while you are performing a task. This is particularly useful in query editors, where the dynamic help window displays help links as you are typing a query. You can click on a help link to launch Books Online in the context of the help topics as shown in Figure 4-18. This is particularly useful in situations where you want to look up the syntax in Books Online quickly. Alternatively, you can highlight a statement and click F1 to launch help in the context of the statement.

To open the Dynamic Help window, select Help, Dynamic Help from the main menu in Management Studio.

To launch Help from Management Studio, press F1 at any time. By default, Help opens as an external window to the Management Studio shell. However, the help windows are still associated with Management Studio, so when you close Management Studio, the help windows close as well. You can optionally open Help as a document window inside Management Studio.

You can configure Help and SQL Server Books Online to launch as document windows inside Management Studio window by following these steps:

1. On the Tools menu in Management Studio, select Options.
2. In the Options dialog, expand Environment, expand Help, and click on General.
3. In the left pane, click the Show Help Using drop-down arrow, select Integrated Help Viewer (see Figure 4-19), and click OK.

You may need to restart Management Studio for the new setting to take effect.

Solution Explorer

Solution Explorer is a component of Microsoft SQL Server Management Studio that enables you to view and manage project items and perform item management tasks in a solution or a project (see Figure 4-20). It also enables you to use the SQL Server Management Studio editors to work on items associated with one of the script projects. Solution Explorer provides an interface with version control software and the functionality to check in and check out scripts.

To view Solution Explorer, you can press Ctrl+Alt+L on the keyboard, or select View, Solution Explorer from the main menu in Management Studio.

Properties Window

The Properties window (see Figure 4-21) provides the functionality to view and change properties of a selected object that are located in editors and designers. By default, the Properties Window is located in the bottom-right corner of Management Studio. By default, the Registered Server window appears in the top-left corner of Management Studio. You can easily move, hide, or autohide the window to allocate more space to other components. To quickly view the Properties window, click F4 or choose View, Properties Window from the main menu in Management Studio.

Properties window

Each field in the Properties window displays different types of editing fields, depending on the particular property. Properties shown in grey are read-only.

Toolbox

Toolbox displays objects that can be dragged and dropped into designers and editors. In Management Studio, Toolbox is empty, and it is populated only when the Maintenance Plan Designer is in focus. In Figure 4-22, Toolbox displays a number of Maintenance Plan tasks that can be dragged and dropped into the Maintenance Plan Designer.

Web Browser

SQL Server Management Studio has an embedded Internet Explorer, which enables you to browse web pages in a document window (see Figure 4-23). This is particularly useful because you can search external resources such as the Microsoft Developer Network (www.msdn.com) or other community web pages without leaving SQL Server Management Studio. You can open the Web Browser window from the main menu in Management Studio. In the main menu, point to View, Web Browser and then select Home or Search. Alternatively, you can open the browser window by selecting Community, Developer Center in the main menu.