Establishing Server Groups and Registering SQL Server in Enterprise Manager

As Enterprise Manager can manage multiple servers, it needs some way to connect to and organize the servers. Connecting to the server is done by registering the server. After the servers are registered, they can be organized into logical groups.

Server Groups

Server Groups in EM are used to group servers and named instances. When EM is first installed, a default group named SQL Server Group exists. Additional groups can be created to logically organize servers. For example, servers could be grouped by department or geographic location. Another good use of groups is to separate development servers from production; this reduces the risk of inadvertently making changes to production databases.

To create a new server group, right-click Microsoft SQL Servers or an existing server group, and select New SQL Server Group from the pop-up menu. This will open the Server Group window. Enter a name for the window and select whether it will be a top-level group or a subgroup of an existing group. Figure 4.1 shows the Server Groups window.

Figure 4.1. Creating a new server group.

Registering Servers

When accessing EM for the first time, your first task will be to register your servers. If you are running EM on the server, the local server is registered automatically in the SQL Server Group. If you want to register other servers or if you are running EM from your workstation, right-click the group to which you want to add a server and select New SQL Server Registration. You must be a member of the sysadmin role on the server to perform the registration. The first time you run this, the Register SQL Server Wizard runs, stepping you through the registration process. If you select From Now On I Want to Perform This Task Without Using a Wizard, clicking Next will bring you directly to the Registered SQL Server Properties window, shown in Figure 4.2.

Figure 4.2. Registering a SQL server.

Select the server name from the drop-down box, an authentication method, the group in which you want to register the server, and the options you would like to apply to the registration. After the server is registered, if you want to change any options, right-click the server in EM and select Edit SQL Server Registration Properties.

Connecting to and Disconnecting from Servers

After a server is registered, connecting to the server is simply a matter of selecting it in Enterprise Manager. If the registration was configured to use SQL Server authentication, and to Always Prompt for a Username and Password, you will be required to provide a valid login. If you receive an error message stating The server is not known to be running , select Yes to connect. This is common when EM has just started and it has yet to poll the server's state. If you still can't connect to the server, check your network connectivity to the server in question. If the network connectivity is okay, try adding the server as an alias through the Client Network Utility. This might help EM to resolve a path to the server. To disconnect, right-click the server and select Disconnect from the pop-up menu.

Starting and Stopping Servers

SQL Server can be stopped and started from the EM console. Right-clicking a server will display the pop-up window shown in Figure 4.3.

Figure 4.3. Starting and stopping a server from EM.

Note that Start, in this example, is not available. This indicates that the server is already running. The available options are to stop the server, which stops the SQL Server service and any dependent services, such as the SQL Agent, and to pause the server, in which case the server remains running but no new connections are allowed. If the server has been paused , then a Continue option will appear in the pop-up menu to resume SQL server in a normal connection state. If the server has been stopped, then the Start option will be available to start the server.

NOTE

Occasionally, Enterprise Manager and the taskbar Service Manager get confused as to what state the server is in and won't allow services to be stopped or started. In this case, go to the Services applet in the Control Panel, or use the net stop/net start commands to stop and restart the appropriate services.