Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Sep 10, 2000

Create Your Own Administration Tool for MSDE - Page 2

By Danny Lesandrini

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

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM