Azure: Copy data from one database to another using Azure Data Factory – II

In the previous post, we discussed how to create Azure SQL Server and Azure SQL Database. Now that we are ready with source data/table and destination table, let’s create Azure Data Factory to copy the data.

3. Azure Data Factory:

Click on Create a resource –> Analytics –> Data Factory

aa

Fill the mandatory fields and click Create

a1

After creating data factory, let’s browse it.Click on Author and Monitor.

aaaa

Click on Author icon on the left –> Click on Connections –> Click on +New under Linked Services tab to create a new linked service.

bb

Select Azure SQL Database — >Click Continue.

cc

Fill the mandatory fields in the next screen. Fill below details on the screen from the previous post:

  • Server Name: Azure SQL Server
  • Database Name: SourceSQLDB
  • Username and Password: SQL Authentication Credential used while setting up Azure SQL server

Click on Test Connection. Once it’s successful, click on Finish.

dd

After creating linked server for source, let’s create a Data set 

ee

On the next screen, Select Azure SQL Database and click on Finish.

ff

On the next screen, give a name in General tab.

i1

Then go to Connection tab. Select the source linked server that we just created and the view that we had created in the previous post.

j0

Now that we have created Data Set and Linked Server for source, let’s create the same for destination as well where the data will be copied.

Click on Connections –> +New under Linked Service tab

bb

Fill the mandatory fields in the next screen. Fill below details on the screen from the previous post:

  • Server Name: Azure SQL Server
  • Database Name: DestinationSQLDB
  • Username and Password: SQL Authentication Credential used while setting up Azure SQL server

Click on Test Connection. Once it’s successful, click on Finish.

d1

After creating linked server for destination, let’s create a Data set for destination

ee

On the next screen, Select Azure SQL Database and click on Finish.

ff

On the next screen, give a name in General tab.

ii

Then go to Connection tab. Select the destination linked server that we just created and the destination table that we had created in the previous post.

jj

Once we are done with the source and destination linked servers, source and destination data sets, let’s create a pipeline in the data factory.

1

Start typing “copy” in the Activities text box to select Copy Data Activity. Give a name to the pipeline under General tab.

kk

Then under Source tab, select source data set as “SourceAzureSQLDB” that we created above. In Use Query there are 3 options

  • Table: copy the entire data in the table/view to the destination table
  • Query: Write a select statement. Result set of the select statement will be copied to the destination table
  • Stored procedure: Result set of the stored procedure will be copied to the destination table

Since we want to copy the data of the view entirely we will select Table as Use Query.

ll

Then go to Sink tab. Select destination data set “DestinationAzureSQLDB” that we have created above

mm

Then moving to Mapping tab, click on Import schemas button to create the mapping between the source and destination fields.

nn

Once done, let’s validate the changes. If no errors found we can publish the changes.

oo

After publishing the changes, let’s select the Pipeline –> Trigger –> Trigger Now to copy the data from source view to the destination table.

xx

Once triggered, Click on Monitor icon on the left –> Select pipeline Runs tab.

yy

Since it is succeeded now, let’s check and compare the data in source view and destination table.

Source Database: SourceSQLDB, No. of Records: 847, View: CustomersView

vv

Destination Database: DestinationSQLDB, No. of Records: 847, Table: CustomersView

ww

So, now the data has been copied to the destination database successfully.

We can also schedule the trigger to run after a certain interval of time. Below are the steps to do it.

Click on the Author icon on the left–> Triggers –> +New

pp

Fill the mandatory fields. We are scheduling the trigger to run every 5 minutes. Select the options as shown below and do not check Activated button as it can be activated once the trigger is associated with any pipeline. Click on Finish.

qq

Then go to the Pipeline –> Trigger –> New/Edit and select the trigger that we just created from the list.

rr

ss

After selecting the trigger Publish the changes. Then go to the trigger and edit it –> Select Activated checkbox this time –> click on Next.

tt

Click on Finish –> As the message is obvious,  Publish the trigger.

t0

After publishing the trigger, Click on Monitor icon on the left –> Trigger Runs tab. You should be able to see the status of each trigger that runs.

uu

So, in this series of the blog post, we learned how to copy data form one database to another using Azure data Factory by manually running the pipeline and scheduling the pipeline by creating trigger.

Hope it helps !!

Advertisements

4 thoughts on “Azure: Copy data from one database to another using Azure Data Factory – II

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s