[Edit] ***** The beta has now been released. Please visit http://www.red-gate.com/messageboard/viewforum.php?f=35 and sign up to get download instructions ******** [/Edit]

Next week we will begin to roll out the beta for SQL Bundle 5 – as long as no show stopping bugs turn up in the next few days. In light of this I thought I would spend a little time and write about the major changes to the part of the bundle I have been working on – SQL Data Compare 5.0.

This is the final stage of a long running project on the usability aspects of our products. The outcome is a major redesign of the UI for both SQL Compare and SQL Data Compare. We hope you’ll like them.

In addition to this Richard (Mitchell) has been hard at work on the SQL Data Compare 5.0 engine allowing it to deal with remapping, custom comparison keys, better handling of servers with different collations and a more flexible type of comparison system. I hope you will agree when you see the new engine in action that he’s done an excellent job.

I’ve been told I’m allowed to give you a sneak preview of the beta so let’s have a look at what you can expect.

When you first open the product up you will see our new Comparison Projects screen. This lists all of the recent comparisons you have performed, remembering options (which are now per project rather than application wide), any selections you have made, and comparison keys you have set up and any remapping you have performed.

 
You can also add comments to each project, allowing you to organise your comparisons in an easier and more natural way. 

Let’s go through the steps of creating a new project and have a look at the new features we have added since version 4.0.

The project configuration wizard has changed but we still have the familiar select databases control. We have done quite a lot of work on the SQL Server discovery so nearly all SQL Server instances on the local network should show up.

The “Select tables to include/exclude” section has been extended to allow you to set custom comparison keys. One much requested feature of this upgrade is that there is no longer the requirement to have a primary key or index set on your tables and views. 

And even if a primary key or index does exist you can override it. We have also added functionality which allows you to compare only parts of tables, by providing a where clause. In my example database I know that the first 100,000 records of [dbo].[BigAndRandom] are identical so I want to filter these out to save time and disk space – I just right click the table and select “Edit Where Clause” and off I go!

The next tab allows you to remap tables against one another. In my example database I have the table [dbo].[a] and [dbo].[arename] which by default don’t map. I can now use the UI to manually map these tables to one another:

First I select them then I hit map and the engine will now compare these two objects – once again, if I need to, I can set up a comparison key for them:

 
As well as supporting table-level mappings you can also remap roles/schemas/owners and individual columns within tables. You get the flexibility to work exactly the way you feel most comfortable.

The main results page has also undergone a massive overhaul. We have added an interactive help bar to give you hints as you work your way around the application.

The title bar at the top of the screen allows you to see easily which databases you are working on. The grid below is then grouped into objects with differences, identical objects, objects which couldn’t be compared (either due to being previously unmapped or not having a comparison key set) and objects which we could have compared but you’ve chosen not to include in the comparison.

If we expand the top group to see the objects with differences then we are presented with a list of names and numbers:

Here we can see all of the objects in our database which have been compared and found to be different. Starting from the left, the first column tells us the type of the objects being compared, the second column tells us the total number of different records between the two databases. The third, fourth, eighth and ninth columns tell us the name and owner of the object in each database with the middle three columns giving us a break down of where the differences occur: records which exist only on the left; records in both but differ; or records that are only on the right. The final column tells us the number of identical records in the database.

We can select to include and exclude all insert/update/delete statements. If we need fine grained control over which records are migrated we can use the data viewer. Click on any row in the grid and you can explore the data within that table:

There is no arbitrary limit on the number of records which can be compared and displayed: in this example here the BigAndRandom table contains 11 columns with 500,000 different records. As you can see above I’ve filtered out the first 100,000 records in this case and the comparison for the whole database took a little under 25 seconds. That’s more records than most competing products can deal with and several magnitudes faster than any of our competitors. And that’s just for SQL Server 2000. As far as we’re aware, when it comes to SQL Server 2005 support, there are no products accept ours out there.

So taking a closer look, our data viewer contains 5 tabs: the first allows you to see all records in the table; the other 4 are designed so you can see only the part of the table you are interested in. You can use the checkboxes in the first column to include and exclude individual records that you are intending to migrate. This viewer now supports both sorting and searching through your data.

Finally let’s have a look at the synchronization wizard. In the new UI you’re presented with an unmistakeably clear indication of the direction of synchronization. Hopefully this will mean synchronization is not as stressful an operation as it used to be!

The direction can be changed via the menus on the main form or by double clicking on the big arrows. The main arrow then changes colour to green to add an extra level of assurance that you’re happy about the direction of the synchronization.

Next you are presented with a summary of what the script will do. Don’t worry, you can still access the script. Just hit the View SQL Script button and all will be revealed.

Finally you have the option to run your script – saving a backup copy – or to launch it in SQL Query Analyser/Management Studio or any other tool of your choice.

 

So if you haven’t tried using Red Gate SQL Bundle before or an old hat then hopefully there are enough reasons for you to download the beta and give it a try.

If you are interested in joining the beta program, which should launch in the next week or so, then please email me james.moore@red-gate.com with your name, email address and company name and I will pass your name on to our beta co-ordinator – remember you still have time to have an effect on how the most comprehensive schema and data comparison tools on the market develop.