question

AndrewHopkinson-5477 avatar image
0 Votes"
AndrewHopkinson-5477 asked AnnuKumari-MSFT commented

What is the best tool for mapping SQL Server DW tables Azure Synapse

Hello, I am in the process of trying to migrate SQL Server 2012 data warehouse tables and cubes to Azure Synapse. Can someone please recommend a good tool for this? Thank you.

sql-server-generalazure-synapse-analyticssql-server-analysis-services
· 4
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.

Do you have an idea of what the architecture is in the to be state or are you simply moving to synapse?

Sqlpools? Sparkpools? Adls parquet files with external tables?

Is it simply a lift and shift of existing tables ? How do you run updates to the existing dw? Ssis? Procs?

0 Votes 0 ·

Probably SQL Pools. Currently the existing databases are being fed by many SSIS packages loading flat files. The plan is to use Azure Data lake storage Raw and Curated zones to feed into Synapse. This is not quite a lift and shift as they will not be using Azure Analysis Services for the cubes and they wish to use materialized views (if that is possible). Thank you for your help!

1 Vote 1 ·
AnnuKumari-MSFT avatar image AnnuKumari-MSFT AndrewHopkinson-5477 ·

Hi @AzureAaronHughes ,
Just checking in to see if the suggestions helped. Please do consider clicking Accept Answer and Up-Vote for the same as accepted answers help community as well. If you have any further query do let us know.

0 Votes 0 ·

Hi @AndrewHopkinson-5477,
Just checking in to see if any of the answers helped. Please do consider clicking Accept Answer and Up-Vote for the same as accepted answers help community as well. If you have any further query do let us know.

0 Votes 0 ·
Yufeishao-msft avatar image
0 Votes"
Yufeishao-msft answered Yufeishao-msft commented

Hi @AndrewHopkinson-5477,

Check out this doc: Migrate a data warehouse to a dedicated SQL pool in Azure Synapse Analytics

Plan your migration before you get started, to ensure that you data, table schemas, and code are comptible with Azure Synaspse Analytics


If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.




· 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.

Ok thank you, I was hoping there was an actual tool for this, but I guess it's all a manual process?

0 Votes 0 ·
Yufeishao-msft avatar image Yufeishao-msft AndrewHopkinson-5477 ·

It is complicate to migrate data warehouse, it is unlikely to have an off-the shelf tool for all tasks and can option to introduce automation to reduce work, be like use Azure Data Factory to create a migration control pipeline to control execution and so on

https://docs.microsoft.com/en-us/azure/cloud-adoption-framework/plan/data-warehouse-migration#de-risking-your-data-warehouse-migration-project

0 Votes 0 ·
AzureAaronHughes avatar image
0 Votes"
AzureAaronHughes answered

From what you've said, you could use data migration service or Synapse pipelines to move existing data from the DW to SQLPools, Synapse Pipelines also allow you to now use the SSIS IR which will allow a lift and shift of your SSIS into the cloud.
in terms of any Stored procs you can move these however the MPP architecture and SQL Syntax that is avaliable in SQLPools is different to SQL Server and therefore you will have to re work these for the new solution.

This method would get you migrated quickest I think; however I would then suggest moving the SSIS packages over to Synapse Pipelines and rearchitecting the SQLPool objects to take advantage of the other functionality the SQLPools gives you.

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.