Advantages of ApexSQL Diff

ApexSQL Diff is a tool for comparison and synchronization of SQL Server database schemas and it can compare live databases, backups, script folders, and source control projects. In this article, key and unique features, as well as important advantages of ApexSQL Diff will be shown and explained.

SSIS package comparison

In addition to mentioned data sources, ApexSQL Diff can compare different types of SSIS packages. To start SSIS comparison click the click the arrow on the New button from the Home tab and choose SSIS compare:

In the Data sources tab you can choose between five different SSIS packages that can be compared:

Learn more on How to compare SSIS packages and export difference results.

Object filter

ApexSQL Diff has the ability to select object types and individual objects for the comparison after setting up data sources. The Object filter feature can filter out objects and in that way speed up the comparison process, by excluding objects that are not needed for the comparison.

To turn on the Object filter, click the Show advanced options button in bottom left corner of the New project window and the Object filer tab will appear on the left side:

In the Object filter tab, choose which object types to include in the comparison:

Furthermore, specific objects of any object type can be filtered. In the example above, the Use filter option is checked for procedures and the Procedures node appeared under the Common nodes. With a click on the Procedures node, the new window is shown along with a list of all procedures that can be checked/unchecked for the comparison process:

Difference filter

After the comparison process is done and results are shown in the Results grid, more object filtering can be applied. The Difference filter is one more filtering option to filter out the comparison result even more. Objects differ based on several attributes.

For example, DDL triggers can differ in extended properties, but if that doesn’t need to be taken into consideration, the extended properties can be ignored and that DDL trigger won’t be shown as not equal in the Results grid section:

In the above screenshot, the extended properties are unchecked in the Difference filter and after a click on the Apply filter button, the results will be refreshed and DDL trigger won’t be shown as a not equal object:

Difference by type

One more feature that can help to quickly determine differences is the Differences by type feature. It shows which specific type has differences, which are shown in detail in the Script difference view pane:

Native source control integration

ApexSQL Diff has native support for Git, Mercurial, Team Foundation Server, Apache Subversion, and Perforce.

With native integration, ApexSQL Diff can seamlessly create a source control repository totally independently, without the requirement for additional components and or interaction with other interfaces e.g. a separate source control GUI.

When Source control is set as a destination data source, ApexSQL Diff can compare and synchronize directly against the source control project:

Labels

ApexSQL Diff’s source control implementation has an option to compare e.g. a database with a specific label of a source control project, by selecting the Get by label option in the Source control wizard. In addition, ApexSQL Diff can perform the synchronization process to a specific label and in that way take a snapshot of the current state between two data sources:

The label can be used afterwards to revert the database to a specific state.

Data source export

ApexSQL Diff can export a database to three different data sources: Snapshot, Script folder, and Source control. The complete database can be easily exported to any of these three data sources with a click on the Export data sources button in the New project window:

Additional scripts

When the Show additional options button is clicked, the Additional scripts tab is shown. Under this tab, pre-processing and/or post-processing script can be loaded and/or created, which could be helpful in certain scenarios.

For example, if there is a need to synchronize a source database that contain tables from the “Resources” schema, with several databases and not all of them contain “Resources” schema, the pre-processing script has to be created that will check if a “Resources” schema exists, and if not to create it:

IF NOT EXISTS ( SELECT schema_name 
				FROM information_schema.schemata 
				WHERE schema_name 
				= 
				'Resources' ) 
BEGIN EXEC sp_executesql N'CREATE SCHEMA Resources';

Also, if some different user has to be created for the destination database, which exists in the source database, then a post-processing script can be used to create it:

CREATE LOGIN <SClogin> 
    WITH PASSWORD = '<goodpassisentered>'; 
USE <TargetDB>; 
GO 
CREATE USER <SCuser> FOR LOGIN <SClogin>; 
GO

To load the already created script, just check the Use this script option and provide the path to the script. If a script needs to be created, check the Use embedded script option and click the Edit button that will open a built-in editor where a script can be entered:

Include owners

In the New project window, under the Options tab, there is the Manage owners option. This option provides the ability to decide whether to include/exclude the owner name in a database object reference in the synchronization script.

Owners can be managed in the following ways:

1. Exclude owners
   • Always,
   • Only if different from the connected database,
   • Only if different from the database owner

2. Include owners:

   

By default, all objects are added to the synchronization script as they exist in each compared database.

The Exclude owners option can be helpful when objects are the same, but their schema names differ in master and target database: in the master database objects are under the “dbo” schema name and in the target database are under different schema name. This option will exclude schemas from the ApexSQL Diff’s synchronization script and after the script is executed, objects will be synchronized to the destination (target) database and they will keep their schema names.

When the Include owners option is selected, it will force adding schemas to the ApexSQL Diff’s synchronization script and therefore lead to synchronizing objects with the same owners and object names in both, source and destination database. With this option selected, it is clear which objects will be synchronized and there is no room for errors nor schema ambiguity.

Here is an example of a synchronization script when the Manage owner option is checked to Include/Exclude owners:

Performance

ApexSQL Diff has a high-performance comparison engine that compares most databases significantly faster than the leading competitor. Please see the full details of our recent performance tests here.

Three different editions

The application has Community, Standard, and Professional versions with various options included.

The Free Community edition supports SQL Server Express databases, SQL Azure databases, and basic comparison and synchronization options.

The Standard edition fully supports SQL Server editions (2005-2014) and comparison and synchronization of ApexSQL snapshots.

The Professional edition includes all options from the Standard edition, including the CLI support, augmentation of the deployment script with pre or post-synchronization scripts, deployment of the latest object changes directly from source control, ability to create a live database from scripts, and to restore selected objects from database backups.

For more information on the different editions in ApexSQL Diff, please visit this link.