I have configured Azure Data Sync between our SQL on-prem and Azure SQL. Sync direction is to Hub (Azure) and conflict resolution is set to Member Win. Sync group has been configured successfully, and I have selected the tables I want to sync in the Hub (I synced schema previously using Data Migration). I have 83 tables that are selected for synchronization.
Problem: When starting the synchronization, I receive the following error:
Database provisioning failed with the exception "SqlException Error Code: -2146232060 - SqlError Number:3906, Message: Failed to update database "*****" because the database is read-only. SqlError Number:2759, Message: CREATE SCHEMA failed due to previous errors.
Why would Data Sync be attempting to CREATE SCHEMA on my read-only on-prem instance, when I have configured it to synchronize in the other direction?
Appreciate any insight you can offer,
Cheers,
You need to give dbowner access to role you are using with agent so that it can create a schema initially. It will create schema and tables with datasync.tablename that will have sync logs for respective table. If you sync 10 tables then 10 such tables will be created.