How do I migrate on-prem SQLServer 2019 database to Azure Synapse Database (Pool or Dedicated)?

Sharad Devganiya 6

How do I migrate on-prem SQLServer 2019 database to Azure Synapse Database (Pool or Dedicated)?
We have 1000s of table so can not use ADF. Importing .bacpac is not working in synapse.

1 answer

KranthiPakala-MSFT 46,422 Reputation points Microsoft Employee

2021-01-07T00:18:26.153+00:00

Hi @Sharad Devganiya ,

Welcome to Microsoft Q&A and thanks for using this forum.

Could you please confirm if your On-prem SQL Server database has >5000 tables or <5000 tables ? In case if it is <5000 tables you can follow this ADF tutorial - Copy multiple tables in bulk by using Azure Data Factory in the Azure portal

Also please confirm if you have multiple schemas for those tables? If you have multiple schemas with >5000 tables, then you can design a two-level pipeline where the outer pipeline (schema) iterates over an inner pipeline (tables of that schema) as explained in the above mentioned tutorial.

Let us know if this helps. Looking forward to your confirmation.

Thank you
Please sign in to rate this answer.
Sharad Devganiya 6 Reputation points

2021-01-07T02:44:37.083+00:00

We have around 600 tables and 2 schemas.

KranthiPakala-MSFT 46,422 Reputation points Microsoft Employee

2021-01-08T01:53:35.04+00:00

Hi @Sharad Devganiya ,

Thanks for your response. Since you have 600 tables and 2 schemas, You should be able to use ADF to copy tables to Azure Synapse by following the tutorial mentioned in the previous comment. I see that you called out that you cannot use ADF, could you please elaborate the challenges/concerns in using ADF for this requirement?

Also would you please share the reason for moving to Azure Synapse rather than moving to Azure SQL Database? Is this an existing OLTP workload rather than OLAP ?

Few additional details would help us to assist better on your requirement.

Thank you

KranthiPakala-MSFT 46,422 Reputation points Microsoft Employee

2021-01-10T02:05:38.71+00:00

Hi @Sharad Devganiya ,

Following up to see if you have got a chance to see my previous response. If so could you please provide the details as requested to better assist on this requirement.

Thank you

Sharad Devganiya 6 Reputation points

2021-01-10T05:06:17.84+00:00

II tried ADF you mentioned but it failed. Do we need to create tables manually in target database? Looks this ADF mentioned in the guide just copies data.

DetailsErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,

Message=A database operation failed with the following error:

'Could not find object 'ABC_ACT' or you do not have permission.',Source=,''Type=System.Data.SqlClient.SqlException,Message=Could not find object 'ABC_ACT' or you do not have permission.,Source=.Net SqlClient Data Provider,SqlErrorNumber=15165,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=15165,State=1,Message=Could not find object 'ABC_ACT' or you do not have permission.,},],'
Source
Pipeline

KranthiPakala-MSFT 46,422 Reputation points Microsoft Employee

2021-01-12T21:40:42.56+00:00

Hi @Sharad Devganiya ,

Sorry for the delay in my response and apologizes for not mentioning about the auto create tables feature of ADF copy activity in my previous response. While following the tutorial you need to make an additional settings in copy sink. Please enable Auto create table option in your copy activity sink settings. You can find the option on ADF authoring UI –> Copy activity sink –> Table option –> Auto create table, or via tableOption property in copy activity sink payload.

Once you enable this property, ADF will create the tables in sink (supported) data store if they don't exist.

Hope this helps to overcome the error message. Please let me know if you still notice any further issues.

Looking forward to your confirmation.

Thank you

KranthiPakala-MSFT 46,422 Reputation points Microsoft Employee

2021-01-13T20:08:08.373+00:00

Hi @Sharad Devganiya ,

Following up to see if the above solution was helpful to resolve your issue? And if you further query, please do let us know.

Looking forward to your confirmation.

Thank you

KranthiPakala-MSFT 46,422 Reputation points Microsoft Employee

2021-01-14T17:56:17.383+00:00

Hi @Sharad Devganiya ,

Just checking to see if you query was resolved? If you have further questions, please do let us know.

----------

Thank you
Please do consider to click on "Accept Answer" and "Upvote" on the post that helps you, as it can be beneficial to other community members.
Sign in to comment