Create Your Own Administration Tool for MSDE

Sunday Sep 10th 2000 by Danny Lesandrini

According to the Microsoft Office 2000/Visual Basic Programmer's Guide MSDE (Microsoft Data Engine) is a new technology that provides local data storage compatible with Microsoft SQL Server 7.0. This article discusses how to create your own administration tool for MSDE.

What is MSDE

According to the Microsoft Office 2000/Visual Basic Programmer's Guide MSDE (Microsoft Data Engine) is a new technology that provides local data storage compatible with Microsoft SQL Server 7.0. You can think of MSDE as a client/server database engine alternative to the file-server Microsoft Jet database engine. MSDE runs under Windows NT 4.0 or later and Windows 95 or later. It is designed and optimized for use on smaller computer systems, such as a single-user computer or small-workgroup server. MSDE doesn't limit the number of users who can connect to its database, but is optimized for five users.

Because MSDE is based on the same database engine as SQL Server, most Microsoft Access projects or client/server applications can run unchanged on either version. However, unlike SQL Server 7.0, MSDE has a 2-gigabyte database size limit, supports up to 2 processors for Symmetrical Multiprocessing (SMP), and in a replicated database environment cannot be a replication publisher for transactional replication (although it can act as a replication subscriber for both transactional and merge replication, and as a replication publisher for merge replication).

For the developer, this means that you can now create applications that leverage the power of SQL Server while providing your users with a robust, royalty free database. All of the familiar features of SQL Server are available in MSDE: tables, views, stored procedures, rules, constraints, triggers and even the security SQL Server model.

Page 2: How is MSDE administered?

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

Build the Utility

Although it is possible to reproduce any and all of the features of the SQL Server Enterprise Manager, our Admin Utility will include only the following functions:

  • Show/Edit Database Properties
  • Update Database Statistics
  • Run the DTS Import/Export Wizard
  • Manage Login Security
  • Execute Ad-Hoc SQL Statements
In order to accomplish the above tasks, we will need to make a project reference to the SQL DMO and SQL Namespace object libraries.

If the SQL Server client utilities have not been installed on your computer, then you will have to copy the necessary files to the appropriate folders and register them manually. (For more information see the heading Installation Issues and Appendix A, which contains the text of the Redist.txt file that ships with SQL Server.)

Our Admin Utility is simple, consisting of only one form named frmAdminMain. The form contains 4 text boxes for collecting login parameters, namely server name, database name, user name and password. Command buttons allow the user to connect and disconnect from the data source. Once connected, the Admin functions become enabled, as does a text entry box for submitting ad-hoc T-SQL statements for execution. It should be noted that the login supplied by the user must map to a valid SQL Server login with sysAdmin privileges, or much of the functionality will not be available.

Module level variables and constants are all declared with private scope.

' Database Connection Variables
Private strServer As String
Private strDatabase As String
Private strUID As String
Private strPWD As String

' SQL DMO & SQL NS Module Level Variables
Private oSQLServer As SQLDMO.SQLServer
Private oCurDB As SQLDMO.Database
Private oSQLNSObj As SQLNamespaceObject
Private oSQLNS As SQLNamespace
Private nsArray(10) As Long
Private varConnect As Variant

The following code is executed when the user clicks the Connect button and creates the SQL DMO and SQL Namespace objects. Note that the user supplied login parameters are passed in the SQL DMO server Connect method.

' Set SQLDMO object
Set oSQLServer = New SQLDMO.SQLServer
oSQLServer.LoginTimeout = 10
oSQLServer.ODBCPrefix = False

'Attempt a connection, then fill the properties stuff
oSQLServer.ApplicationName = "SQL-DMO Explorer"
oSQLServer.Connect strServer, strUID, strPWD
oSQLServer.Application.GroupRegistrationServer = ""
' Set Current SQLDMO Database object
Set oCurDB = oSQLServer.Databases(strDatabase)

Having attempted to create the SQL DMO server and database objects, you should now check the Errors Collection. If the requested database (or server) doesn't exist, an error will be returned. If no error has occurred, then continue the connect process by creating Namespace Object.

If Err.Number = 0 Then
    ' Create SQL Namespace Object and initialize it
    Set oSQLNS = New SQLNamespace
    varConnect = "Server=" & strServer & ";UID=" & strUID & ";pwd=" & strPWD & ";"
    oSQLNS.Initialize "EMS Admin Namespace", SQLNSRootType_Server, varConnect, hWnd

    ' Get a root object of type Server and walk down the hierarchy from there
    nsArray(0) = oSQLNS.GetRootItem

    ' Get first level server->databases
    nsArray(1) = oSQLNS.GetFirstChildItem(nsArray(0), SQLNSOBJECTTYPE_DATABASES)

    ' Get second level server->databases->database('pubs')
    nsArray(2) = oSQLNS.GetFirstChildItem(nsArray(1), SQLNSOBJECTTYPE_DATABASE, strDatabase)

    ' Get a SQLNamespaceObject to execute commands against on the wanted level
    Set oSQLNSObj = oSQLNS.GetSQLNamespaceObject(nsArray(2))
 MsgBox "Connection Failed!" & vbCrLf & vbCrLf & "Check parameters and try again.", vbCritical, "Error"
 ToggleConnectControlsEnabled False
End If

At this point, assuming that the user has provided valid login parameters, you have now instantiated the two objects necessary to accomplish any administrative task. Namely,

SQLDMO Database Object:		oCurDB
SQLNS Namespace Object:		oSQLNS

The SQLDMO database object can be used to execute any stored procedures, DML or DDL SQL statements. Consider the following syntax:

oCurDB.ExecuteImmediate ("sp_updatestats")
oCurDB.ExecuteImmediate (strTSQL)
The first statement runs the system stored procedure called sp_updatestats, which updates index statistics for all tables in the database. The second example runs a custom T-SQL statement passed in the string variable, strTSQL.

The QueryResults object is used in connection with the ExecuteWithResults() method to return a recordset. From this recordset object you may extract both metadata about the structure of the recordset and the data itself.

Dim qryResult As QueryResults
Set qryResult = oCurDB.ExecuteWithResults(strTSQL)

The SQLDNS Namespace object can be used to launch any of the standard Enterprise Manager dialog boxes and wizards. Some examples are listed below and a complete list of command arguments is supplied in the sidebar, "SQL Namespace Command Arguments.

oSQLNSObj.Commands("Data Export").Execute
oSQLNSObj.Commands("Manage SQL Server Security").Execute

The one last bit of business is to remember to unload all the objects you created in order to recover memory resources. I created a subroutine that is called from both the Disconnect button click and the Form_Unload events. The code simply closes the server object and sets all the objects to nothing.

Set oSQLServer = Nothing
Set oCurDB = Nothing
Set oSQLNSObj = Nothing
Set oSQLNS = Nothing

Because these dialogs and wizards are identical to those exposed through the Enterprise Manager, they encapsulate all the same functionality. You can provide your users with the ability to increase the size of the database and log files, as well as shrink, backup or restore the database. With this object model you can manage user logins and create maintenance jobs. All of this rich functionality is exposed with just a few simple lines of code!

The complete code listing for this utility is available for download at VB6msde001.zip. The finished utility includes code for toggling the enabled property for each control, depending on whether or not you are connected to a SQL Server database. Additional logic is provided to do some simple syntax checking for SQL statements submitted by the user to the SQL DMO ExecuteImmediate() method. Since testing text for malformed SQL statements could be an article in itself I will leave that for another discussion.

Page 4: Installation Issues

Installation Issues

The complete list of files required to install SQL DMO and SQL Namespace listed in Appendix A were taken from the Redist.txt file distributed with SQL Server. Pay special attention to the following instructions about the placement and registration of some of the key library files:

Installation Notes for Distributed Management Objects (DMO)

The sqldmo.dll file must be registered using the regsvr32.exe utility. Example: regsvr32 \mssql7\binn\sqldmo.dll)

The sqlwoa.dll file should reside in the system folder (i.e., \winnt\system32 or \windows\system).

Installation Notes for SQL Namespace (SQLNS) Files

The following files must be registered using the regsvr32.exe utility:


	Example:  regsvr32 \mssql7\binn\sqlns.dll

It has been my experience that these instructions need to be followed exactly. Placing even a single file in the wrong directory or forgetting to register any of the above mentioned files will cause a failure when trying to instantiate a SQL Namespace object. It seems that SQL DMO is more forgiving, but I still suggest that you distribute these components exactly as suggested by Microsoft.

In Summary

The Microsoft Data Engine (MSDE) provides a robust alternative for desktop applications currently using the JET database, file-server option. By designing applications for MSDE you pave the way for a more painless, hassle free upsize to SQL Server when that time comes. Thanks to the SQL DMO and SQL Namespace COM libraries, developers now have the ability to include all the administrative utilities necessary for your client to manage the database without requiring years of training or hours of coding.

In a future article, I will discuss what is required to perform a remote, unattended install of the MSDE database engine on a client machine.

See All Articles by Columnist Danny Lesandrini