Azure Solutions

DevOps: Synapse Deployment

Use Case

• “We want to automate our Synapse deployment process using DevOps everywhere possible”

  

Proposed Solution

• Starter Pipeline: Create and test a minimum viable pipeline to demonstrate basic functionality
• Branch Deployment: Create an automated pipeline that: archives the QA branch, resets the QA branch, and creates a pull reqeust
• Synapse Parameterization: Create and deploy environmentally-specific Integration Runtimes, and parameterized Linked Services, Integration Datasets, Pipelines

Solution Requirements

• DevOps Organization, Project, Repository (dedicated to Synapse), and Branches “DEV”, “QA” and “PROD”
  • “Parallel Jobs” enabled
  • Project Settings » Repos » Respositories » Security » User Permissions… “{repository} Build Service”
    • Allow “Create branch”
    • Allow “Force push…”
• “DEV” Environment
  • Synapse

    • Git Configuration… Repository Type: Azure DevOps Git

      Collaboration Branch “DEV”

  • On-prem machine with:
    • SQL Server configured for SQL Server Authentication and TCP/IP enabled
    • Database “dbDEV”
    • Table: CREATE TABLE [dbo].[myTable]( [id] [int] NULL, [name] [varchar](64) NULL ) ON [PRIMARY]
• “QA” Environment
  • Synapse

    • Git Configuration… Repository Type: Azure DevOps Git

      Collaboration Branch “QA”

  • On-prem machine with:
    • SQL Server configured for SQL Server Authentication and TCP/IP enabled
    • Database “dbQA”
    • Table: CREATE TABLE [dbo].[myTable]( [id] [int] NULL, [name] [varchar](64) NULL ) ON [PRIMARY]

---

---

Exercise 1: Starter Pipeline

In this exercise, we will create and test a minimum viable pipeline to demonstrate basic functionality.

Step 1: Create Service Connection

Navigate to Azure DevOps » Project » Project Settings » Pipelines » Service Connections.

Click “Create service connection”.

On the “New service connection” pop-out, select “Azure Resource Manager”. Click “Next”.

On the “New Azure service connection” pop-out, select “Workload Identity federation (automatic)”. Click “Next”.

Complete the resulting “New Azure service connection” pop-out. Click “Save”.

---

Step 2: Create Pipeline

Navigate to Azure DevOps » Pipelines » Pipelines.

Click “+ Create Pipeline”.

On the “Connect” tab, click “Azure Repos Git”.

On the “Select” tab, click to select your repository.

On the “Configure” tab, click “Starter pipeline”.

On the “Review” tab, click to dropdown “Save and run”. Click “Save”.

On the resulting pop-out, click “Save”.

Click “Edit”.

Replace the default YAML with:

pool:
    vmImage: 'windows-latest'

variables: {o: "https://dev.azure.com/rchapler", p: "devops", r: "synapse", db: "DEV", qb: "QA", pb: "PROD"}

jobs:
- job: archiveQA
  steps:
  - script: echo $(System.AccessToken) | az devops login
    displayName: 'Login to DevOps'
  - task: AzureCLI@2
    displayName: 'Archive QA Branch'
    inputs:
      azureSubscription: "AzureSubscription"
      scriptType: 'pscore'
      scriptLocation: 'inlineScript'
      inlineScript: |
        echo "***** Copying QA Branch to ../archive/QA-{timestamp}"
        $qbid = az repos ref list --org $(o) -p $(p) -r $(r) --filter "heads/$(qb)" | ConvertFrom-Json | Select-Object -ExpandProperty objectId
        $PST = [System.TimeZoneInfo]::ConvertTimeFromUtc([DateTime]::UtcNow, [System.TimeZoneInfo]::FindSystemTimeZoneById("Pacific Standard Time"))
        $dt = Get-Date $PST -Format "yyyyMMdd-HHmmss"
        az repos ref create --name "refs/heads/archive/$(qb)-$dt" --organization $(o) --project $(p) --repository $(r) --object-id $qbid

Logic Explained

• archiveQA is the main job
• Login to DevOps logs into Azure DevOps using the System Access Token
• Archive QA Branch uses the Azure CLI to archive the QA branch by:
  • Getting the current date and time
  • Listing all the branches in the repository {i.e., $branches = az repos ref list...}
  • Finding the object identifier of the current QA branch {i.e., $oid = $branches | Where-Object {$_.name -eq "refs/heads/$b"}...}
  • Creating a new branch based on the current QA branch {i.e., az repos ref create...}

Click “Save”.

---

Step 3: Confirm Success

Click “Run”.

On the resulting “Run pipeline” pop-out, review default settings, check “Enable system diagnostics”. Click “Run”.

Click on the “archiveQA” job.

Confirm job run success and then navigate to DevOps » Repos » Branches and click on the “All tab”.

You can expect to see a new branch named “QA-{datetime}”.

---

Congratulations… you have successfully completed this exercise

---

---

Exercise 2: Branch Deployment

In this exercise, we will create an automated pipeline that completes the following three tasks: 1) Archive QA Branch… make a copy of the current QA branch to a timestamped branch in the “archive” folder 2) Reset QA Branch… force copy PROD branch to QA branch 3) Create a Pull Request… from DEV branch to QA branch

Step 1: Update Pipeline

Navigate to Azure DevOps » Pipelines » Pipelines and replace the existing YAML with:

pool:
    vmImage: 'windows-latest'

variables: {o: "https://dev.azure.com/rchapler", p: "devops", r: "synapse", db: "DEV", qb: "QA", pb: "PROD"}

jobs:
- job: archiveQA
  steps:
  - script: echo $(System.AccessToken) | az devops login
    displayName: 'Login to DevOps'
  - task: AzureCLI@2
    displayName: 'Archive QA Branch'
    inputs:
      azureSubscription: "AzureSubscription"
      scriptType: 'pscore'
      scriptLocation: 'inlineScript'
      inlineScript: |
        echo "***** Copying QA Branch to ../archive/QA-{timestamp}"
        $qbid = az repos ref list --org $(o) -p $(p) -r $(r) --filter "heads/$(qb)" | ConvertFrom-Json | Select-Object -ExpandProperty objectId
        $PST = [System.TimeZoneInfo]::ConvertTimeFromUtc([DateTime]::UtcNow, [System.TimeZoneInfo]::FindSystemTimeZoneById("Pacific Standard Time"))
        $dt = Get-Date $PST -Format "yyyyMMdd-HHmmss"
        az repos ref create --name "refs/heads/archive/$(qb)-$dt" --organization $(o) --project $(p) --repository $(r) --object-id $qbid

- job: resetQA # possible using Git commands, but not CLI/PowerShell or UI
  dependsOn: archiveQA
  steps:
  - task: AzureCLI@2
    displayName: 'Reset QA from PROD'
    inputs:
      azureSubscription: "AzureSubscription"
      scriptType: 'pscore'
      scriptLocation: 'inlineScript'
      inlineScript: |
        echo "***** Resetting QA from PROD"
        git -c http.extraheader="AUTHORIZATION: bearer $(System.AccessToken)" fetch
        git -c http.extraheader="AUTHORIZATION: bearer $(System.AccessToken)" checkout QA
        git -c http.extraheader="AUTHORIZATION: bearer $(System.AccessToken)" reset --hard origin/PROD
        git -c http.extraheader="AUTHORIZATION: bearer $(System.AccessToken)" push origin QA --force

- job: pullrequestDEVtoQA
  dependsOn: resetQA
  steps:
  - script: echo $(System.AccessToken) | az devops login
    displayName: 'Login to DevOps'
  - task: AzureCLI@2
    displayName: 'Create Pull Request'
    inputs:
      azureSubscription: "AzureSubscription"
      scriptType: 'pscore'
      scriptLocation: 'inlineScript'
      inlineScript: |
        echo "***** Creating Pull Request (from DEV to QA)"
        az repos pr create --title "DEV >> QA" --organization $(o) --project $(p) --repository $(r) --source-branch "$(db)" --target-branch "$(qb)"

Logic Explained

• pool specifies that the latest Windows virtual machine image should be used for the pipeline
• variables defines several variables that are used throughout the pipeline
• jobs contains the jobs that make up the pipeline; each job has a series of steps that are executed in order
  • archiveQA logs into DevOps and archives the QA branch by copying the QA branch to an archive folder with a timestamp
  • resetQA resets the QA branch from the PROD branch using Git commands to: 1) fetch the branches 2) checkout the QA branch 3) reset the QA branch to match the PROD branch 4) force push the changes to the QA branch
  • pullrequestDEVtoQA creates a pull request from the DEV branch to the QA branch

---

Step 2: Confirm Success

Navigate to the DEV instance of Synapse Studio and confirm Git Configuration.

Now we will mimic the Synapse deployment process and confirm pipeline functionality.

Recurring Development

Primary Actor: Member of Development Team

Click the branch dropdown and select “+ New branch”.

Complete the “Create a new branch” popup and click “Create”.

Navigate to Synapse » Develop, click “+”, and then select “SQL script” from the resulting menu.

On the resulting “SQL script 1” tab, paste the following T-SQL:

PRINT 'Hello, World';

Our goal is to capture a simple change in our branch, but run the logic if you are interested. Click “Commit all”.

Pull Request

Primary Actor: Member of Development Team

Click the branch dropdown and select “Create pull request”.

On the “New pull request” page, confirm destination “DEV”, review default values and update as desired. Click “Create”.

Automated Pipeline

Primary Actor: Deployment Manager

Continuing on the “Adding sqlscript: SQL script 1” pull request page, review Files, etc. Click “Complete”.

On the resulting “Complete pull request” pop-out, confirm default settings. Click “Complete merge”. Navigate to the “Deploy_toQA” pipeline.

Click “Run”.

On the resulting “Run pipeline” pop-out, review default settings, check “Enable system diagnostics”. Click “Run”.

Confirm successful processing of the three jobs.

---

Congratulations… you have successfully completed this exercise

---

---

Exercise 3: Synapse Parameterization

In this exercise, we will create and deploy environmentally-specific Integration Runtimes, and parameterized Linked Services, Datasets, Pipelines.

Step 1: Integration Runtimes

In this step, we are going to instantiate DEV and QA Integration Runtimes.

Important Synapse Self-Hosted Integration Runtimes facts:

• They cannot be shared by two environments
• Each must be tied to a dedicated on-prem installation
• Linked Service references must be manually updated after being deployed to a new environment

DEV Instance

Navigate to the DEV instance of Synapse Studio and create a new working branch.

Navigate to “Integration runtimes” and click “+ New”.

On the “Integration runtime setup” popout, click “Azure, Self-Hosted” and then “Continue”.

On the next “Integration runtime setup” popout, click “Self-Hosted” and then “Continue”.

On the next “Integraton runtime setup” popout, enter a “Name”. Click “Create”.

On the next “Integration runtime setup” popout, click “Option 1: Express setup” » “Click here to launch…”
Install Microsoft Integration Runtime with the downloaded installer.

When installation is complete, return to Synapse Studio and confirm that the new Integration Runtime is “Status: Running”.

---

Pull Request

Click the branch dropdown, and click “Create pull request”.

On the Azure DevOps » “New pull request” page, review default settings. Click “Create”.

On the Azure DevOps » “Adding integrationRuntime…” pull request page, click “Complete”.

Note: We are skipping normal DevOps processes like review, approval, tie to work items for the purpose of demonstration only

On the “Complete pull request” popout, review / update default settings. Click “Complete merge”.

Navigate to “Repos” » “Files” and select the “DEV” branch.

You should see a file in the “integrationRuntime” folder named “ir-DEV.json”.

---

Deploy to QA

Navigate to the “Deploy_toQA” pipeline and click “Run”. On the resulting “Run pipeline” popout, click “Run” again.

When the pipeline completes successfully, navigate to the pull request at Repos » Pull requests » Active.

Click “Complete” and on the resulting “Complete pull request” popout, click “Complete merge”. Confirm success.

---

QA Instance

Navigate to the QA instance of Synapse Studio, select the QA branch, then Manage » Integration » Integration runtimes.

You will see “ir-DEV” and note that it is “Status: Not Found”.
This is because an integration runtime can only be associated with a single instance of Synapse (in this case, “rchaplers-dev”).
Follow the instructions in Step 1 to instantiate a QA runtime.

Navigate to DevOps » “Repos” » “Files” and select the “QA” branch. Click on “integrationRuntime”.

Roll-over file “ir-QA.json”, click the vertical ellipses, and select “Download” from the resulting menu.

Switch to the “DEV” branch, click the vertical ellipses, and select “Upload file(s)” from the resulting menu.

On the “Commit” popout, “Browse” to the downloaded “ir-QA.json” file. Click “Commit”.

Run the “Deploy_toQA” pipeline and complete the resulting pull request.
“ir-DEV” and “ir-QA” will exist in both environments, but only be “Status: Running” in the related environment.
We will address use of the correct Integration Runtime in Exercise 4.

---

Step 2: Linked Service

Navigate to the DEV instance of Synapse Studio, then Manage » External Connections » Linked Services. Create a new working branch. Click “+ New” and on the “New linked service” popout, search for and select “SQL server”. Click “Continue”.

At the bottom of the popout form, expand “Parameters”, click “+ New” and add a new parameter named “Environment” (Type: String).
Complete the “New linked service” popout form, including:

Prompt	Entry
Connect via integration runtime…	ir-DEV
Server name…	localhost
Database name…	Dynamic Expression: @{concat('db',linkedService().Environment)}
Authentication type…	SQL authentication
User name…	sa (and then the associated password)

Click “Test Connection”, confirm success. Click “Commit”. When prompted “Linked service will be published immediately…” click “OK”.

---

Step 3: Integration Dataset

Continue in the DEV instance of Synapse Studio. Navigate to “Data” » “Linked” tab. Click “+” and then “Integration dataset” on the resulting menu. On the “New integration dataset” popout, search for and select “SQL server”, then click “Continue”. On the “Set properties” popout, select the “dbX” Linked Service. Click “OK”.

Navigate to the “Parameters” tab and create a new “Environment” parameter.

Return to the “Connection” tab and complete the form, including:

Prompt	Entry
Linked service	dbX
Environment	@dataset().Environment
Table	Check “Edit” and enter schema “dbo” and table “myTable”

Click “Test Connection”, enter Environment parameter value “DEV”. Click “OK”. Confirm success. Click “Commit”.

---

Step 4: Pipeline

Continue in the DEV instance of Synapse Studio. Navigate to “Integration”. Click “+” and then “Pipeline” on the resulting menu.

Drag a “Copy data” component from the “Activities” pane » “Move and transform” grouping. On the “General tab”, change the name to “Copy myTable”.

Navigate to “Source” tab and complete the form, including:

Prompt	Entry
Source dataset	dbX
Parameter: Environment	@{if(contains(pipeline().DataFactory, 'dev'), 'DEV', 'QA')}

Click “Preview data” and confirm success.

Navigate to “Sink” tab and complete the form, including:

Prompt	Entry
Source dataset	dbX
Parameter: Environment	@{if(contains(pipeline().DataFactory, 'dev'), 'DEV', 'QA')}

Note: We are copying from and to the same table (with no data) simply as a demonstration of parameterization

Click “Commit” and then “Debug”. Confirm success.

---

Step 5: Deploy to QA

Create and complete a pull request to move changes to the DEV branch. Run the “Deploy_toQA” pipeline in DevOps and complete the resulting pull request. Navigate to your QA environment and modify the Integration Runtime reference in the Linked Service to “ir-QA”.

---

Congratulations… you have successfully completed all exercises

---

---

This site is open source. Improve this page.