Compare databases on different SQL Server versions

When comparing databases hosted on different server versions, you may encounter incompatibilities in schema objects.

The topic lists cases when schema comparison may fail due to these incompatibilities.

SQL Server 2005 (compatibility level 80)

If the compatibility level of a SQL Server 2005 database is set to 80, it conforms to strict rules for views, stored procedures, functions, and DML triggers. Comparison and synchronization may fail.

SQL Server 2008 (compatibility level 90)

If the compatibility level of a SQL Server 2008 database is set to 90, it conforms to strict rules for views, stored procedures, functions, and DML triggers. Comparison and synchronization may fail.

Objects unsupported on the target server

If a source database contains an object type that is not supported by a target server, that object will not appear in the Schema Comparison document during the comparison process.

Objects unsupported on the source server

If a target schema contains objects that are not supported by a source server, those objects are displayed in the Schema Comparison document. Their default operation is set to None and they will not be affected by synchronization. You may change their operation to Drop, so the objects can be dropped from the target schema.

Objects with unsupported key properties on the source server

If a source object contains key properties that are not supported by the target server, dbForge Studio identifies such objects as inconsistent and displays a warning:

Object Table 'table_name' cannot be created on target server. Object definition contains options that are not supported by target server (different versions).

These objects cannot be synchronized.

Objects with unsupported properties or subobjects on the source server

If a source object includes properties or subobjects that the target server does not support, but the object can still be created or synchronized without them, dbForge Studio will perform the synchronization and display a warning:

Object 'Object_properties_name' cannot be created on target server. Object definition contains options that are not supported by target server (different versions).

After synchronization, dbForge Studio will treat the objects as identical.

DDL objects with unsupported properties on the source server

If a source DDL object contains unsupported properties, such as indexes or table properties, these properties will not be displayed in Text Compare.

Objects with unsupported downgraded database properties

When downgrading database properties, the synchronization succeeds. However, the properties unsupported on the source server will not be included in the generated synchronization script.

Partially supported objects

Incompatibilities can occur even when an object type is supported on both the source and target databases. For example, both SQL Server 2008 R2 (10.50) and SQL Server 2016 (13.0) support tables, but a table on one server might include a column with a data type that the other server does not support.

• If an object is supported on the source server but not on the target, it will be assigned the Inconsistent status and excluded from synchronization.
• If an object exists in the target but is not supported on the source, and all source objects are supported on the target, the target schema can still be synchronized. In this case, incompatible objects may have the Different status.

Examples of such incompatibilities include:

• Unsupported column types — For example, SQL Server 2014 (12.0) and earlier do not support the JSON column type.
• Unsupported clustered columnstore indexes — Not available in server versions earlier than SQL Server 2014 (12.0).
• Unsupported COLUMNSTORE_ARCHIVE index property — Not available in server versions earlier than SQL Server 2016 (13.0).
• Unsupported built-in functions — Certain functions used in DDL statements may not be recognized by older server versions.

Partitioned objects

If the source and target tables are identical except for partitioning, the target table can be updated:

• If the target schema supports partitioning, the table status is Different.
• If the target schema does not support partitioning, the status is Equal.

If the source partitioned table has a different structure than the corresponding target table, or if no matching target table exists, the table status is Different.

When the target schema supports partitioning, partitioned tables appear in the schema comparison results. When the target schema does not support partitioning, partitioned tables are ignored.

Objects with syntax incompatibility

If schema objects are functionally identical but have syntax differences caused by different server versions, the DDL is generated using the target object syntax.