SQLClue Configuration Compare is an elegantly powerful easy to use and always free tool to quickly understanding SQL Server database engine configuration & DDL metadata differences between two locations.
SQLClue Configuration Compare target locations include SQL Server instances, SQLClue SQL Configuration Archive repositories, and T-SQL script files. Configuration Compare can target any two instances of SQL Server 2000, 2005 or 2008 and scripts generated from the any of these SQL Server versions. When used in conjunction with one or more SQL Configuration Archive repository, the Configuration Compare tool set will show the differences between the configuration & DDL on a SQL Server instance and any previous version of any configuration item for that SQL Server instance or any other SQL Server instance that has been saved to SQL Configuration Archive. This provides complete information about what has changed on a SQL Server instance, how two SQL Server instances are different, and exactly what is needed to revert a change should a rollback be necessary.
Compare:
current SQL Server item to current SQL Server item
SQL Server to selected SQL Configuration Archive item version
current SQL Server item to Script File
selected SQL Configuration Archive item version to selected SQL Configuration Archive item version
selected SQL Configuration Archive item version to Script File
Script File to Script File
Both SQLClue Workstation and SQLClue Server installations include all Configuration Compare component tools. The configuration items compared are derived from the scripting engine of Microsoft's SQL Server Management Objects (SMO) managed .NET object model. SQLClue Configuration Compare exploits the predictable relationship between the SQLClue SQL Configuration repository hierarchy and a SQL Server's SMO hierarchy. The well documented SMO object model helps to produce the most accurate and meaningful compare results possible. For example, only type matched SMO items and collections are comparable and all SMO Scripting Options can be allied as needed for optimal comparison relevance.
[Image 1]
[Image 1] shows the Configuration Compare UI before a compare is performed shows the mirror image compare panes. Each pane includes a sub-panel used to select items or collection to compare and a sub-panel to display the compare results. Each time SQLClue is launched, the static usage tips and Quick Start information in this image are shown in the comparison results panes. [Image 2] shows a completed compare.
[Image 2]
In addition to rigorous SMO object model integration, comparison items can be filtered and compare content can be fully matched and filtered using REGEX conformed Regular Expression. A dynamic dialog is used to configure SMO scripting options, Regular Expressions and all other Configuration Compare output options. In [Image 3] some of the available options are shown.
[Image 3]
When possible, configuration items are compared and presented at the user interface as T-SQL scripts. Items not readily scriptable, (e.g. Active Directory" configuration, Policies, Certificates, etc.) are rendered and compared as standardized textual name-pair tabulations.
Example of a database certificate documented as property name-pairs:
ActiveForServiceBrokerDialog = True
ExpirationDate = 12/11/2010 4:42:47 AM
ID = 256
Issuer = Encryption of DBA sensitive values
Owner = dbo
PrivateKeyEncryptionType = MasterKey
Serial = 0c 3b 3b 6d 32 d3 d4 8c 43 ef 71 b9 3d 4a 37 af
Sid = 0x1600000910003B6B236F3FB58E14D6C72FDE9E7C2B45BDFD62C
StartDate = 12/11/2009 4:42:47 AM
Subject = Encryption of DBA sensitive values
Thumbprint = 0x3B6B236F3FB58E14D6C72FDE9E7C2B45BDFD62C
LastBackupDate = 1/1/0001 12:00:00 AM
PolicyHealthState = Unknown
Every time an object or collection (e.g. table, users, etc.) is compared using the SQLClue UI rich text environment, an HTML rendering of the compare is also generated in the background. This HTML compare rendering can be easily shared with others. By default the HTML is directed to the current user's "Documents" folder on the SQLClue host. The same HTML document is overwritten during each compare to avoid an unneeded accumulation of files that you do not need. Check out this sample HTML output of the stored procedure compare shown in [Image 2].
Comparisons between two SQL Server Instances, a SQL Instance and a SQLClue Configuration Archive repository, or two items, collections or hierarchies from the same or two different SQL Configuration Archive repositories are possible. Repository items are archived with the same script or standardized textual name-pair tabulation as used by the Configuration Compare component.
Files from the file system can also be compared with the Configuration Compare. One interesting use I have found is to compare the .showplan XML output taken from the plan cache DMV output or from the graphical show plan within SQL Server Management Studio - makes seeing a subtle difference in a complex query plan a snap. There are numerous file compare scenarios where the Configuration Compare can make your work easier. Once you use it a few times you will begin to see new possibilities everywhere.
Compares can be completed in "batch mode" or interactively. Hierarchical compares (e.g. all collections and items in a schema, database or server) are supported in batch mode only. Simple compares of items or a single collection may also be conducted using the SQLClue user interface (UI) or via batch execution mode. For example, a comparison of the tables in a given database on two servers can be done interactively. However, if the two databases under consideration have thousands of tables with many differences, the batch mode compare would be more appropriate. A compare can be a long running operation; each is executed on a WinForms BackgroundWorker thread whether run interactively or in batch mode. This allows the SQLClue user to continue with other tasks within SQLClue or elsewhere on the desktop while awaiting the compare results. The duration of compare is a function of the number of items being compared and the size of each item. However, some SQLClue compares with interactive support - in particular when the "With Dependencies" SMO scripting option is enabled or when the number of objects in a collection is large - may require several minutes to complete. Single item compares will complete in a few seconds.
The SQLClue Configuration Compare is unique from the many other SQL Server metadata comparison tools available. The most important difference being the SQL Configuration Repository support for historical compares. In addition, SQLClue Configuration Compare includes scripting and formatting flexibility not found in aggregate in other compare tools. For example, .NET Regular Expressions can be used to match object names and within metadata text during the comparison of an objects properties or DDL; results can be shown in various levels of detail; and result lines can be numbered. It is difficult to imagine a valid compare scenario not supported by SQLClue's Configuration Compare.
No data or configuration is ever added, changed or removed on any SQL Server instance targeted by a SQLClue Configuration Compare
After downloading SQLClue, see the Configuration Compare Tutorial included in the help documentation to get started. Within minutes you will know how to quickly complete the most powerful and precise SQL Server Configuration & DDL compares available.
This page was last modified on Saturday, February 13, 2010 10:30:53 AM