Microsoft

Purpose of this blog:

 

1.Defined end-end process of SQL Pool Deployment from Visual studio SSDT code check-in to  continuous integration(Build pipeline) and continuous deployment(Release pipeline) from project perspective.

 

2. Reusable YAML CI and CD templates which can be implemented in any project because these templates was successfully implemented in projects

 

Developer Workflow


Continuous integration:


- Develop code in Visual Studio using SSDT on your local machine.(Follow the steps below)


a. Make sure to have SSDT installed on Visual Studio. Follow these steps to get SSDT properly configured:
 Install Visual Studio 2019 - Azure Synapse Analytics | Microsoft Docs


b. Connect to git repository and database using Visual Studio and make changes.
Follow the steps detailed in the Microsoft Documentation here: Source Control Integration - Azure Synapse Analytics | Microsoft Docs

Akshay_Attota_0-1645477901822.png

 


c. Use the Schema Compare tool to download the latest changes from the source Synapse Dedicated SQL
pool to your local SQL project in Visual Studio. You can individually select each change you want to
include.

Note: Click the video below to see how to do a schema compare with a SQL server to your local SQL project.
https://www.youtube.com/watch?v=fKM3OGaS5pQ 


d. IMPORTANT:
Make sure to do a local build to see if the project builds successfully before committing and merging with
the main branch.

Akshay_Attota_1-1645477959879.png


e. Commit and push code to a git branch.

Akshay_Attota_2-1645478036135.png

 


Release Engineer Workflow
Continuous integration:

 Build
a. Run build pipeline on your new code that is in the repository. This will use the project solution file to
build a dacpac artifact. Then it will publish this as a release artifact.

 

Akshay_Attota_3-1645478148102.png

 


Continuous delivery:

Deployment to target environment

a. Run the release pipeline for the specified target environment.
- This will download the previously generated build artifact and dacpac file. It will also download secure
connection strings from Azure KeyVault. Then it will deploy to your Synapse Dedicated SQL Pool using
dacpac deployment.

 

Akshay_Attota_4-1645478250082.png

 


CI/CD Pipeline Setup
Assumptions:
- Have SQL Server Code in your repository

- Have deployed a dedicated SQL pool to Azure with its accompanying resources

- Folder Structure for example Contoso DB(Below screenshot)

Akshay_Attota_5-1645478347278.png

 


Build Pipeline

Requirements:

- Make sure the pipeline runs on a Windows Agent
- Reason for windows agent : This is because the Visual Studio Build task will only work on windows


Steps:
- Build Task:
      - Use the Visual Studio build solution task
      - Direct the task to the database solution file ('ContosoDb/ContosoDb.sln' in our POC)
      - Set the configuration to release, if not set this will configure as debug
      - Example:

 

- task: VSBuild@1
  displayName: 'Build solution'
  inputs:
    solution: $(workingDirectory)/<name of solution file>.sln
    configuration: release

 

 


- Copy Files Task:
      - Copy the files from the build directory to the $(build.artifactstagingdirectory)
      - Example:

 

- task: CopyFiles@2
  displayName: 'Copy Files to:  $(build.artifactstagingdirectory)'
  inputs:
    SourceFolder: '$(workingDirectory)'
    TargetFolder: ' $(build.artifactstagingdirectory)'

 

 


- Publish Build Artifact Task
      - Publish the build artifact with a specified name
      - Example:

 

 

- task: PublishBuildArtifacts@1
  displayName: 'Publish Artifact: dacpac'
  inputs:
    ArtifactName: dacpac

 

 


Full CI YAML Pipeline Example

 

name: Release-$(rev:r)

trigger: none

variables:
  vmImageName: 'windows-2019'
  workingDirectory: '$(System.DefaultWorkingDirectory)/<folder path where there is sln file>'

stages:
- stage: Build
  displayName: Build stage

  jobs:

  - job: Build
    displayName: Build
    pool:
      vmImage: $(vmImageName)

    steps:
    - task: VSBuild@1
      displayName: 'Build solution'
      inputs:
        solution: $(workingDirectory)/<name of solution file>.sln
        configuration: release

    - task: CopyFiles@2
      displayName: 'Copy Files to:  $(build.artifactstagingdirectory)'
      inputs:
        SourceFolder: '$(workingDirectory)'
        TargetFolder: ' $(build.artifactstagingdirectory)'

    - task: PublishBuildArtifacts@1
      displayName: 'Publish Artifact: dacpac'
      inputs:
        ArtifactName: dacpac

 

 


Release Pipeline
Requirements:
- Make sure the pipeline runs on a Windows Agent 


Steps:
- Deployment Job
      - Create a deployment job that uses the windows agent
      - Example:

 

jobs:
- deployment: DeploySynapsePool
  displayName: Deploy Synapse Dedicated SQL Pool
  pool:
    vmImage: $(vmImageName) #vmimage is windows-2019
  environment: DataEnv
  strategy:
    runOnce:
      deploy:
        steps:

 


-Azure Key Vault Task
      - Use the an Azure Key Vault to take store the connection string to the Dedicated SQL Pool
      - Grab this connection string from the key vault and input it as an Azure Pipelines Variable
      - Example:

 

- task: AzureKeyVault@1
  inputs:
    azureSubscription: '$(azureSubscription)'
    KeyVaultName: '$(keyvault)'
    SecretsFilter: 'sqlconnstring'
    RunAsPreJob: true

 

 


- SQL Dacpac Deployment Task
      -Specify the location of the .dacpac file in the build artifact from the build pipeline
      -Specify the connection string that was taken from the Azure Key Vault task
      -Example:

 

- task: SqlAzureDacpacDeployment@1
  inputs:
      azureSubscription: '$(azureSubscription)'
      AuthenticationType: 'connectionString'
      ConnectionString: '$(sqlconnstring)'
      deployType: 'DacpacTask'
      DeploymentAction: 'Publish'
      DacpacFile: '$(Pipeline.Workspace)/dacpac/dacpac/bin/Release/<name-of-dacpacfile>.dacpac.dacpac'

 


Full Example CD YAML Pipeline

 

name: Release-$(rev:r)

trigger: none

resources:
  pipelines:
    - pipeline: dacpac
      source: <name of CI Pipeline>
      trigger:
        branches:
          - main

variables:
  - name: azureSubscription
    value: '<name of Service Connection>'
  - name : 'keyvault'
    value : '<Name of Keyvault>'
  - name : 'vmImageName'
    value : 'windows-2019'

stages:
- stage: Release
  displayName: Release stage

  jobs:
  - deployment: DeploySynapsePool
    displayName: Deploy Synapse Dedicated SQL Pool
    pool:
      vmImage: $(vmImageName)
    environment: DataEnv
    strategy:
      runOnce:
        deploy:
          steps:
          - task: AzureKeyVault@1
            inputs:
              azureSubscription: '$(azureSubscription)'
              KeyVaultName: '$(keyvault)'
              SecretsFilter: 'sqlconnstring'
              RunAsPreJob: true
          - task: SqlAzureDacpacDeployment@1
            inputs:
                azureSubscription: '$(azureSubscription)'
                AuthenticationType: 'connectionString'
                ConnectionString: '$(sqlconnstring)'
                deployType: 'DacpacTask'
                DeploymentAction: 'Publish'
                DacpacFile: '$(Pipeline.Workspace)/dacpac/dacpac/bin/Release/<name-of-dacpacfile>.dacpac'