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

Andrew Hopkinson 6

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.

AaronHughes 391 Reputation points

2022-02-25T16:50:20.383+00:00

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?
Andrew Hopkinson 6 Reputation points

2022-02-25T16:57:29.15+00:00

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!
AnnuKumari-MSFT 31,061 Reputation points Microsoft Employee

2022-03-07T06:21:26.817+00:00

Hi @AaronHughes ,
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.
AnnuKumari-MSFT 31,061 Reputation points Microsoft Employee

2022-03-10T16:57:03.157+00:00

Hi @Andrew Hopkinson ,
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.

2 answers

YufeiShao-msft 7,056 Reputation points

2022-02-28T06:02:50.6+00:00

Hi @Andrew Hopkinson ,

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.
Please sign in to rate this answer.
Andrew Hopkinson 6 Reputation points

2022-02-28T17:48:16.353+00:00

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

YufeiShao-msft 7,056 Reputation points

2022-03-01T01:28:49.787+00:00

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://learn.microsoft.com/en-us/azure/cloud-adoption-framework/plan/data-warehouse-migration#de-risking-your-data-warehouse-migration-project
Sign in to comment
AaronHughes 391 Reputation points

2022-03-03T08:05:27.777+00:00

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.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment