5-309 1 2

The second development tools session focused on SQL-DMO, SQL-DMF, and SQL-OLE.  I don't get into the DMO side of things, but after seeing some of the things that can be done to combine with what I already know, I'll start back into learning VB so that I can leverage this.

There are two ways of managing your servers.  You can either utilize T-SQL or you can use SQL-DMF (SQL Distributed Management Framework).  SQL-DMF is divided into three layers that encompass all of the functionality you would need to access any part of SQL Server.

SQL-DMO, residing at the middle layer of SQL-DMF, provides a flexible and powerful interface into SQL Server.  It consists of 4 subsystems, core, scripting, SQL Agent, and replication.  At the core of SQL-DMO reside all of the objects contained within SQL Server including the server itself.  SQL-DMO is nothing more than a set of COM interfaces used to administer and manage SQL Server.  It contains 140 interfaces, 3000+ methods/properties, and over 7,520 entry points into SQL Server.  It is a thread safe, in process COM server that has no user interface.  Internally, it is 100% Unicode.  Anything that you can accomplish via other means can also be done via SQL-DMO and it is the integration point into SQL Server for any other product.

You can host SQL-DMO in custom interfaces written in/for tools such as Visual C++, VB, MTS, and ASP.  You can use automation via IDispatch with things such as Perl, VBScript, JavaScript, and MS Office products.  It consists of a core set of components as follows: sqldmo.dll, sqldmo.rll (type library and language dependent resources), sqlsvc.dll (database layer), sqlsvc.rll (language resources), sqlwoa.dll (Unicode redirection layer), sqlrsld.dll (resource loader), and ODBC.

SQL-DMO utilizes a metadata cache in order to speed up operations.  The first call to a collection causes the metadata to be cached.  This can be an expensive operation when first accessing a collection, but subsequent calls occur very rapidly, because the data is already available.  The QueryResults objects are also cached.   If you need to rebuild the cache, simply call the refresh method of a collection which causes the cache to be invalidated and reloaded.  The cache remains as long as a connection to SQL Server is open and the SQL Server object is valid.  It is destroyed when you close the connection and destruct the SQL Server object.  SP2 for SQL Server 7.0 is supposed to add a destruct of the cache when the object handle is destroyed.

A large part of this session was code and demos which I won't try to reproduce here.   The first demo simply showed how to backup all databases on a server.  Within this was also some insight into the system tables.  A status of 992 means a database is in recovery mode.

When writing your code always start with the SQL Server object since it is the root of everything.  Use the hierarchy to get your application object.  Keep in mind that internally, everything is Unicode.  When testing the server version, start with SQL-DMO.  Issuing a PingSQLServerVersion will return the version information for SQL Server.  Do not call refresh within a for each loop since you will destruct the cache each time you do this and be forced to rebuild in constantly.  Only by issuing a disconnect will you release the metadata cache.  Utilize all of the callback methods when using events.  Do not use QueryResults within DMO for ad hoc queries as it is much more expensive than using something like ADO.  When you have to mix and match within your application, always use a namespace qualifier to distinguish between SQL-DMO and SQL-OLE.

5-309 1 2