SQLServerCentral Article

Top Ten Features of Enterprise Manager


More than once, I have heard, “I never knew that feature existed

in Enterprise Manager”, so I thought I would conjure up a Top Ten List for

Enterprise Manager.  However, I will not say that there may be other features

that are not as equally important and these are not necessarily in any order. 

The nice thing about Enterprise Manager (see Figure 1) is that it can make

some of the daily mundane chores a lot easier.

Enjoy the read and be sure to leave me “pithy” comments.  J  I welcome suggestions for other good

tips and tidbits.

Figure 1

Enterprise Manager's Top Ten Features:

  1. Configuration Properties – This is pretty much self explanatory.  If you haven’t already checked out my article on this, Exploring

    SQL Server 2000 Configuration Properties, you should.  It’s a great

    outline/definition of each of the property settings in Enterprise Manager.

  2. Query Designer under Databases/Tables – If you

    are stuck building a complex query with joins or you are new to building

    queries, you need to check out my article on using Query Designer (Visual

    Basic Tools) in Enterprise Manager.  It explains how Query Designer’s Query

    By Example (QBE) can be utilized to your advantage.  Check it out at Building

    Joins the Easy Way.

  3. Databases/Views

    You can look into the details of your views through Enterprise Manager. 

    Views are virtual tables defined by queries.  Basically, it’s a filter. 

    You can easily manage your views by expanding a server group, then the server. 

    Look under Database and then Views.  By right clicking on

    a view, you can easily create, design, and open views.  You can also easily

    manage triggers and permissions as well as generate scripts and display

    dependencies (Figure 2).  You can also see the view's properties by right

    clicking on the view in question and clicking on Properties (Figure


Figure 2

Figure 3

  1. Databases/Stored Procedures – Stored procedures

    are programs written in the Transact-SQL programming language.  They are

    programs stored on the SQL Server and are executed on the server.  This

    makes processing faster, more secure, with less traffic, and modular ‘cause

    the procedure only has to be created once.  Applications execute stored

    procedures so they can process results (i.e. inserting a customer into the

    database through the client interface). You can easily manage your stored

    procedures by expanding a server group, then the server.  Look under Database

    and then Stored Procedures.  By right clicking on a stored procedure,

    you can create new stored procedures.  You can also easily manage permissions

    as well as create new publications, generate scripts, and display dependencies

    (see Figure 4).  You can also see the stored procedure's properties by right

    clicking on the stored procedure in question and then clicking on Properties

    (Figure 5).

Figure 4

Figure 5

  1. Management/SQL Server

    Agent/Jobs – If you ever need to run scheduled tasks (i.e.  backups,

    DTS packages, replication) during certain times/ intervals, this option

    is a must for setting them up easily.  Also, if you need to set up alerts

    via email/paging, for example, this is where to do it (see Figure 6).  Make

    sure that if you need to set up tasks to run regularly, you need to set

    up the SQL Agent to Autostart when the Operating System Starts (see

    Item 1 above on where to find this under configuration properties) and also

    to Autostart SQL Server Agent if it stops unexpectedly.  You can

    find this after expanding a server group, then the server.  Look under Management

    and then right-click SQL Server Agent.  You can find it under Properties.

Figure 6

6. Management/SQL Server Agent/Database Maintenance Plans – Besides

primarily helping you with backing up your databases and transaction logs,

the wizard (see Figure 7) will help you to reorganize the data on the data

and index pages, compress data files, update index statistics, perform internal

consistency checks of data and data pages, and set up log shipping (for synchronizing

databases with standby servers).  You can find the wizard after expanding

a server group, then the server.  Look under Management and then right-click

Database Maintenance Plans.  If you want to just set up an immediate

backup job or to schedule one a backup job, you can expand a server group,

then the server.  Look under Management and then right-click Backup.

Figure 7

  1. Management/SQL Server Agent/SQL Server Logs – A

    new error log is created every time you open a new instance in SQL.  The

    error log allows you to check on processes like backups, restores, and scripts

    to make sure they have been completed (see Figure 8).  You can view the

    error logs after expanding a server group, then the server.  Look under

    Management, click on SQL Server Logs, and then click on the

    log you want to see.

Figure 8

  1. Security/Logins – You can grant a Windows NT 4.0

    or Window 2000 user or group login access to SQL Server.  You should only

    grant database access to individual Windows NT 4.0 and Windows 2000 users

    if the user needs to perform exclusive activities other than the Windows

    NT 4.0 or Windows 2000 groups that have already been granted access to the

    database, and of which, they are a member.  You can add or delete users/groups

    login after expanding a server group, then the server.  Look under Security,

    click on Logins, and then right click and choose New Login

    Otherwise, right click the user/group you want to delete and choose Delete

    (see Figures 9 and 10).

Figure 9

Figure 10

  1. Security/Server Roles – You can add members to

    the following fixed server roles (see Figure 11).  Look under Security,

    Server Roles, right click on the appropriate Server Role and then

    choose Properties.  Under the General tab, choose Add (see

    Figure 12).

Figure 11

Figure 12

  1. Data Transformation Services – Enterprise Manager,

    via DTS, allows you to easily shuffle data back and forth that derives from

    different sources and formats.  These sources include SQL Server, Access,

    Excel, Oracle, dBase, Paradox, ODBC, HTML, and other OLE DB providers. 

    The simplest was to implement a DTS package is to right click on Databases

    and choose All Tasks and then choose Import Data or Export

    Data (see Figure 13).  (Note: there are other places in the console

    tree that you can find this option.)  That will bring up the DTS Wizard

    (see Figure 14).  You will be guided through screens to choose your source

    and destination for the data (Figures 15 and 16).  For more intermediate

    and advanced DTS packages, you can use DTS Designer by right clicking

    Database Transformation Services and choosing New Package

      Of course, you can also programmatically write and compile DTS packages

    (i.e., Visual Basic, C++).

Figure 13>/center>

Figure 14

Figure 15

Figure 16

Some Enterprise Manager Tips:

  • Right clicking on Databases will give you options

    for Backup/Restore/Attach Databases, Import/Export Data, and the

    Copy Database Wizard

  • Right clicking on a database under Databases (see

    Figure 17), will give you further options

    for Importing/Exporting, Maintenance, Scripting, Backup/Restore, Shrink/Detach/Take

    Offline, Copy Subscription Database, and View Replication Conflicts.

Figure 17

  • Do not forget to choose Refresh from the right

    click menus or from the Toolbar, each time you make a change (i.e.

    create a table) in Enterprise Manager.  Otherwise, the results will not


October 14, 2004