SQL Data Sync | Snapshot isolation transaction failed accessing database '***' because snapshot isolation is not allowed in this database.

Manivannan Sivaraj 0 Reputation points
2023-07-31T18:37:17.06+00:00

Hi,

I'm setting up a SQL Data sync using PowerShell following this article: https://learn.microsoft.com/en-us/azure/azure-sql/database/scripts/sql-data-sync-sync-data-between-sql-databases?view=azuresql-db

Sync Hub is an Azure SQL Database. Sync Member is also an Azure SQL Database. Both the databases are in the same sql server (Azure).

When I start sync operation (using the command 'Start-AzSqlSyncGroupSync'), it fails with the message that Hub database does not allow snapshot isolation. And, recommended to alter DB to ALLOW_SNAPSHOT_ISOLATION.

Actual Error message: Snapshot isolation transaction failed accessing database <DBName> because snapshot isolation is not allowed in this database. Use ALTER DATABASE to allow snapshot isolation. For more information, provide tracing ID to customer support.

So, I ran the following queries against both hub and member databases to turn on snapshot isolation:

ALTER DATABASE [DB name] 
SET ALLOW_SNAPSHOT_ISOLATION ON
  
ALTER DATABASE [DB name] 
SET READ_COMMITTED_SNAPSHOT ON


I'm still getting the same error though. Any suggestions please?

Azure SQL Database
{count} votes

2 answers

Sort by: Most helpful
  1. Manivannan Sivaraj 0 Reputation points
    2023-08-02T19:32:44.08+00:00

    Executing the command "ALTER DATABASE [DB name] SET ALLOW_SNAPSHOT_ISOLATION ON" on the hub database worked, and made the sync successful. We can mark this as resolved.


  2. Oury Ba-MSFT 14,931 Reputation points Microsoft Employee
    2023-08-02T22:41:14.8466667+00:00

    @Manivannan Sivaraj Thank you for confirming issue is resolved.

    Issue:

    You were trying to do a data sync between 2 databases in the same Server but getting an error message which stated that "Snapshot isolation transaction failed accessing database <DBName> because snapshot isolation is not allowed in this database. Use ALTER DATABASE to allow snapshot isolation. For more information, provide tracing ID to customer support."

    Resolution:

    It worked by Executing the command "ALTER DATABASE [DB name] SET ALLOW_SNAPSHOT_ISOLATION ON" on the hub database.

    You also had a predecessor script that reset the snapshot_isolation flag back to OFF. Upon fixing it, sync happened successfully.

    Please do not forget to mark as accept answer.

    It will be beneficial for other community members with the same issue.

    Regards.,

    Oury

    0 comments No comments