SQL Compare 14

This topic provides information that may help you to understand the behavior of the deployment script.

Column order

Column order isn't forced unless you select the Force column order project option.

For example, your source database has a table that contains ColA and ColB, in that order, and your target database has the same table but with ColB then ColA. If Force column order isn't selected, SQL Compare shows the tables as identical objects in the comparison results. If the option is selected, SQL Compare shows the columns as different objects; you can select the objects for deployment.

When column order is to be changed in a database, SQL Compare provides a warning in the Summary page of the deployment wizard to notify you that the table will be rebuilt. SQL Compare uses temporary tables to make sure that any data in the table isn't lost.

Renamed columns

SQL Compare attempts to recognize renamed columns by the similarity of the names and the data types of the columns. When a renamed column is recognized as such, SQL Compare renames the column as appropriate.

However, if the names and data types are very different, SQL Compare may consider the renamed column to be a completely different column. In this case, if ColA in your source database is renamed to ColB in your target database, when SQL Compare creates the deployment script, ColA will be created in the target database as a new column and ColB will be deleted. To avoid data loss, before you deploy the databases you must take care to preserve any data in the two columns, and merge them following the deployment.

Renamed objects

SQL Compare will detect inconsistencies in SQL Server when the name of an object such as a stored procedure, view, or function has been changed using sp_rename. In SQL Server, using sp_rename doesn't change the corresponding name in the object definition. SQL Compare will fix this inconsistency if the object needs to be altered by editing the name within the object definition to match the object name.

It isn't considered best practise to use sp_rename to rename stored procedures, triggers, user-defined functions, or views.

Updated views

If your views have not been updated by the deployment script and they contain a SELECT * statement, you must refresh them using sp_refreshview, to reflect any changes that have been made to the underlying objects on which the view depends. Refer to your SQL Server documentation for more information.

It's not best practice to use SELECT * statements in views; we recommend you specify an explicit column list.

Database diagrams

SQL Compare doesn't compare or deploy database diagrams.

System objects

SQL Compare doesn't compare or deploy system objects, except for users, roles, and system schemas.

Replication

If objects that are used in replication are deployed, errors may occur. For example, SQL Compare can't drop a table if it is used for replication.

Users

In Microsoft Windows, users are a composite of the domain name or computer name and the user name, for exampleComputer1\WindowsUser1. SQL Compare references only the user name, so that Computer1\User1 and Computer2\User1would be considered as the same. Therefore, if you intend to deploy users, make sure that their user names are different.

SQL Compare compares and deploys changes to users, such as changes to permissions. However, SQL Compare doesn't compare or deploy modifications to user passwords.

Filegroups

SQL Compare supports the deployment of databases that use multiple filegroups. However, you must make sure that the filegroups have been created on the target server prior to deployment. If the filegroups do not exist, the deployment will fail.

When a filegroup is to be changed in a database, SQL Compare provides a warning in the Deployment script page of the deployment wizard to notify you that the table will be rebuilt. SQL Compare uses temporary tables to make sure that any data in the table isn't lost.

Encrypted database objects

You have the option to decrypt text objects databases created using the WITH ENCRYPTION option.

Disabling this option can result in faster performance. To disable this option, on the Options tab of the Project Configuration dialog box, clear the Decrypt encrypted objects check box.

When this option is disabled, SQL Compare can't compare the encrypted objects, or display their creation SQL scripts, and can't deploy them.

If an encrypted object which can't be decrypted exists in both databases, it is shown under the objects that exist in both but are different group in the comparison results in the main window (select Type of Difference in the Group by box to arrange objects by difference). SQL Compare can't compare the encrypted objects, or display their creation SQL scripts, and can't deploy them.

CLR assemblies

When a CLR assembly is to be updated, if possible SQL Compare achieves this by using ALTER ASSEMBLY.

If SQL Compare determines that it would not be possible to use ALTER ASSEMBLY, any table that contains a CLR type from the updated assembly is rebuilt twice:

in the first rebuild, the CLR type columns are converted to nvarchar
The CLR type columns are dropped and recreated.
in the second rebuild, the nvarchar data is converted to the final CLR type

Data is preserved.

The ToString representation of the CLR user-defined type must be the same for both the old and the new assembly, otherwise the deployment script may fail, or the data may be corrupted.

To force SQL Compare to use the double table-rebuild method, select the Do not use ALTER ASSEMBLY to change CLR objects project option.

Partition schemes and partition functions

Partition schemes can be specified for tables so that the table is stored in several filegroups. By default, SQL Compare ignores filegroups. However, if you clear the project option Ignore filegroups, partition schemes, and partition functions, SQL Compare deploys the files.

For updates to partition schemes, a large amount of disk space may be required on the defined filegroups, because partition ranges must be merged and split.

In certain cases, for example when a partition function changes from left range to right range, it is necessary to drop and recreate partition functions and partition schemes. In these cases, the table is rebuilt twice:

in the first table rebuild, the content is saved to a temporary filegroup
in the second table rebuild, the table is migrated from the temporary filegroup to a new partition scheme

Data is preserved.

If a CLR assembly deployment also requires a table to be rebuilt twice, the CLR assembly and the partition scheme are deployed at the same time.

Certificates, symmetric keys, and asymmetric keys

SQL Server severely restricts access to certificates, symmetric keys, and asymmetric keys. Consequently, SQL Compare can't compare all of the properties for a symmetric key.

If certificates, symmetric keys, and asymmetric keys are selected for deployment, only the permissions are deployed.

To ignore all certificates, symmetric keys, and asymmetric keys in the deployment, select the Ignore certificates, symmetric and asymmetric keys project option.

Extended properties on databases

Extended properties on databases that differ are not displayed in the comparison results, but are always deployed. If you do not want them to be deployed, select the Ignore extended properties project option.

Numbered stored procedures

SQL Compare doesn't deploy numbered stored procedures. However, you can deploy them by running the deployment script in your SQL application.