Azure SQL sync failed due to snapshot isolation in other azure database in other region

Question

question

peter-1892 asked Oct 21, '21 AlbertoMorillo commented Oct 22, '21

Azure SQL sync failed due to snapshot isolation in other azure database in other region

Having issues with the above.

Error Message reads ;

Sync failed with the exception "SqlException ID: 7ed05768-c403-4de2-82a6-9786fc4d2db1, Error Code: -2146232060 - SqlError Number:3952, Message: Snapshot isolation transaction failed accessing database 'TestData' because snapshot isolation is not allowed in this database. Use ALTER DATABASE to allow snapshot isolation. For more information, provide tracing ID ‘0763be30-388e-460b-ba8d-f65594f57bc4’ to customer support."

Read all the doc and have also done the following .

using in TSQL in SSMS

ALTER DATABASE TestData
SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE TestData
SET READ_COMMITTED_SNAPSHOT ON

azure-sql-database azure-sqldatabase-edge

image.png (37.0 KiB)

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 1 · 2021-10-21T16:56:41.46Z

AlbertoMorillo answered Oct 21, '21 AlbertoMorillo commented Oct 22, '21

SQL Data Sync is trying to enable on SQL Azure database the same isolation level your database has on premises, but it is not possible to change the isolation level of a SQL Azure database. That is the reason the SQL Data Sync process will fail.

Set the isolation level of the member database, to the same settings it has before the deployment of Data Sync. Set the isolation level to read committed (SQL Server default) or to READ_COMMITTED_SNAPSHOT.

 alter database [DB] set read_committed_snapshot on with rollback immediate;

· 6

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

peter-1892 · Oct 22, 2021 at 03:03 AM

Hi AlbertoMorillo,

Still receiving the error .

"Sync failed with the exception "SqlException ID: 2496f19a-3313-4b50-8440-d188e0aa2611, Error Code: -2146232060 - SqlError Number:3952, Message: Snapshot isolation transaction failed accessing database 'TestData' because snapshot isolation is not allowed in this database. Use ALTER DATABASE to allow snapshot isolation. For more information, provide tracing ID ‘3942ae78-86e4-4c17-83bd-e4fb53b081da’ to customer support."

I have even deleted the other region and did accordingly.

Then tried adding this

ALTER DATABASE TestData SET ALLOW_SNAPSHOT_ISOLATION ON .

Check in the SSMS , it shows that it changed.

Then add the region member back again but still failed with the above error.

It is supposed to be a simple procedure yet it is difficult to get it activated and running smoothly.

Have been reading and searching yet couldn't find a remedy for this.

Regards

0 ·

peter-1892 peter-1892 · Oct 22, 2021 at 01:51 PM

Found out the reason why it keeps failing.
Key issue is to make sure all the sync databases must be set.

0 ·

AlbertoMorillo peter-1892 · Oct 22, 2021 at 01:58 PM

Make sure you have followed the steps provided here.

0 ·

Show more comments

question