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 3).

Figure 2

Figure 3

4. 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

5. 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

7. 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

8. 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

9. 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

10. 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 display. 

October 14, 2004