For many years I have made a practice of scripting every table, procedure, trigger, user, role and permission in every database on the SQL Server’s I administer. More recently I have even become quite fastidious about storing those scripts in SourceSafe. This practice has provided me a failsafe level ability to complete every structural rollback that has been necessary and it has proven to be a most useful tool in identifying problems on a server.

The DBA retroactively scripting DDL is clearly no replacement for sound development practices. It is simply a necessary redundancy. The problem really is that SQL Server does not provide a native integration with SourceSafe or any other source control application. This can easily result in a breakdown or miscue in the developers’ practice of checking scripts into SourceSafe as they are introduced at some predetermined point in the software lifecycle. Let’s face it, many developers these days are working close to - if not in - sweat shop conditions. Any time there is a manual multi-step procedure like keeping SQL Server stuff in SourceSafe, the heat of the moment is bound to produce inconsistencies.

Simply scripting things and keeping them on the file system just isn’t good enough. While I would advocate that you as a DBA would be better served regularly scripting database objects and saving them to the file system than not, there are a few serious limitations to such an endeavor. First, it can be very time consuming, especially if you have to rely on the scripting engine as presented in SQL Server Enterprise Manager or Query Analyzer. Lots and lots of click and wait…  Secondly, it is very cumbersome to maintain a historical record of change in the file system. You end up with multiple script copies and a logistics nightmare that only gets worse as time goes on. When the day rolls around that you need to see when something changed, you’ll find yourself with many text files open on the desktop in search of a needle in a  haystack. Finally, scripting from SQL Server Enterprise Manager or Query Analyzer - and many of the third party tools available - leaves you vulnerable to inconsistencies from one script generation episode to the next. Enterprise Manager likes to produce Scripts differently than Query Analyzer and in both cases you have to make sure you pick the same scripting options – and there are several – each time you script a database’s DDL. Query Analyzer is a bit friendlier about remembering scripting options and Enterprise Manager will let you do all objects in a database at one time. Wouldn't it be great if have the benefits of both?

Using an automated tool that will consistently use the same options is going to make your life easier in the long haul. That way you can compare apples to apples regardless of the ‘diff’ tool you use to research a change. Using the SQLDMO object model to put together a standard scripting method is a project well within the technical grasp of a DBA with only rudimentary VB skills. Additionally, there are a number of scripts available on the internet that have already done the coding work for you.

What doesn’t seem to be easily found on the internet and is not quite so easy for the VB capable DBA is putting together the code to check those scripts into SourceSafe or what ever source control repository is in use in your organization. With SQL Server 7.0 I had pretty good luck using the SP_OAs (the OLE Automation system stored procedures) to generate my scripts and then invoke the SourceSafe command line from the xp_cmdshell interface to automate script generation and archiving to SourceSafe. I could put this all into some stored procedures and then run the job from the SQL Agent at night when no one was around. For those of you still in the SQL 7 world, you can find a pretty exhaustive discussion and a working set of scripts at http://www.nyx.net/~bwunder/dbChangeControl/body.htm.

The limitations of the SP_OAs in terms of memory leaks with SQL Server 2000 forced me to look for alternatives to this T-SQL scripted method. (see my sswug.org article, “Automation and T-SQL” for more discussion of the problem I’ve encountered when using the SP_OAs with SQL Server 2000) I didn’t want to go without the benefits of having a DDL archive in SourceSafe, so I set about the task of moving to a VB based solution. I was able to get a significant performance improvement from VB through conversion of the SourceSafe command line interface activities to the SourceSafe API. I also improved the granularity of the scripting options. With T-SQL, I simply took everything that was on the SQL Server. With the VB solution I was able to easily say which databases I wanted to script and which SQLDMO collections I wanted to go after. (In SQLDMO collections are such things as tables, procedures, users, etc. see the BOL chapter on “Building SQL Server Applications” for a full run down on SQLDMO). And I was also able to track and even generate automated reports of the changes that were occurring on any given SQL Server.  A couple of years ago, I mad the VB tool available as a freeware download. A slightly dated version is downloadable from www.sqlservercentral.com as “Bill Wunder’s DDL Archive Utility” . Over time I've made several enhancements to the tool so if you have been using it for a while you may want to check out the most recent version. If you work in a Windows 2000 environment and use SourceSafe for source control and you’re looking for a highly effective fast track method to getting you own DDL archive in operation I heartily encourage you to investigate this tool. Drop me an email if you are interested.

With Windows XP and Windows 2003 much of the VB6 and the Windows Scripting Host functionality in that utility are not well supported, so as I move to those environments I’m finding a real need to move the Archive Utility to .Net. I have released a new version of the Archive Utility well suited for Windows XP and Windows 2003 with several really cool new features. The .Net release is entirely database driven, even more user friendly, and will produce the best scripts yet!

Watch this space to be among the first to get a chance to experience it! I'm planning to put together an article to describe some of interesting Common Language Runtime (CLR) SQL Server integration, XML and .Net COM Interop adventures I've been through in getting this little piece of software out the door. Some of the stuff we're all going to want to know more about as Yukon approaches.

Bill