Data Inspector

DBA xPress ›› Features ››
Parent Previous Next

 Data Inspector


Data Inspector will create detailed comparisons between the data stored in two different databases and enable users to sync data between databases.

Feature Highlights

  Compare data from two different databases

  Easily copy data from one database to another


Table of Contents

  Comparison Phases

o   Load data sources

o   Set Comparison Options

o   Edit Mappings

o   Compare Data

o   View Differences

o   View action plan

o   Generate Scripts

o   Run Synchronization

o   Process Complete

  Options


Comparisons are at the heart of Data Inspector, and they all follow a six step process

 

Data Inspector always starts with a source and a target, and the source is always on the left and the target is always on the right. The synchronization direction cannot be switched in Data Inspector - this is because this allows more flexibility in choosing which columns and objects are mapped from left to right (for example, when comparing a non-updatable view in the source database with a table in the target).

 

The data sources represent the structure of the data storage within the databases. The first step in the process relates to the process of loading the data sources for the source and target. The next step is setting the comparison options, including editing any mappings. When the options have been set, the data can be compared. Once that is done, we can view the differences and choose the data that we want to synchronize. Once we have chosen which data is to be synchronized, an action plan is created, which details all the steps necessary to synchronize the chosen data. Synchronization can be in different steps based on the synchronization types available.

 

Important Note

The data sources in Data Inspector are always live databases. Because Data Inspector is engineered to facilitate comparison of very large databases, comparing with snapshots becomes impossible.

 

Before getting started with comparisons, however, we must first create a new comparison scenario.


To create a new comparison, click the Data Inspector icon in Pragmatic workbench 

 

There are four pages to the new comparison scenario dialog. On the above page, you simply enter a name to refer to the comparison scenario.

 

The second and third pages both look very similar:

 

 

You can select a SQL Server from the drop-down list, or click on the "Find Local Servers" button to find any SQL Servers on the local network.

 

Important Note

Data Inspector automatically starts a search for local servers when it starts, so it is not necessary to use the "Find Local Servers" button unless you know a new server has appeared since the application started, or the dialog is opened before the initial search completes.

 

If you click on "SQL Server Security" then you are given the option to enter a username and password. You can select a server and click 'OK' in order to profile space usage on the server as a whole, or you can select a database in order to profile space usage on that single database.

 

The last page of the dialog provides you with the opportunity to customize the options that will be used for comparison and generation.

The default options are those specified in the preferences dialog.

 

 

When done configuring your options, click "Finish" and the comparison will load. This will take you through several Comparison Phases.


There are two comparison types available in Data Inspector

INDEX scan and IDENTITY segment. They both do similar jobs, in that they read data from a SQL Server table in an ordered fashion, and compare the values of the keys from each table to find out which stream is more 'advanced'. The major difference is that index scan comparisons open the whole table in one go, and read from start to finish. IDENTITY segment comparisons open the tables block by block, based on the IDENTITY segment block size specified in preferences.

 

IDENTITY segment comparisons only work on tables where the comparison key is a single column index, and that column is also an IDENTITY column. Index scan comparisons are used at all other times.

 

Important Note

Index scan comparisons work best on clustered indexes where there are no strings involved in the key. Where strings are involved, the sort order is specified with a binary collation, and this causes the scan to require a further sort after being read from disk. When using non-clustered indexes, sort operations are always required, and can be very costly.