Data Compare Project

This project type compares the data from tables and views of two SQL databases and highlights differences in the data.To do this SQL Delta reads the selected tables of each database and sorts the table data based on a unique column or set of columns, normally the primary key however this can be user defined. The data is then compared and rows may be shown as added, deleted, different or simply the same.

The tables in source can also be mapped to tables in the target so even table with different names and different column structures can be compared and this is all done in Table Selection.

The data compare process will transfer each row of data from each selected table and this may generate a large volume of network traffic and require a large volume of local storage.

Data Compare needs an Index or Compare Column(s)

The data compare must sort each table based on a predefined sort column or set of columns and then uses this column or columns as the primary comparison. The Index Column(s) must result in a unique value for the table.

For example a common index is ID and is an integer type and ID is selected as the Index column. Data compare sorts the source table and target table on ID and reads the data.

The Primary key is normally the most effective comparison value(s) which is why we use the term Select Index however you may have a table without a Primary key or you prefer to compare the table on a different set of columns.

To Begin

To create a data compare project there are four primary steps:

Project Type

Step 1 is to choose the project type, Data Compare. This can be set as the default project within DeltaConfig or within System Preferences.

Database Selection

Step 2 is to select the Source server and database. 

Step 3 is to select the Target server and database.

See Database Selection for more on configuring the source and target database.

Table Selection

Step 4 is to make the Table Selection to define the tables to compare. SQL Delta will automatically calculate all tables that exist in both source and target that have a primary key and include these as the default compare. So step 4 is a common step it is not required for the data compare to work unless the tables have no primary keys or require extensive mapping. If you click run after configuring the source and target database the defaults available tables will be used. This will also continue so any changes to the table schema will be automatically updated.

Ready to Run the Compare

Once you have completed these steps the project can be optionally given a name and then run. The run process will automatically save the project to disk and begin the compare. If you prefer to run the project at a later time click save and then close the edit project tab. If automatic save projects is switched on within System Preferences then you only need to close the project. When you run the project a new tab will appear and this is the Data Compare results tab.

When naming a project we recommend only using characters that are also valid within the Windows file system. SQL Delta will strip invalid characters from the filename however keeping the filename and the project name the same may reduce any confusion if you need to locate the project file.

Transfers the Source Connection Settings to the Target Connection.

Swaps the Source and Target settings.

Runs a test of the connection. If you are having troubles connecting to a database firstly try connecting to the server (remove the database text). If the server is remote and the connection slow then try changing the Project Options/Load Options Timeout values. Also by setting the Slow Connection to True SQL Delta will configure internal settings to expect a slow response from the server. There are limits to the effectiveness of timeouts and the slow connection setting, SQL Server may issue a timeout error regardless of the settings.

Play Sound

Since projects now run in the background and multiple projects can run simultaneously you may want to be alerted when a lengthy compare has finished. Play sound provides a feedback mechanism at specific points in a project life.

No Sound - as it says no sound is played.

All Processed - Plays a sound after the compare completes, the scripts is loaded or script run is complete.

After Compare - Only play a sound when the compare finishes and the results are displayed

After Script Load - Sometimes very large schema scripts can take time to load and a sound plays once the script has been displayed

After Script run - Like load sometimes very large scripts can take sometime to run and a sound plays when the run is complete.