The SQL Server Administrator's Console

The SQLClue Configuration Archive monitors and stores SQL Server configuration changes. Not only are the usual suspects: changes to Data Definition Language (DDL) Items such as stored procedures, functions, tables and triggers included, but also all other database engine, server and database options and settings such as the encryption hierarchy, certificates, credentials, Service Broker and Database Mail. The result is a complete and fully versioned history of database engine change.

 

 

When targeting SQL Server 2005 and SQL Server 2008 Event Notifications can be enabled to provide a complete database change audit history of the SQL Server. Predetermined Changes will raise SQL Server Events that fully describe the change. The event data is immediately captured on a queue at the server where the change occurs. Information such as the original login making the change and the actual statement used to effect the change remain available. The queued changes are processed to the Archive using a schedule that suits the needs of the organization.

 

[Image 1]

 

In [Image 1] the queue of changes on a SQL Server awaiting archive into the SQLClue host data store is shown. One event from the queue - the creation if a Full-Test Index on a table - has been selected and is displayed in the DDL Event Viewer. Changes can be seen in the queue immediately after the change occurs. In [Image 1] this index can be seen to be part of a CREATE TABLE operation. Creating the table actually involves multiple DDL events. The CREATE TABLE is a DDL event. Each CREATE INDEX is a DDL event, CREATE TRIGGER and CREATE PARTITION would also be DDL events. When the events are processed into the SQL Configuration Archive only one document is associated with the table definition. That document will include the parts of the table created by each event. The Archive record that contains the document will also contain all of the DDL events as XML. You can view the text of the archive document for the table above here and the Event Data stored with the record here.  The example document files were produced using the "Copy Definition to Clipboard" and "Copy EventData to Clipboard" capabilities of the SQL Configuration Catalog report shown in [Image 2]. That is how easy it is to get scripts of the archive that pasted into the SSMS query tool and executed if, for example, a stored procedure needed to be rolled back. 

 

The reporting system is integrated with the compare tool. Drill through report paths lead through changes by SQL Instance, Date, collection, item or textual content. A complete catalog is of change is exposed through the application's Visual Studio 2008 ReportViewer control implementation or optionally from a Report Server using the included rdl files. The ReportViewer based reports offer an interactive research experience while the Report Server based reports provide a slightly less rich experience to a wider audience.

 

[Image 2]

 

The robust and powerful Configuration Compare tool is also integrated with the SQL Configuration Catalog. If a document has multiple versions, the versions can be compared using the control seen at the upper right in [Image 2]. Of course the Configuration Compare also supports direct compares between two SQL Servers, compares between a SQL Server and a Repository Archive, and even compares of source control "working folders" files and a SQL Server or Repository.

 

The right way to use SQLClue depends upon which of the many ways it can be used fits your needs. It would be my pleasure to provide my advice and ideas on how SQLClue could best be used in your scenario. Just drop me an email and briefly describe your scenario or ask any questions you might have about using SQLClue.         

 

The SQL Configuration Archive is a next generation replacement for Bill Wunder's DDL Archive Utility. The SQL Configuration Repository captures all DDL that was handled by the Archive Utility. In addition, the SQL Configuration Repository captures all SQL Server objects new in SQL Server 2005 and SQL Server 2008 plus all non-scriptable SQL Server database engine configuration settings and properties. See a complete feature listing and functional comparison of SQLClue and the DDL Archive Utility here.