Using SQL Server Enterprise Manager

3 4

Enterprise Manager is part of the Microsoft Management Console (MMC). MMC is a central application that is used to manage all aspects of a system running Windows 2000 Server. In Windows 2000 and in future versions of Windows, MMC will play an increasingly important role in the management of Microsoft BackOffice applications such as Microsoft Exchange Server, Microsoft Proxy Server, Microsoft Site Server, Microsoft Systems Management Server, and Microsoft SNA Server.

Managing SQL Server

Enterprise Manager is the most complete tool for configuring and managing your SQL Server installation. Whereas Service Manager allows you only to start, pause, or stop a service, Enterprise Manager allows you to stop and start a server, and it also enables you to perform the following tasks:

  • Register your server
  • Configure local and remote servers
  • Configure and manage a multiple-server installation
  • Set up login security and add users, system administrators, and operators
  • Assign a system administrator (sa) password
  • Create and schedule jobs
  • Create alerts and configure SQL Server to communicate to system administrators through e-mail
  • Set up and manage databases, tables, indexes, views, stored procedures, rules, triggers, defaults, backup devices, and error logs
  • Manage other SQL Server services

Enterprise Manager, shown in Figure 8-5, is your one-stop shop for managing all these tasks and more. The remainder of this chapter will help you get started using Enterprise Manager. Later chapters will show you how to use Enterprise Manager to perform more advanced SQL Server tasks.

click to view at full size.

Figure 8-5. The SQL Server Enterprise Manager.

The following list describes four tasks that you can perform using Enterprise Manager. You must perform these tasks before you begin using your SQL Server installation for the first time. We will examine each of these tasks in detail in the sections that follow this list.

  • Create a server group. By creating a server group, you can restrict access to information to only that group. Accounts with similar resource needs can and should be gathered into groups to simplify account administration.
  • Register your server. You must register your server with MMC before you can begin to manage it.
  • Access your server. Once your server is registered, you can view and configure a number of properties. If you have a multiple-server environment, you can use Enterprise Manager to manage and configure the servers from a single location.
  • Change the default system administrator password. When SQL Server is installed, the default system administrator account is configured with no password. You should specify a password before you begin to use SQL Server.

Creating Server Groups

Enterprise Manager enables you to create groups of servers to help with your administrative tasks. Server groups help you organize a related set of servers for easy access—much like folders help you organize related files. You can then perform actions that will affect every server in the group by using one command, rather than by repeating the command for each server. By default, when you installed SQL Server, a SQL Server group was created, called SQL Server Group. To create a server group, follow these steps:

  1. Click Start, point to Programs, point to Microsoft SQL Server 2000, and then choose Enterprise Manager to start the Enterprise Manager application.
  2. The left side of the Enterprise Manager window shows the server group folders as subfolders of Microsoft SQL Servers, and the right side of the window contains the server group icons. To create a SQL Server group, right-click the Microsoft SQL Servers folder and then choose New SQL Server Group from the shortcut menu that appears.
  3. Type a name for the new server group in the Server Groups dialog box that appears, as shown in Figure 8-6. If you click Sub-group Of, you can select the group of which your new server group will be a subgroup. If you click Top Level Group, your new server group will be a top-level SQL Server group, on the same level as SQL Server Group. Click OK to save your new group.

    Figure 8-6. The Server Groups dialog box.

Registering Your Server

After you have created a SQL Server group, you can register your local or remote servers as members of the group. To register a server, follow these steps:

  1. Right-click a server group icon in the right-hand pane of the Enterprise Manager window. (If the Microsoft SQL Servers heading is expanded, you can also right-click the group folder name in the left pane.) Choose New SQL Server Registration from the shortcut menu.
  2. The Register SQL Server Wizard welcome screen appears. For many of the routine management tasks you will perform using Enterprise Manager, a wizard is provided to help you through the process. Click Next to continue the registration process.
  3. The Select A SQL Server screen appears, as shown in Figure 8-7. The SQL Server installations available on your network are displayed in the Available Servers list box. Select the servers you want to register (or type a server name in the text box), and then click Add to move the server name to the Added Servers list box. After you have completed your selections, click Next.
  4. The Select An Authentication Mode screen appears. Select the type of security you want to use to connect to your SQL Server installation. SQL Server security is reviewed in detail in Chapter 34. (If you performed a Typical installation, SQL Server is already configured to use the Windows NT authentication mode.) Click Next to continue.

    click to view at full size.

    Figure 8-7. The Select A SQL Server screen.

  5. The Register SQL Server Wizard - Select SQL Server Group screen appears, as shown in Figure 8-8. You can select an existing group to which you can add your server or create a top-level server group for your server. If you want to add the server to an existing group, click the first option in the screen, and then select the group name from the drop-down list. If you want to create a group, click the second option and then type the group name in the text box. Click Next to continue.
  6. The Completing The Register SQL Server Wizard screen appears. The listed server will be registered. If you need to make any changes, click Back; otherwise, click Finish to complete the registration process.

    click to view at full size.

    Figure 8-8. The Select SQL Server Group screen.

  7. The Register SQL Server Messages dialog box appears (Figure 8-9), confirming that your registration was successful. Click Close to close this dialog box.

    Figure 8-9. The Register SQL Server Messages dialog box.

Accessing Your Server

Once you have successfully registered your server using Enterprise Manager, you can access all of its properties, databases, and objects. To view the properties and objects of the newly registered server, first expand the server group name in the left-hand pane of the Enterprise Manager window. A list of all the servers in the group appears. Then expand the server name to display its properties and objects, as shown in Figure 8-10. (Later chapters will explain in detail how to manage and configure these properties.)

click to view at full size.

Figure 8-10. The properties and objects of a server.

Changing the Default Passwords

All SQL Server installations have a built-in administrative account, the sa account. (The term "sa" is short for "system administrator.") On new SQL Server installations, the sa user account is not assigned a password. To ensure the highest level of security for your SQL Server installation, you should assign a password to the sa account. To do so, follow these steps:

  1. As the previous section outlines, access the server whose sa password you want to change.
  2. Expand the Security folder and then click Logins to display the installed SQL Server user accounts in the right-hand pane, as shown in Figure 8-11.

    click to view at full size.

    Figure 8-11. The installed SQL Server user accounts.

  3. Right-click the sa user account and then choose Properties from the shortcut menu that appears. The SQL Server Login Properties window appears (Figure 8-12).

    Figure 8-12. The SQL Server Login Properties window.

    You can specify several other settings via the SQL Server Login Properties window. We will describe these features in Chapter 34.

  1. Type a new password in the Password text box, and then click OK to display the Confirm Password dialog box.
  2. Retype the password and then click OK. You have just taken a critical step in securing your SQL Server installation.

CAUTION


Be sure to remember the password you have chosen. If you forget your password, you will have to reinstall SQL Server.

Managing Other Services

Enterprise Manager can also be used to manage the SQL Server component services: SQL Server Agent, Microsoft Distributed Transaction Coordinator, and Microsoft Search. It is the only tool available to manage these services, aside from Service Control Manager and SQL Service Manager, which allow you only to start and stop the component services, as we mentioned earlier.

SQL Server Agent

Enterprise Manager provides a user-friendly interface for managing SQL Server Agent. To access the properties of the SQL Server Agent service, follow these steps:

  1. In Enterprise Manager, expand the server you want to access, and then expand the Management folder, as shown in Figure 8-13.

    click to view at full size.

    Figure 8-13. The Management folder in Enterprise Manager.

  2. Right-click SQL Server Agent in the left pane or right-click the SQL Server Agent icon in the right pane, and you will see the shortcut menu. From this menu, you can stop or start the SQL Server Agent service; view the error log; start the wizards to make this server a master or target for running jobs; create a job, an alert, or an operator; or view the Properties window. These options will be described in more detail in Chapter 31.
  3. From the shortcut menu, choose Properties. The SQL Server Agent Properties window appears, as shown in Figure 8-14.

    Figure 8-14. The SQL Server Agent Properties window.

  4. In this window, you can configure various options for the SQL Server Agent service by clicking the various tabs: General, Advanced, Alert System, Job System, and Connection. At the bottom of the window, there is a Help button that explains in detail each option on the displayed tab.

Microsoft Distributed Transaction Coordinator

The only options for Microsoft Distributed Transaction Coordinator in Enterprise Manager are to stop and start the service. To do this, expand the server you want to access and then expand the Support Services folder, as shown in Figure 8-15.

click to view at full size.

Figure 8-15. The Support Services folder.

Right-click Distributed Transaction Coordinator in the left or right pane. You can choose either Start or Stop from the shortcut menu to start or stop this service.

Microsoft Search

To access the menu options for the Microsoft Search service, open the Support Services folder, as shown in the previous section, and then right-click Full-Text Search in either the left or right pane. From this menu, you can stop or start the service, clean up full-text catalogs, or view the service's properties.



Microsoft SQL Server 2000 Administrator's Companion
Microsoft SQL Server 2000 Administrators Companion
ISBN: B001HC0RPI
EAN: N/A
Year: 2005
Pages: 264

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net