Getting Schema Information for the databse failed with the exception 500 is the max tables azure data sync problem

0
votes

Error : Getting Schema Information for the database failed with the exception unable to process a schema with 2434 500

Database Sync Group error on Azure syncing local offline server.

What's the best way to sync offline database even if it's not this way

2
azureazure-sql-database

2 Answers

1
votes

Please reference the Data Sync Limitations on service and database dimensions:

enter image description here

I agree with @Alberto Morillo, your exception should be "Unable to process a schema with 2434 tables, 500 is the max....".

Here's the Azure official blog talk about how to Sync SQL data in large scale using Azure SQL Data Sync. It gives you a solution to solve the exception:

Sync data between databases with many tables

Currently, data sync can only sync between databases with less than 500 tables. You can work around this limitation by creating multiple sync groups using different database users. For example, you want to sync two databases with 900 tables. First, you need to define two different users in the database where you load the sync schema from. Each user can only see 450 (or any number less than 500) tables in the database. Sync setup requires ALTER DATABASE permission which implies CONTROL permission over all tables so you will need to explicitly DENY the permissions on tables which you don’t want a specific user to see, instead of using GRANT. You can find the exact privilege needed for sync initialization in the best practice guidance. Then you can create two sync groups, one for each user. Each sync group will sync 450 tables between these two databases. Since each user can only see less than 500 tables, you will be able to load the schema and create sync groups! After the sync group is created and initialized, we recommend you follow the best practice guidance to update the user permission and make sure they have the minimum privilege for ongoing sync.

enter image description here

Hope this helps.

0
votes

The full error message you are receiving may look like "Getting schema information for the database failed with the exception "Unable to process a schema with 2434 tables, 500 is the max. For more information, provide tracing ID '8d609598-3dsf-45ae-93v7-04ab21e45f6f' to customer support."

It is a current limitation on SQL Data Sync that if the database has more than 500 tables, it does not get the schema for you to select the tables - even if you want to select and sync only 1 table.

A workaround for you is to delete the extra tables or move the un-needed tables into another DB. Not ideal, we agree, but is a workaround for now. To try the workaround perform the following steps.

  1. Script the tables you want to sync(less than 500 tables per synchronization group)
  2. Create a new temporary database and run the script to create the tables you want to sync
  3. Register and add the new temporary database as a member of the sync group
  4. Use the new temporary database to pick the tables you want to sync
  5. Add all other databases that you want to sync with (on-premise databases and hub database)
  6. Once the provisioning is done, remove the temporary database from the sync group.