Run two or more projects side by side in separate tabs, whether it's two schema compare projects on completely separate servers or a schema compare and data compare project on the same server.
Because each process is run in separate threads you can be reviewing or even running an update script while waiting for other compare processes to complete.
You can assign each project to a project groups making organizing your projects easier. You can place all your data compare projects into a data group. Place a specific server group of projects under one group.
Comparing two databases is quick and easy. In a new project select your source database target then your target database and click to run your schema compare.
SQL Delta shows an overview of all the objects that are different or not in either the source or target. By selecting each object you can see at the schema level the various differences.
Each schema compare project has a range of options to reduce discovery common differences. For example objects constraints with system names are usually different between source and target and these can be ignored.
For some projects your target schema names (owners) are different to your source schema names. Each compare project has the option to map source schema names to target schema names.
SQL Delta can then optionally automatically change the definitions to use the correct schema name. For example your source database uses "dbo" and your target uses "prod" as the schema name for all objects. Simply map dbo to prod and any script changes will ensure the target object is prod.object instead of dbo.object.
Within the compare results the objects can be filtered based on a range of options. For example hide all objects that are the same is a frequent filter.
Filters can be persisted so whenever you re-run the project the filter is re-applied. This persistence can also be used to preselect or pre-deselect objects which is handy if you have objects in your source that should not be sent to target.
You can apply a filter to the schema load to exclude objects from loading. For example you could have a set of functions that are part of your source database that are never part of your target database. By adding the load filter you can exclude these functions from the load and therefore the compare.
Once the schema compare tab is shown simply click Script to generate the deployment script and then click Execute to apply to the target.
SQL Delta can generate an HTML report of the schema compare highlighting the object differences throughout the schema.
Sometimes it's nice to be able to change the script or add script to the final deployment script. The synchronize script is completely editable plus within project options you can add custom script that is added to the sync script for each deployment.
Data compare uses one or more indexes columns, usually the primary key, to determine if a row in source matches a row in the target table. If a match is found then data compare scan each column looking for differences.
By default Data Compare is straight forward, select the source and target databases and run the compare. SQL Delta automatically determines the tables with primary keys and matching column names and performs the compare.
If you wish to get more granular you can select the tables to compare, define your own index columns and even define the columns to compare.
Sometimes a table in the source database has a different name to a table in the target database. Data compare can map the source table name to the target table name. The same applies to columns where a source column can be mapped to a target column. Data compare takes care of the name changes within the deployment scripts.
Table data can be filtered to exclude specific rows during load and the filters use standard where clause formats.
Columns can be configured to apply a conversion for comparison or for both comparison and scripting.
For example you might like to compare a numeric column that stores to 2 decimal places (money values) however in this instance only whole numbers are significant to you.
You can apply a round(..) conversion to the column and this will remove the decimal values and compare the whole numbers.
Table data can be filtered to exclude specific rows during load and the filters use standard where clause formats.
Views can be optionally included as part of the data compare.
A source view could be used to map to a target table providing a method of populating a target table from a view.
The data compare script can be huge and is automatically saved to file during the scripting process.
Data compare script display can show millions of lines of code so you can view even the largest deployment scripts.
Generate an HTML report showing the difference summary for your data compare.
Schema View is an editor function that shows the entire schema of your database.
For objects such as tables and views you can also see the data within the table or view.
Looking for something within your database? Schema view can filter down to the definition level of objects so you can quickly locate elements within your database.
Run DML or DDL queries against your database.
For example perhaps the schema compare shows an unexpected change to a source table and you want to review the data and other information relating to both source and target database.
From the project manager duplicate your compare project, switch it to a schema view project and run it. You can now see the original schema and data within tables and views. Perhaps the data explains the change or comments in the table provide more information.
Each project can have a range of events that fire after the project has run.
- Display the Synchronize script
- Save the Synchronize script
- Execute the Synchronize script
- Generate Compare Report
- Run project after Compare
- Run project after Script
- Save Source/Target as Snapshot
- Execute a Script
Combined with command line functionality allows SQL Delta to run in a fully automated mode.
SQL Delta can be run by passing a project name as a parameter in the command line. For example in Windows run CMD and then with SQL Delta in the path you can type sqldelta.exe "myproject" and your myproject will automatically run.
Another option is to create a shortcut and add your project name to the target.
SQL Delta locates the project within the project manager list, matching against either name or filename and runs the project based on details found. Alternatively the project filename is matched against existing filenames, excluding any path.
Our change is if a project filename cannot be matched to an existing filename in the project manager list then that project file becomes a template project. Template projects cannot be edited or saved, basically they don't exist in the project manager.
The benefit of template project is the connection details can be dynamically changed via command line switches. Using a batch process multiple databases can be compared using a template project just by changing the command line switches. The limitation of templates is they do not access any filters that may have been persistent to a project.
A template project can still have On Completion events, even load filters and mapping. For data compare projects we recommend using the Automatic mode to allow SQL Delta to automatically load and map the source and target tables each time the project runs which means even dynamic changes to the connection information don't limit the data compare.
To create a template project the project filename cannot exist in the SQL Delta project manager. If a project filename is found in the project manager list then any command line connection data switches are ignored.
"project name" or "\path\projectfilename.xdp"
/A Run all checked projects found in the Project manager
/L Generate a log file regardless of project settings. A configured log file will take precedence over this switch
/M:x Force the display to switch to Monitor number x. SQL Delta launches on the last monitor used. In rare cases i that monitor is no longer available SQL Delta may launch off screen. Use this setting to force SQL Delta to launch on a specific monitor.
/Q Quiet or Silent mode when the GUI is essentially hidden. SQL Delta will automatically shut down once the project has run and all on completion events have finished
/R Once a compare completes automatically generate a sync script and deploy. If combined with /L then a log of differences, script errors and after script differences is generated. On completion events take precedence and this switch will be ignored.
Connection Data Switches - Replace the template project content with the following:
/SS="Server Name" Source Server Name e.g. "My Server"
/SU="User Name" Source User name
/SP="!4GFDDGF" Source Password. Normally use the SQL Delta encrypted password which contains an ! at the start otherwise plain text
/SD="Database Name" Source Database e.g. "My Database"
/TS="Server Name" Target Server Name e.g. "My Server"
/TU="User Name" Target User name
/TP="ClearPass" Target Password. Normally use the SQL Delta encrypted password which contains an ! at the start otherwise plain text
/TD="Database Name" Target Database e.g. "My Database"
If more than one template filename is used then each project will have its details overridden by any alternate data. If an On Completion event runs another project then it's details will not be changed by the alternate data.
Filenames
Normally the default folder locations will be used when running a command line project. For example a report will be saved to the reports folder shown in System Settings.
However the CLI project folder will take precedence over the default when running a CLI project from a different location.
If the CLI project folder has reports, script and temp folders then they will be used otherwise the project folder will store any output.
Some On Completion events require a filename and if the filename contains a backslash (\) then that path will override any system defaults. The rules are:
If the file name starts with [Drive Letter]:\ or \ then use the path as an absolute path.
If the file name contains a \ then extract the path and use it relative to the current project folder.
Otherwise save the file to the default location such as script or report.
Finally if a folder does not exist then the current project file location will be used.
A Snapshot is an XML representation of the database schema and this file can then be stored with version control or just copied to another location as a schema backup.
Both Schema Compare and Schema View projects can generate a snapshot of the database schema.
A snapshot can be used as the source of a schema compare providing a way to restore from a backup or simply compare changes between snapshots.
There are 4 compare reports and two schema reports available in Schema Compare. Each report is created as an HTML file.
The first four reports provide different levels of detail for the schema compare. The source/target schema reports generate an HTML file displaying the schema details for the entire database.
Generate a summary HTML file to show the differences for the data compare project.
Generate a tree view HTML file to show the database schema or generate an HTML view of the selected object.