Schema and Data Comparison for SQL Server version 11 - New and Updated Features
December 2, 2019
This page contains information about the new features, enhancements and updates included in the version 11 of the Schema and Data Comparison for SQL Server. To view the release notes for other versions, click
here.
Version 11 of the comparison tools for SQL Server includes the following new and updated features:
Azure Active Directory Authentication for SQL Server
In addition to the Windows Authentication and SQL Server Authentication, the comparison tools now support:
- Azure Active Directory Integrated Authentication
- Azure Active Directory Password Authentication
New Schema Differences Report
Schema compare includes a new differences report. The report is generated as an html file, with embedded JavaScript for navigation and filtering, useful for large databases with many objects. The UI provides a few options that allow you to customize the report:
- You can choose the objects that should be included in the report by status, such objects that are equal, different or missing.
- You can include additional properties for columns, such as the column data type, nullability, identity settings and other properties.
- You can script columns included in indexes and constraints, such as the primary keys, foreign keys, unique and edge constraints.
- You can include the SQL expressions of check and default constraints.
- You can script the SQL definition of views, procedures, functions and triggers.
New Data Differences Report
Version 11 includes a new data differences report, generated and saved as an html file, with embedded JavaScript for filtering and navigation. The report provides a table summary, which could include views depending on the comparison options, with their data differences, such as the number of rows that are equal, different of missing on one databases. You can filter out tables that are equal and leave only those that are different or vice versa.
Support for Graph Tables and Edge Constraints
Version 11 of the comparison tools supports the new SQL Server graph structure. This includes the node tables and the edge tables, introduced on SQL Server 2017, and the edge constraints, introduced on SQL Server 2019. New schema and data comparison options allow you to exclude these objects if necessary.
Careful consideration and extensive testing went into ensuring the proper synchronization of graph data. Unlike traditional tables, which contains only user-columns, graph tables have system-generated columns, such as $node_id, $edge_id, $from_id and $to_id, which are used by SQL Server to support the graph structure. While SQL Server allows inserts and deletes, it doesn't permit updating of these columns. We took great care to properly update the graph data, so that the graph structure is maintained and remains consistent between the databases that are being synchronized.
New CLR Assembly Options
Starting with SQL Server 2017, the code access security in .NET framework is no longer used as a security boundary for CLR assemblies. A new configuration option, named "clr strict security", has been added to further enhance the security of CLR assemblies. When this option is ON, which is the default value, SQL Server treats all assemblies, even those marked with the SAFE attribute or the EXTERNAL_ACCESS attribute, as if they were UNSAFE.
With this new security policy in place, creating an assembly in the same way as it was done prior to SQL Server 2017, may not be sufficient. For this reason, schema compare includes four new assembly options:
- Add assembly to the trusted assembly list: White-list the assembly by adding it to the trusted assembly list. This is done by calling sp_add_trusted_assembly with the correct hash value generated from the assembly binaries. You must be a sysadmin or have CONTROL SERVER permission to add assemblies to the trusted list.
- Set the database TRUSTWORTHY ON (not recommended): Generates a statement that makes the database TRUSTWORTHY. You must be a sysadmin to use this option.
- Disable "CLR strict security" option (not recommended): Generates a statement that disables the option "CLR strict security". You must have ALTER SETTINGS server-level permission to change this option. ALTER SETTINGS permission is implicitly held by the sysadmin and serveradmin fixed server roles.
- None of the above: Choose this option when you have taken the necessary steps to allow CLR assemblies in your database.
You can also choose to remove an assembly from the trusted list, when the assembly is dropped. For more details about CLR assemblies on SQL Server 2017 or higher, check the
CLR Strict Security option.
Other Improvements, Updates and Fixes
- Support for Azure External Users: Schema compare now supports external users on Azure SQL Databases. This includes both the external users and the external groups.
- Check constraint mappings: Previous versions of the schema compare mapped the check constraints by name or by the column on which the constraint is defined. This feature however was supported for check constraints on a single column. Version 11 removes this limitation and supports the column-mapping for composite check constraints on multiple columns.
- Unique constraint mappings: Schema compare now supports mapping of unique constraints based on their columns, in addition to mapping by name. Unique constraints mapped by columns do not trigger e schema difference if their name is different.
- Synchronizing data changes that do not allow updates: Data compare can synchronize changes between tables, which, for various reasons, do not support updates. An identity column, that is not part of the data key, is one example. We scan the database tables for these cases and adjust the script automatically to accommodate them.
- Using delete/insert instead of updates: Data compare adds a new option that allows you to synchronize data changes using inserts/deletes instead of updates. This could prove useful for databases that do not permit or "prefer" updates, such as databases containing financial data.
- Improved mapping: Data compare contains an improved mapping functionally, which handles duplicated objects better than the previous versions. Duplicates are logged and reported, instead of aborting the entire operation.
- Unique constraint on user-defined table types: Schema compare fixes an issue with the unique constraints on user-defined table types. The table type is no longer flagged as different when the constraints on the source and target database have a different name, since the name of these constraints can’t be scripted in this case.
- IGNORE_DUP_KEY index option on in-memory table types: Schema compare fixes an issue with the IGNORE_DUP_KEY option for indexes created on in-memory table types. This option is omitted since it is not supported by SQL Server.
