How the Utlity can Help You
Use Cases for the Archive Utility
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 2000 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 versioning 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. This means that no matter how hard you try to procedurally enforce a requirement that all changes to a database must be saved in source control, the source repository and the database can and will become different over time. 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 not re-creatable 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 the 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 data and structural changes being lost as are recovered in an active production or 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 thus far unheard of 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 date of exactly what has been on the SQL Server.
In many shops it is desirable to maintain an accurate record of structural components to turn over to the various auditors that might become interested. With Bill Wunder's DDL Archive Utility's historical precision even the most diligent auditor will be justifiably impressed with your record keeping.
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 more importantly 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 licensed 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, assuming you follow acceptable SQL Server best practices, 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 these databases. 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 managed console application interface for command line usage. Both user interfaces employ a common class library for all SQL Server scripting and SourceSafe check-in activity. All compiled components are written in VB.NET. Managed code using SQLXML3.x and ADO.Net are combined with necessary 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. (See the Licensing Help pages for more information.)
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 SQLDMO - or SQLNS in the case of DTS Packages - collection 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 check-in and only generate scripts to the file system by selecting "None - Script Only" from the SourceSafe Share combo box list. Also note that if a database is included in archival processing but for any reason other than simply not there is unavailable - such as being set to offline or in single user mode or suspect or in emergency mode - no archival operations will be performed on that database until it returns to normal or is completely removed from the server.
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 number of example queries to extract useful changes from this table are included in the installation script and created on each SQL Server when the Archive Utility is installed. Additional scripts will be made available to licensed users. And of course you can always write your own when your needs are specific to your shop. Refer to the Reporting Help Document for additional details.