What’s new in ApexSQL Diff 2017

In the new version of ApexSQL Diff 2017, many new features and enhancements have been added.

SQL Server Integration Services package comparison

From this release ApexSQL Diff supports SSIS package comparison and it offers support for five different packages:

  1. Package files or file system
  2. SSIS package store for SQL Server from 2005 to 2016
  3. SQL Server
  4. SSISDB catalog
  5. SSIS project file

SSIS comparison can be initiated from the main application window by clicking on the New button arrow and choosing the SSIS compare option:

Also, it can be initiated from the Project management window, by clicking the arrow next to the New button and choosing the SSIS:

In the Data sources tab, choose the source and destination packages that need to be compared and click the Compare button:

Once the comparison is finished, package differences will be shown in the Results grid, along with the XML differences of the compared packages that can be reviewed in the Difference view panel and those differences can be exported into an HTML report:

Create snapshot or rollback script of a destination database before synchronization

In addition to standard backup the database option, when using the Synchronize now action (direct synchronization) ApexSQL Diff can now create ApexSQL snapshot of the destination database that will capture the destination database schema state before the synchronization.

The second option, creating a rollback script can come in handy if synchronized changes are not correct or needed ones, so the rollback script can rollback all changes made during the synchronization and gets back the destination database state as it was before the synchronization.

Both created files will be date stamped during the creation, so it can be easier to find the exact needed one:

Create script folder backup for destination script folder or source control before synchronization

One more new option is the Backup script folder option that can create a backup of the script folder or source control destination data source, before it’s synchronized.

This option creates a copy of the destination data source as the script folder to a specified location and it contains state of scripts of all objects before the synchronization:

XML export customization options

As the other reports and exports have their customization options, ApexSQL Diff now supports customization options for XML schema difference export:

While in previous versions everything from the Results grid was exported to an XML file, now it can be customized by choosing to include only specific objects, different or equal, source or destination only. Also, the application now supports including DDL scripts of the checked objects and it can be used to build a “smart” SQL Server Data dictionary.

Export schema comparison differences to SQL script and a database table

In this release, two new exporting features are introduced. Now the schema comparison results can be exported into:

  1. SQL file
  2. SQL Server database

When exporting to a SQL file, a created file will contain 4 create table statements, that should create the following tables with corresponding details:

[dbo].[SchemaComparisonJob]
[dbo].[SchemaComparisonDetails]
[dbo].[SchemaComparisonObjectDiffs]
[dbo].[SchemaComparisonWarnings]

The created SQL script looks like this:

If you want to export the comparison results directly into a database, once the Export to database option is selected, the Export to database window is shown in which server, authentication, and database should be selected and the OK button is clicked:

Once the comparison results are exported, expand the Tables node in Object Explorer in SQL Server Management Studio and the above mentioned four tables will be shown and their content can be reviewed:

New Script difference view panel options

Four new options are added to the Script difference view panel and all of them are accessible from the right-click context menu:

  1. Find
  2. Increase/Decrease font size
  3. Word wrap
  4. Change colors

The Find option allows to find specific word or character in the source or destination script. Additionally, when the Advanced button is clicked, additional find options are shown that can be used to locate a desired word:

Using the Increase/Decrease font size option, the font size of the source and destination script can be easily manipulated to increase or decrease font size based on user’s preferences.

The Word wrap option moves the long rows into the next one, so it doesn’t extend past the vertical scroll bar and in that way eliminates the horizontal scroll bar and need to move it to the right in order to see the long rows.

The Change colors option allows manipulating with color highlighting of the background and with source and destination differences highlighting.

Default colors for the highlighting in the Script difference view panel is shown below:

To change the color highlighting, right-click in the Script difference view panel and choose the Change colors option:

When each of the shown colors is clicked the Color window is show where basic or custom colors can be chosen to replace default coloring. Once everything is set up, click the OK button and changes will be shown in the Script difference view panel:

No differences detected return code

The new return code is returned when the compared data sources are equal or no objects have been included in the comparison process, and the /rece switch is used.

Learn more about Utilizing the “no differences detected” return code in ApexSQL Diff.

26 new CLI switches

In this release, 26 new CLI switches are introduced into ApexSQL Diff. These new CLI switches cover all new GUI functionalities and a couple of previous ones.

The list of all new CLI switches with their descriptions, is shown below:

/backup_set1Specifies the file name of the source differential backup set
Alias: /bs1
Format: /backup_set1:filepath
/backup_set2Specifies the file name of the destination differential backup set
Alias: /bs2
Format: backup_set2:filepath
/integration_
services_
packages1
Integration services packages used as the source data source
Alias: /isp1
Format: /integration_services_packages1:
service[:SQL2005|SQL2008|SQL2012|
SQL2014|SQL2016].package1…packageN[:password]

(service can be: servicesname or [ipaddress])
/integration_
services_
packages2
Integration services packages used as the destination data source
Alias: /isp2
Format: /integration_services_packages2:
service[:SQL2005|SQL2008|SQL2012|
SQL2014|SQL2016].package1…packageN[:password]

(service can be: servicesname or [ipaddress])
/package_files1Package files used as the source data source
Alias: /pfs1
Format: /package_files1:file1
[:password]…fileN[:password]
/package_files2Package files used as the destination data source
Alias: /pfs2
Format: /package_files2:file1
[:password]…fileN[:password]
/sql_server_
packages1
SQL Server packages used as the source data source
Alias: /ssp1
Format: /sql_server_packages1:server[.user][.password]:package1.package2…
packageN[:package_password]

(server can be: servername or [ipaddress])
/sql_server_
packages2
SQL Server packages used as the destination data source
Alias: /ssp2
Format: /sql_server_packages2:server[.user]
[.password]:package1.package2…
packageN[:package_password]

(server can be: servername or [ipaddress])
/ssisdb_
catalog_
packages1
SSISDB catalog packages used as the source data source
Alias: /sdbp1
Format: /sql_server_packages2:server[.user]
[.password]:package1.package2…packageN
[:package_password]
(server can be: servername or [ipaddress])
/ssisdb_
catalog_
packages2
SSISDB catalog packages used as the destination data source
Alias: /sdbp2
Format: /ssisdb_catalog_packages2:server[.user]
[.password]:package1.package2…packageN
[:package_password]
(server can be: servername or [ipaddress])
/ssis_project_
file1
SSIS project file used as the source data source
Alias: /sspr1
Format: /ssis_project_file1:file_path
/ssis_project_
file2
SSIS project file used as the destination data source
Alias: /sspr2
Format: /ssis_project_file2:file_path
/create_snapshot_
before_sync
Create snapshot of destination database before synchronization
Alias: /csnp

/snapshot_
file_name
Full path and file name for the destination database snapshot, along with a “.axsnp” extension
Alias: /sfn
Format: /snapshot_file_name:
DestinationSnapshot.axsnp
/create_rollback_
script_before_sync
Create rollback script for destination databases before synchronization
Alias: /crbs

/rollback_script_
file_name
Full path and file name for the rollback script, along with a “.sql” extension
Alias: /rsfn
Format: /rollback_script_file_name:
RollbackScript.sql
/backup_script_
folder
Create script folder backup for destination script folder or source control before synchronization
Alias: /bsf

/backup_script_
folder_path
Full path to the folder for the script folder backup
Alias: /bsfp
Format: /backup_script_folder_path:FolderName
/summary_outputFull path and file name for the synchronization summary output along with the ‘.log’ extension
(if omitted ‘C:\Users\UserName\AppData\Local\ApexSQL\ApexSQLDiff\Log\Changes.log’ will be used)
Alias: /suo
Format: /summary_output:SyncSummary.log
/warnings_outputFull path and file name for the synchronization warnings output along with the ‘.log’ extension
(if omitted ‘C:\Users\UserName\AppData\Local\ApexSQL\ApexSQLDiff\Log\Warnings.log’ will be used)
Alias: /wao
Format: /warnings_output:Warnings.log
/comparison_
summary_output
Full path and file name for the comparison summary output along with the ‘.log’ extension
Alias: /cso
Format: /comparison_summary_output:
CompSummary.log
/xml_export_
options
Set of primary XML schema export options:

  • Equal [e]
  • Different [d]
  • Source [s]
  • Destination [t]
  • CheckedOnly [co]
  • IncludeScripts [is]
  • Default [dft] – combination of Equal, Different, Source and Destination options

Alias: /xeo
Format: xml_export_options:[e|d|s|t|co|is]
Note: Arguments can be combined

/xml_export_
options2
Set of primary XML schema export options:

  • Equal [e]
  • Different [d]
  • Source [s]
  • Destination [t]
  • CheckedOnly [co]
  • IncludeScripts [is]
  • Default [dft] – combination of Equal, Different, Source and Destination options

Alias: /xeo2
Format: xml_export_options2:[e|d|s|t|co|is]
Note: Arguments can be combined

/beepPlays sound upon finished operation
Alias: /be

/log_levelSet one of the following logging levels:

  • Error
  • Warning
  • All
  • None

Alias: /log
Format: /log_level:[Error|Warning|All|None]

/return_error_
code_on_equal
Forces return code 102 – no differences detected, if the compared data sources are equal, otherwise the application returns 0 return code – success
Alias: /rece

For the complete list of check out the article ApexSQL Diff Command Line Interface (CLI) switches.

March 1, 2017