Bill Wunder’s DDL Archive Utility meets VB.Net

By Bill Wunder

SQLDMO (SQL Distributed Management Objects) has been with us for a few generations of SQL Server. For me, it’s hard to imagine getting my job done without the power and elegance of SQLDMO. I use DMO in my backup processing, in maintenance of my warm standby servers, in countless DTS activex scripts, and to capture the DDL source on all my SQL Servers every night and check all changes into a SourceSafe Database.

The history of SQLDMO has been bumpy to be sure. When SQL7 came around it was necessary to pretty much ditch all the existing SQL 6.5 SQLDMO code. Then, with SQL2000, it was possible to keep using the SQL7 code, but the object model was expanded and some care was necessary to make sure that all SQLDMO code was producing the desired – and often different - results when the code was executed against a SQL Server 7 or a SQL Server 2000. More recently, Even when I changed my desktop to Windows XP I discovered that the VB6 implementation of my script generation utility choked when it was executed on this platform.

Knowing that I didn't want to do without the utility. my choices became quite clear. I could either stay stuck on Windows 2000 or update the code to VB.NET. Since it is well known that .Net’s Common Language Runtime is coming to SQL Server v.next, I decided that digging into VB.Net was a smart thing for me to do. Besides, I really like the XP desktop - in particular the Silver XP desktop and the fact that XP annoys all the Windows 2000 bigots.

I also identified some other changes that would result in a kinder gentler DDL Archive Utility. I wanted to make it easier to generate a delta report each day. The old method of loading the log file into a SQL Server and parsing the somewhat verbose output into a succinct list of changes was awkward and many others that I had shared the utility with found it difficult to set up. I wanted to be able to easily customize how new databases on a SQL Server would be treated. Previously the utility simply ignored new databases until I edited one of the .ini files scattered across the file system hierarchy produced to stage the DDL scripts on their way to SourceSafe. I wanted better error handling. I wanted and easier command line interface. I even wanted to see if I could make the DDL Archive Utility into a product good enough to supplement my kids’ college funds.

Finally, the product is ready. In the end, I decided to continue to make the Utility free to anyone that wants to use it. I did implement a licensing program that will require a small fee in order to access some of the value added features such as batch processing, DTS package change documentation, and support. As a bonus to all the intelligent readers of this article I am offering one of these full license free and with no strings attached. Please email me and I’ll tell send you the installation package and what information I need from you in order to process your free license.

Overview of the Utility

For those that are unfamiliar with the earlier versions of the DDL Archive Utility or those that would like to get an idea of what the new .Net version is all about, please read on. Bill Wunder's DDL Archive Utility is intended to be used as a vehicle to generate and archive Microsoft's SQL Server 2000 Data Definition Language (DDL) scripts into a Microsoft Visual SourceSafe repository. SQL Server does not provide a native integration with SourceSafe or any other source control application. While there are a number of products available that offer some level of integration between SQL Server development efforts and a Version or Source Control software application, the reality is that any user that can directly access a SQL Server can create and manipulate the DDL on that SQL Server. There is no product currently available that can truly lock users into using only the source control access path when making DDL changes on a SQL Server.

The result often is that the current state of the stored procedures and table structures on a SQL Server is unknown and therefore ultimately irreproducible should that become a necessity.

Obviously, in the ideal software environment, changes would be well documented. In the real world, rapidly changing needs and the necessity for rapid changes to resolve pressing issues regularly occur that may not get the proper testing and don't get integrated with the standard source control system in many shops. To provide a layer of protection and documentation for such changes, a tool is required that will regularly extract the actual DDL from a production system and store it for safekeeping. This is the forte of the Archive Utility.

Even at the development level there remains a risk that objects currently under development are not properly stored in the standard source control system at all times. There is a risk that the development DDL can become damaged, corrupted, or even modified to an undesired state before it is properly checked in to the development source control system. While it is possible to restore from last know good backup in such cases it is as often as not an expensive recovery path in terms of time and productivity and can result in as many changes being lost as are recovered in a heavily used and fluid development environment. Often it would be useful and many times faster to be able to quickly retrieve a script to recreate such damaged items from an object archive and repopulate any pertinent lost test data.

Whether in production or development it is not uncommon to discover a need to rollback a change or a set of changes on a server. Trying to use the source control in use by development staff, even in the rare case where use of source control is impeccable can be problematic because of the iterative and changeable nature of software development. Often it is difficult and overly complicated to determine which version to roll back to in a forward looking development source control repository. The last thing you want to happen in a rollback situation is a compounding of software problems by rolling back to the wrong version. If you know what day the structure or procedure was last correct you can easily determine the correct version in the Archive Utility generated SourceSafe Repository. The Archive Utility accurately shows the objects and the sequence by datd of exactly what has been on the SQL Server.

There are several additional use cases that support the need for a DDL archive of every SQL Server in every software development environment. Bill Wunder's DDL Archive Utility offers a highly consistent archival solution to meet this wide variety of needs at a very reasonable cost and with little to no drain on the time of a shops expensive DBA staff. The Archive Utility can be easily configured to automatically archive selected DDL from a SQL Server using a wide variety of timers and tasks as a driver. For example, the Scheduled Tasks component on a Windows workstation or server can script and archive all SQL Servers in a shop in unattended mode during off hours to effectively leverage the hardware and save the DBA for critical highly skill requirement tasks.

Tracking Database structural changes

Unlike scripts produced through SQL Server's Enterprise Manager and The Query Analyzer's Object Browser, the scripts produced by the Archive Utility will always be the same each time they are generated. The scripts will completely and accurately reproduce the database object that they document. With other tools the number of options and settings is not fixed and with these other tools script generation is always a manual process so there is a significant chance for phantom differences reported by SourceSafe due to a missed option or setting and also a drain of time and energy in identifying and "signing off" on these phantom differences.

While the Archive Utility will always script an object with the same options and setting, it is flexible in that it allows you to specify which databases you want to archive and which types of objects you want archive in each database. For example, there is no value in archiving the "pubs" database. Likewise, It would not be useful to archive the tables and procedures in the "msdb" database, but you may want to track the users and roles in "msdb". The Archive Utility does not force you to spend CPU cycles and storage space archiving items that are not valuable to your circumstances. Instead it accepts the settings you specify and remembers those setting for each subsequent archive operation.

If you do need to change the configured settings of what is and is not being archived, it's good to know that making the changes is quick and easy using the Interactive Configurator. It's also good to know that changes to the configuration are tracked and stored in SourceSafe along with generated DDL scripts so you'll always have a full history not only of what DDL changes occurred and when, but also which objects where being tracked and when changes occurred to the tracking configuration.

Straightforward yet Modular Component Architecture

The Archive Utility includes an easy to use Interactive Configurator component that doubles as an interactive archive interface as well as the CLR based console application interface for command line usage. Both interfaces use a common library for all SQL Server scripting and SourceSafe check in activity. All compiled components are written in VB.NET CLR managed code using SQLXML3.x and ADO.Net are combined with Interop masking of SQLDMO COM based technology to talk to the SQL Servers and Interop marshalling of the SourceSafeTypeLib to create a robust and seamless heterogeneous execution environment.

The Interactive Configurator can be used free of charge for your complete evaluation. In order to access the console interface the utility must be licensed.

Always use the Interactive Configurator to create or modify the Archive configuration for a SQL Server. Checking a check box in either of the Interactive Configurator’s data grids will cause the COM collection – SQLDMO or SQLNS in the case of DTS Packages - identified by that check box to be scripted. Un-checking a check box will suppress scripting for the collection identified by that check box. Un-checking all collections for a database will cause that database to not be considered for archive. Note that you can disable the SourceSafe Check in and only generate scripts to the filesystem by selecting "None - Script Only" from the SourceSafe Share combo box list.

Database Components

A handful of stored procedures and tables are necessary to store and maintain the Archive Utility's configuration data and also to log all actions taken by the utility. All components are defined by the script instArchUtil.sql located in the application folder. It is recommended that users not modify the structure of the tables or the stored procedures used by the Configurator to access and maintain the data. It is also recommended that users make full use of the logging history stored in the table ArchUtilChanges. A couple of example queries to extract useful changes from this table are included in the script and created on each SQL Server when the Archive Utility is installed. Additional scripts will be made available to licensed users.

I have enjoyed many expressions of appreciation on the earlier version of this Utility. It is my opinion that the new .Net release is greatly improved. I’m hopeful that all of you will take the time to check out this new version and let me know what you think.

 

Bill