Azure SQL Sync Group Db Isolation Level Error

Question

An error is given stating the database Isolation Level state is different between two databases when replicating between an Azure SQL database and an on-premise SQL database using the SQL Agent software installed on the on-premise server.

How do I fix this?

Answer 1

Unfortunately one of the requirements for synchronizing databases using SQL Data Sync is that all hub and member databases must use snapshot isolation level. For more information about SQL Data Sync requirements please visit this documentation.

Set the isolation level of the on-premises database as shown below:

ALTER DATABASE MyDatabase  
SET ALLOW_SNAPSHOT_ISOLATION ON  

ALTER DATABASE MyDatabase  
SET READ_COMMITTED_SNAPSHOT ON

Answer 2

View the isolation state using this sql:

select name, snapshot_isolation_state, is_read_committed_snapshot_on from sys.databases

You want these settings to be both ON like below and the same on both the Azure and on-premise database.

The sql below is used to change the settings:

ALTER DATABASE {name} SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE GO

ALTER DATABASE {name} SET ALLOW_SNAPSHOT_ISOLATION ON GO