Managing Your SQL Servers
- 2/15/2012
Managing Servers
Servers and databases are the primary resources you manage in SQL Server Management Studio. When you select a top-level group in the 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 a local server is not shown, you need to update the local registration information. If the remote server you want to manage is not shown, you need to register it.
Registration saves the current connection information and assigns the server to a group for easy future access using the Registered Servers view. After you register a server, you can connect to the server to work with it and then disconnect when you have finished simply by double-clicking the server entry in the Registered Servers view. If you are not automatically connected, you can force a connection by right-clicking the server entry and then selecting New Query (if you want to create an 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 the Registered Servers view.
You can manage previous registrations in a variety of ways:
Import registration information on previously registered SQL Server 2000 servers.
Update registration information for local servers.
Copy registration information from one computer to another by importing and exporting the information.
Registering a Connected Server
Any server to which you have connected in Object Explorer can be registered easily. Registration saves the current connection information and assigns the server to a group for easy future access using the Registered Servers view. To register a connected server, follow these steps:
In Object Explorer view, right-click any server to which you are currently connected, and then choose Register to display the New Server Registration dialog box, shown in Figure 1-6.
On the General tab, the current values for the server name and authentication type are filled in for you. Although the Registered Server Name option is set to the same value as the server name, you can modify this name and add a description.
On the Connection Properties tab, you can specify the database to which you want to connect and set options for networking and connections. If you want to encrypt the connection, select the Encrypt Connection check box.
To test your settings before you save the registration settings, click Test. If the test is unsuccessful, verify the settings and then make changes as necessary. As discussed in Chapter 2, SQL Server doesn’t allow remote connections by default, so you must change the configuration settings to allow remote connections.
Click Save to save the server registration.
Figure 1-6 The New Server Registration dialog box.
)
By default, the server is added to the top-level group. To move the server to a new level in the server group hierarchy, follow these steps:
Right-click the server in the Registered Servers view, point to Tasks, and then select Move To.
In the Move Server Registration dialog box, you can move the server to a different level by selecting the subgroup into which you want to place the server.
Click OK.
Registering a New Server in the Registered Servers View
You do not have to connect to a server in Object Explorer to register the server. You can register new servers directly in the Registered Servers view by following these steps:
In the Registered Servers view, use the toolbar to select the type of server you want to connect to, such as Database Engine.
Expand the available groups as necessary. In the Registered Servers view, right-click the group into which you want to register the server, and then select New Server Registration to display the New Server Registration dialog box, shown previously in Figure 1-6.
In the Server Name box, type the FQDN or host name of the server on which SQL Server is running, such as EngDBSrv12.cpandl.com or EngDBSrv12.
Use the Authentication list to choose the option for authentication type, which is either Windows Authentication or SQL Server Authentication (based on the authentication types selected when you installed the server). Provide a 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 reenter it each time you connect, select Remember Password.
You also can specify connection settings by 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. If you want to encrypt the connection, select the Encrypt Connection check box.
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 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.
Registering Previously Registered SQL Server 2000 Servers
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 the Registered Servers view, use the toolbar to select the type of server you are registering, such as Database Engine.
Right-click the Local Server Groups entry, point to Tasks, and then 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 in locally to the computer on which the servers were registered previously.
Updating Registration for Local Servers
Local servers are registered automatically (in most cases). If you have added or removed SQL Server instances on the local computer and those instances are not displayed, you 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:
In the Registered Servers view, use the toolbar to select the type of servers you are registering, such as Database Engine.
Right-click the Local Server Groups entry, point to Tasks, and then select Register Local Servers.
Copying Server Groups and Registration Details from One Computer to Another
After you register servers in SQL Server Management Studio and place the servers into a specific group hierarchy, you might 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 by 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 the Registered Servers view by pressing Ctrl+Alt+G.
In the Registered Servers view, use the toolbar to select the type of servers you want to work with, such as Database Engine.
Right-click the Local Server Groups entry, point to Tasks, and then select Export to display the Export Registered Servers dialog box, shown in Figure 1-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 Local Server Groups entry.
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.
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 the currently logged in user’s Documents folder. Under Export Options, type a name for the registration server file, such as CurrentDBConfig.
Figure 1-7 The Export Registered Servers dialog box.
By default, the current authentication details for server connections are not exported into the saved 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 is successful, you see a dialog box confirming this. Click OK in the dialog box. If there is 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 need to copy the file to this computer now.
Select the Registered Servers view by pressing Ctrl+Alt+G.
In the Registered Servers view, use the toolbar to select the type of server you want to work with, such as Database Engine.
Right-click the Local Server Groups entry, point to Tasks, and then select Import to display the Import Registered Servers dialog box, shown in Figure 1-8.
Figure 1-8 The Import Registered Servers dialog box.
In the dialog box, click the button to the right of the Import File text box, and then use the Open dialog box that appears 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 is successful, you see a dialog box confirming this. Click OK in the dialog box. If there is a problem, note and correct it.
)
)
Editing Registration Properties
You can change a server’s registration properties at any time by right-clicking the server entry in the Registered Servers view in SQL Server Management Studio and then selecting Properties. Use the Edit Server Registration Properties dialog box to make changes. The only property you cannot change is the server type. Be sure to test the settings before saving them.
Connecting to a Server
After you register a server, connecting to it is easy. Right-click the server entry in the Registered Servers view in SQL Server Management Studio, and then select New Query (if you want to create an SQL query) or Object Explorer (if you want to view and manage the server). You also can double-click the server entry to establish a connection and then work with the server in the Object Explorer view.
Disconnecting from a Server
When you have finished working with a server, you can disconnect from it. This eliminates the back-and-forth communications to the server. To disconnect, right-click the server’s entry in the Object Explorer view in SQL Server Management Studio, and then select Disconnect from the shortcut menu.
Moving a Server to a New Group
To move the server to a new group, complete the following steps:
Right-click the server you want to move in the Registered Servers view, point to Tasks, and then select Move To from the shortcut menu to display the Move Server Registration dialog box.
In the Move Server Registration dialog box, expand the Local Server Groups entry to see a list of subgroups. Expand the subgroups as necessary. You can now do the following:
Move the server to the top-level group by selecting the top-level group. This makes the server a member of the top-level group.
Move the server to a different level by selecting the subgroup into which you want to place the server.
Click OK.
Deleting a Server Registration
If you change a server name or remove a server, you might 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 Registered Servers view and then select Delete. When prompted to confirm the action, click Yes to delete the server registration details.