Servers and databases are the primary resources you manage in SQL Server Management Studio. When you select a top-level group in Registered Servers view, you can see the available server groups. If you expand the view of these groups by double-clicking the group name, you can see the subgroups or servers assigned to a particular group. Local servers are registered automatically (in most cases). If the remote server you want to manage is not shown, you will need to register it. If a local server is not shown, you will need to update the local registration information. Once you do that, you can connect to the server to work with it and then disconnect when you are finished simply by double-clicking the server entry in Registered Servers view. If you are not automatically connected, you can force a connection by right-clicking the server entry, pointing to Connect, and then selecting New Query (if you want to make a SQL query) or Object Explorer (if you want to view and manage the server).
You can start the registration process by using either of the following techniques:
Register a server to which you are connected in Object Explorer.
Register a new server in Registered Servers view.
You can manage previous registrations in a variety of ways. You can:
Import registration information on previously registered SQL Server 2000 servers.
Update registration information for local servers.
Copy registration information from one computer to another using import and export.
Any server to which you have connected in the Object Explorer can be registered easily. Registration saves the current connection information and assigns the server to a group for easy future access using Registered Servers view. To register a connected server, follow these steps:
Right-click any server to which you are currently connected in Object Explorer view, and then choose Register to display the Register Server dialog box shown in Figure 5-5.
The server name defaults to the current name. You can modify this name and add a description if desired.
Under Server Group, you will see the top-level server group and any second-level server groups you created. You can now:
Add the server to one of the top-level or second-level groups by clicking the group name.
Add the server to a lower-level group by expanding the server group entries until the group you want to use is displayed. Select this group.
Add the server to a new group by clicking New Group. Create the new group using the New Server Group dialog box.
Click Save.
You do not have to connect to a server in Object Explorer to register it. You can register new servers directly in Registered Servers view by following these steps:
In Registered Servers view, use the toolbar to select the type of server to which you want to connect, such as Database Engine.
Expand the available groups as necessary, and then right-click the group into which you want to place the server in Registered Servers view.
Point to New, and then select Server Registration to display the New Server Registration dialog box shown in Figure 5-6.
In the Server Name box, type the fully qualified domain name or host name of the server on which SQL Server is running, such as corpsvr04.cpandl.com or CorpSvr04.
Use the Authentication selection menu to choose the authentication type, either Windows Authentication or SQL Server Authentication (based on the authentication types selected when you installed the server). Provide a Windows user name or SQL Server login ID and password as necessary.
Windows Authentication. Uses your current domain account and password to establish the database connection. This authentication type works only if Windows authentication is enabled and you have appropriate privileges.
SQL Server Authentication. Allows you to specify a SQL Server login ID and password. To save the password so that you do not have to re-enter it each time you connect, select Remember Password.
You can also set connection settings using the options on the Connection Properties tab. These options allow you to connect to a specific database instance and to set the network configuration.
The registered server name is filled in for you based on the previously entered server name. Change the default name only if you want SQL Server Management Studio to use an alternate display name for the server.
To test the settings, click Test. If you successfully connect to the server, you will see a prompt confirming this. If the test fails, verify the information you provided, make changes as necessary, and then test the settings again.
Click Save.
Registration details for servers registered by SQL Server 2000 can be imported into SQL Server Management Studio. This makes it easier to work with existing SQL Server 2000 installations. If the SQL Server 2000 installations were previously registered on the computer, you can import the registration details into a specific server group by completing the following steps:
In Registered Servers view, use the toolbar to select the type of servers you are registering, such as Database Engine.
Expand the available groups as necessary, and then right-click the group into which you want to place the SQL Server 2000 servers. Select Previously Registered Servers.
Available registration information for SQL Server 2000 servers will be imported. If an error prompt is displayed, you might not be logged on locally to the computer on which the servers were registered previously.
Local servers are registered automatically (in most cases). If you have added or removed SQL Server instances to the local computer and those instances are not displayed, you will need to update the local server registration. Updating the registration information ensures that all currently configured local server instances are shown in SQL Server Management Studio.
To update registration details for local servers, follow these steps:
Once you have registered servers in SQL Server Management Studio and placed the servers into a specific group hierarchy, you may find that you want to use the same registration information and server group structure on another computer. SQL Server Management Studio allows you to copy registration information from one computer to another using an import/export process. You can copy the registration details with or without the user names and passwords.
To export the registration and group information to a file on one computer and then import it onto another computer, complete the following steps:
Start SQL Server Management Studio on the computer with the registration and group structure details that you want to copy.
Select Registered Servers view by pressing Ctrl+Alt+G.
In Registered Servers view, use the toolbar to select the type of servers you want to work with, such as Database Engine.
Right-click the top-level group, and then select Export to display the Export Registered Servers dialog box shown in Figure 5-7.
Under Server Group, select the point from which the export process will begin. You can start copying registration information at any level in the group structure:
To copy the structure for a top-level group, all its subgroups, and all registration details for all related servers, select the top-level group.
To copy the structure for a subgroup, its subgroups (if any), and all registration details for all related servers, select a subgroup.
To copy the registration details for a single server, select the server.
To copy the structure for a subgroup and all registered servers, right-click the subgroup, and then select Export.
The server group structure and registration details are exported to a Registration Server File with the .regsrvr extension. By default, this file is created in your My Documents folder. Under Export Options, type a name for the Registration Server File, such as CurrentDBConfig.
By default, the current authentication details for server connections are not exported into the save file. If you want to export user names and passwords, clear the Do Not Include User Names And Passwords In The Export File check box.
Click OK. If the export was successful, you will see a dialog box confirming this. Click OK in the dialog box. If there was a problem, note and correct the problem.
Start SQL Server Management Studio on the computer to which you want to copy the server group and registration details. If you did not place the registration server file on a secure network share, you will need to copy the file to this computer now.
Select Registered Servers view by pressing Ctrl+Alt+G.
In Registered Servers view, use the toolbar to select the type of servers you want to work with, such as Database Engine.
Right-click the top-level group, and then select Import to display the Import Registered Servers dialog box shown in Figure 5-8.
Click the button to the right of the Import File text box in the dialog box, and then use the Open dialog box that displays to select the Registration Server File you want to import.
Under Server Group, select the server group under which you want the imported groups and servers to be created.
Click OK. If the import was successful, you will see a dialog box confirming this. Click OK in the dialog box. If there was a problem, note and correct the problem.
You can change a server’s registration properties at any time by right-clicking the server entry in Registered Servers view in SQL Server Management Studio, pointing to Edit, and then selecting SQL Server Registration Properties. Use the Edit Server Registration Properties dialog box to make necessary changes. The only property you cannot change is the server type. Be sure to test the settings before saving them.
Once you have registered a server, connecting to it is easy. Right-click the server entry in the Registered Servers view in SQL Server Management Studio, point to Connect, and then select New Query (if you want to make a SQL query) or Object Explorer (if you want to view and manage the server). Or you can double-click the server entry to establish a connection and display the server’s Properties dialog box.
Note
SQL Server Management Studio connects to other SQL servers using the network protocol set in the registration properties. If you have disabled the network protocol or remote access entirely, however, you will not be able to connect to that server in SQL Server Management Studio. You will need to make the appropriate changes in the registration properties or in the surface area configuration. Chapter 3 discusses surface area configuration.
When you are finished working with a server, you may want to disconnect from it. This cuts down on the back-and-forth communications to the server. To disconnect, right-click the server’s entry in Object Explorer view in SQL Server Management Studio, and then select Disconnect from the shortcut menu.
To move the server to a new group, complete the following steps:
Right-click the server in Registered Servers view and select Move To from the shortcut menu to display the Move Server Registration dialog box.
In the Move Server Registration dialog box, expand the top-level group to see a list of subgroups. Expand subgroups as necessary. You can now:
Move the server to the top-level group by selecting the top-level group. This will make the server a member of the top-level group.
Move the server to a different level by selecting a subgroup into which you want to place the server.
Click OK.
If you change a server name or remove a server, you may want to delete the server registration in SQL Server Management Studio so that SQL Server Management Studio no longer tries to connect to a server that cannot be accessed. Right-click the server entry in the Console Root pane, and then select Delete. When prompted to confirm the action, click Yes to delete the server registration details.