Database schema not showing up because of table limit on Azure

Question

I am trying to sync 11 tables between NAV and Azure database. When I click ‘refresh schema’ (on Azure Portal) to show me available tables in member database, it says there are too many tables to display.

The error I get is:

Getting schema information for the database failed with the exception “Unable to process a schema with 5338 tables, 500 is the max For more information, provide tracing ID ‘e1f68119-372f-4d78-aeee-6b74ae02b06b’ to customer support

Is there a workaround to loading more than 500 tables? In the end I only want select a few tables to sync

Answer 1

Hi @Thelonius Monk , thanks for replying back.

<As the text was crossing 1600 characters, providing this as answer>

Its my bad, as we have provided permissions on schema and database, so all objects in this schemais available for testuser. We need to deny the permission to tables that we don't want to migrate. Below is updated script for the same.

 --Run in master db  
 create login testlogin with password = 'password@123';  
      
 --Run in your database  
 create user testuser for login testlogin;  


 --Permission to create table  
 GRANT CREATE TABLE TO testuser;  
 --Permission to alter table  
 GRANT ALTER ON [dbo].[testtableA] TO testuser;  
 --Permission to select schema  
 GRANT SELECT ON SCHEMA :: dbo TO testuser WITH GRANT OPTION;    
 --Permission to alter schema  
 GRANT ALTER ON SCHEMA :: dbo TO testuser WITH GRANT OPTION;    
 --Permission to select,insert, update on dbo.testtableA schema (We need to add all tables that we want to migrate data from)  
 GRANT INSERT, SELECT , UPDATE ON [dbo].[testtableA] TO testuser;  
--We need to provide deny permission for tables that we want to exclude. (We need to exclude all those tables that we don't want to migrate)  
DENY INSERT, SELECT , UPDATE ON [dbo].[testtableB] TO testuser;  

In this case while migrating the tables we will not see testtableB but all other tables in dbo schema will be available, so we need to deny permissions to other tables as well that we dont want to migrate.

Please note I also removed the alter permission in database as it overwrites other permissions. We can run below script to disable it for existing users:

DENY ALTER TO testuser;  

Please let me know if this works, or else we can further discuss the same.

----------

If answer helps, you can mark it 'Accept Answer'

Answer 2

Hi @Thelonius Monk , welcome to Microsoft Q&A forum.

Yes you mentioned correctly there is limit of showing number of tables in Azure Data Sync as per the current architecture. However there is a workaround to achieve it. I would suggest you to go through the article mentioned below where it talks about creating users with specific table permission and just migrating these tables.

Sync SQL data in large scale using Azure SQL Data Sync

Below are the steps we can perform:

A) Create a login in master database and respective user in main database as below.

--Run in master db  
create login testlogin with password = 'password@123';  
  
--Run in your database  
create user testuser for login testlogin;  

B) Provide necessary permissions at database level as mentioned below. Run these scripts through admin user
--Permission to alter database

GRANT ALTER TO testuser;  
--Permission to create table  
GRANT CREATE TABLE TO testuser;  
--Permission to alter table  
GRANT ALTER ON [dbo].[testtable] TO testuser;  
--Permission to select schema  
GRANT SELECT ON SCHEMA :: dbo TO testuser WITH GRANT OPTION;    
--Permission to alter schema  
GRANT ALTER ON SCHEMA :: dbo TO testuser WITH GRANT OPTION;    
--Permission to select,insert, update on dbo.testtable schema (We need to add all tables that we want to migrate data from)  
GRANT INSERT, SELECT , UPDATE ON [dbo].[testtable] TO testuser;  

Then we can create a new sync group and provide these new login details on Hub database as mentioned in below screenshot:

We will be able to see only those tables where we granted permission. We can create multiple sync group based on users if we have more than 500 tables.

The article mentioned above has covered these points.

Please let me know if this helps or we can discuss further.

----------

If answer helps, you can mark it 'Accept Answer'

Answer 3

Hi @AnuragSharma-MSFT

These are the steps I applied:

1) Run the following scripts successfully (assuming we want to include table A and B, exclude table C and D)

--Run in master db
  create login testlogin with password = 'password@123';

  --Run in your database
  create user testuser for login testlogin;
  --Permission to create table
  GRANT CREATE TABLE TO testuser;
  --Permission to alter table
  GRANT ALTER ON [dbo].[testtableA] TO testuser;
  GRANT ALTER ON [dbo].[testtableB] TO testuser;
  --Permission to select schema
  GRANT SELECT ON SCHEMA :: dbo TO testuser WITH GRANT OPTION;  
  --Permission to alter schema
  GRANT ALTER ON SCHEMA :: dbo TO testuser WITH GRANT OPTION;  
  --Permission to select,insert, update on dbo.testtableA schema (We need to add all tables that we want to migrate data from)
  GRANT INSERT, SELECT , UPDATE ON [dbo].[testtableA] TO testuser;
  GRANT INSERT, SELECT , UPDATE ON [dbo].[testtableB] TO testuser;
 --We need to provide deny permission for tables that we want to exclude. (

 DENY INSERT, SELECT , UPDATE ON [dbo].[testtableC] TO testuser;
 DENY INSERT, SELECT , UPDATE ON [dbo].[testtableD] TO testuser;

DENY ALTER TO testuser

2) Go to sync group created yesterday, change the credentials of Hub database to 'testlogin' and 'password@123'

3) Go to 'Tables', select the sync group (not hub) and press refresh schema

I tried the above but still get the same error. Is there something I missed?