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).
Figure 4-1. SQL Server Management Studio and some of its main components.
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.
Figure 4-2. Management Studio can be installed as part of the workstation components from the SQL Server setup program.
The option, Workstation components, Books Online and development tools in the setup program installs more than just Management Studio. If you want to install only the management tools, click on Advanced and exclude all other components but Management Tools in the subsequent screen.
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 provides the functionality to specify the necessary parameters to connect to a server (see Figure 4-3).
Figure 4-3. Connection dialog in SQL Server Management Studio.
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.
You can substitute the full server name with (local) or . (dot character) to connect to a local server. For example, in Figure 4-3 if Management Studio were running on the same computer as MICHAEL1, you could enter (local)\YUKON1 or .\YUKON1 in the Server name field of the connection dialog.
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.
Figure 4-4. Advanced connection options for SQL Server Database Engine.
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 (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).
Figure 4-5. Registered Servers provides a list of the frequently used servers.
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 serversin 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.
You can create Registered Servers for SQL Server 2000 and SQL Server 2005 instances. However, there are no iconic or graphical representational differences between a SQL Server 2000 and SQL Server 2005 registered server. If you are planning to create registered servers for both your SQL Server 2000 and SQL Server 2005 servers, it is a good idea to create your SQL Server 2000 registered servers in a separate server group named something like "SQL2000." This will help you to distinguish the SQL Server 2000 registered servers quickly.
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.
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.
Figure 4-6. The Registered Servers menu provides a variety of management options.
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.
Figure 4-7. Object Explorer and its various sub-components.
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.
Closing the Object Explorer window (clicking on the [x]) does not disconnect it from the server; it only hides it.
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.
Figure 4-8. Object Explorer provides the functionality to create DML and DLL statements for an object.
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.
Figure 4-9. Tables in 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.
Figure 4-10. Object Explorer filter settings for AdventureWorks tables.
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.
Figure 4-11. Tables in AdventureWorks database after filtering.
The Object Explorer filter settings are removed and not persisted after you disconnect Object Explorer from the server.
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.
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.
Figure 4-12. The Summary window provides an alternative method for viewing objects on the server with additional metadata.
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.
The Summary window performs better than Object Explorer for populations of nodes with many objects. The difference becomes more visible when populating nodes with 1,000 or more objects. It is recommended that you use the Summary window for navigation if the population of Object Explorer becomes a bottleneck.
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.
Figure 4-13. A typical management dialog in SQL Server Management Studio.
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:
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.
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.
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.
Most management dialogs provide several scripting options that can be accessed from the management dialog toolbar (see Figure 4-14).
Figure 4-14. The management dialog toolbar provides various scripting options
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.
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.
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.
Figure 4-15. The management dialog toolbar provides various scripting options.
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.
Figure 4-16. Management dialogs leverage SQL Server object models to perform actions on Database Engine, Analysis Services, Replication, SQL Server Agent, and Notification Services.
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.
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.
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.
Figure 4-17. Management Studio Query Editor and its components.
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.
Figure 4-18. The Dynamic Help window automatically displays links to help topics relevant to the statements in the Query Editor window.
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.
Figure 4-19. The Integrated Help Viewer option will set Help and SQL Server Books Online as document windows inside Management Studio.
You may need to restart Management Studio for the new setting to take effect.
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.
Figure 4-20. Solution Explorer in Management Studio.
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.
Figure 4-21. The Properties window supports changing properties of objects that are located in editors and designers.
Each field in the Properties window displays different types of editing fields, depending on the particular property. Properties shown in grey are read-only.
Do not confuse the Properties window with Properties dialogs. Properties dialogs are rich management dialogs that provide the functionality to modify properties of an object on the server. To launch Properties dialogs, select Properties from the Object Explorer context menu.
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.
Figure 4-22. Toolbox is populated with Maintenance Plan tasks only when Maintenance Plan designer is in focus.
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.
Figure 4-23. The embedded browser in Management Studio provides web browsing functionality without the need to leave the environment.