question

peter-1892 avatar image
0 Votes"
peter-1892 asked AlbertoMorillo commented

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

142321-image.png



azure-sql-databaseazure-sqldatabase-edge
image.png (37.0 KiB)
5 |1600 characters needed characters left characters exceeded

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

1 Answer

AlbertoMorillo avatar image
0 Votes"
AlbertoMorillo answered AlbertoMorillo commented

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

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

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 Votes 0 ·

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

0 Votes 0 ·

Make sure you have followed the steps provided here.



0 Votes 0 ·
Show more comments