How to compare and synchronize schemas on different SQL Server versions

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

Not Supported Objects

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

If a target schema contains objects that are not supported on the source 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 that the object can be dropped from the target schema.

Partially Supported Objects

Sometimes there are some incompatibilities even if the database object type is supported on both source and target databases. For example, tables are supported by both SQL Server 3.23 and SQL Server 5.x but a table on one server may have a column of the 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, 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 a corresponding target table or there are no corresponding target tables (having the same name). In that case, the table has Different status.

If a 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 the target object DDL.

Want to Find out More?

Overview

Overview

Take a quick tour to learn all about the key benefits delivered by dbForge Studio for SQL Server.
All Features

All features

Get acquainted with the rich features and capabilities of the Studio in less than 5 minutes.
Request a demo

Request a demo

If you consider employing the Studio for your business, request a demo to see it in action.
Ready to start using dbForge Studio for SQL Server?
Download a free 30-day trial