Red-Gate recently released their SQL Comparison and Synchronization Toolkit, a COM library that they used to build both SQL Compare and SQL Data Compare products. That means you can now access the functionality provided by the applications directly rather than having to go through the user interface. That's a powerful concept and one worth exploring!
The toolkit contains a 49 page Word document that goes into good detail explaining how to use the COM object. If you've used VB with COM before I think you'll find the object model pretty straight forward. The toolkit also includes four sample applications, two standard VB apps that are simplified versions of SQL Compare and Data Compare, and two command line versions of those same apps. Complete source code for all four included! Here is what the two graphical versions look like:
If you're not familiar with the full versions, I've done reviews of both SQL Compare and SQL Data Compare. I've used both tools for several projects and have been very pleased with the results. In particular I think SQL Compare is THE tool for a DBA to have - why waste time generating change scripts when you can have a tool do it for you?
So why would you need the toolkit? If you use these products sooner or later you'll find yourself wishing for the ability to run them as a job. Maybe to automatically deploy changes from a test server to a QA server nightly. Or to sync the data on your test server with the data on your live server so that your testers always have good data to work with. The toolkit is the difference
between Enterprise Manager and T-SQL/DMO - sometimes you need more control than a graphical tool allows. I'll be honest, I wish ALL tools were built this way. Nothing better than getting to the point where you need to build an automated solution and discovering an object library that you can use from VB! This is the approach Microsoft takes with most of their applications (Word and Excel are great examples). Here is a look at what the toolkit looks like in the VB object browser:
Here is a small portion of the sample code included in the toolkit - this shows the code used to generate the 'change' script:
| ' Create a new EventSink object. This gives a standard way of receiving ' Status events for all processes. See m_obEventSink_Status Set m_obEventSink = New SQLDataCompareLib.EventSink With obUtils' Must set the event sink property to this forms object Set .EventSink = m_obEventSink ' Get the update scripts, passing in the comparing databases, link database to use, ' a collection of TableDifferences to create scripts for, the strings to apply ' the two scripts to, the global options settings and the amount ' of records to script. This produces two scripts, one to make ' the data for selected tables the same in database2 as database1 and vice-versa. .GetUpdateScripts m_obDatabase1, m_obDatabase2, m_obDatabase3, coTableDifferences, sLeft, sRight, g_lOptions, g_lRecords End With |
Pricing on this product isn't cheap - $4950 for the first year license and $2950 for annual renewals. In addition to the license and support, you also get a 10 user license for their Compare bundle - a $1900 value. Terms of the license are pretty important for a product like this since they are selling the 'engine' that their product is built on. The purchaser agreement states that you are restricted from making a product that would compete directly with any sold by Red-Gate. You can take a look at the full details of the license here.
