Running All Create SQL Scripts Hosted in Synapse Workspace Using Azure DevOps Pipeline CI/CD

As organizations adopt Azure Synapse Analytics for their data warehousing and big data analytics workloads, they need to automate their deployment and release processes to ensure consistency and reliability. Azure DevOps provides a robust and flexible platform for continuous integration and continuous deployment (CI/CD) of Synapse Analytics artifacts, including SQL scripts, notebooks, pipelines, and more.

In this blog post, we will focus on how to use Azure DevOps Pipeline to run all the Create SQL scripts that are hosted in Synapse Workspace at the end of the deployment process. We will provide step-by-step instructions, code snippets, and best practices for achieving this goal.

Step 1: Create a Synapse Workspace

To start, we need to have a Synapse Workspace that contains one or more SQL scripts that we want to run. Follow these steps to create a Synapse Workspace:

1. Sign in to the Azure portal and navigate to the Synapse Workspace resource.
2. Click on the "Add" button to create a new Synapse Workspace.
3. Provide a unique name, subscription, resource group, and region for the workspace.
4. Review and accept the default settings for the workspace, such as workspace pricing tier, workspace storage account, and workspace managed virtual network.
5. Click on the "Review + create" button to create the workspace.
6. Wait for the workspace to be created, which may take a few minutes.

Step 2: Create a SQL script in Synapse Workspace

Now, let's create a sample SQL script in Synapse Workspace that we will use for testing the pipeline later. Follow these steps to create a SQL script:

1. Sign in to the Azure Synapse Studio by clicking on the "Open Synapse Studio" button on the Synapse Workspace resource page.
2. Navigate to the "Develop" tab and click on the "New" button to create a new SQL script.
3. Provide a name and a description for the script.
4. Write the SQL code that creates a sample table in the workspace.
5. Click on the "Save" button to save the script.

Step 3: Create an Azure DevOps Pipeline

Next, let's create an Azure DevOps Pipeline that will deploy the Synapse Workspace artifacts and run the SQL scripts at the end of the deployment. Follow these steps to create a pipeline:

1. Sign in to the Azure DevOps portal and navigate to the project that contains the Synapse Workspace artifacts.
2. Click on the "Pipelines" menu and then click on the "New pipeline" button.
3. Select the "Azure Repos Git" option as the source of the pipeline.
4. Select the repository that contains the Synapse Workspace artifacts.
5. Select the "Starter pipeline" template as the starting point for the pipeline.
6. Click on the "Save and run" button to save and run the pipeline.

Step 4: Add a PowerShell script to the pipeline

To run the SQL scripts in Synapse Workspace, we need to add a PowerShell script to the pipeline that will execute the scripts. Follow these steps to add a PowerShell script to the pipeline:

1. Open the pipeline editor by clicking on the "Edit" button on the pipeline page.
2. Navigate to the "Tasks" section and click on the "+" button to add a new task.
3. Search for the "PowerShell" task and after adding this task, when the pipeline is run, it will list out all the SQL scripts found in the Synapse Workspace for the specified environment.
4. To actually run these SQL scripts, we need to add another task that will connect to the Synapse Workspace and execute these scripts. For this, we can use the Azure Synapse Analytics SQL script execution task.
5. Here's an example YAML code for this task:

- task: SqlTask@2

  inputs:

    azureSubscription: '<Azure subscription name>'

    sqlServerName: '<Synapse workspace name>'

    databaseName: '<Database name>'

    sqlFile: '**/Create*.sql'

 

In this code, we are specifying the Azure subscription name, Synapse workspace name, and database name where the SQL scripts are located. We are also using the **/Create*.sql pattern to search for all SQL scripts that start with "Create" in any folder.

This task will connect to the Synapse workspace and execute all the SQL scripts found by the previous task.

With these two tasks added to our pipeline, we can now automatically run all the Create SQL scripts that are hosted in the Synapse Workspace after deploying our artifacts to the specified environment.

By using Azure DevOps and Azure Synapse Analytics together, we can automate the entire deployment process for our data analytics solution, including the creation of the necessary database objects. This reduces the time and effort required for manual deployment, improves consistency and accuracy, and ensures that the same process is followed for every deployment.