SQL-DMO part 1: The SQL-DMO API

Problem/Question/Abstract:

This is the first part of a serie of articles about the SQL Distributed Management Objects known as SQL-DMO API. In this first article I'll talk about SQL-DMO, whats the purpose of it and what you'll gain if you use it.
I will also show how to install it, so you can use it within our Delphi projects.

Answer:

Some notes

Parts of the following two topics were copied from Microsoft SQL Server Books Online (BOL), because they are already very descriptive, and I would not write it better.

What is SQL-DMO?

From the Microsoft SQL Server Books Online:

SQL Distributed Management Objects (SQL-DMO) encapsulate the objects found in Microsoft® SQL Server™ 2000 databases. SQL-DMO allows applications written in languages that support Automation or COM to administer all parts of a SQL Server installation. SQL-DMO is the application programming interface (API) used by SQL Server Enterprise Manager in SQL Server 2000; therefore, applications using
SQL-DMO can perform all functions performed by SQL Server Enterprise Manager.

SQL-DMO is intended for any Automation or COM application that must incorporate SQL Server administration, for example:
Applications that encapsulate SQL Server as their data store and want to shield users from as much SQL Server administration as possible.
Applications that have specialized administrative logic incorporated the application itself.
Applications that want to integrate SQL Server administrative tasks in their sown user interface.

All SQL-DMO documentation is available upon the installation of SQL Server within Microsoft Books Online (BOL), and many samples are available under the \Program Files\Microsoft SQL Server\80\Tools\DevTools\Samples\sqldmo directory.

If you use Microsoft SQL Server Desktop Engine (MSDE), you can see that you don't have the necessary administrative tools. SQL-DMO is a option to create a managment tool for MSDE. Of course that there are around several tools to do that (including Microsoft SQL Server Enterprise Manager), but it's not a bad option we build our own administrative tool, because it's always a good way to learn some more!

SQL-DMO availability

SQL-DMO uses the Microsoft® SQL Server™ ODBC driver to connect to and communicate with instances of SQL Server. Stored procedures supporting SQL-DMO are installed on each instance of SQL Server.

SQL-DMO clients require one of these operating systems:
Microsoft Windows NT® version 4.0 (Service Pack 5 or later).
Microsoft Windows® 98 or Microsoft Windows® 95.
Or
Microsoft Windows® 2000.

SQL-DMO clients require SQL Server ODBC Driver, version 3.80 or later, which ships with SQL Server 2000. The client network library must be properly configured.

SQL-DMO locates instances of SQL Server using the SQL Server instance name.
SQL-DMO does not use ODBC data source definitions for connection, and you need not use the ODBC Administrator to create data source definitions for servers administered by SQL-DMO applications.

Stored procedures that support SQL-DMO are created as part of an instance of SQL Server 2000. The Transact-SQL script Sqldmo.sql is shipped with SQL Server 2000 and can be used to reinstall the required stored procedures if necessary.

All required SQL-DMO components are installed as part of an instance of Microsoft® SQL Server™ server or client. SQL-DMO is implemented in a single dynamic-link library (DLL). You may develop SQL-DMO applications on either a client or a server. When using an OLE Automation controller as a development platform, such as Delphi, no additional files are required. You can also use the SQL-DMO header files, but they need to be translate to Delphi first.

I don't see an obvious advantage about using the SQL-DMO header files, and seems to me that it's much easier just use OLE automation.

SQL-DMO sample applications, providing additional reference material for SQL-DMO application development, are included with SQL Server.

To register the SQL-DMO components on a client computer
From C:\Program Files\Microsoft SQL Server \80\Tools\Binn\Resources\<language> directory, execute:
\Program Files\Microsoft SQL Server \80\Tools\Binn\REGSVR32 SQLDMO.DLL
From any directory, execute:
C:\Program Files\Microsoft SQL Server \80\Tools\Binn\REGSVR32.EXE

C:\Program Files\Microsoft SQL Server \80\Tools\Binn\resources\1033\SQLDMO.RLL

Delphi instalation

Because you'll be using SQL-DMO with OLE automation, you need to import a type library for it.

This is done by using the 'Import Type Library' function under the 'Project' menu.
Then select the 'Microsoft SQLDMO Object Library (Version 8.0)' for SQL Server 2000 and install it as VCL Components or just create the interface units. Just choose the option you prefer. Personnaly I prefer just creating the unit and don't install it in the VCL palette.

This will create a unit of about 2.5Mb (about 65k lines) taking several minutes (about 6 minutes in a PII 400Mhz) to create it.

Interfaces versus wrapper classes

The beauty of wrapper classes generated by Delphi is that they are easy to use like a regular VCL component or class.
Normally a wrapper class joins the main interface and a event sink interface (if it's available). While the main interface has the main properties and methods, the event sink interface gives us the events part.
So instead of having to work with separated interfaces, we have both options wrapped together in a class.

Nevertheless, a problem arises. After doing some research I concluded that it's better not to use the wrapper classes generated by Delphi. Why this?
The problem, is that several functions I checked were incorrect. In some cases was code that was missing, in other cases was the existence of incorrect code.
The best option now passes by using the interfaces directly, or then do a complete verification (probably rewrite) to the imported type library so it could be correct.
Anyway, I doubt that anyone out there has the patience to verify 65k lines of code.

This is not a big problem. The direct use of interfaces it's not difficult and it's really straight forward. The problem here concentrates now in the use of the event sink interfaces.
To solve this problem, and after reading this article I decided by using this utility that generates classes for the event sink interfaces.
In the article you can read that the objective of these classes is to give the same functionality that Visual Basic WithEvents keyword gives.

You end up by having a SQLDMO_TLB.pas generated by Delphi and a SQLDMOEvents.pas generated by EventSinkImp. Both units are included in the samples package. Just remember that these units are for use with MSSQL 2000.

The first sample - "The login dialog"

Great part of the samples I will provide in the several parts of this article are based on the samples provided with SQL Server DevTools located in the \Program Files\Microsoft SQL Server\80\Tools\DevTools\Samples\sqldmo directory.

Because in every sample I will provide we need a connection to the server, it's logical that the first sample should be the connection creation and validation.

This is a generic login dialog to create a connection to a database server using SQL-DMO API. Basically, it shows a list of available servers and asks the user to input the connection parameters. If the connection it's established it returns a connection handle to be used in the process.

Personnaly I like the login dialog used by the Query Analyser, because it provides a simple but complete way of connecting to a SQL Server. This login dialog gives you the option to connect to a SQL Server using windows or SQL Server autenthication, and you can choose between a listed SQL Server or specify one.


The login dialog has two major functions:

the function where we'll get the available servers

procedure TfrmLogin.GetAvailableSQLServers(aStrings: TStrings);
var
  lServers: NameList;
  i: Integer;

begin
  // Clear servers list
  aStrings.Clear;

  // Obtain servers list
  lServers := FSQLServerApp.ListAvailableSQLServers;
  for i := 1 to lServers.Count do
    aStrings.Add(lServers.Item(i));
end;

this function will use the FSQLServerApp interface function ListAvailableSQLServers that returns a name list containing all active SQL Servers. Tests I've made seems to garantie that this function doesn't really on registered SQL Servers, so it's just what we really need.
After getting the available SQL Servers, you only need to save their names, in this case in a string list for posterior use.

One important note is that SQL-DMO colecctions have start index 1 and not 0.

the function where we'll connect to the specified SQL Server

procedure TfrmLogin.btnOKClick(Sender: TObject);
begin
  FSQLServer.LoginTimeout := -1; // -1 is the ODBC default (60) seconds
  FSQLServer.ApplicationName := Forms.Application.Name;
  FSQLServer.LoginSecure := rbWindows.Checked;
  FSQLServer.AutoReConnect := False; // Do not reconnect automatically

  // Connect to the Server
  FSQLServer.Connect(cbServers.Text, edtLogin.Text, edtPassword.Text);
end;

FSQLServer.ApplicationName setting has the purpose of helping to identify our application within SQL Server processes enumeration.
another thing is that we don't need to have two diferent connection statements because when FSQLServer.LoginSecure is specified, the login and password are ignored in the FSQLServer.Connect.

After a successful connection, we have a valid FSQLServer handle available for all operations we need. This way, it's useful to have a public property making this interface available for operations within our application.

Regularly we need to know if the connection is available, and we do this verification by using the FSQLServer.VerifyConnection(SQLDMOConn_CurrentState). Due to the high usage of this function I preferred to encapsulate this function in another public property:

property Connected: Boolean read GetConnected;

function TfrmLogin.GetConnected: Boolean;
begin
  Result := FSQLServer.VerifyConnection(SQLDMOConn_CurrentState);
end;

Now, we can used it like this:

if frmLogin.Connected then
  DoSomething;

"Events"

SQL Server sends messages, warnings and errors in the form of events. If some of them we don't need or care, others are very important to us.
For the purpose of having a generic way of catching all the events sent by SQL Server I prefer to do a generic form (created in the login form) that will catch the events for all the connected interfaces.

The events form is very simple having just one memo where the events will be written and the available sink objects.

The creation of this form is made in the login form creation

procedure TfrmLogin.FormCreate(Sender: TObject);
begin
  // Create an instance of the SQL-DMO Application and SQLServer interfaces
  FSQLServerApp := CoApplication.Create;
  FSQLServer := CoSQLServer.Create;

  // Events form
  FEventsForm := TfrmEvents.Create(self);
  FEventsForm.ServerSink.Connect(FSQLServer);
end;

being the FSQLServer interface attached to the ServerSink object.

This way we have a form

that will provide a central point for event analysis.

Conclusion

In the first part of this article you learn:
what SQL-DMO is
what are it's advantages
and how to use it
how to connect to SQL Server
how to use the messages sent by SQL Server using the events sink interfaces wrapped as simple objects

all this groupped in two generic units that will be used on following samples.

In the second part of this article I'll show how to backup databases using SQL-DMO and also use events to show backup progress.

You can download the code referent to this article here.