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:
- 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 greatoutline/definition of each of the property settings in Enterprise Manager.
- 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.
- 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
- 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
- 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
- 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
- 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
- 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
- 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