Integrating database development into continuous integration and continuous deployment (CI/CD) workflows is the best practice for Azure SQL managed instance database projects. Automating the process through a deployment pipeline is always recommended. This automation ensures that ongoing deployments seamlessly align with your continuous local development efforts, eliminating the need for additional manual intervention.

This article guides you through the step-by-step process of creating a new azure SQL managed instance database project, adding objects to it, and setting up a CICD deployment pipeline using GitHub actions.

Prerequisites

• Visual Studio 2022 Community, Professional, or Enterprise
• Azure DevOps environment
• Contributor permission within Azure DevOps Con
• Sysadmin server roles within Azure SQL managed instance

Step 01

Open Visual Studio, click Create a new project

Search for SQL Server, select SQL Server Database Project

Provide the project name, folder path to store .dacpac file, create

Step 2

Import the database schema from an existing database. Right-click on the project and select 'Import'. You will see three options: Data-Tier Application (.dacpac), Database, and Script (.sql). In this case, I am using the Database option and importing form Azure SQL managed instance

To proceed, you will encounter a screen that allows you to provide a connection string. You can choose to select a database from local, network, or Azure sources, depending on your needs. Alternatively, you can directly enter the server name, authentication type, and credentials to connect to the database server. Once connected, select the desired database to import and include in your project.

Step 3

Configure the import settings. There are several options available, each designed to optimize the process and ensure seamless integration.

• Import application-scoped objects: will import tables, views, stored procedures likewise objects.

• Imports reference logins: login related imports.
• Import Permissions: will import related permissions.
• Import database settings: will import database settings.
• Folder Structure: option to choose folder structure in your project for database objects.
• Maximum files per folder: limit number files per folder.

Click Start which will show the progress window as shown. Click “Finish” to complete the step.

Step 4

• To ensure a smooth deployment process, start by incorporating any necessary post-deployment scripts into your project. These scripts are crucial for executing tasks that must be completed after the database has been deployed, such as performing data migrations or applying additional configurations.
• To compile your database project in Visual Studio, simply right-click on the project and select 'Build'. This action will compile the project and generate a sqlproj file, ensuring that your database project is ready for deployment.
• When building the project, you might face warnings and errors that need careful debugging and resolution to ensure the successful creation of the sqlproj file. Common issues include missing references, syntax errors, or configuration mismatches.
• After addressing all warnings and errors, rebuild the project to create the sqlproj file. This file contains the database schema and is essential for deployment.
• Ensure that any post-deployment scripts are seamlessly integrated into the project. These scripts will run after the database deployment, performing any additional necessary tasks.
• To ensure all changes are tracked and can be deployed through your CI/CD pipeline, commit the entire codebase, including the sqlproj file and any post-deployment scripts, to your branch in Azure DevOps. This step guarantees that every modification is documented and ready for deployment.

Step 5

Create Azure DevOps pipeline to deploy database project

Step 6

To ensure the YAML file effectively builds the SQL project and publishes the DACPAC file to the artifact folder of the pipeline, include the following stages.

stages:
- stage: Build
  jobs:
  - job: BuildJob
    displayName: 'Build Stage'
    steps:
    - task: VSBuild@1
      displayName: 'Build SQL Server Database Project'
      inputs:
        solution: $(solution)
        platform: $(buildPlatform)
        configuration: $(buildConfiguration)

    - task: CopyFiles@2
      inputs:
        SourceFolder: '$(Build.SourcesDirectory)'
        Contents: '**\*.dacpac'
        TargetFolder: '$(Build.ArtifactStagingDirectory)'
        flattenFolders: true

    - task: PublishPipelineArtifact@1
      inputs:
        targetPath: '$(Build.ArtifactStagingDirectory)'
        artifact: 'dacpac'
        publishLocation: 'pipeline'

- stage: Deploy
  jobs:
  - job: Deploy
    displayName: 'Deploy Stage'
    pool:
      name: 'Pool'
    steps:
    - task: DownloadPipelineArtifact@2
      inputs:
        buildType: current
        artifact: 'dacpac'
        path: '$(Build.ArtifactStagingDirectory)'

    - task: PowerShell@2
      displayName: 'upgrade sqlpackage'
      inputs:
        targetType: 'inline'
        script: |
          # use evergreen or specific dacfx msi link below
          wget -O DacFramework.msi "https://aka.ms/dacfx-msi"
          msiexec.exe /i "DacFramework.msi" /qn

    - task: SqlAzureDacpacDeployment@1
      inputs:
        azureSubscription: '$(ServiceConnection)'
        AuthenticationType: 'servicePrincipal'
        ServerName: '$(ServerName)'
        DatabaseName: '$(DatabaseName)'
        deployType: 'DacpacTask'
        DeploymentAction: 'Publish'
        DacpacFile: '$(Build.ArtifactStagingDirectory)/*.dacpac'
        IpDetectionMethod: 'AutoDetect'

Step 7

To execute any Pre and Post SQL script during deployment, you need to update the SQL package, obtain an access token, and then run the scripts.

# install all necessary dependencies onto the build agent
    - task: PowerShell@2
      name: install_dependencies
      inputs:
        targetType: inline
        script: |
          # Download and Install Azure CLI
          write-host "Installing AZ CLI..."
          Invoke-WebRequest -Uri https://aka.ms/installazurecliwindows -OutFile .\AzureCLI.msi
          Start-Process msiexec.exe -Wait -ArgumentList "/I AzureCLI.msi /quiet"
          Remove-Item .\AzureCLI.msi
          write-host "Done."

          # prepend the az cli path for future tasks in the pipeline
          write-host "Adding AZ CLI to PATH..."
          write-host "##vso[task.prependpath]C:\Program Files (x86)\Microsoft SDKs\Azure\CLI2\wbin"
          $currentPath = (Get-Item -path "HKCU:\Environment" ).GetValue('Path', '', 'DoNotExpandEnvironmentNames')
          if (-not $currentPath.Contains("C:\Program Files (x86)\Microsoft SDKs\Azure\CLI2\wbin")) { setx PATH ($currentPath + ";C:\Program Files (x86)\Microsoft SDKs\Azure\CLI2\wbin") }
          if (-not $env:path.Contains("C:\Program Files (x86)\Microsoft SDKs\Azure\CLI2\wbin")) { $env:path += ";C:\Program Files (x86)\Microsoft SDKs\Azure\CLI2\wbin" }
          write-host "Done."

          # install necessary PowerShell modules

          write-host "Installing necessary PowerShell modules..."
          
          Get-PackageProvider -Name nuget -force

          if ( -not (Get-Module -ListAvailable -Name Az.Resources) ) {
            install-module Az.Resources -force
          } 

          if ( -not (Get-Module -ListAvailable -Name Az.Accounts) ) {
            install-module Az.Accounts -force
          } 

          if ( -not (Get-Module -ListAvailable -Name SqlServer) ) {
            install-module SqlServer -force
          } 

          write-host "Done."
        
    - task: AzureCLI@2
      name: run_sql_scripts
      inputs:
        azureSubscription: '$(ServiceConnection)'
        scriptType: ps
        scriptLocation: inlineScript
        inlineScript: |
          # get access token for SQL
          $token = az account get-access-token --resource https://database.windows.net --query accessToken --output tsv

          # configure OELCore database
          Invoke-Sqlcmd -AccessToken $token -ServerInstance '$(ServerName)' -Database '$(DatabaseName)' -inputfile '.\pipelines\config-db.dev.sql'