New features in xSQL Schema Compare SDK version 9

This page contains information about the new and improved features included in the version 9 of xSQL Comparison SDK. Version 9 brings support for on-premise SQL Server 2016 and the changes on Microsoft Azure v12.

Features in this release are organized into the following categories:



SQL Server 2016 and Azure v12 Features


Version 9 brings full support for the new, on-premise, SQL Server 2016 and the latest features on Microsoft Azure v12:

  • Security Policies
    Security policies are supported on SQL Server 2016 and Azure v12. The new comparison option, Compare security policies, determines whether the policies are compared and synchronized.
  • Column Master Keys
    Column master keys are part of the new "Always Encrypted" feature on SQL Server 2016. The comparison SDK introduces three new options for comparing and synchronizing column master keys:
    • Compare column master keys: indicates whether the master keys should be compared.
    • Compare column master key provider: indicates whether the key-store-provider of a master key should be compared.
    • Compare column master key path: indicates whether the key-path component of a master key should be compared.
  • Column Encryption Keys
    Version 9 supported the new column encryption keys on SQL Server 2016 and Azure v12. A new comparison option, Compare column encryption keys, determines whether the encryption keys should be compared and synchronized.
  • Natively-Compiled Functions
    SQL Server 2016 introduces natively-compiled functions. The schema comparison SDK supports the new functions. Similar to other natively-compiled objects, these functions are non-transactional.
  • Natively-Compiled Triggers
    Schema comparison SDK supports the new natively-compiled triggers. These triggers are non-transactional.
  • New Features on Columns
    The comparison SDK supports the following new column features:
    • 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 referenced by the system-versioning feature.
    The following new options control the comparison of these features:
    • Compare column encryption: determines whether the properties used by the column encryption should be compared.
    • Script column encryption: indicates whether the column encryption should be scripted.
    • Compare column mask: determines whether the column dynamic data mask should be compared.
    • Script column mask: indicates whether the column dynamic data mask should be scripted.
  • System-Versioned Tables
    The comparison SDK supports the following properties used by the SQL Server 2016 system-versioning feature:
    • The new column attributes GENERATED ALWAYS AS ROW START|ROW END.
    • The new HIDDEN attribute of the system-time 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, non-partitioned, or tables with a partitioned history.
    • System-versioning on memory tables.
    System-versioning is supported on Azure v12.

    The following new options control the comparison of system-versioning properties:
    • Compare system versioning: determines whether the sys-versioning should be compared.
    • Ignore system-versioning history table name: indicates whether the name of the history table should be ignored.
    • Script system versioning: determines whether the sys-versioning should be 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 changes to the memory tables. The comparison SDK 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. These triggers are natively-compiled.
    • System-versioning on memory tables.
    • The new memory table IDENTITY property (limited however to IDENTITY(1,1)).
    • Extended properties.
    • A new comparison options, compare hash index bucket count, which determines whether the BUCKET_COUNT property of an hash index should be compared.
  • Remote Data Archiving (Stretched Tables)
    The comparison SDK supports the new remote data archiving (RDA). The comparison and synchronization of this feature is controlled via the following options:
    • Compare remote data archiving: determines whether the RDA feature should be compared.
    • Script remote data archiving: determines whether the RDA should be scripted.
    • Script history remote data archiving: determines whether the RDA of a history table should be scripted.
    • Abandon remote data when disabling archiving: indicates whether the remote data should be discarded when the RDA is disabled.
    Stretched databases are supported as well with some restrictions.
  • User-Defined Table Types
    The comparison SDK now supports indexes on regular 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
    The comparison SDK 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 the version 9 of xSQL Schema Compare SDK:
    • 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.

    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. Some operations 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.

    Setup and Installation


    Version 9 includes some changes and improvements in the installation package:
    • It removes a setup dependency on .NET 2.0. Only .NET 4.0 is required now to install and use the comparison SDK.
    • Similar to the previous release, the setup will automatically remove the previous version of the comparison SDK.

    Licensing and Support


    If you have a valid license and install the version 9 of the schema comparison SDK, you may or may not get support for SQL Server 2016, depending on your licensing options:
    • If you have a Gold 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.