SQL Compare beta v11.0.0.226 & we need your help!

chengvoon.tong

We've now released the seventh beta of SQL Compare 11 (11.0.0.226), which you can download from the Red Gate website or via Help > Check for Updates if you are currently using a v11 beta.

The full release notes for all versions of SQL Compare v11 are available in this thread.

Features for this version:
This version includes additional support for the new SQL Server 2014 features. SQL Compare can now compare and deploy:
• natively compiled stored procedures
• clustered columnstore indexes
• incremental statistics

If your deployment includes memory-optimized tables and/or natively compiled stored procedures, you need to:
• make sure that the target database has a memory-optimized filegroup
• turn on the ‘Do not use transactions in deployment scripts’ option

Known issues and upcoming improvements
We're currently working on adding support for:
• deployment using transactions for other object types when they're deployed alongside memory-optimized tables. For example, if you deploy functions alongside memory-optimized tables, there will be no transaction block for the section in the deployment script for deploying the functions
• memory-optimized table types. This latest SQL Compare beta may display incorrect SQL for these objects, and attempts to deploy them are likely to fail or result in incorrect deployment

If the ‘Ignore filegroups, partition schemes and partition functions’ option is enabled, the deployment of incremental statistics might fail if they rely on partition schemes or functions which are being ignored

N.B. SQL Compare 11 won't include support for comparing and deploying backup files (native or SQL Backup) containing memory-optimized tables.

Bug fixes
This version includes the following fixes:
• SC-7378: Deployment no longer fails when rebuilding FILESTREAM table with ‘Ignore system named constraint and index names’ option enabled
• SC-7423: Scripts folder no longer contains duplicate constraint when "Database Project Compatible script folder output' option is enabled
• SC-7415: Creating snapshots from backup no longer fails with null reference exception
• SC-6605: Creating identity key with large seed value no longer causes conversion overflow exception
• SC-7402: Circular reference error is no longer thrown incorrectly on views in scripts folder
• SC-7023: Fix for the issue where mapping owners for comparison throws an error
• SC-7368: Parsing now works when hex value is used in OPTIMIZE FOR statement
• SC-6879: Fix for the issue where automatic column mapping always forces a table rebuild

Thanks,
SQL Compare Team

chengvoon.tong

We have reached a point with the implementation of SQL Compare’s support for SQL Server 2014 where we really need help with how the community wants us to handle a scenario:

Microsoft’s first cut of memory-optimized tables and natively compiled stored procedures in SQL Server 2014 does not allow schema/ddl operations inside of transactions. As SQL Compare wrap all operations up in transactions by default - to ensure we don’t leave a database in a partially deployed state - we have a conflict of behavior. Whilst it is possible to disable transactions most people don’t for the obvious reasons.

We have come up with a number of possible ways to address this issue:

1) Only allow MOTs and NCSPs to be scripted when you have transactions disabled. We would add extra error checking to the scripts SQL Compare generates so they would fail on the first error encountered. If the script fails through connection error, permission violations or an illegal schema operation we would provide users with information about where the failure occurred and a suggested course of action. However, users would have to carry out these actions themselves. If users wanted to still use transactions for their non memory-optimized objects they would have to carry out deployments in two phases – migrating the MOTs and NCSPs with the transaction option disabled and then migrating the rest of their database with the transaction option enabled.

In addition to this we could provide extra functionality which would help with the recovery of the database to its intended new state or roll back to the pre deployment state, for example:

a. Creating a backup at the start of the script
b. Creating a SQL Compare snapshot at the start of the script (although this would not help with recovering data)
c. For recovering data that might have been dropped as part of schema operations we would have to create extra tables in the target database during the deployment to safely hold data until the deployment completed.

2) Have SQL Compare generate a script which has all the MOT and NCSP schema operations at the top of the script in a non-transactional area, followed by the non memory-optimized work in a transactional area. If a failure occurred the database would still be left in an inconsistent state which users would have to recover themselves, as we cannot rollback the non-transactional area, however this would be reduced to just the MOT and NCSP objects.

We feel that (2) offers nicer behavior. However, as our user base covers a wide range of experience types, from accidental DBAs to SQL gurus, we are concerned that not all of them will be aware of the limitation that memory-optimized objects have to be deployed outside of transactions. Therefore, they may get an unpleasant surprise when they discover that our previously entirely transactional scripts are now only partly transactional. Admittedly users of MOTs and NCSPs will be working with the Enterprise edition of SQL Server, therefore this change isn’t going to affect all users.

The main question is, how upset will people be if their ‘transactional’ script turns out to not cover MOTs and NCSPs?

There is a precedent for this as users/schemas creation is problematic in transactions, although this is fairly lightweight when compared to tables.

Any thoughts about these options from the community would be gratefully received.

Thanks,
SQL Compare Team