Azure DevOps Automated CICD pipeline with SQL Server Git Integration

Recently I realised working with quite a few data teams across UK and US that many of these data teams still using VM hosted SQL Server and manually merging into main. To be honest, I helped the teams to get started with simple azure CICD build and release pipeline for the SQL Server. This was possible without incurring any additional costs or without adding new services to their Microsoft Subscriptions. You do not need additional VMs. You can use your existing one to leverage as a Self Hosted Agent. You will also have to configure the Yaml Pipeline. I will talk you through the process here.

Things you will need:

SQL Server, SSMS, Git, Visual Studio SSDT Project, DACFx sqlpackage.exe .Net Framework Lastest standalone version installed (optional)

Step 1: Get the DACPCAC file to get started with your sql server exiting project.

From the SSMS, create a the .dacpac for your existing database.

Step 2: Setting up a git repo on the Azure DevOps repo.

Now navigate to your azure devops account and set up your git repo or azure repos there. Now save the repository location somewhere safe.

Step 3: Set up the project on visual studio based on the dacpac created earlier

Create a new visual studio SQL Database Project and from the solution explorer, right click on your mouse and add the data-tier application .dacpac file.

Step 4: Now, push your project to the github project.

After you have connected to the remote repo, click on create and push. You have the repo uploaded now. You can go to your azure devops repos and see that the repo is set up. You can access all your files there.

Step 5: Setup the Self Hosted agent on the Azure Devops and install the agent on your system using PAT

First> go into this location: https://dev.azure.com/yout_org_name/yout_proj_name/_settings/agentqueues

Second, Add Pool > Self Hosted Pool> Name your agent pool; I have named Bosspc
Third, follow these instructions. on the screen.

Fourth, windows key>services>Azure Pipeline Agent> Start

Fifth, check if you have the pipeline online in your azure devops agent pool.

Step 5: Creating YAML pipeline

Okay, before creating the YAML, you need to download and install sqlpackage.exe
Download and install SqlPackage - SQL Server | Microsoft Learn
Make sure its the last option you are downloading and installing on your agent

# Starter pipeline
# Start with a minimal pipeline that you can customize to build and deploy your code.
# Add steps that build, run tests, deploy, and more:
# https://aka.ms/yaml

trigger:
- master

stages:
- stage: Build
  displayName: building artifact
  jobs:
  - job: BuildslnDacpac
    displayName: Building Dacpac
    pool: 
      name: Bosspc
      demands:
      - agent.name -equals Bosspc
    steps:
    - task: MSBuild@1
      inputs:
        solution: '**/*.sln'

    - task: PublishBuildArtifacts@1
      inputs:
        PathtoPublish: '$(Build.SourcesDirectory)'
        ArtifactName: 'drop'
        publishLocation: 'Container'

- stage: Deploy
  displayName: master deploying
  dependsOn: Build
  condition: succeeded()
  jobs:
  - deployment: DeploymentJob
    pool:
      name: Bosspc
      demands:
      - agent.name -equals Bosspc
    environment: Deployment
    strategy:
      runOnce:
        deploy:
          steps:
          - task: DownloadBuildArtifacts@1
            inputs:
              buildType: 'current'
              downloadType: 'single'
              artifactName: 'drop'
              downloadPath: '$(Build.ArtifactStagingDirectory)'

          - task: SqlDacpacDeploymentOnMachineGroup@0
            inputs:
              TaskType: 'dacpac'
              DacpacFile: '$(Build.ArtifactStagingDirectory)\**/*.dacpac'
              TargetMethod: 'server'
              ServerName: 'localhost,2021'
              DatabaseName: 'test1'
              AuthScheme: 'sqlServerAuthentication'
              SqlUsername: 'admin'
              SqlPassword: 'admin'
              AdditionalArguments: '/TargetEncryptConnection:False'

NOTE:

In the dacpac task, make sure you add following argument. It is a new update and will screw you up.
/TargetEncryptConnection:False

Make sure you visit this site to understand a bit more about the sqlpackage deployment SqlPackage in development pipelines - SQL Server | Microsoft Learn

DotNetCoreCli@2 task has got an issue at the build stage when trying to work with the self hosted local agent. I have worked with the MSbuild instead.

You can also automated you SSIS pipelines with azure. SQL Server Integration Services DevOps overview - SQL Server Integration Services (SSIS) | Microsoft Learn