CODEWITHSUNDEEP
azureazure-sql-databasedata-warehouseazure-synapse
Mikaela79
Mikaela79

Reputation: 35

Migrate data from on-prem DW to Azure

I have an on-prem Dat Warehouse using SQL Server, what is the best way to load the data to SQL Data Warehouse?

Upvotes: 1

Views: 740

Answers (2)

Matt Usher
Matt Usher

Reputation: 1325

The process of loading data depends on the amount of data. For very small data sets (<100 GB) you can simply use the bulk copy command line utility (bcp.exe) to export the data from SQL Server and then import to Azure SQL Data Warehouse. For data sets greater than 100 GB, you can export your data using bcp.exe, move the data to Azure Blob Storage using a tool like AzCopy, create an external table (via TSQL code) and then pull the data in via a Create Table As Select (CTAS) statement.

Using the PolyBase/CTAS route will allow you to take advantage of multiple compute nodes and the parallel nature of data processing in Azure SQL Data Warehouse - an MPP based system. This will greatly improve the data ingestion performance as each compute node is able to process a block of data in parallel with the other nodes.

One consideration as well is to increase the amount of DWU (compute resources) available in SQL Data Warehouse at the time of the CTAS statement. This will increase the number of compute resources adding additional parallelism which will decrease the total ingestion time.

Upvotes: 4

Srini Acharya
Srini Acharya

Reputation: 535

SQL database migration wizard is a helpful tool to migrate schema and data from an on-premise database to Azure sql databases. http://sqlazuremw.codeplex.com/

Upvotes: 0

Related Questions