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.
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.
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:
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.
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).
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.
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.
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.
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.
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.
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.
Follow these steps to configure a server:
Follow these steps to create, manage, and delete devices:
Figure 7.12.
The Server Config-uration/Options dialog box.
Figure 7.13.
The Manage Database Devices dialog box.
Figure 7.14.
The Edit Database Device dialog box.
Refer to Chapter 8, "Managing Devices," for more
information on managing devices.
Follow these steps to create, manage, and delete a 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.
Follow these steps to create, manage, and drop database objects such as tables, indexes, triggers, views, stored procedures, rules, defaults, and user-defined datatypes:
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.
Follow these steps to manage logins:
Figure 7.20.
The Manage Logins dialog box.
Refer to Chapter 10, "Managing Users," for more
information on controlling logins.
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:
Figure 7.21.
The Generate SQL Scripts dialog box.
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:
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.
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:
Figure 7.23.
The Manage Scheduled Tasks dialog box.
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:
Figure 7.24.
The Manage Alerts and Operators dialog box.
Refer to Chapter 26, "Automating Database Administration Tasks," for more information on alerts.
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 |
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.
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.
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.