DBA xPress Data Inspector

Overview

IconDescription
DBA xPress Data Inspector IconData Inspector creates detailed comparisons between the data stored in two different databases and enables you to sync data between databases.

Feature Highlights
  • Compare data from two different databases
  • Copy data from one database to another

Note:  The data sources in Data Inspector are always live databases.

Creating a New Comparison Scenario

Create a new comparison scenario for your source by completing the following steps:

1. Select Data Inspector in the SentryOne workbench to open the Add Comparison Scenario form.SentryOne Workbench select Data Inspector

2. Enter a meaningful comparison name, then select Next to continue.DBA xPress Data Inspector Add Comparison Scenario Enter Name

3. Select a Source for your comparison. Enter a SQL Server connection or select a previous connection from the drop down list. Enter your connection credentials , then select a database for the comparison. Select Next to continue.

DBA xPress Data Insepctor Select Source SQL Server
DBA xPress Data Inspector Select Source Enter Credentials
DBA xPress Data Inpsector Select Source Next

Note:  Data Inspector automatically starts a search for local servers when it starts. Select Find Local Servers if you know a new server has appeared since the application started, or the dialog opens before the initial search completes.

Note:  Selecting SQL Server Security gives you the option to enter a username and password. Select a server, then select OK to profile space usage on the server as a whole. Select a database to profile space usage on that single database.

4. Select a Target for your comparison. Enter a SQL Server connection or select a previous connection from the drop down list. Enter your connection credentials, then select a database for the comparison. Select Next to continue.DBA xPress Data Inspector Select Target

5. Configure the Comparison Options and Generation Pptions for your comparison scenario. Once you've completed your configuration selections, select Finish to begin the Comparison process.DBA xPress Data Inspector Choose Options

Note:  The default options are selected in the preferences dialog. 

Comparison and Generation Options

There are two comparison types available in Data Inspector; INDEX Scan and IDENTITY segment. INDEX scan and IDENTITY segment do similar jobs; they read data from a SQL Server table in an ordered fashion, and compare the values of the keys from each table to determine which stream is more advanced.

INDEX scan comparisons open the whole table in one process, and read from start to finish. IDENTITY segment comparisons open the tables block-by-block, based on the IDENTITY segment block size that's 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.

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 an additional sort after being read from disk. When using non-clustered indexes, sort operations are always required, and can be costly for performance.

The following is a detailed list of the Comparison and Generation Options:

Comparison Options

The comparison options apply to each comparison performed and specify how the two data sources will be compared and matched.

DBA xPress Data Inspector Choose Options Comparison Options

OptionDescription
Compare intersecting IDENTITY ranges onlyWhen this option is set, and a comparison uses a segmented IDENTITY comparison, then only the ranges of values that are present in both databases will be compared. This can be useful for comparisons between databases where replication occurs, and the data that has not yet been replicated should be ignored. For more information, see comparison types.
Ignore computed columns by defaultIgnores any column comparisons that involve computed columns by default.
Ignore ROWGUIDCOL columns by defaultIgnores any column comparisons that involve ROWGUIDCOL columns by default.
Ignore non-key IDENTITY valuesIgnores any column comparisons that involve IDENTITY columns when the column is not part of the comparison key.
Match objects in any schemaAllows data storage objects to be matched against data storage objects in other schema if no match is found in the same schema.
Use case-sensitive object matchingMatches data storage objects using case sensitive string matching, for databases where a case-sensitive collation is being used.
Use binary CLR type comparisonsIf this option is set, then CLR type columns are compared and Synchronized using their binary representation. The default is that CLR type columns are compared and Synchronized using their string representation.
Use segmented IDENTITY comparisonsWhen this option is set, and the comparison key selected for a table is a single-column index that only involves an IDENTITY column, then use segmented IDENTITY comparison. For more information, see comparison types.
IDENTITY segment block size (rows)The block size to be used with segmented IDENTITY comparisons. For more information, see comparison types.
Ignore string caseWhen this option is set, then string comparisons are case-insensitive.
Trim strings for comparisonWhen this option is set, then leading or trailing spaces are ignored for string comparisons.
Ignore string line endingsWhen this option is set, then the line ending style is ignored for string comparisons.
Maximum comparison threadsSpecifies the maximum number of comparison threads. When most comparisons are using either IDENTITY segment comparisons, or are index scan comparisons which are scanning the clustered index, then increasing the number of threads can significantly improve the comparison throughput.
Data read timeout (seconds)The timeout (in seconds) used for the initial data read for any comparison.

Generation Options

The generation options apply to each synchronization performed and specify how the schema modification SQL will be generated under various circumstances.DBA xPress Data Inspector Choose Options Generation Options

OptionDescription
Batch separatorSpecifies the batch separator that is used to delimit SQL batches. In 99.9% of cases this will want to remain as GO.
Use transacted modification SQLWhen this option is set, DML operations will be wrapped in a transaction in order to allow consistent updates to take place in case any updates fail.
Disable DML triggers during synchronizationWhen this option is set, DML triggers on target objects will be disabled during synchronization.
Disable foreign keys during synchronizationWhen this option is set, foreign keys on target objects will be disabled during synchronization. It is recommended to keep this option set, as disabling it may cause synchronizations which involve circular foreign keys or cross-related foreign keys to fail.
Max rows per batchThis is the maximum number of rows that will be emitted into a single batch in the synchronization SQL.

Comparison Phases

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 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).

DBA xPress Data Inspector Comparison Phases

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.  

Loading Data Sources

After selecting Finish on your Comparison Scenario, your selected Data sources are loaded. DBA xPress Data Inspector Loading Data Sources

Select Cancel to cancel the process. The next screen that displays is the set comparison options screen.

Setting Comparison Options

DBA xPress Data Inspector Set Comparison Options

Set Comparison Options displays two categories of objects:

  • Objects where a match can be found
  • Objects where a match was not found 

Objects where a match was found:

ObjectDescription
Fully Matched ObjectsAll columns within the object match. Fully matched objects are represented by a green icon. 
Partially Matched ObjectsSome of the columns within the object match. Partially matched objects are represented with a yellow icon.

Edit Mappings

Double click the desired comparison, or select a comparison and then select Map Columns to open the Edit Mappings & Filters window and change the mappings for a column. 

DBA xPress Data Inspector Set Comparison Options Map Columns
DBA xPress Edit Mappings & Filters window

 Use the Edit Mapping & Filters window to set a data filter for the source and target objects, and change the source and target objects entirely. Select the source and target objects from the drop-down boxes and select the index that will be used to walk the comparison.

DBA xPress Edit Mappings & Filters window Source Object
DBA xPress Edit Mappings & Filter window Target Object
DBA xPress Edit Mappings & Filter window Selected Index

Disable comparisons for a selected column by selecting the check box to the left of the column row. DBA xPress Edit Mappings & Filters window disable comparison

Change the source and target columns by selecting the drop-down list in a column row. DBA xPress Edit Mappings & Filters Comparison drop down

The color of the column row changes to indicate the status of the column:

ColorDescription
YellowIndicates the row is a part of the comparison key.
GreenIndicates a matched row.
Red Indicates an unmatched row.
GrayIndicates the column row is not selected for the comparison.

Select the Filter button for the source or target to open the Edit Filter window for your selection. Enter the WHERE clause for the relevant storage object. To remove the filter select Clear. Select OK to save your filter.DBA xPress Edit Filter window

Note:  When a filter is active for the source or target, the filter icon is gold.

After finalizing your selections in the Edit Mappings & Filters window, select OK  to save your changes and return to Set Comparison Options.DBA xPress Edit Mappings & Filters select Ok

Set Comparison Options Filters

The following table describes the Set Comparison Options Filter button:

ButtonDescription
DBA xPress Data Inspector Set Comparison Options Filter buttonOpens the Filter window. 
DBA xPress Data Inspector Set Comparison Options Filter Show Filtered itemsDisplays items that are currently filtered. Filtered items and categories appear gray in the comparison. 
DBA xPress Data Inspector Set Comparison Options Filter Hide Filtered ItemsHides currently filtered items.

Note:  This option is selected by default.

Filters Window 

Select the Filter button to open the Filters window. 

DBA xPress Filters window

Use the Filters window to change the mode that filters apply, and add, edit or remove filters. The overall filter mode applies to the complete list of filters, rather than each individual one. 

Note:  The overall filter mode drop down box is not displayed when editing filters for a comparison scenario.

Once you've added a filter, the Filter button displays in gold to indicate that you have active filters. If you select Show filtered items, any filtered items display in a grayed out format. DBA xPress Data Inspector Set Comparison Options Show Filtered Items active

Categories which contain only filtered items are grayed, and they also say 0 selected showing that all items in those categories have been deselected. Categories with some items filtered are semi-grayed, and report the selected number of objects normally. You can expand the items and select filtered items as you would normally.

Note:  Filters are saved with the comparison scenario, and are automatically applied the next time the comparison opens. If there are specific objects that you want to ignore every time the comparison is run, it's beneficial to set up a filter to automatically de-select those objects.

Comparing Data

After you have finished making your selections on the Set Comparison Options page, select Compare to begin the comparison process, and display the following screen:

DBA xPress Data Inspector Comparing Data screen

The Comparing Data screen displays three tabs: Active, Pending, and Complete. 

Comparing Data TabDescriptionImage
ActiveThe Active tab displays the comparisons that are currently running in Data Inspector.DBA xPress Data Inspector Comparing Data Active tab
PendingThe Pending tab displays the comparisons that haven't started in your comparison.DBA xPress Data Inspector Comparing Data Pending tab
CompleteThe Complete tab displays the comparisons that have completed in your comparison.DBA xPress Data Inspector Comparing Data Complete tab

Select Cancel at any point in the Comparison to stop the comparison process. 

Note:  Selecting cancel can take a while to stop the comparison because the rows have to continue to be streamed from the SQL Server.

Once the comparison completes, the View Differences screen displays.

Choosing Synchronization Data

View the differences within your selected source and target on the View Differences screen.

DBA xPress Data Inspector View Differences Screen

The top of the View Differences screen displays the names of the source and target being compared. The arrow points from the source to the target, with the target always displayed in red. 

Note:  The direction of synchronization remains fixed in Data Inspector, from left (source) to right (target).

Each item on the View Differences screen displays the following information:

  • The number of rows in source only
  • The number of rows in target only
  • The number of rows that are different in the source and target

Select the checkbox next to a desired row if you want to exclude that row from the synchronization.DBA xPress Data Inspector View Differences exclude row

Exclude an entire category by selecting the red X for that category. Include an entire category by selecting the green check for that category. 

DBA xPress Data Inspector View Differences exclude category
DBA xPress Data Inspector View Differences include category

Note:  All categories are included by default.

Select Show Grid for a desired row to display the data difference grid. 

DBA xPress Data Inspector View Differences Show Grid
DBA xPress Data Inspector View Differences Grid

Data inspector is unique in showing row differences as a normal grid with a vertical split. Rows that are only in the target are highlighted blue; rows that are only in the source are highlighted in green. Data differences between the two sides are highlighted in red while NULL values are highlighted in yellow. Multiple highlights can be combined. You can select portions of the grid in the usual way, and the selection is displayed symmetrically on the left and right hand sides.

DBA xPress Data Inspector View Differences Blue and Green rows
DBA xPress Data Inspector View Differences Red rows

Toolbar Configuration buttons

ButtonDescription
DBA xPress Data Inspector Refresh buttonSelect refresh to reload the comparison.
DBA xPress Data Inspector Settings buttonSelect Settings to change your configured settings prior to synchronization.

After configuring your comparison, select Next Step to open the View Action Plan screen. DBA xPress Data Inspector View Differences select Next Step

Creating an Action Plan

DBA xPress Data Inspector Action Plan screen

The Action Plan screen displays an overview of the data modifications that can be performed to synchronize your source and target. The source is displayed on the top left of the screen, and the target that you want to modify is displayed on the top right. The Action Plan screen also displays the number of inserts, updates, and deletes to complete the synchronization. 

Note:  The Action Plan grid displays the inserts, updates, and deletes per object. The complete number of inserts, updates, and deletes is displayed at the bottom of the screen.

Select one of the following synchronization options for your Source and Target:

Synchronization Options

OptionDescription
DBA xPress Data Inspector Action Plan Direct Synchronization buttonSynchronizes the data into the target database directly.
DBA xPress Data Inspector Action Plan Script Synchronization buttonGenerates a script to synchronize the data into the target database.
DBA xPress Data Inspector Action Plan Hybrid Synchronization buttonSynchronizes the data into the target database directly, and generates a synchronization script for reference.

Note:  Select Back to return to the View Differences screen.

Synchronizing the Source and Target

After reviewing your Action Plan, and selecting a synchronization option, you are ready to synchronize your selected Target.

Select Synchronize to begin the selected synchronization process:

Direct

DBA xPress Data Inspector Action Plan Direct Synchronize

The direct method first generates scripts, then runs the synchronization. 

DBA xPress Data Inspector Generating Scripts screen
DBA xPress Data Inspector Running Synchronization screen

Warning:  

During the Generating Scripts sequence, you can select Cancel to stop the process and return to the Action Plan screen. Once script generation has completed, the synchronization begins, and you can't stop the process.

After the Running Synchronization screen has completed, the Process Complete screen displays. The direct method displays any run errors that occurred during the synchronization. Select the Run Errors tab to display the errors.

DBA xPress Data Inspector Process Complete Screen
DBA xPress Data Inspector Process Complete Run Errors

Script

DBA xPress Data Inspector Action Plan Script Synchronize

The script method generates a script for the synchronization. Enter a name for your script, and then select Save to save the file and begin generating the script.  

DBA xPress Data Inspector Save Data Modification SQL
DBA xPress Data Inspector Generating Scripts screen

Note:  During the Generating Scripts sequence, you can select Cancel to stop the process and return to the Action Plan Screen.

After the script has finished generating the Process Complete screen displays.

DBA xPress Data Inspector Process Complete screen

Hybrid

DBA xPress Data Inspector Action Plan Hybrid Synchronize

The hybrid method first generates scripts and then runs the synchronization. Enter a name for your script, and then select Save to save the file and begin generating the script.   

DBA xPress Data Inspector Save Data Modification SQL
DBA xPress Data Inspector Generating Scripts screen
DBA xPress Data Inspector Running Synchronization screen

Warning:  

During the Generating Scripts sequence, you can select Cancel to stop the process and return to the Action Plan screen. Once Script generation has completed, the synchronization begins, and you can't stop the process.

After the Running Synchronization screen has completed, the Process Complete screen displays. The hybrid method displays any run errors that occurred during the synchronization. Select the Run Errors tab to display the errors.

DBA xPress Data Inspector Process Complete screen
DBA xPress Data Inspector Process Complete Run Errors