INTRODUCING TOAD FOR SQL SERVER PT III

Up to this point, I have demonstrated my mastery of the Roman Numeral System, Toad’s IntelliSense and Group Execute technology, and Toad’s SQL Optimization technology.

In this post I’ll finish the conversation with an overview of Toad’s Compare and Synch feature set.

For your reading enjoyment and my time constraints and sanity, I will show the Server Compare in a step-by-step manner, and then show the Schema and Data compares as an ‘end result.’ The process for each of the three compares is similar, and once you have grasped the wizard conceptually for one, you should be good to go for the others.

Compare and Synch in a Nutshell

Server Compares
To get started you will obviously need to be able to connect to the two servers in question. You will need to use an Administrator-level account for obvious reasons. For example a lower level account would lack the privileges necessary to compare logins between two different servers.

Provide Connection Information for the Compare

Take notice to the ‘Offline Snapshot’ option for the comparison source or target. Once you have performed a server compare, you are able to save the characteristics of each server to a local file. That file can then be used as a ‘point-in-time’ reference point to the server. This could be useful for administrators that would like to keep track of their servers as they upgrade them from one release or patch set to the next.

After the report is generated, the results are then available for review. Missing, different, and equal properties are displayed by default, although you can toggle each category on or off using the Server Compare toolbar. For this example I have selected only the ‘Different’ category.

Select a property, see the difference, view the Synch script for either direction

The Synchronization is Optional!
If you are just looking for a reporting tool, then this will work fill that need as well. However, if you do need to generate change scripts, then continue reading.

Synch SetA to SetB or go the opposite direction

Once the script is generated, it is up to you to validate it for your task.

Read it, review it, run it, or save it for later!

Schema Compare
This feature unfortunately does not get off to a very strong start. The name is misleading as the feature actually does a DATABASE compare. You can compare databases from different servers or from the same server. The offline snapshot technology is also available here.

The Compare Report is available in multiple formats.

View Differences Side By Side or View the Synch Script for each object

Toad does validate the synch scripts and will warn you if the possibility of data loss occurs. For example this difference report shows a column of length 50 in one database and of 25 in another. For Toad to synch A to B, you would lose upwards of 25 characters on that column.

Script warnings alert you to possible data loss. Remember to validate all scripts!

Compare Data
This may be the most interesting of the compare features. This technology allows a SQL user to compare data between one set of tables to another set. The tables can reside in any database on any server. The tables do not need to share common table or column names. Of course it is easier to setup the compare if the names are the same and if they share common primary keys.

The tool allows you to view table differences side by side at the row and/or column level. You can choose to compare all the data or use a WHERE clause to limit the data being processed. The technology is fairly robust
but will consume quite a bit of PC resources. I have successfully ran a two million to two million row table compare in several minutes. As with all software programs, be careful what you ask for!

Table data differences

For more information on the Data Compares, check out an earlier post of mine on ToadWorld.