question

TheloniusMonk-0135 avatar image
0 Votes"
TheloniusMonk-0135 asked TheloniusMonk-0135 published

Database schema not showing up because of table limit on Azure

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

azure-database-mysql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AnuragSharma-MSFT avatar image
1 Vote"
AnuragSharma-MSFT answered TheloniusMonk-0135 published

Hi @TheloniusMonk-0135, 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'







· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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. (We need to exclude all those tables that we don't want to migrate)
    DENY INSERT, SELECT , UPDATE ON [dbo].[testtableC] TO testuser;
    DENY INSERT, SELECT , UPDATE ON [dbo].[testtableD] TO testuser;

    DENY ALTER TO testuser

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




0 Votes 0 ·

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. (We need to exclude all those tables that we don't want to migrate)
DENY INSERT, SELECT , UPDATE ON [dbo].[testtableC] TO testuser;
DENY INSERT, SELECT , UPDATE ON [dbo].[testtableD] TO testuser;

DENY ALTER TO testuser

  1. 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?

0 Votes 0 ·
AnuragSharma-MSFT avatar image
0 Votes"
AnuragSharma-MSFT answered AnuragSharma-MSFT commented

Hi @TheloniusMonk-0135, 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:

127567-image.png

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'









image.png (39.3 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @AnuragSharma-MSFT

Just to confirm the steps, assuming I have two tables 'testtableA' and 'testtableB';

  1. I create new login testlogin with password = 'password@123';

  2. I run the following scripts:


--Run in your database
create user testuser for login testlogin;
GRANT ALTER TO testuser;
--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.testtable 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;


3). I create new sync group and sync agent, register it on on-premise computer

4)I change the username and password on the Hub to 'testlogin' and 'password@123', as shown by you in the picture

5). I click refresh schema (after selecting new sync group)


Is there something I missed? As I get the same error (500 table limit)




0 Votes 0 ·

I am not sure anything happens when i change the username and password on the hub. I tried changing the username from 'testlogin' to 'testuser' and the same error persists.

0 Votes 0 ·
TheloniusMonk-0135 avatar image
0 Votes"
TheloniusMonk-0135 answered TheloniusMonk-0135 published

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?






5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.