SQL Server 2012 provides various tools that are used for different purposes, such as system installation, configuration, auditing, and performance tuning. (All these tools will be discussed in different chapters of this tutorial.) The administrator’s primary tool for interacting with the system is SQL Server Management Studio. Both administrators and end users can use this tool to administer multiple servers, develop databases, and replicate data, among other things.
To open SQL Server Management Studio, choose Start ➜ All Programs ➜ Microsoft SQL Server 2012 ➜ SQL Server Management Studio.
SQL Server Management Studio comprises several different components that are used for the authoring, administration, and management of the overall system. The following are the main components used for these tasks:
- Registered Servers
- Object Explorer
- Query Editor
- Solution Explorer
The first two components in the list are discussed in this section. Query Editor, and Solution Explorer are explained later in this chapter, in the section “Authoring Activities Using SQL Server Management Studio.”
To get to the main SQL Server Management Studio interface, you first must connect to a server, as described next.
Connecting to a Server
When you open SQL Server Management Studio, it displays the Connect to Server dialog box (see Figure 3-1), which allows you to specify the necessary parameters to connect to a server:
- Server Type – For purposes of this chapter, choose Database Engine.
- Server Name – Select or type the name of the server that you want to use. (Generally, you can connect SQL Server Management Studio to any of the installed products on a particular server.)
- Authentication – Choose between the two authentication types:
- Windows Authentication – Connect to SQL Server using your Windows account. This option is much simpler and is recommended by Microsoft.
- SQL Server Authentication – The Database Engine uses its own authentication.
When you click Connect, the Database Engine connects to the specified server. After connecting to the database server, the default SQL Server Management Studio window appears. The default appearance is similar to Visual Studio, so users can leverage their experience of developing in Visual Studio to use SQL Server Management Studio more easily. Figure 3-2 shows the SQL Server Management Window with several panes.
Registered Servers
Registered Servers is represented as a pane that allows you to maintain connections to already used servers (see Figure 3-2). (If the Registered Servers pane isn’t visible, select its name from the View menu.) You can use these connections to check a server’s status or to manage its objects. Each user has a separate list of registered servers, which is stored locally.
You can add new servers to the list of all servers, or remove one or more existing servers from the list. You also can group existing servers into server groups. Each group should contain the servers that belong together logically. You can also group servers by server type, such as Database Engine, Analysis Services, Reporting Services, and Integration Services.
Object Explorer
The Object Explorer pane contains a tree view of all the database objects in a server. (If the Object Explorer pane isn’t visible, select View ➜ Object Explorer.) The tree view shows you a hierarchy of the objects on a server. Hence, if you expand a tree, the logical structure of a corresponding server will be shown.
Object Explorer allows you to connect to multiple servers in the same pane. The server can be any of the existing servers for Database Engine, Analysis Services, Reporting Services, or Integration Services. This feature is user-friendly, because it allows you to manage all servers of the same or different types from one place.
Organizing and Navigating SQL Server Management Studio’s Panes
You can dock or hide each of the panes of SQL Server Management Studio. By rightclicking the title bar at the top of the corresponding pane, you can choose between the following presentation possibilities:
- Floating – The pane becomes a separate floating pane on top of the rest of SQL Server Management Studio panes. Such a pane can be moved anywhere around the screen.
- Dockable – Enables you to move and dock the pane in different positions. To move the pane to a different docking position, click and drag its title bar and drop it in the new position.
- Tabbed Document – You can create a tabbed grouping using the Designer window. When this is done, the pane’s state changes from dockable to tabbed document.
- Hide – Closes the pane. (Alternatively, you can click the × in the upper-right corner of the pane.) To display a closed pane, select its name from the View menu.
- Auto Hide – Minimizes the pane and stores it on the left side of the screen. To reopen (maximize) such a pane, move your mouse over the tabs on the left side of the screen and click the push pin to pin the pane in the open position.
To restore the default configuration, choose Window ➜ Reset Window Layout. The Object Explorer pane appears on the left, while the Object Explorer Details tab appears on the right side of SQL Server Management Studio. (The Object Explorer Details tab displays information about the currently selected node of Object Explorer.)
Within the Object Explorer and Registered Servers panes, a subobject appears only if you click the plus (+) sign of its direct predecessor in the tree hierarchy. To see the properties of an object, right-click the object and choose Properties. A minus (–) sign to the left of an object’s name indicates that the object is currently expanded. To compress all subobjects of an object, click its minus sign. (Another possibility would be to doubleclick the folder, or press the left arrow key while the folder is selected.)
