SQL to Azure Sync

Question

I have an on-promis database in SQl Server and I want to sync it to a database in Azure which I have created recently, Everything is done properly. However, I recieve the error message mentioned below about sync process unsuccessful. Having navigated through possible solutions on the internet, I came across a video stating that the source database must login with a user that has ALTER granted permission. I can't make any changes to this user. What else can I do to get rid of this error?

"Database provisioning failed with the exception "SqlException Error Code: -2146232060 - SqlError Number:15247, Message: User does not have permission to perform this action. SqlError Number:2759, Message: CREATE SCHEMA failed due to previous errors. For more information, provide tracing ID ‘89ac76b0-f0fa-42c4-87d4-e996b388a255’ to customer support."

Answer 1

I'm not a fan of SQL Data Sync to get to Azure.

We have a large number of customers who are using on-premises SQL Server databases that use transactional replication to sync to Azure SQL Database and are very happy with the outcomes.

We do this to get cloud-based replicas of on-premises data, and that's then used to build analytics. It works well.

If you need two-way sync, you'll need the Azure Sync options but I suspect you'll get very limited success with it. There is a walkthrough in this video: https://www.youtube.com/watch?v=YMpD4KOoWsk

Answer 2

Try to use the SQL Admin you defined when created the Azure SQL databases for the provisioning and SQL Data Sync setup. If you are using an account with less permissions, please make sure the account has the following permissions: Create/Alter Table, Alter Database, Create Procedure, Select/ Alter Schema, Create User Defined Type.

Answer 3

Hi @Sahar Meghrazi ,

Welcome to Microsoft Q&A forum.

As I understand, you are trying to perform SQL Data sync and getting error.

Could you please ensure below pointers are considered for this sync:

1). The Hub Database must be an Azure SQL Database.

2). If you're using an on-premises database as a member database, you have to install and configure a local sync agent.

You have to open outbound TCP port 1433 in the firewall to let the client agent communicate with the server.

Refer: https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-data-sync-sql-server-configure?view=azuresql#add-a-database-on-a-sql-server-instance-as-a-member-to-a-sync-group

3). Snapshot isolation must be enabled for both Sync members and hub. For more info, see Snapshot Isolation in SQL Server.

4). Moving servers between different subscriptions isn't supported.

5). Microsoft Entra (formerly Azure Active Directory) authentication isn't supported.

Other limitations to be considered:

• A table can't have an identity column that isn't the primary key.
• A primary key can't have the following data types: sql_variant, binary, varbinary, image, xml.
• Be cautious when you use the following data types as a primary key, because the supported precision is only to the second: time, datetime, datetime2, datetimeoffset.
• The names of objects (databases, tables, and columns) can't contain the printable characters period (.), left square bracket ([), or right square bracket (]).
• A table name can't contain printable characters: ! " # $ % ' ( ) * + - or space.
• Microsoft Entra (formerly Azure Active Directory) authentication isn't supported.
• If there are tables with the same name but different schema (for example, dbo.customers and sales.customers) only one of the tables can be added into sync.
• Columns with user-defined data types aren't supported.
• Moving servers between different subscriptions isn't supported.
• If two primary keys are only different in case (for example, Foo and foo), Data Sync won't support this scenario.
• Truncating tables isn't an operation supported by Data Sync (changes won't be tracked).
• Using an Azure SQL Hyperscale database as a Hub or Sync Metadata database isn't supported. However, a Hyperscale database can be a member database in a Data Sync topology.
• Memory-optimized tables aren't supported.
• Schema changes aren't automatically replicated. A custom solution can be created to automate the replication of schema changes.
• Data Sync supports only the following two index properties: Unique, Clustered/Non-Clustered. Other properties of an index, like IGNORE_DUP_KEY or the WHERE filter predicate, aren't supported and the destination index is provisioned without these properties even if the source Index has these properties set.
• An Azure Elastic jobs database can't be used as the SQL Data Sync Metadata database, and vice versa.
• SQL Data Sync isn't supported for ledger databases.

If issue is related to insufficient privileges, then try Resolution. Grant log-on-as-a-service credentials to the user account:

1. Go to Start > Control Panel > Administrative Tools > Local Security Policy > Local Policy > User Rights Management.
2. Select Log on as a service.
3. In the Properties dialog box, add the user account.
4. Select Apply, and then select OK.
5. Close all windows.

If you receive the error message Sync0022 Customer does not have authorization to perform action 'syncGroupOperationResults/read', the account attempting the operation does not have sufficient subscription-level permissions. Add:

• "Microsoft.Sql/locations/syncMemberOperationResults/read"
• "Microsoft.Sql/locations/syncAgentOperationResults/read"
• "Microsoft.Sql/locations/syncGroupOperationResults/read"

For more information, see Resource provider operations RBAC and SQL Data Sync Database accounts with least required privileges.

Let us know if above helped. If not, please share more details and observations so that we can assist you.

Thanks