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

Column order

Column order is not 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 is not 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 synchronization.

When column order is to be changed in a database, SQL Compare provides a warning in the Summary page of the synchronization wizard to notify you that the table will be rebuilt. SQL Compare uses temporary tables to ensure that any data in the table is not 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 synchronization script, ColA will be created in the target database as a new column and ColB will be deleted. To avoid data loss, before you synchronize the databases you must take care to preserve any data in the two columns, and merge them following the synchronization.

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 does not 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 is not 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 synchronization 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.

Note that it is not best practice to use SELECT * statements in views; it is recommended that you specify an explicit column list.

Database diagrams

SQL Compare does not compare or synchronize database diagrams.

System objects

SQL Compare does not compare or synchronize system objects, except for users, roles, and system schemas.

Replication

If objects that are used in replication are synchronized, errors may occur. For example, SQL Compare cannot 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 synchronize users, ensure that their user names are different.

SQL Compare compares and synchronizes changes to users, such as changes to permissions. However, SQL Compare does not compare or synchronize modifications to user passwords.

Filegroups

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

When a filegroup is to be changed in a database, SQL Compare provides a warning in the Synchronization script page of the Synchronization wizard to notify you that the table will be rebuilt. SQL Compare uses temporary tables to ensure that any data in the table is not lost.

Encrypted database objects

If you are synchronizing a SQL Server 2000 database that contains an encrypted user-defined function, stored procedure, trigger, or view and you have system administrator permissions, SQL Compare decrypts the object and you can view its internal SQL in the synchronization script. If you do not have system administrator privileges, you cannot synchronize the encrypted object.

In SQL Compare version 7.1 (and later) you can decrypt text objects in SQL Server 2005 and SQL Server 2008 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 on 2005 and 2008 databases check box.

When this option is disabled, SQL Compare cannot compare the encrypted objects, or display their creation SQL, and cannot synchronize them.

SQL Compare version 7.0 (and earlier) cannot decrypt objects that are encrypted in a SQL Server 2005 or SQL Server 2008 database. If an encrypted object that cannot 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 bybox to arrange objects by difference). SQL Compare cannot compare the encrypted objects, or display their creation SQL, and cannot synchronize 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. Note that the ToString representation of the CLR user-defined type must be the same for both the old and the new assembly, otherwise the synchronization 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

In SQL Server 2008 and SQL Server 2005, 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 synchronizes the files. Note that 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. Note that if a CLR assembly synchronization also requires a table to be rebuilt twice, the CLR assembly and the partition scheme are synchronized 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 cannot compare all of the properties for a symmetric key.

If certificates, symmetric keys, and asymmetric keys are selected for synchronization, only the permissions are synchronized.

To ignore all certificates, symmetric keys, and asymmetric keys in the synchronization, 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 synchronized. If you do not want them to be synchronized, select the Ignore extended properties project option.

Numbered stored procedures

SQL Compare does not synchronize numbered stored procedures. However, you can synchronize them by running the synchronization script in your SQL application.