DBA xPress Schema Inspector

Overview

IconDescription
DBA xPress Schema Inspector IconSchema Inspector allow you to view the differences between databases and synchronize the schemas between them.

Feature Highlights
  • View differences between databases
  • Synchronize the schemas in two different databases

Creating a New Comparison Scenario

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

1. Select Schema Inspector in the SentryOne workbench to open the Add Comparison Scenario form.DBA xPress select Schema Inspector

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

3. Select the Left Hand Side for your comparison. Use the tabs at the top to select the style of data source. Select one of the following tabs:

TabTab Instruction Image

Database

Select the Database tab to use a live database server for your comparison.

Enter a SQL Server connection or select a previous connection from the drop down list. Enter your connection credentials , and then select a database for the comparison. Note:  Schema 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. DBA xPress Add Comparison Scenario Left Hand Side Database selection

Snapshot

Select the Snapshot tab to use a previously taken snapshot for your comparison.

Select Select File to open the Load Snapshot window. Select your Snapshot file, and then select Open to add your snapshot to the comparison.

For information about creating a snapshot, see the Schema Inspector Snapshot Tool article.
DBA xPress Load Snapshot windowDBA xPress Add Comparison Scenario Left Hand Side Snapshot selection

Blank 

Select the Blank tab to use an empty database for the comparison.

N/ADBA xPress Add Comparison Scenario Left Hand Side Blank selection

After choosing your data source and completing its form, select Next to continue.

4. Select a Right Hand Side for your comparison. Repeat the steps from step three for the Right Hand Side of the comparison. Select Next after making your selections. 

DBA xPress Add Comparison Scenario Right Hand Side Database selection
DBA xPress Add Comparison Scenario Right Hand Side Snapshot selection
DBA xPress Add Comparison Scenario Right Hand Side Blank selection

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.DBA xPress Add Comparison Scenario Choose Options

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

Comparison and Generation Options

Comparison Options

The comparison options apply to each comparison performed and specify how the two data sources are compared and matched. The following Comparison options are available:

OptionDescription
Ignore ANSI NULLSIgnores the state of SET ANSI NULLS during comparison for SQL programmable objects.
Ignore CollationIgnores collation differences.
Ignore WITH NOCHECKIgnores WITH NOCHECK on check and foreign key constraints.
Ignore Data Space AssignmentsIgnores the data spaces that objects are assigned to.
Ignore IDENTITY PropertyIgnores columns whereby one is an IDENTITY column and the other is not.
Ignore IDENTITY Seed & IncrementIgnores columns whereby both are IDENTITY columns but the seed and/or increments differ.
Ignore NullabilityIgnores nullability on columns and types.
Ignore QUOTED IDENTIFIERIgnores the state of SET QUOTED IDENTIFIER during comparison for SQL programmable objects.
Ignore Referential ActionsIgnores ON UPDATE and ON DELETE clause differences for foreign keys.
Ignore Object PrincipalsIgnores differences in object owning principals.
Ignore EXECUTE AS PrincipalsIgnores EXECUTE AS clauses.
Ignore PartitioningIgnores differences in partition ordinals on indexes.
Ignore SQL CommentsIgnores comments in SQL programmable objects.
Ignore SQL White SpaceIgnores white space in SQL programmable objects.
Ignore SQL CaseIgnores differences in case in SQL programmable objects.
Ignore Target Key IndexesIgnores indexes that are used by foreign keys on the referenced table.
Use Case Sensitive MatchingCauses object name matching to be case sensitive.
Ignore Column LengthIgnores the specified length of columns.
Ignore NOT FOR REPLICATIONIgnores the differences in specification of NOT FOR REPLICATION clauses.
Ignore Precision & ScaleIgnores the precision and scale of columns.
Ignore SCHEMABINDINGIgnores the WITH SCHEMABINDING property.
Ignore IGNORE DUPLICATE KEYIgnores use of IGNORE DUPLICATE KEY.
Ignore Fill FactorIgnores index fill factors.
Ignore Index PaddingIgnores index padding.
Ignore Computed Column PersistenceIgnores computed columns marked as PERSISTED.
Ignore Page/Row Lock AllowancesIgnores allow page lock and allow row lock settings on indexes.
Ignore Schema Collection PropertiesIgnores XML Schema Collection assignments.
Ignore Key AscendancyIgnores key sort orders on indexes.
Ignore INCLUDE ColumnsIgnores INCLUDED columns on indexes.
Ignore WITH RECOMPILEIgnores the WITH RECOMPILE clause.
Ignore WITH CHECK OPTIONIgnores the WITH CHECK OPTION clause.
Ignore NULL ON NULL INPUTIgnores the NULL ON NULL INPUT property.
Ignore Maximum Queue ReadersIgnores the maximum queue readers property on service queues.
Ignore Permission SetsIgnores permission differences on assemblies.
Ignore Boundary ValuesIgnores boundary value alignments on partition functions.
Ignore Accent SensitivityIgnores accent sensitivity on full text indexes.
Ignore Change Tracking StatesIgnores change tracking states on full text indexes.
Ignore Selected StoplistsIgnores stop list differences on full text indexes.
Ignore Service NamesIgnores service names on event notifications.
Ignore Broker InstancesIgnores broker instances on event notifications and routes.
Ignore ROWGUIDCOL PropertiesIgnores the ROWGUIDCOL property.
Ignore Column SparsityIgnores differences in column sparsity.
Ignore Data Space Default SelectionsIgnores data space DEFAULT settings.
Ignore Full Text Catalog DefaultsIgnores full text catalog DEFAULT settings.
Find Constraints By SignatureEnables the ability to match constraints by their schema, rather than their name. This is useful when comparing databases which are using system-named constraints.
Consider Column OrderConsiders a table as different if it’s columns are in a different order.
Find Objects In Any SchemaAllows objects to be found in other schema if they are not found in the correct schema.
Ignore Corrected Object NamesIgnores object names that are not correct because sp_rename has been used.

Generation Options

The generation options apply to each synchronization performed and specify how the schema modification SQL generates under various circumstances. The following generation options are available:

OptionDescription
Batch SeparatorThe batch separator that's emitted between SQL batches in saved scripts.
Use Object Presence ChecksWhether to use IF EXISTS and IF NOT EXISTS when generating DDL SQL.
Use Transacted Modification SQLWhether to place schema modifications in a transaction, with rollback management.
Always Specify CollationWhether collation should always be specified, or only specified when it is different from the database default.
Replace User Defined Types With Base TypesWhether user defined types should be scripted as their base type.
Keep Trigger OrderWhether trigger order should be preserved.
Allow System Named ConstraintsWhether system named constraints should be scripted as system named constraints, or whether they should explicitly use the generated name of the source constraint.
Warn About UNSAFE AssembliesWhether to warn about UNSAFE assemblies.
Auto Create Files For File groupsWhether to create files for file groups in the default location.
Use Online Index BuildingWhether to use online index rebuilds.
Emit Warnings In Output SQLWhether to emit the generation warnings in comments with each batch.
Emit Table Constraints In-LineWhether to emit check, default, unique and primary key constraints with their related tables.
Fix Object NamesWhether to fix object names that have become incorrect through the use of sp_rename.
Rebuild tables when altering assembliesWhether to rebuild tables when altering assemblies.

Comparison Phases

DBA xPress Schema Inpsector Comparison Phases

All comparisons have a left and right hand side data source. The first two steps in the process relate loading the data sources for the left and right hand side, and then comparing the two. 

Data Sources can be one of the following types:

Data SourceDescription
Live DatabaseThe schema is read directly from a database on a server.
SnapshotThe schema is read from an (.xml) file containing a snapshot of the previously saved schema.

Note:  Using a Snapshot for the comparison can be useful in many scenarios, for example:
  • Comparing with a production environment database without direct access to that production environment.
  • Comparing with a known previous state.
  • Re-creating a database schema from a customer site.
  • Running a release methodology based on previously published schema.
BlankThe schema is empty. This is useful for scripting the contents of a database. Note:  Use this option to produce drop scripts for every object in a database.

There are also a lot of options which control both the differences a comparison takes into account, and how the synchronization scripts  generate. Documentation can be generated from a comparison, as a differences report (documenting the difference in state between the databases) and a synchronization report (documenting which objects were synchronized).

After selecting your data sources, you choose the objects that you want to synchronize. An action plan is then created that details all the necessary steps to synchronize the chosen object, including any dependencies.

Loading Data Sources

After selecting to Finish your Comparison Scenario, your selected Data sources are loaded.

DBA xPress Schema Inspector Loading Data SourcesSelect Cancel to cancel the process. The next screen that displays is the Comparing Data Sources screen.

Comparing Data Sources

After your data sources have finished loading, the Comparing Data Sources screen displays.DBA xPress Schema Inspector Comparing Data Sources

Select Cancel to cancel the process. The next screen that displays is the View Differences screen.

Choosing Objects

Once the comparison for your data sources has completed, the View Differences screen displays. 

Note:  The View Differences screen categorizes the comparison differences by type. Change this categorization by setting group comparison results by type option in the preferences dialog. Each type maintains a display of how many entries underneath it are selected for synchronization.

The names of the two data sources being compared are shown on their respective left and right hand sides. The arrow points from the source to the target, and the target is always colored red. Select the top arrow to change the direction of the synchronization.

DBA xPress Schema Inspector View Differences Change Synchronization
DBA xPress Schema Inspector View Differences Choose Synchronization

Select the blue drop-down arrow to display the list of individual differences for a selected category.DBA xPress Schema Inspector View Differences select Category

Deselect a check box next to any individual item to remove it from the synchronization. 

DBA xPress Schema Inspector View Differences Unselect Item
DBA xPress Schema Inspector View Differences Item Unselected

Select the red X for a category to remove the entire category from synchronization.

DBA xPress Schema Inspector View Differences Unselect Category
DBA xPress Schema Inspector View Differences Category removed from syncrhonization

Select the green check mark for a category to include the entire category during synchronization. 

DBA xPress Schema Inspector View Differences Include Category
DBA xPress Schema Inspector View Differences Category included in synchronization

After completing your comparison selections, select Next Step to continue to the View Action Plan screen.

Toolbar Buttons

DBA xPress Schema Inspector Toolbar buttons

The following is a detailed list of the toolbar buttons on the Schema Inspector View Differences page:

File Buttons

ButtonDescriptionImage
DBA xPress Schema Inspector New Scenario buttonOpens the Add Comparison Scenario window and allows you to create a new scenario.DBA xPress Add Comparison Scenario window
DBA xPress Schema Inspector Open Scenario buttonOpens the Open Scenario window and allows you to select a previously created scenario.

Select the desired scenario, then select Open to load your comparison.
DBA xPress Open Scenario window
DBA xPress Schema Inspector Save Scenario buttonSaves the current comparison scenario.
DBA xPress Schema Inspector Save Scenario As buttonOpens the Save Scenario As window and allows you to save the current scenario as a new file.

Enter a file name, then select Save to save your comparison.
DBA xPress Save Scenario As window

Snapshot

ButtonDeescriptionImage
DBA xPress Schema Inspector Take Snapshot buttonOpens the Choose Source for Snapshot window.

Enter a database connection for your snapshot, then enter your credentials. Select OK to begin loading the database for the Snapshot. Enter a name for the Snapshot, then select Save to save your snapshot.
DBA xPress Choose Source to Snapshot windowDBA xPress Save Snapshot window

Scenario

ButtonDescriptionImage
DBA xPress Schema Inspector Settings buttonSelect Settings to change your configured settings prior to synchronization.DBA xPress Schema Inspector Edit Comparison Scenario
DBA xPress Schema Inspector Refresh buttonSelect Refresh to reload the comparison.DBA xPress Schema Inspector Loading Data Sources

View Differences Page Buttons

ButtonDescriptionImage
DBA xPress Schema Inspector Next Step buttonSelect Next Step to continue to the View Action Plan screen.
DBA xPress Schema Inspector Statistics buttonSelect Statistics to open the Comparison Statistics window for your comparison.DBA xPress Schema Inspector Comparison Statistics window
DBA xPress Schema Inspector Filter buttonThe Filter button is separated into two parts.

Select the left side of the filter button to open the Filters window. Select the filter button drop-down arrow to select Show Filtered Items, or Hide Filtered Items.

Note:  Hide Filtered Items is enabled by default. Selecting to Show Filtered Items displays filtered items and categories in gray.
DBA xPress Schema Inspector Filter options
DBA xPress Schema Inspector Report buttonSelect Report to open the Generate Report window.DBA xPress Schema Inspector Generate Report window

Adding Filters

Select the left side of the filter button to open the Filters window.

DBA xPress Schema Inspector View Differences select Filter button
DBA xPress Choose Filter window

The Choose Filter window allows you to apply, add and edit, and remove filters. The filtering system for the DBA xPress Schema Inspector is used in the same way as the Object Browser filtering. For more information about the filtering window, see Object Browser Filtering.

Note:  The overall filter mode applies to the complete list of filters, rather than each individual one. The overall filter mode drop down box does not display when editing filters for use with a comparison scenario.

After applying a filter, the Filter button displays in Gold. Select Show Filtered Items to display items that are filtered.DBA xPress Schema Inspector View Differences active filter

Note:  Categories that contain no items are grayed, and they list 0 selected displaying that all items in those categories have been deselected. You can expand the items and select the filtered items as you would normally.DBA xPress Schema Inspector View Differences filtered category

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 runs, it's beneficial to set up a filter to automatically de-select those objects.

Generating Reports

Select the Report button to open the Generate Report window. 

DBA xPress Schema Inspector Generate Report window

Use the Generate Report window to create synchronization documentation. Synchronization documentation documents the actions that were taken against two data sources. The documentation includes sections for directly selected objects as well as dependencies, and documents the DDL that was generated in order to make the modifications. It also contains a list of the comparison options and the generation options in use at the time of the comparison.

Generate a Synchronization report by completing the following steps:

1. Select Report to open the Generate Report window.DBA xPress Schema Inspector View Differences select Report

2. Select your reporting type, then enter a destination folder. Select OK to generate your report. DBA xPress Generate Report window select options

Success: After your report has finished generating, it is ready to view! DBA xPress Schema Inspector HTML Report

Reporting Type Buttons

ButtonDescription
DBA xPress Generate Report window HTML buttonHTML reports use standard HTML files with one topic per page.
DBA xPress Generate Report window HTML Frame set buttonHTML frame set reports keep the navigation for the report in a frame on the left.
DBA xPress Generate Report window Rich Text Format buttonRTF reports can be loaded into many different editors, and combine all topics into a single document file.

The bottom half of the form asks you to specify the destination where the documentation will be saved.

Note:  For both HTML type reports, the destination is a folder, whereas for the RTF style of reporting, the destination is a file.

Creating an Action Plan

The View Action plan screen displays a breakdown of the schema modifications that will be performed. The source is displayed on the top left, and the target that will be modified is displayed on the top right.DBA xPress Schema Inspector View Action Plan screen

Select the checkboxes to include object dependencies and enable output controls. By enabling output controls, you can select which objects to include or exclude from the change script.DBA xPress Schema Inspector View Action Plan Enable output control

Warning:  

Enabling output control allows you to skip individual elements from change scripts. By doing so, you may end up with broken scripts. For example, because a dependent object can no longer be created the script is broken. Please carefully check any generated scripts.

The drop down list allows you to select the version of SQL Server that you want the generated script to run against.DBA xPress Schema Inspector View Action Plan Target Server Version drop down

Note:  The drop down list defaults to the server version of the target when using either a database or a snapshot as the target. When using blank as the target, this defaults to the server version of the source.

ButtonDescriptionImage
DBA xPress Schema Inspector View Action Plan Preview buttonDisplays a preview of the SQL script.DBA xPress Schema Inspector Preview Script window
DBA xPress Schema Inspector Synchronize buttonBegins the synchronization process for your selection.DBA xPress Schema Inpsector Generating Scripts

Synchronizing Your Comparison

After configuring the options listed above, you must select a synchronization method. The following synchronization methods are available:

Synchronization MethodDescriptionAvailability
DBA xPress Schema Inspector View Action Plan Direct Synchronization buttonDirect synchronization executes the schema on the target database. The SQL used for modification is not saved, though a report can still be generated.

Warning:  

This method modifies the database if a synchronization is needed.
Available when the target is a database.
DBA xPress Schema Inspector View Action Plan Script synchronization buttonScript synchronization generates the schema modifications into a SQL script file, and no execution is performed.Available when the target is a database, a snapshot or blank.
DBA xPress Schema Inspector View Action Plan Hybrid syncronization buttonHybrid synchronization executes the schema modifications directly on the target database, and saves the schema modifications into a SQL script file.

Warning:  

This method modifies the database if a synchronization is needed.
Available when the target is a database.

Direct 

Select the direct synchronization method, then select Synchronize to begin synchronization. DBA xPress Schema Inspector View Action Plan select Synchronize

Direct synchronization first generates scripts, then runs the synchronization. 

DBA xPress Schema Inspector Generating Scripts
DBA xPress Schema Inspector Running Syncrhonization

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.

Note:  If there are any warnings during your synchronization, the View Warnings screen displays. Review the warnings associated with your synchronization, then select Synchronize to continue running the synchronization.DBA xPress Schema Inspector View Warning page

After the synchronization completes, the Operation Complete screen displays. The direct method displays any run errors and generation warnings that occurred during the synchronization. Select the Run Errors tab to display run errors, then select the Generation Warnings tab to display generation warnings.

DBA xPress Schema Inspector Operation Complete
DBA xPress Schema Inspector Operation Complete Generation Warnings
DBA xPress Schema Inspector Operation Complete Run Errors

Script

The Script method generates a script for the synchronization. Select the Script synchronization method, then select Synchronize to open the Save Schema Modification SQL window .DBA xPress Schema Inspector View Action Plan select Synchronize

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

DBA xPress Schema Inspector Save Schema Modification SQL
DBA xPress Schema Inspector Generating Scripts

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. The script method displays any generation warnings associated with your synchronization. Select the Generation Warnings tab to view any warnings associated with your synchronization.

DBA xPress Schema Inspector Operation Complete
DBA xPress Schema Inspector Operation Complete Generation Warnings

Hybrid

The hybrid method first generates scripts and then runs the synchronization. Select the Hybrid synchronization method, then select Synchronize to open the Save Schema Modification SQL window.

Enter a name for your script, then select Save to save the file and begin generating the script. DBA xPress Schema Inspector View Action Plan select Syncrhonize

Hybrid synchronization first generates scripts, then runs the synchronization. 

DBA xPress Schema Inspector Generating Scripts
DBA xPress Schema Inspector Running 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.

Note:  If there are any warnings during your synchronization, the View Warnings screen displays. Review the warnings associated with your synchronization, then select Synchronize to continue running the synchronization.DBA xPress Schema Inspector View Warnings

After the synchronization screen completes, the Operation Complete screen displays. The hybrid method displays any run errors and generation warnings that occurred during the synchronization. Select the Run Errors tab to display run errors, and select Generation Warnings tab to display generation warnings.

DBA xPress Schema Inspector Operation Complete
DBA xPress Schema Inspector Operation Complete Generation Warnings
DBA xPress Schema Inspector Operation Complete Run Errors