Update: SolarWinds DBA xPress is now a FREE tool. In versions 2021.8 and later it is a standalone product and no longer requires a license.
Download: See the DBA xPress product page to learn more.
Overview
| Icon | Description |
|---|---|
 | Data Inspector creates detailed comparisons between the data stored in two different databases and enables you to sync data between databases. Feature Highlights
|
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. Open DBA xPress and then select Data Inspector from the Feature Finder menu to open the Add Comparison Scenario form.
2. Enter a meaningful comparison name, then select Next to continue.
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.
 |  |  |
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.
5. Configure the Comparison Options and Generation Options for your comparison scenario. Once you've completed your configuration selections, select Finish to begin the Comparison process.
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.
| Option | Description |
|---|---|
| Compare intersecting IDENTITY ranges only | When 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 default | Ignores any column comparisons that involve computed columns by default. |
| Ignore ROWGUIDCOL columns by default | Ignores any column comparisons that involve ROWGUIDCOL columns by default. |
| Ignore non-key IDENTITY values | Ignores any column comparisons that involve IDENTITY columns when the column is not part of the comparison key. |
| Match objects in any schema | Allows 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 matching | Matches data storage objects using case sensitive string matching, for databases where a case-sensitive collation is being used. |
| Use binary CLR type comparisons | If 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 comparisons | When 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 case | When this option is set, then string comparisons are case-insensitive. |
| Trim strings for comparison | When this option is set, then leading or trailing spaces are ignored for string comparisons. |
| Ignore string line endings | When this option is set, then the line ending style is ignored for string comparisons. |
| Maximum comparison threads | Specifies 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.
| Option | Description |
|---|---|
| Batch separator | Specifies 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 SQL | When 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 synchronization | When this option is set, DML triggers on target objects will be disabled during synchronization. |
| Disable foreign keys during synchronization | When 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 batch | This 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).
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. 
Select Cancel to cancel the process. The next screen that displays is the set comparison options screen.
Setting 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:
| Object | Description |
|---|---|
| Fully Matched Objects | All columns within the object match. Fully matched objects are represented by a green icon. |
| Partially Matched Objects | Some 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.
 |  |
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.
 |  |  |
Disable comparisons for a selected column by selecting the check box to the left of the column row. 
Change the source and target columns by selecting the drop-down list in a column row. 
The color of the column row changes to indicate the status of the column:
| Color | Description |
|---|---|
| Yellow | Indicates the row is a part of the comparison key. |
| Green | Indicates a matched row. |
| Red | Indicates an unmatched row. |
| Gray | Indicates 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.
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.
Set Comparison Options Filters
The following table describes the Set Comparison Options Filter button:
| Button | Description |
|---|---|
 | Opens the Filter window. |
 | Displays items that are currently filtered. Filtered items and categories appear gray in the comparison. |
 | Hides currently filtered items. Note: This option is selected by default. |
Filters Window
Select the Filter button to open the 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. 
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:
The Comparing Data screen displays three tabs: Active, Pending, and Complete.
| Comparing Data Tab | Description | Image |
|---|---|---|
| Active | The Active tab displays the comparisons that are currently running in Data Inspector. |  |
| Pending | The Pending tab displays the comparisons that haven't started in your comparison. |  |
| Complete | The Complete tab displays the comparisons that have completed in your comparison. |  |
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.
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.
Exclude an entire category by selecting the red X for that category. Include an entire category by selecting the green check for that category.
 |  |
Note: All categories are included by default.
Select Show Grid for a desired row to display the data difference 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.
 |  |
Toolbar Configuration buttons
| Button | Description |
|---|---|
 | Select refresh to reload the comparison. |
 | Select Settings to change your configured settings prior to synchronization. |
After configuring your comparison, select Next Step to open the View Action Plan screen.
Creating an Action Plan
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
| Option | Description |
|---|---|
 | Synchronizes the data into the target database directly. |
 | Generates a script to synchronize the data into the target database. |
 | Synchronizes 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
The direct method first generates scripts, then runs the synchronization.
 |  |
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.
 |  |
Script
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.
 |  |
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.
Hybrid
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.
 |  |  |
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.
 |  |
