visual-studioazure-sql-databaseschema-compare

Azure Sql database schema missing constraints after schema compare

I created a blank SQL database in Azure.

From Visual Studio 2017, I performed a Schema Compare, and updated the blank Azure database to my schema. There were no errors so I didn't check everything was exactly the same.

I setup replication and replicated all data fine.

Upon performing another schema compare, I discovered that all foreign key constraints are missing, along with default values and indexing.

Solution

  • It appears that the initial snapshot taken for replication does not replicate constraints and default values, due to entity replication being done in an arbitrary order; these constraints would cause errors.

    After removing seed column NOT FOR REPLICATION using

    ALTER TABLE [dbo].[ColumnName] ALTER COLUMN Id DROP NOT FOR REPLICATION;

    I could do another schema compare to re-apply all constraints and default values.