Azure SQL DW to Azure SQL DW using Polybase

Question

I know you can use polybase using external table to load large volume of data from Blob Storage to Azure SQL DW. But is there any possibility that we can import the data from SQL DW to another SQL DW using polybase directly? Or is there some other way? There must be some way to avoid control node in both SQL DW.

score 8 · Answer 1 · answered Mar 09 '17 at 10:37

8

You might be better off using Azure Data Factory to move data between two Azure SQL Data Warehouses. It would make light work of moving the data, but beware any data movement costs, particularly moving across region. Start here. Check the 'Use Polybase' checkbox.

If you do just want to use Polybase and Blob Storage, then you would have to:

first export the data from the source system internal tables to blob store using CETAS.
in the target system create external tables over the files in blob store
n the target system import the data from the external tables into the database using CTAS.

answered Mar 09 '17 at 10:37

wBob

13,710
3
20
37

2

Thank you. I think CETAS->Blob->CTAS is way to go. I couldn't find any better solution – HimalayanNinja Mar 11 '17 at 00:25
Consider marking that as an answer if you have found it helpful. – wBob Mar 13 '17 at 21:08

score 2 · Answer 2 · answered Mar 07 '17 at 19:29

2

As far as I know, you have to use PolyBase and either Blob Storage or Data Lake Store to get the maximum throughput (bypass the control node)

answered Mar 07 '17 at 19:29

Sascha Dittmann

616
4
9

score 2 · Answer 3 · answered Mar 14 '17 at 16:27

2

You can create a new SQL DW from a geo-backup which should be a complete copy of the SQL DW with a 24 hour SLA. First click on create new SQL DW and select backup as an option as opposed to blank or sample.

answered Mar 14 '17 at 16:27

hiroki

434
2
15

This is potentially a good option @hirokibutterfield if you want to duplicate an entire warehouse. I think there are some constraints around where you can restore *from*, for example can you only restore from between [paired data centers](https://learn.microsoft.com/en-us/azure/best-practices-availability-paired-regions)? – wBob Mar 14 '17 at 17:12
You can restore into any data center but the costs and time to restore vary. The paired region is the fastest and cheapest, followed by the original region, and slowest and most expensive is any other data center as you incur egress charges. – hiroki Mar 14 '17 at 17:36

Azure SQL DW to Azure SQL DW using Polybase

3 Answers3