In this post I will show you how to handle schema changes in Azure SQL Data Sync. It follows on my previous post which gave a quick overview of Azure SQL Data Sync.
How To Handle Schema Changes In Azure SQL Data Sync
Since schema changes are not handled automatically by Azure SQL Data Sync you have to perform some manual steps in the Azure Portal or automate the steps. I’m going to cover doing it manually but same sequence of steps can be automated using PowerShell cmdlets or direct calls to the REST API.
Refreshing The Schema
For Azure SQL Data Sync to pickup changes in the schema of Sync Group members you have to refresh the schema.
In the Azure Portal browse to your Sync Group.
Select properties and disable automatic sync to prevent errors while changes are taking place.
Select Tables and for each database in the Sync Group click on refresh schema to pickup any changes. Select the table and columns to sync and click on save.
Adding A New Column To Sync
There are two scenarios here:
- Adding a new column that is empty or the same in all databases
In this case you create the column in all the databases with a null or default value that will result in the same rows across all the databases.
- Adding an existing column with data that was not synced yet
In this case the column exists with possibly different data between databases. If the rows are different between databases your conflict resolution policy and client sync sequence will determine the end result. If the conflict resolution policy is set to “hub wins” the member databases will end up with the values from the hub. If the policy is set to “client wins” the last client to sync will set the values in the hub and those values will be synced to the other clients.
- Disable automatic sync.
- Add the column in all Sync Group databases.
- Refresh the schema for each database in the Sync Group.
- Enable automatic sync.
Changing the data type of a column
You can change the data type of a column if the change will not cause any data loss for instance changing int to bigint.
- Disable automatic sync.
- Change the column type in all Sync Group databases.
- Refresh the schema for each database in the Sync Group.
- Enable automatic sync.
If the change will lead to data loss you can treat it as removing and adding a new column to achieve the same outcome.
Changing the name of a column
Changing the name of column basically involves removing the existing column and adding a new one.
- Disable automatic sync.
- Change the column name in all Sync Group databases.
- Refresh the schema for each database in the Sync Group and select the new column.
- Enable automatic sync.
NOTE: You cannot change a column if it is used in a filter.
Deleting a column
- Disable automatic sync.
- Delete the column from all databases in the Sync Group.
- Refresh the schema for each database in the Sync Group and select the new column.
- Enable automatic sync.
NOTE: You cannot remove a column if it is used in a filter.
