How is MSDE administered?
Application developers commonly use the Enterprise Manager and Query Analyzer utilities to
create and modify database objects. This is an excellent solution in the development environment,
but your end users will not likely have access to these tools. There are also some compelling
reasons not to install SQL Server client utilities on an end user's machine. Although you as a
developer may be accustom to the complex interface, your clients are not. There is a steep
learning curve and the Enterprise Manager's interface can be intimidating, even fear inspiring,
for the first time user.
Wouldn't it be a better solution if you could provide a subset of the myriad of DBA services
exposed through the SQL Server client utilities? Why overwhelm your users with a massive
administration utility when all they really need to do is edit database properties, do backups
or restores, shrink database files and update index statistics?
Well, it is possible to roll your own Admin Utility. Microsoft makes available two type libraries
with automation interfaces for the administration of SQL Server databases. They are the SQL
Distributed Management Objects (SQL-DMO) and SQL Namespace (SQL-NS) COM libraries.
These APIs expose all the features, functionality, dialogs and wizards of the SQL Enterprise
Manager to the application developer. As a result, you can create an application that completely
shields your users from many of the complexities of SQL Server administration and still provides
an interface for performing the necessary database maintenance. The benefits of creating your own
Admin Utility include:
- Provides a simple interface to accomplish necessary database maintenance.
- Allows you to conceal potentially dangerous actions, like the ability to drop (delete) tables,
stored procedures or even the database itself.
- Makes it possible to add custom functions that are peculiar to your application's requirements.
For example, the application that was the inspiration for this article required some custom
administrative services that were best performed from SQL Server. The application administrator
needed the ability to read a list of user names and e-mail addresses from the tblUsers table and
send mail messages to all users informing them of upcoming events, such as upgrades, or system
maintenance. Additionally, by reading from the tblTasks table, the admin could determine who was
currently logged on and either send mail messages to those individuals, or if necessary, log them
out for maintenance purposes. The Admin could then lock users out of the application until maintenance
was finished. Of course, all of these actions could be accomplished from the Enterprise Manager and
Query Analyzer, but for end users, checking a checkbox and clicking a button is more intuitive than
typing text at a command prompt.
Page 3: Build the Utility
» 
IE 7
Firefox 2.0
Comment and Contribute
