Schema Inspector will allow users to view differences between databases and synchronize the schemas between them.
• View differences between databases
• Synchronize the schemas in two different databases
Table of Contents
Comparisons are at the heart of Schema Inspector, and they all follow a five step process
All comparisons have a left and right hand side - these are the data sources. The first two steps in the process relate to the process of loading the data sources for the left and right hand side, and then comparing the two. Once that is done, we can choose the objects that we want to Synchronize. After that, an action plan is created, which details all the steps necessary to synchronize the chosen object, including any dependencies, and then synchronization can take place.
Data sources can be one of three types
• A live database - This is where the schema is read directly from a database on a server.
• A snapshot -This is where the schema is read from a file containing a snapshot of the schema previously saved. This can be useful in many scenarios, for example
o Comparing with a production environment database without direct access to that production environment
o Comparing with a known previous state
o Re-creating a database schema from a customer site
o Running a release methodology based on previously published schema
• Blank - This is where the schema is empty. This is useful for scripting the contents of a database. It can, conversely, be used to produce drop scripts for every object in a database.
There are also a lot of options which control both what differences a comparison will take into account, and how the synchronization scripts are generated. Documentation can be generated from a comparison, both in the form of a differences report (documenting the difference in state between the databases) and a synchronization report (documenting which objects were synchronized).
Before getting started with comparisons, however, we must first create a new comparison scenario.
Create a New Comparison
Open the Schema Inspector from the 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:
On these pages, you can use the tabs at the top to select the style of data source. It can either be a live database server, a snapshot that you have taken previously, or blank, which represents an empty database. When using a database, 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.
Schema 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.
When using a snapshot, you select the snapshot file that you wish to use. There are no options for the blank source. 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 you have finished configuring your comparison scenario, click on 'Finish' and the comparison will begin loading.
Comparison documentation can be generated in two forms: Difference documentation and Synchronization documentation.
Difference documentation is generated from the view differences page of a comparison, and documents the state of the two data sources, irrespective of what action is being taken to synchronize them. Difference documentation contains a list of objects in the left hand source only, a list of objects in the right hand source only, a list of objects that are in both sources that differ (with a list of their differences) and, optionally, a list of objects that are equivalent. The documentation also contains a list of the comparison options in use at the time of the comparison.
Synchronization documentation is generated from the process complete page of a comparison, and 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. The documentation also contains a list of the comparison options and the generation options in use at the time of the comparison.
When generating documentation from either location, a dialog similar to the following is shown:
At the top are buttons to select the type of report that is generated. There are HTML reports, which use standard HTML files with one topic per page, HTML frame set reports which keep the navigation for the report in a frame on the left (much like this help) and RTF reports, which 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 into which the documentation will be saved. For both HTML type reports, the destination is a folder, whereas for the RTF style of reporting, the destination is a file.