|
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.
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.
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.
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
|