CODEWITHSUNDEEP
azure-sql-databaseazure-synapseazure-synapse-pipeline
theprof86
theprof86

Reputation: 33

How to copy from DEV SQL Pool to Production SQL Pool

We have about 300 tables with millions of rows in our Development Azure Dedicated SQL Pool. We want to be able to copy data from the DEV SQL Pool to our Production SQL Pool. One way to do this is to use the BCP tool but we're wondering if there is a better way.

We tried creating a Synapse Pipeline in Production but it won't let us "select" the DEV SQL Pool linked service. Is this even possible to connect from Production Synapse Workspace (that is connected to our PROD SQL Pool) to our DEV SQL Pool?

We tried creating a Synapse Pipeline in Production but it won't let us "select" the DEV SQL Pool linked service.

Upvotes: 1

Views: 451

Answers (1)

Dileep Raj Narayan Thumula
Dileep Raj Narayan Thumula

Reputation: 3145

I have tried the below approach As an example I have created a table in DEV Synapse. I used the Copy activity to load the table in PROD synapse.

  • I have created the Azure SQL database as the SOURCE and SINK dataset.

  • In the Linked Service page, you will find a dropdown list where you should choose your server name and the name of your database. After that, you'll need to provide your username and password to establish the connection.

Source dataset Linked Service: enter image description here

Sink dataset Linked Service: enter image description here

In the Sink dataset settings, there's an option labeled "Table" where you should enable the "Auto create table" feature. This will automatically create a table for you with the specified settings. enter image description here

enter image description here

enter image description here

Know more about Load data into dedicated SQL pool using the copy activity

Upvotes: 0

Related Questions