Schema and Data Comparison for SQL Server version 9 - New and Updated Features

This page contains information about the new features, changes and enhancements included in the version 9 of the xSQL Schema and Data Comparison for SQL Server. Version 9 not only brings support for the on-premise SQL Server 2016 and the new features on Azure v12, but it also contains over twenty new comparison options and includes the new command line wizards for schema and data compare. To view release notes for other versions, click here.

Features in this release are organized into the following categories:

New SQL Server 2016 and Azure v12 features
New schema comparison options
New schema compare command line wizard
Other schema compare changes
Schema compare bug fixes

New data compare features
New data compare options
New data compare command line wizard
Other data compare changes

Changes in the installation package
Licensing and support

New in Schema Compare Version 9

New SQL Server 2016 and Azure v12 Features

Version 9 brings full support for the on-premise SQL Server 2016 as well as the latest version of Azure v12:

Security Policies: Schema compare supports the security policies on-premise and on Azure databases. It identifies and synchronizes security policies on all database objects that are permitted by SQL Server: regular disc tables, in-memory tables and views. The new option, Compare security policies, determines whether the policies are compared and synchronized.
Column Master Keys: Schema compare supports the column master keys. Three new options control the comparison of the CMK:
- Compare column master keys: determines whether the CMK are compared.
- Compare column master key provider: indicates whether the key-store-provider of a CMK is compared.
- Compare column master key path: indicates whether the key-path component of a CMK is compared.
Version 9 supports the CMK on Azure v12 with some restrictions imposed by the cloud infrastructure.
Column Encryption Keys: Schema compare supports the column encryption keys on-premise and on Azure databases. The new option, Compare column encryption keys, determines whether the CEK are compared and synchronized.
Natively-Compiled Functions: Schema compare supports the new natively-compiled functions. This type of function, similar to the memory tables and other natively-compiled objects, is non-transactional.
Natively-Compiled Triggers: Version 9 supports the new natively-compiled triggers. This type of trigger is non-transactional.
New Features on Columns: SQL Server 2016 introduces many new features on columns. Schema compare supports the followings:
- Encryption: The new column encryption specified via the clause ENCRYPTED WITH (…).
- Dynamic Data Mask: Dynamic data mask specified via the clause MASKED WITH (FUNCTION = email()|default()|partial()…). Dynamic data mask provides data obfuscation.
- System-versioning columns: Includes new attributes that identify a column as the sys-time column for the system-versioning feature.
The following new options control the comparison of these new features:
- Compare column encryption: determines whether the properties used by the column encryption are compared.
- Script column encryption: indicates whether the column encryption is scripted.
- Compare column mask: determines whether the column dynamic data mask is compared.
- Script column mask: indicates whether the column dynamic data mask is scripted.
System-Versioning or Temporal Tables: System-versioning is a major feature that contains many parts and involves many objects. Below is a list of the main sys-versioning components that schema compare supports:
- The new column attributes GENERATED ALWAYS AS ROW START|ROW END.
- The new HIDDEN attribute of the system-versioning columns.
- The new table embedded clause: PERIOD FOR SYSTEM_TIME (<valid-from-column>, <valid-to-column>).
- The new table option: SYSTEM_VERSIONING = ON (HISTORY_TABLE = <table>, DATA_CONSISTENCY_CHECK = ON|OFF).
- System-versioning on partitioned tables, or non-partitioned tables with a partitioned history.
- System-versioning on memory tables.
- System-versioning on Azure v12.
System-time columns are always marked as equal and do not trigger a schema difference. Schema compare scripts them even when the target table is not sys-versioned. This allows you to enable the table sys-versioning later, if needed, using just an ALTER TABLE statement.
The following new options control the comparison of sys-versioning infrastructure:
- Compare system versioning: determines whether the sys-versioning is compared.
- Ignore system-versioning history table name: indicates whether the name of the history table should be ignored and not compared.
- Script system versioning: determines whether the sys-versioning is scripted.
- Drop system-versioning history table: indicates whether the history table should be dropped when the system-versioning is disabled or the primary table is dropped.
In-Memory Tables: SQL Server 2016 brings many enhancements to the memory tables. Schema compare supports the followings:
- The new unique constraints on memory tables.
- The new check constraints on memory tables.
- The new foreign keys on memory tables.
- The new AFTER triggers on memory tables. Triggers are created as natively-compiled objects.
- System-versioning on memory tables.
- The new memory table IDENTITY property (limited however to IDENTITY(1,1)).
- Extended properties on the new objects.
- A new comparison options, compare hash index bucket count, which determines whether the BUCKET_COUNT property of an hash index is compared.
Stretched Tables: Schema compares supports the stretched tables or the remote data archiving (RDA) feature. RDA is supported even on system-versioned tables. The following new options control the comparison of the stretched tables:
- Compare remote data archiving: determines whether the RDA feature is compared.
- Script remote data archiving: determines whether the RDA is scripted.
- Script history remote data archiving: determines whether the RDA of a history table is scripted.
- Abandon remote data when disabling archiving: indicates whether the remote data is discarded when the RDA is disabled.
Stretched Database: Schema compare supports a stretched database. It cannot however enable stretching automatically for the following reasons:
- Database stretching relies on the database master key. Schema compare cannot create the master key since its password is not kept in the database catalog.
- Database stretching communicates with the remote data via a database-scoped credential. The credential requires an Azure account, which schema compare cannot create on its own.
Despite these limitations, schema compare generates a template script that you can manually run in SSMS, with the necessary adjustments, and enable the database stretching.
User-Defined Table Types: Version 9 now supports indexes on regular table types, similar to the memory table types.
Columnstore Indexes: Columnstore indexes include the following enhancements:
- The new filter predicate, specified via the WHERE clause.
- The new COMPRESSION_DELAY option.
New Permissions: Schema compare supports all new permissions introduced in SQL Server 2016. The same permissions are supported on Azure v12 as well.

New Schema Comparison Options

Below is a summary of the new options included in this version of the schema compare:

Compare security policies: indicates whether the security policies are compared.
Compare column master keys: indicates whether the column master keys are compared.
Compare column master key provider: indicates whether the provider of a column master key is compared.
Compare column master key path: indicates whether the key-path property of a column master key is compared.
Compare column encryption keys: indicates whether the column encryption keys are compared.
Compare column encryption: indicates whether the properties related to the column encryption, such as the encryption key or the encryption algorithm, are compared.
Compare column mask: indicates whether the column dynamic data mask is compared.
Compare system versioning: indicates whether the sys-versioning feature is compared.
Ignore system versioning history table name: indicates whether the name of the history table associated with the sys-versioning is ignored.
Compare hash index bucket count: indicates whether the BUCKET_COUNT property of an hash index is compared.
Compare remote data archiving: indicates whether the remote data archiving feature (or stretching) is compared.
Script column encryption: indicates whether the column encryption is scripted.
Script column mask: indicates whether the column dynamic data mask is scripted.
Script system versioning: indicates whether the sys-versioning is scripted.
Drop system versioning history: indicates whether the history table should be dropped when the sys-versioning is disabled.
Script remote data archiving: whether the remote data archiving (or stretching) is scripted.
Script history remote data archiving: whether the remote data archiving (or stretching) is scripted a system-versioned table.
Abandon remote data when disabling archiving: whether the Azure remote data should be discarded when the stretching is disabled.

New Schema Compare Command Line Wizard

The command line wizard is a new tool that provides a simple, step-by-step, wizard-based interface for generating command line config files. Version 9 adds also a new option in the schema compare UI that allows you to create the command line files directly from a comparison session.

Other Schema Compare Changes

The following changes effect the UI or the command line of the schema compare:

The database selection popup now contains the entity filters. You can specify them before comparing the schema.
The command line <Database> element contains the following new options:
- ConnectionString: a compact way of specifying the database connection settings all in one connection string.
- PacketSize: the SQL Server connection packet-size
- Pooled: indicates whether the connection should be pooled
- OtherProperties: other connection properties as name=value separated by a semicolon: name1=value1;name2=value2...
The command line supports all the new comparison options.
Schema compare now uses UTF-8 encoding for all files.

Schema Compare Bug Fixes

Version 9 fixes the following issues:

Fixes an issue with the SPARSE column. The NULL|NOT NULL constraints must be specified after the SPARSE attribute.
Fixes an issue with the extended characters, such as Chinese letters. Some operations, such as copying the schema script, didn't handle these characters properly.
Fixes an issue with the DATA_COMPRESSION attribute on a filestream table. If the filestream table contains an embedded primary key, data compression must be specified either under the primary key or the table, but not both.

New in Data Compare version 9

New Data Compare Features

Data compare version 9 contains the following new features:

It compares the system-versioned tables, both regular disc tables and memory tables.
It can disable the system-versioning before synchronizing the data.
It compares the data on stretched tables.
It can set the stretch table query scope, so that queries against the stretched tables can select local data only or local and remote data.
It supports the new memory table foreign keys and natively-compiled triggers.

New Data Compare Options

Data compare contains the following new options:

Compare system versioned tables: indicates whether the sys-versioned tables (or temporal tables) are compared.
Disable system versioning: whether the sys-versioning is disabled before synchronizing the data.
Compare stretched tables: whether the stretched tables are compared.
Stretched table query scope: set the query scope, so that queries against a stretched table select the local data only or local and remote data.

New Data Compare Command Line Wizard

The command line wizard is a new tool that provides a simple, step-by-step, wizard-based interface for generating command line config files. Version 9 adds also a new option in the data compare UI that allows you to create the command line files directly from a comparison session.

Other Data Compare Changes

Below are some additional data compare changes. They effect the UI or the command line.

The command line <Database> element contains the following new options:
- ConnectionString: a compact way of specifying the database connection settings all in one connection string.
- PacketSize: the SQL Server connection packet-size
- Pooled: indicates whether the connection should be pooled
- OtherProperties: other connection properties as name=value separated by a semicolon: name1=value1;name2=value2...
The command line supports all the new comparison options.
Data compare now uses UTF-8 encoding for all files.

Installation Package

Version 9 includes some changes and improvements in the installation package:

It removes a setup dependency on .NET 2.0. From now on, only .NET 4.0 is required to install and use the comparison bundle.
The Snapshot Converter utility is no longer included in the installation package. If you have old snapshots and need to convert them, please email us at support@xsql.com and we will provide this utility to you.
This release will automatically remove the previous version of the comparison bundle, if you have it installed in your machine.

Licensing and Support

If you have a valid license and install version 9 of the comparison bundle, you may or may not get support for SQL Server 2016, depending on your licensing options:

If you have a subscription, SQL Server 2016 will be supported and no additional action is required.
If you have a perpetual license with an active support/maintenance plan, we will email you a new license. When you activate the new license, version 9 will support SQL Server 2016
If you have a perpetual license and your support has expired, version 9 will not support SQL Server 2016. Other SQL Server versions will continue to work with no restrictions.