Comparing and Synchronizing Schemas on Different Microsoft SQL Server Server Versions

When comparing schemas on different Microsoft SQL Server servers, some incompatibilities can occur in schema objects. For example, when comparing schemas on Microsoft SQL Server 3.23 and 5.x. Schema on Microsoft SQL Server 5.x can have such objects as views, triggers, stored routines that are not supported on 3.23 server. This topic describes how dbForge Studio for SQL Server treats such incompatibilities.

Not Supported Objects

If source schema contains objects that are not supported on the target Microsoft SQL Server server, they are not displayed in the Schema Comparison document and are not affected by synchronization.

If target schema contains objects that are not supported on the source Microsoft SQL Server server, they are displayed in the document. Their default operation is None and they will not be affected by synchronization. You may change their operation to Drop, so the object will be dropped from the target schema.

Partially Supported Objects

Sometimes there are some incompatibilities even if the type of the database object is supported on both source and target database. For example, tables are supported by both Microsoft SQL Server 3.23 and Microsoft SQL Server 5.x, but a table on one server may have a column of a type that is not supported on the other one.

When an object is supported on the source server but is not supported on the target server, it will have Inconsistent status and cannot participate in synchronization. When there is an object in the target that is not supported in the source, but source objects are supported on the target server, target schema can be synchronized and incompatible database objects have Different status.

Here are some examples of such incompatibilities:

  • Unsupported column type (for example, Microsoft SQL Server 3.23 does not support BIT column type).
  • Unsupported table engine.
  • Unsupported ROW_FORMAT
  • Usage of unsupported built-in functions in the DDL.

Partitioning

Partitioning is treated in another way than other incompatibilities. Let us examine several situations:

  • The source and target tables have the same structure except for partitioning. In that case, the target table can be updated. If the target schema supports partitioning, the status is Different, otherwise it is Equal.
  • Source partitioned table has another structure than corresponding target table or there are no corresponding target table (having the same name). In that case the table has the Different status.

If target schema supports partitioning, partitioned tables are included into schema comparison results, otherwise they are ignored.

Syntax Incompatibility

In cases when equal schema objects have syntax differences, because they are placed on different server versions, the object DDL is generated according to target object DDL.