Using Azure Data Factory V2 to Pause and Resume Azure SQL Data Warehouse

Using Azure Data Factory V2 to Pause and Resume Azure SQL Data Warehouse

In this post, I will show how to automate the process to Pause and Resume an Azure SQL Data Warehouse instance in Azure Data Factory v2 to reduce cost. I am assuming that you already know how to provision an Azure SQL Data Warehouse, Azure Logic Apps and Azure Data Factory V2.

Before deep dive on how to, let’s have a quick overview of what is Azure Data Factory (ADF), Azure SQL Data Warehouse (SQL DW) and Azure Logic Apps.

Azure SQL DW, Logic Apps and ADF - Pic 1

Azure SQL DW is a cloud-based data store used to process and store petabytes of data and it is built on MPP (Massively Parallel Process) architecture. Azure SQL DW is key component of Microsoft Big Data offering. It is important to highlight that Azure SQL DW is not built for operational workloads (OLTP).

Azure Data Factory is a cloud-based data orchestration built to process complex big data using extract-transform-load (ETL), extract-load-transform (ELT) and Data Integration solutions. I will use Azure Data Factory V2, please make sure you select V2 when you provision your ADF instance.

Azure Logic Apps is a cloud base solution that helps you build, schedule, and automate processes as workflows so you can integrate apps, data, systems, and services across enterprises or organizations. I will use Azure Logic Apps to Resume, Pause and check Azure SQL DW State.

Setup an Azure SQL DW

Resource Group Name: Blog

Azure SQL Server: azuresqlsrv-zenatti

Azure SQL DW database: sqldw

Tier: 100

Azure SQL DW APIs

I will use the following Azure SQL DW APIs:

Resume (POST): https://management.azure.com/subscriptions/{subscription-id}/resourceGroups/{resource-group-name}/providers/Microsoft.Sql/servers/{server-name}/databases/{database-name}/resume?api-version=2014-04-01-preview HTTP/1.1

Pause (POST): https://management.azure.com/subscriptions/{subscription-id}/resourceGroups/{resource-group-name}/providers/Microsoft.Sql/servers/{server-name}/databases/{database-name}/pause?api-version=2014-04-01-preview HTTP/1.1

Check State (GET): https://management.azure.com/subscriptions/{subscription-id}/resourceGroups/{resource-group-name}/providers/Microsoft.Sql/servers/{server-name}/databases/{database-name}?api-version=2014-04-01 HTTP/1.1

In the APIs above, you will need to type your subscription id, resource group name, server name and database name. See below how should be:

https://management.azure.com/subscriptions/834b52ae-c00a-42cc-8f85-edd3860b2b7f/resourceGroups/Blog/providers/Microsoft.Sql/servers/azuresqlsrv-zenatti/databases/sqldw/resume?api-version=2014-04-01-preview

https://management.azure.com/subscriptions/834b52ae-c00a-42cc-8f85-edd3860b2b7f/resourceGroups/Blog/providers/Microsoft.Sql/servers/azuresqlsrv-zenatti/databases/sqldw/pause?api-version=2014-04-01-preview

https://management.azure.com/subscriptions/834b52ae-c00a-42cc-8f85-edd3860b2b7f/resourceGroups/Blog/providers/Microsoft.Sql/servers/azuresqlsrv-zenatti/databases/sqldw?api-version=2014-04-01

Service Principal and SQL Server Access

Before setup and configuring Logic Apps, you need to create a Service Principal (Client Id and Client Secret), please follow the steps from: https://docs.microsoft.com/en-us/azure/azure-resource-manager/resource-group-create-service-principal-portal

After creating and save the Client Id and Client Secret, you need to give access to the Client Id to access Azure SQL Server, as below:

  • Click over SQL Server

Azure SQL Server - Pic 2

  • Click on Access Control (IAM) and Add

Access Control (IAM) - Pic 3

  • Select Role as SQL Server Contributor, Type Client Id on Select field and Save.

Access Control (IAM) Add Permission - Pic 4

Configuring Logic Apps

In the Azure Logic Apps, I will use Azure SQL DW REST API to Resume, Pause and check Azure SQL DW State.  You need to create three Logic Apps as below:

Logic Apps - Pic 5

Logic-App-SQL-DW-Resume

  • Create a Logic App “Logic-App-SQL-DW-Resume”
  • Open Logic App Designer and Select “When a HTTP request is received”

Logic-App-SQL-DW-Resume - Pic 1

  • Add an Action HTTP and enter the following:
    • Method: POST
    • Uri: <enter the SQL DW API to Resume>
    • Authentication: Active Directory OAuth
    • Tenant: <Tenant Id – See below how to get Tenant Id>
    • Audience: https://management.azure.com/
    • Client ID: <Client Id>
    • Credential Type: Secret
    • Secret: <Client Secret>

 Logic-App-SQL-DW-Resume - Pic 2

  • Save and Click on Run to test it, you should get a successful run as below:

Logic-App-SQL-DW-Resume - Pic 3

  • Copy the URL for the Logic App and save to use in the ADF Pipeline

Logic-App-SQL-DW-Resume - Pic 4

Logic-App-SQL-DW-Pause

  • Create a Logic App “Logic-App-SQL-DW-Pause”
  • Open Logic App Designer and Select “When a HTTP request is received”

 

Logic-App-SQL-DW-Pause - Pic 1

  • Add an Action HTTP and enter the following:
    • Method: POST
    • Uri: <enter the SQL DW API to Pause>
    • Authentication: Active Directory OAuth
    • Tenant: <Tenant Id – See below how to get Tenant Id>
    • Audience: https://management.azure.com/
    • Client ID: <Client Id>
    • Credential Type: Secret
    • Secret: <Client Secret>

Logic-App-SQL-DW-Pause - Pic 2

  • Save and Click in Run to test it, you should get a successful run as below:

Logic-App-SQL-DW-Pause - Pic 3

  • Copy the URL for the Logic App and save to use in the ADF Pipeline

Logic-App-SQL-DW-Pause - Pic 4

Logic-App-SQL-DW-State

  • Create a Logic App “Logic-App-SQL-DW-State”
  • Open Logic App Designer and Select “When a HTTP request is received”

Logic-App-SQL-DW-State - Pic 1

  • Change Method to GET

Logic-App-SQL-DW-State - Pic 2

  • Add an Action HTTP and enter the following:
    • Method: GET
    • Uri: <enter the SQL DW API to check SQL DW State>
    • Authentication: Active Directory OAuth
    • Tenant: <Tenant Id – See below how to get Tenant Id>
    • Audience: https://management.azure.com/
    • Client ID: <Client Id>
    • Credential Type: Secret
    • Secret: <Client Secret>

Logic-App-SQL-DW-State - Pic 3

  • Add an Action “Request – Response”. Add Status code and Body as below:

Logic-App-SQL-DW-State - Pic 4

  • Save and Click in Run to test it, you should get a successful run as below:

Logic-App-SQL-DW-State - Pic 5

  • Copy the URL for the Logic App and save to use in the ADF Pipeline

Logic-App-SQL-DW-State - Pic 6

Build ADF Pipeline

Provision an Azure Data Factory V2.

SQL DW Resume

Create a Pipeline and name it as “SQL DW Resume” and follow steps below:

  • Create 2 parameters in the pipeline as below:
    • SQLDWResume: Enter the URL from Logic App “Logic-App-SQL-DW-Resume”
    • SQLDWState: Enter the URL from Logic App “Logic-App-SQL-DW-State”

ADF SQL DW Resume - Pic 1

  • Add a Web activity and Name as SQL DW Resume
  • Add an Until activity and Name as Until SQL DW Resumed
  • Link Web Activity to Until Activity

ADF SQL DW Resume - Pic 2

  • In Web Activity, click on Parameters and in url add:

@pipeline().parameters.SQLDWResume

ADF SQL DW Resume - Pic 3

  • Click on Settings and select POST in Method

ADF SQL DW Resume - Pic 4

  • In Until Activity, click on Settings and in expression add:

equals(‘Online’, coalesce(activity(‘SQL DW State’).output.properties.status, ‘null’))

ADF SQL DW Resume - Pic 5

  • Click on Activities and click on Add activity

ADF SQL DW Resume - Pic 6

  • Inside the Until Activity, Add a Web Activity and a Wait Activity. Link Web Activity to Wait Activity.
  • Rename Web Activity to SQL DW State
  • Rename Wait Activity to Wait 30 seconds

ADF SQL DW Resume - Pic 7

  • In the Wait Activity, go to Settings and enter 30 on “Wait time in seconds”

ADF SQL DW Resume - Pic 8

  • In the Web Activity, go to parameters and in url enter:

@pipeline().parameters.SQLDWState

ADF SQL DW Resume - Pic 9

  • Click on Settings and select GET in Method

ADF SQL DW Resume - Pic 10

  • Now you can Debug your pipeline. Don’t forget to Publish it.

ADF SQL DW Resume - Pic 11

SQL DW Pause

Create a Pipeline and name it as “SQL DW Pause” and follow steps below:

  • Create 2 parameters in the pipeline as below:
    • SQLDWPause: Enter the URL from Logic App “Logic-App-SQL-DW-Pause”
    • SQLDWState: Enter the URL from Logic App “Logic-App-SQL-DW-State”

ADF SQL DW Pause - Pic 1

  • Add a Web activity and Name as SQL DW Pause
  • Add an Until activity and Name as Until SQL DW Pause
  • Link Web Activity to Until Activity

ADF SQL DW Pause - Pic 2

  • In Web Activity, click on Parameters and in url add:

@pipeline().parameters.SQLDWPause

ADF SQL DW Pause - Pic 3

  • Click on Settings and select POST in Method

ADF SQL DW Pause - Pic 4

  • In Until Activity, click on Settings and in expression add:

@equals(‘Paused’, coalesce(activity(‘SQL DW State’).output.properties.status, ‘null’))

ADF SQL DW Pause - Pic 5

  • Click on Activities and click on Add activity

ADF SQL DW Pause - Pic 6

  • Inside the Until Activity, Add a Web Activity and a Wait Activity. Link Web Activity to Wait Activity.
  • Rename Web Activity to SQL DW State
  • Rename Wait Activity to Wait 30 seconds

ADF SQL DW Pause - Pic 7

  • In the Wait Activity, go to Settings and enter 30 on “Wait time in seconds”

ADF SQL DW Pause - Pic 8

  • In the Web Activity, go to parameters and in url enter:

@pipeline().parameters.SQLDWState

ADF SQL DW Pause - Pic 9

  • Click on Settings and select GET in Method

ADF SQL DW Pause - Pic 10

  • Now you can Debug your pipeline. Don’t forget to Publish it.

ADF SQL DW Pause - Pic 11

Get Tenant Id

To find the Tenant Id go to the page top right and click on Question and on “Show diagnostics”.

Get Tenant ID - Pic 1

After downloading the PortalDiagnostics.json, search for tenants word and you will find the tenant id, as below:

Get Tenant ID - Pic 2

Hope it helps you.

Thanks,

Sergio

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: