Azure Learning #4 Data Sync configuration fails with error “Failed to update database 'xxxxx' because the database is read-only”

Recently I came across a customer who encountered the following error when configuring Azure Data Sync .

Database re-provisioning failed with the exception 'SqlException Error Code: -2146232060 - SqlError Number:3906, Message: Failed to update database 'xxxxx' because the database is read-only. SqlError Number:2759, Message: CREATE SCHEMA failed due to previous errors. For more information, provide tracing ID '#########' to customer support.'

To give some background, the customer was setting up sync between an Azure SQL Database and an on-premise SQL 2014 database

This error was encountered after due completion of Step 1 -> Creating a Sync Group , Step 2-> Adding Sync Members & Step 3-> Configure Sync Group as mentioned under Azure Documentation.

To illustrate please find the below screenshots from a test conducted in the lab environment.

Step 1 : Sync group created -> "TestSyncGroup"

Step 2: Sync Hub database added -> "test"

Step 3 : Hub database table being added -> "dbo.Table_1" ( Primary Key DbCol(int))

Error as seen from Sync logs under Azure Portal

One may wonder why the sync failed after the database was provisioned successfully as seen above.

Point to be noted: We have adhered to the data sync current limitations namely

We could create the group and add the database as a member. Then why did it fail?

Mystery isn't it. Well actually not, the error message is quite self-explanatory in this case.

Answer : We received the mentioned error because the hub database was a secondary database. It was a member of geo-replication configured on a different Azure SQL Server.

Geo-replication is a functionality in Azure SQL Database which allows you to create secondary read-only database in the same or different data center locations (regions).

Cause:

When you add the table members to the sync group it attempts to create internal tables, triggers and stored procedures within the Azure SQL hub database to track changes made to the Hub database. Once these objects are created they are used for Data Sync to carry out changes made on hub database to the member database. As Geo-replication ensures that the secondary database is in read mode only, Data Sync fails when attempting to create these required objects.

Hope you find this blog helpful. Do feel free to leave a feedback.

In our next blog we shall see how to resolve the error "Login failed. For more information, provide tracing ID '******' to customer support.'"