CI/CD of MSFT Fabric Database via Azure DevOps

Problem Statement

Is it possible to implement CI/CD for the MSFT Fabric database via Azure DevOps?

Prerequisites

Service Principal
How to Create a Service Principal
AD Account
Azure Data Studio / Visual Studio
Fabric Capacity
Fabric Database
Azure DevOps

Solution

DACPAC is your ally in deploying schema changes or managing versions across environments, all while keeping the existing data untouched.

A DACPAC is the build output of a Database project.

We need to create a Database Project for Microsoft Fabric Database, which can be achieved via the ways.

Enable Git Integration in the workspace managing the Dev version of the Fabric Database.

Dev version

Leverage Visual Studio / Azure Data Studio to generate the database project. In this demo, we will be leveraging Azure Data Studio to achieve this.

Assuming Azure Data Studio is installed as stated in the Prerequisites, install the SQL Database projects extension.

Azure Data Studio

Connect to the Dev version of the Fabric database via Azure Data Studio and right-click Select the 'Create Project from Database' option.

Fabric database

Outcome

Outcome

After that, the Project synchronizes within the Azure DevOps repo.

After the Database Project creation, we will leverage Azure DevOps Azure Pipelines and Release Classic editor to generate and publish the DACPAC to the intended destination Fabric database.

In this scenario, we would be leveraging the Azure Pipelines Agent pool for the Build pipeline creation, and it would be using the latest Windows version.

Azure Pipelines Agent

MSBuild task: To generate the DACPAC via the SQL Project file from the Azure DevOps Repo configured in the Build pipeline.

DACPAC

Note. One can parameterize the above highlighted Project parameter via the Azure pipeline variable group.

Copy files and Publish build artifacts tasks: Copy project build output into Azure DevOps artifacts location.

Copy project

Build output

Outcome

After successful pipeline execution, the DACPAC artifact is published.

Pipeline execution

After creating DACPAC, we would leverage the Release functionality to publish the DACPAC in the intended Fabric Database.

Release functionality

The Artifacts section is set up to leverage the latest artifact from the Build pipeline.

We will be using Windows's latest Azure Agent pool during the release.

Build pipeline

And the Service principal to authenticate to the destination Fabric database.

So, we need to grant the Service principal 'Member' access within the workspace.

Member

Powershell task: Generate Access Token via the Service principal for authentication, install SQL package exe, and publish the DACPAC.

SQL package

Powershell code

$TenantID = "$(TenantId)"
$clientID = "$(ClientId)"
$clientSct = "$(ClientSecret)"
$resourceAppIdURI = 'https://database.windows.net/'

$tokenResponse = Invoke-RestMethod -Method Post -UseBasicParsing `
    -Uri "https://login.windows.net/$($TenantID)/oauth2/token" `
    -Body @{
        resource       = $resourceAppIdURI
        client_id      = $clientId
        grant_type     = 'client_credentials'
        client_secret  = $clientSct
    } -ContentType 'application/x-www-form-urlencoded'

if ($tokenResponse) {
    Write-Debug "Access token type is $($tokenResponse.token_type), expires $($tokenResponse.expires_on)"
    $Token = $tokenResponse.access_token
}

dotnet tool install -g microsoft.sqlpackage

& "C:\Users\VssAdministrator\.dotnet\tools\sqlpackage.exe" `
    /SourceFile:"$(SourceFilePath)" `
    /Action:Publish `
    /TargetServerName:"$(TargetServer)" `
    /TargetDatabaseName:"$(TargetDatabase)" `
    /AccessToken:$Token

where values appended with $ like $(TenantId) are Pipeline variables.

Outcome

Pipeline variables

Build Pipeline YAML

# Variable 'DACPACPath' was defined in the Variables tab
jobs:
- job: Job_1
  displayName: Agent job 1
  pool:
    vmImage: windows-latest
  steps:
    - checkout: self
      fetchDepth: 1
    - task: MSBuild@1
      displayName: Build DACPAC
      inputs:
        solution: $(DACPACPath)
    - task: CopyFiles@2
      displayName: 'Copy Files to: $(build.artifactstagingdirectory)/DB/'
      inputs:
        SourceFolder: '$(agent.builddirectory)\s\'
        TargetFolder: '$(build.artifactstagingdirectory)/DB/'
    - task: PublishBuildArtifacts@1
      displayName: 'Publish Artifact: drop'