2

I need to setup a constant sync between two databases on Azure on the same SQL Server. The database is about 2TB with 2000 tables and about 20 million rows.

I cannot setup an Azure data sync because each time, it freezes on "refresh schema" in the Azure portal. I know there is a limitation of 500 tables, but it takes too long before the schema is visible to select less than the 2000 tables that need to be synced.

Another thing we have tried is to initialize the second database with the tables we want from the first database. Those tables are empty and then we can "refresh schema" on the empty tables and set a sync from member to hub. However, when doing this, the initial sync does not work. The second database remains empty while in the portal, the sync seems to run OK.

Is there another possibility to setup a sync with such a large database?

Will it help to create a data sync between empty tables, run the initial sync and then insert all the rows into the empty tables? This way the initial sync will work (because there is no data) and all the other data will be synced like other data that is appended in the future.

EDIT: According to the following blog (https://azure.microsoft.com/sv-se/blog/sync-sql-data-in-large-scale-using-azure-sql-data-sync/), you should explicitly deny permissions on the tables. I have done this, but I now get an error while trying to retrieve the Schema because there are tables with '.', '[' or ']' in their name. Even though I deny the permissions on these tables, Azure gives an error (it probably executes some query to get the schema and in the results, the tables that the user has no access to, are still displayed).

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Lennart
  • 383
  • 4
  • 16
  • 1
    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. Reference this document https://azure.microsoft.com/sv-se/blog/sync-sql-data-in-large-scale-using-azure-sql-data-sync/, hope it helps. – Leon Yue Jan 22 '20 at 07:08
  • @LeonYue The problem is that I cannot select less than 500 tables, because the "refresh schema" in the azure portal does not complete because of the large amount of tables – Lennart Jan 22 '20 at 08:19
  • I am experiencing the same problem...and yet the database I'm trying to sync only contains 166 tables. The "Refresh Schema" just doesn't complete. – Shawn de Wet Mar 03 '20 at 04:23
  • 1
    Uhg it turns out I was using invalid credentials to connect to the hub db! I only saw this though when I exited out of the sync-setup process, and then found the partially-created sync on the overview page, and drilled into the logs view. – Shawn de Wet Mar 03 '20 at 04:37

0 Answers0