Chapter 7
Managing the Enterprise



The SQL Server 6.x Enterprise Manager greatly simplifies database management. The Enterprise Manager combines the features found in version 4.2x's Object Manager and SQL Administrator. Now, from a single interface, a DBA can concurrently administer multiple servers without the burden of having to use multiple administration products.

Starting the Enterprise Manager

To start the Enterprise Manager, double-click the SQL Enterprise Manager icon in the Microsoft SQL Server 6.5 (Common) group (see Figure 7.1).

Figure 7.1.
The SQL Enterprise Manager icon.


NOTE: Because of its 32-bit architecture, Enterprise Manager can be run only from Windows NT or Windows 95. It cannot be run from Windows 3.1.

Navigating the Enterprise Manager

Because of its graphical interface, the Enterprise Manager minimizes the number of commands required to administer a server. Following are common methods of navigation in the Enterprise Manager:

Registering a Server

The first time you start the Enterprise Manager, you are prompted to register a server. When you register a server, you provide the Enterprise Manager with a logical name and user login with which to connect to the SQL Server database engine.

Follow these steps to register a server (see Figure 7.2):

Figure 7.2.
Registering a server.

  1. From the Register Server dialog box, enter the name of the server you want to register.

  2. Select the type of login to use: trusted or standard. Trusted security offers the advantage of having to maintain only a Windows NT login account and password. With standard security, you must maintain a network account as well as a SQL Server account and password.

  3. If using standard security, enter the login ID and password.


    NOTE: To use trusted security, SQL Server must be installed with Windows NT integrated security or mixed security.
  4. Select a server group or create a new server group.

  5. Click the Register button to register the server with the Enterprise Manager.


NOTE: You can use the Enterprise Manager to administer a 4.2x version of SQL Server. Run the script SQLOLE42.SQL. This script can be found in the install subdirectory of SQL Server (for example, c:\mssql\install\sqlole42.sql).

Starting, Pausing, and Stopping SQL Server

To start, pause, or stop SQL Server, select a server and click the Start/Pause/Stop Server toolbar button. This action takes you to the SQL Server Manager dialog box (see Figure 7.3). From this dialog box, you can start, pause, or stop SQL Server.

Figure 7.3.
The SQL Server Manager dialog box.

Connecting to a Server

Once you have opened the Enterprise Manager and started SQL Server, click the plus (+) sign next to the server icon to connect to a server. If a connection is successfully made, the connected symbol appears next to the server status icon (see Figure 7.4).


NOTE: If you are unable to establish a connection to SQL Server from the Enterprise Manager, make sure that the MSSQLServer service is currently running.

Figure 7.4.
A successful server connection.

Disconnecting from a Server

To disconnect from a server, select the server and right-click. From the shortcut menu, select Disconnect.


NOTE: You are automatically disconnected from SQL Server when you close the Enterprise Manager.

Starting, Stopping, and Configuring SQL Mail, SQL Executive, and Distributed Transaction Coordinator

To start, stop, or configure SQL Mail, SQL Executive, or the Distributed Transaction Coordinator (DTC), right-click the desired service and select the appropriate option (see Figure 7.5). This action opens the corresponding dialog box (see Figures 7.6, 7.7, and 7.8).

Figure 7.5.
Services associated with the server.


Figure 7.6.
The SQL Mail Configuration dialog box.


Figure 7.7.
The ConFigure SQL Executive dialog box.


Figure 7.8.
The DTC Configuration dialog box.

Common Tasks

The following sections provide brief descriptions of how to perform common administration tasks from the Enterprise Manager. Many of these tasks are explained in greater detail in other sections of this book.

Execute and Analyze Queries

To execute and analyze queries, select a server from the Enterprise Manager and click the SQL Query Tool toolbar button. This action takes you to the Query dialog box (see Figure 7.9). From this dialog box, you can issue Transact SQL statements, view results, and analyze query performance and optimization plans.

Figure 7.9.
The Query dialog box.


TIP: From the Query dialog box, you can concurrently run multiple SQL statements against the server. Click the New Query toolbar button in the Query dialog box. This action opens a new connection to SQL Server, which can be used to issue a new query while maintaining previous connections. This feature enables you to switch connections while queries are being processed. Because the processing takes place on the server and not on the client, your machine is free to continue with other tasks. Queries that are being processed have a globe icon next to the query number (see Figure 7.10).

Figure 7.10.
An example of multiple-query processing.


TIP: Also from the Query dialog box, you can run an individual Transact SQL statement by highlighting just the text and clicking the Execute Query toolbar button (see Figure 7.11).

Figure 7.11.
Executing only the highlighted text.

Manage Server Configurations

Follow these steps to configure a server:

  1. Select a server to configure from the Enterprise Manager.

  2. Click the Configure SQL Server toolbar button in the Enterprise Manager. The Server Configuration/Options dialog box appears (see Figure 7.12). From this dialog box, you can configure server level options and security and view SQL Server build information.

Manage Devices

Follow these steps to create, manage, and delete devices:

  1. Select the server from the Enterprise Manager for which you want to manage a device.

  2. Open the Database Devices folder by clicking the plus (+) sign next to the Database Devices folder. To manage an individual device, right-click the appropriate device. To manage multiple devices, right-click the Databases Devices folder. The shortcut menu appears.

  3. From the shortcut menu, select Edit. The Manage Database Devices dialog box appears (see Figure 7.13).

    Figure 7.12.
    The Server Config-uration/Options dialog box.


    Figure 7.13.
    The Manage Database Devices dialog box.

  4. Double-click a device in the Manage Database Devices dialog box to open the Edit Database Devices dialog box (see Figure 7.14). Use this dialog box to edit an existing device.

Figure 7.14.
The Edit Database Device dialog box.


Refer to Chapter 8, "Managing Devices," for more information on managing devices.

Manage Databases

Follow these steps to create, manage, and delete a database:

  1. From the Enterprise Manager, select the server for which you want to manage a database.

  2. Open the Databases folder by clicking the plus (+) sign. From this folder, you can create a new database, edit an existing database, or delete a database. Right-click the appropriate object to activate the corresponding database menu.

  3. To manage multiple databases, right-click the Databases folder. From the shortcut menu, select Edit. This action displays the Manage Databases dialog box (see Figure 7.15). Double-click a database in the Manage Databases dialog box to open the Edit Database dialog box (see Figure 7.16). Use this dialog box to edit an existing database.

Refer to Chapter 9, "Managing Databases," for more information on managing databases.

Figure 7.15.
The Manage Databases dialog box.


Figure 7.16.
The Edit Database dialog box.

Manage Database Objects

Follow these steps to create, manage, and drop database objects such as tables, indexes, triggers, views, stored procedures, rules, defaults, and user-defined datatypes:

  1. From the Enterprise Manager, select the server for which you want to manage a database object.

  2. Open the Database folder by clicking the plus (+) sign. Open the database that contains the objects you want to work with by clicking the plus (+) sign next to the corresponding database.

  3. Open the Objects folder by clicking the plus (+) sign. From this folder, you can manage tables, views, stored procedures, rules, defaults, and user-defined datatypes (see Figure 7.17). For example, to manage a table, click the plus sign next to the Tables folder. From the Tables folder, double-click a table. This action opens the Manage Tables dialog box; use this dialog box to alter an existing table or create a new table (see Figure 7.18).

Figure 7.17.
Managing database objects.


Figure 7.18.
Managing tables.


You can also manage object permissions by right-clicking the appropriate object and selecting the Permission option from the shortcut menu. This action displays the Object Permissions dialog box (see Figure 7.19).

Figure 7.19.
The Object Permissions dialog box.

Manage Logins

Follow these steps to manage logins:

  1. From the Enterprise Manager, select the server for which you want to manage logins.

  2. Click the Manage Logins toolbar button in the Enterprise Manager window. The Manage Logins dialog box appears. From this dialog box, you can add, edit, and drop server logins (see Figure 7.20). You can also control a user's database access and default database from the Database Access section of the dialog box.

Figure 7.20.
The Manage Logins dialog box.


Refer to Chapter 10, "Managing Users," for more information on controlling logins.

Generate SQL Scripts

From the Enterprise Manager, you can generate SQL scripts that contain the data definition language used to create an object in a database. This enables you to reverse-engineer existing objects.


TIP: An easy way to add or delete a column from a table is to generate the corresponding SQL scripts, modify the script, and then re-create the object from the modified script. SQL scripts are also useful for performing keyword searches. Suppose that you want to determine how many tables have the column au_id varchar(11). An easy way to determine this is to generate the data definition language for all the tables in the database and then search with a text editor for au_id.

Follow these steps to generate SQL scripts:

  1. From the Enterprise Manager, select the server and database from which you want to generate a SQL script.

  2. From the Object menu, select Generate SQL Scripts. This action displays the Generate SQL Scripts dialog box. From this dialog box, you can generate the appropriate SQL syntax (see Figure 7.21).

Figure 7.21.
The Generate SQL Scripts dialog box.

Manage Backups and Recovery

SQL Server provides a robust backup and restoration component that enables you to tailor your backup strategy to maximize data recovery. Follow these steps to manage backups:

  1. From the Enterprise Manager, select the server and database you want to back up or restore.

  2. From the Tools menu, select Database Backup/Restore. This action displays the Database Backup/Restore dialog box. From this dialog box, you can back up and restore databases (see Figure 7.22).

Figure 7.22.
The Database Backup/Restore dialog box.


Refer to Chapter 14, "Backups," and Chapter 15, "Recovery," for more information on backups and restoration.


TIP: Use the Enterprise Manager's Task Scheduler to schedule backups on a recurring basis.

Schedule Tasks

The Task Scheduler can automatically execute a task at a preset time interval. Database dumps, transaction log dumps, and DBCC commands are just a few of the types of administrative tasks that can be automated with Task Scheduler. To manage scheduled tasks, follow these steps:

  1. Click the Manage Scheduled Tasks toolbar button. This action displays the Manage Scheduled Tasks dialog box (see Figure 7.23).

    Figure 7.23.
    The Manage Scheduled Tasks dialog box.

  2. From this dialog box, you can add, edit, or delete a task. You can also view currently running tasks.

Manage Alerts

The Enterprise Manager provides a built-in Alert Manager that enables you to define various types of alerts. The Alert Manager can automatically notify an operator through e-mail or a pager, providing a more proactive approach to data-base administration. To manage alerts, follow these steps:

  1. Click the Manage Alerts and Operators toolbar button. This action displays the Manage Alerts and Operators dialog box (see Figure 7.24).

    Figure 7.24.
    The Manage Alerts and Operators dialog box.

  2. From the Manage Alerts and Operators dialog box, you can add, edit, and delete alerts. You can also define operators for e-mail and pager notification.

Refer to Chapter 26, "Automating Database Administration Tasks," for more information on alerts.

Manage Replication

SQL Server provides a graphical replication model that uses a publish and subscribe metaphor to simplify replication initialization and management.

Replication management has three components: Replication Topology, Replication--Manage Publications, and Replication--Manage Subscriptions.

To manage each of these components, click the corresponding toolbar button (see the following chart). Refer to Chapter 12, "Replication," for more information on replication.

Toolbar Button Associated Component
Replication Topology
Replication--Manage Publications
Replication--Manage Subscriptions

Monitor User Activity

With SQL Server 6.x, you can easily monitor user activity to help you pinpoint query problems and isolate bottlenecks. From the User Activity dialog box, you can kill a process and send an e-mail message to a user.

To monitor user activity, click the Current Activity toolbar button. This action opens the Current Activity dialog box (see Figure 7.25). From this dialog box, you can view and terminate different processes.

Figure 7.25.
The Current Activity dialog box.

Refer to Chapter 23, "Multi-User Considerations," for more information on monitoring user activity.

Summary

As you can see, the graphical interface provided by the Enterprise Manager simplifies the tasks required to manage SQL Server. However, to manage a production environment, a DBA must know more than how to right-click an object. A DBA must be knowledgeable about the various components of SQL Server and how they interact. With that in mind, the next chapter discusses one of the most fundamental components of SQL Server: devices.


DISCLAIMER


To order books from QUE, call us at 800-716-0044 or 317-361-5400.

For comments or technical support for our books and software, select
Talk to Us.

© 1997, QUE Corporation, an imprint of
Macmillan Publishing USA, a Simon and Schuster Company.