Azure SQL Data sync

Question

Azure SQL Data sync

salilsingh-9961 351

Hi Team,

I have restored .bak file of AdventureWorks2012 to an on-premises SQL server.
Using Migration Assistant, I have migrated the on-premises SQL Server db to Azure SQL Server db.
Using Azure Data sync, inside a sync group I am syncing migrated azure SQL db to another Azure SQL db (on same server).

While doing the syncing, I could see out of 71 tables, data for only 33 tables inserted into new DB, for other tables it was giving errors related to limitation of Azure SQL sync.
Does this means Azure SQL data sync is not a good option for syncing data such as tables/data obtained after restoring AdventureWorks2012.bak file?
If yes, what other options I have to sync this data?

Thanks,
Salil

Anurag Sharma 17,631 Reputation points

2021-03-31T06:34:24.31+00:00
Hi @salilsingh-9961 , welcome to Microsoft Q&A forum.

Once we restore the database through any means, Data Sync should work fine.

Could you please provide below information to understand more on the issue?

After using data migration assistant, were you able to see all the 71 tables with data in Azure SQL database?

Could you provide the screenshot of the error you are getting?
salilsingh-9961 351 Reputation points

2021-03-31T07:37:54.137+00:00
Hi @AnuragSharma-MSFT ,

Please see below replies -

After using data migration assistant, I was able to see all the 71 tables with data in Azure SQL database.

In a SyncGroup, in tables section when I selected the member database (which is Azure SQL db containing 71 tables from Adventureworks2012 db), after clicking on Refresh Schema button, after selecting some tables, on the right side, i could see some columns of tables are listed as unsupported, so I had to exclude those tables, this caused foreign key issues to come as excecluded tables were having foreign key relationships with included tables which I am using in sync process.
Few more errors came as few columns in tables were computed columns which are not supported through azure data sync.

Thanks,
Salil

1 answer

Your answer

Anurag Sharma 17,631 Reputation points

2021-03-31T06:34:24.31+00:00

Hi @salilsingh-9961 , welcome to Microsoft Q&A forum.

Once we restore the database through any means, Data Sync should work fine.

Could you please provide below information to understand more on the issue?

After using data migration assistant, were you able to see all the 71 tables with data in Azure SQL database?

Could you provide the screenshot of the error you are getting?
salilsingh-9961 351 Reputation points

2021-03-31T07:37:54.137+00:00

Hi @AnuragSharma-MSFT ,

Please see below replies -

After using data migration assistant, I was able to see all the 71 tables with data in Azure SQL database.

In a SyncGroup, in tables section when I selected the member database (which is Azure SQL db containing 71 tables from Adventureworks2012 db), after clicking on Refresh Schema button, after selecting some tables, on the right side, i could see some columns of tables are listed as unsupported, so I had to exclude those tables, this caused foreign key issues to come as excecluded tables were having foreign key relationships with included tables which I am using in sync process.
Few more errors came as few columns in tables were computed columns which are not supported through azure data sync.

Thanks,
Salil

Answer 1

Anurag Sharma 17,631

Hi @salilsingh-9961 , thanks for replying back.

I was trying to repro the issue and was able to observe the similar behavior with Adventureworks2012.bak.

I noticed that the table columns which were giving the unsupported error were all user defined data types column which are not supported in Azure Data Sync. Please refer to below screenshot for the same.

Workaround to achieve the same would be to use .bacpac file for importing the data.

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

----------

if answer helps, please mark it 'Accept Answer'

salilsingh-9961 351 Reputation points

2021-04-01T05:00:51.87+00:00

Hi @AnuragSharma-MSFT ,

My requirement is to sync the data from on-premises db to Azure SQL db, so importing data through .bacpac file is not an option for me.

Thanks,
Salil
Anurag Sharma 17,631 Reputation points

2021-04-01T05:19:55.507+00:00

Thanks for replying back.

Yes Azure Data Sync comes with few limitations as mentioned above. However if we just want to load the data into Azure SQL Database we can still use the Data Migration Assistant to generate schema as well as data scripts.

We can also use Azure Data Factory to load the on-premise data into Azure SQL Database.

If this is one time sync, we can also use SSMS to generate the Schema and Data scripts and run on required database.
Anurag Sharma 17,631 Reputation points

2021-04-05T08:34:14.37+00:00

Hi @salilsingh-9961 , just wanted to follow back on this and check if any more information needed. If answer helps, you can mark it as 'Accept Answer'

Share via

Azure SQL Data sync

1 answer

Your answer