Overview
| Icon | Description |
|---|---|
 | Schema Inspector allow you to view the differences between databases and synchronize the schemas between them. Feature Highlights
|
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.
2. Enter a meaningful comparison name, then select Next to continue.
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:
| Tab | Tab 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. |  | ||
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. |
| ||
Blank Select the Blank tab to use an empty database for the comparison. | N/A |  |
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.
 |  |  |
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
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:
| Option | Description |
|---|---|
| Ignore ANSI NULLS | Ignores the state of SET ANSI NULLS during comparison for SQL programmable objects. |
| Ignore Collation | Ignores collation differences. |
| Ignore WITH NOCHECK | Ignores WITH NOCHECK on check and foreign key constraints. |
| Ignore Data Space Assignments | Ignores the data spaces that objects are assigned to. |
| Ignore IDENTITY Property | Ignores columns whereby one is an IDENTITY column and the other is not. |
| Ignore IDENTITY Seed & Increment | Ignores columns whereby both are IDENTITY columns but the seed and/or increments differ. |
| Ignore Nullability | Ignores nullability on columns and types. |
| Ignore QUOTED IDENTIFIER | Ignores the state of SET QUOTED IDENTIFIER during comparison for SQL programmable objects. |
| Ignore Referential Actions | Ignores ON UPDATE and ON DELETE clause differences for foreign keys. |
| Ignore Object Principals | Ignores differences in object owning principals. |
| Ignore EXECUTE AS Principals | Ignores EXECUTE AS clauses. |
| Ignore Partitioning | Ignores differences in partition ordinals on indexes. |
| Ignore SQL Comments | Ignores comments in SQL programmable objects. |
| Ignore SQL White Space | Ignores white space in SQL programmable objects. |
| Ignore SQL Case | Ignores differences in case in SQL programmable objects. |
| Ignore Target Key Indexes | Ignores indexes that are used by foreign keys on the referenced table. |
| Use Case Sensitive Matching | Causes object name matching to be case sensitive. |
| Ignore Column Length | Ignores the specified length of columns. |
| Ignore NOT FOR REPLICATION | Ignores the differences in specification of NOT FOR REPLICATION clauses. |
| Ignore Precision & Scale | Ignores the precision and scale of columns. |
| Ignore SCHEMABINDING | Ignores the WITH SCHEMABINDING property. |
| Ignore IGNORE DUPLICATE KEY | Ignores use of IGNORE DUPLICATE KEY. |
| Ignore Fill Factor | Ignores index fill factors. |
| Ignore Index Padding | Ignores index padding. |
| Ignore Computed Column Persistence | Ignores computed columns marked as PERSISTED. |
| Ignore Page/Row Lock Allowances | Ignores allow page lock and allow row lock settings on indexes. |
| Ignore Schema Collection Properties | Ignores XML Schema Collection assignments. |
| Ignore Key Ascendancy | Ignores key sort orders on indexes. |
| Ignore INCLUDE Columns | Ignores INCLUDED columns on indexes. |
| Ignore WITH RECOMPILE | Ignores the WITH RECOMPILE clause. |
| Ignore WITH CHECK OPTION | Ignores the WITH CHECK OPTION clause. |
| Ignore NULL ON NULL INPUT | Ignores the NULL ON NULL INPUT property. |
| Ignore Maximum Queue Readers | Ignores the maximum queue readers property on service queues. |
| Ignore Permission Sets | Ignores permission differences on assemblies. |
| Ignore Boundary Values | Ignores boundary value alignments on partition functions. |
| Ignore Accent Sensitivity | Ignores accent sensitivity on full text indexes. |
| Ignore Change Tracking States | Ignores change tracking states on full text indexes. |
| Ignore Selected Stoplists | Ignores stop list differences on full text indexes. |
| Ignore Service Names | Ignores service names on event notifications. |
| Ignore Broker Instances | Ignores broker instances on event notifications and routes. |
| Ignore ROWGUIDCOL Properties | Ignores the ROWGUIDCOL property. |
| Ignore Column Sparsity | Ignores differences in column sparsity. |
| Ignore Data Space Default Selections | Ignores data space DEFAULT settings. |
| Ignore Full Text Catalog Defaults | Ignores full text catalog DEFAULT settings. |
| Find Constraints By Signature | Enables 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 Order | Considers a table as different if it’s columns are in a different order. |
| Find Objects In Any Schema | Allows objects to be found in other schema if they are not found in the correct schema. |
| Ignore Corrected Object Names | Ignores 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:
| Option | Description |
|---|---|
| Batch Separator | The batch separator that's emitted between SQL batches in saved scripts. |
| Use Object Presence Checks | Whether to use IF EXISTS and IF NOT EXISTS when generating DDL SQL. |
| Use Transacted Modification SQL | Whether to place schema modifications in a transaction, with rollback management. |
| Always Specify Collation | Whether collation should always be specified, or only specified when it is different from the database default. |
| Replace User Defined Types With Base Types | Whether user defined types should be scripted as their base type. |
| Keep Trigger Order | Whether trigger order should be preserved. |
| Allow System Named Constraints | Whether 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 Assemblies | Whether to warn about UNSAFE assemblies. |
| Auto Create Files For File groups | Whether to create files for file groups in the default location. |
| Use Online Index Building | Whether to use online index rebuilds. |
| Emit Warnings In Output SQL | Whether to emit the generation warnings in comments with each batch. |
| Emit Table Constraints In-Line | Whether to emit check, default, unique and primary key constraints with their related tables. |
| Fix Object Names | Whether to fix object names that have become incorrect through the use of sp_rename. |
| Rebuild tables when altering assemblies | Whether to rebuild tables when altering assemblies. |
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 Source | Description |
|---|---|
| Live Database | The schema is read directly from a database on a server. |
| Snapshot | The 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:
|
| Blank | The 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.
Select 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.
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.
 |  |
Select the blue drop-down arrow to display the list of individual differences for a selected category.
Deselect a check box next to any individual item to remove it from the synchronization.
 |  |
Select the red X for a category to remove the entire category from synchronization.
 |  |
Select the green check mark for a category to include the entire category during synchronization.
 |  |
After completing your comparison selections, select Next Step to continue to the View Action Plan screen.
Toolbar Buttons
The following is a detailed list of the toolbar buttons on the Schema Inspector View Differences page:
File Buttons
| Button | Description | Image |
|---|---|---|
 | Opens the Add Comparison Scenario window and allows you to create a new scenario. |  |
 | Opens the Open Scenario window and allows you to select a previously created scenario. Select the desired scenario, then select Open to load your comparison. |  |
 | Saves the current comparison scenario. | |
 | Opens 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. |  |
Snapshot
| Button | Deescription | Image | ||
|---|---|---|---|---|
 | Opens 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. |
|
Scenario
| Button | Description | Image |
|---|---|---|
 | Select Settings to change your configured settings prior to synchronization. |  |
 | Select Refresh to reload the comparison. |  |
View Differences Page Buttons
| Button | Description | Image |
|---|---|---|
 | Select Next Step to continue to the View Action Plan screen. | |
 | Select Statistics to open the Comparison Statistics window for your comparison. |  |
 | The 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. |  |
 | Select Report to open the Generate Report window. |  |
Adding Filters
Select the left side of the filter button to open the Filters 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.
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.
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.
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.
2. Select your reporting type, then enter a destination folder. Select OK to generate your report. 
Success: After your report has finished generating, it is ready to view! 
Reporting Type Buttons
| Button | Description |
|---|---|
 | HTML reports use standard HTML files with one topic per page. |
 | HTML frame set reports keep the navigation for the report in a frame on the left. |
 | RTF 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.
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.
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.
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.
| Button | Description | Image |
|---|---|---|
 | Displays a preview of the SQL script. |  |
 | Begins the synchronization process for your selection. |  |
Synchronizing Your Comparison
After configuring the options listed above, you must select a synchronization method. The following synchronization methods are available:
| Synchronization Method | Description | Availability |
|---|---|---|
 | Direct 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. |
 | Script 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. |
 | Hybrid 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. 
Direct synchronization 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.
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.
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.
 |  |  |
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 .
Enter a name for your script, 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. The script method displays any generation warnings associated with your synchronization. Select the Generation Warnings tab to view any warnings associated with your synchronization.
 |  |
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. 
Hybrid synchronization 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.
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.
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.
 |  |  |
