Build and Release SQL .dacpac using GitHub actions
What is SQL dacpac file.
A data-tier application (DAC) is a logical database entity that defines all of the SQL Server objects — such as tables, views, and instance objects, including logins — associated with a user’s database. A DAC is a self-contained unit of the entire database model and is portable in an artifact known as a DAC package, or .dacpac. It enables developers and database administrators to deploy dacpacs to new or existing databases. Deployments to an existing database updates the database model from the existing state to match the contents of the dacpac.
Challenge
One of the biggest challenge for Data engineers/DBA’s is to maintain consistency across all environments and there shouldn't be any variances related with Tables/Schema’s/Objects/Indexes/SP’s etc. And mainly to avoid any manual changes in Databases.
How to Maintain consistency?
One of the easy way to Maintain consistency across environments is via using CI/CD (Automated Build and Release Pipelines).
How do I achieve this?
One of the easiest way is to Create a new Build and Deploy them using CI/CD pipelines, Here we are going to use GitHub actions.
Build Steps:
- The first option is for a DevOps engineer/DBA to create a new Dacpac file using either with Visual Studio or Visual Studio code.
- Generate a new dacpac file and it will create a new Microsoft .net solution and generate DBO objects.
3. Once the new solution is ready check Target platform as each DBA/ Developer/DevOps engineers might be generating Dacpac images using different SSDT target environments.
One of the Important thumbrule while generating
If you are using SQL Data warehouse then you need to select your target platform as “Microsoft Azure SQL Data Warehouse”
and if you are using Azure SQL and then your target platform should be “SQL server 2016 and above”.
4. Lets create our first GitHub action workflow file, and start adding steps.
jobs:
build :
runs-on: windows-latest
steps:
- name: Checkout
uses: actions/checkout@v2
- name: Navigate to Workspace
run: |
cd DBADirectory
pwd
- name: Setup MSBuild
uses: microsoft/setup-msbuild@v1.3.1
- name: Build DACPAC
run: msbuild 'DBADirectory/DBADirectory.sln' /p:TargetFrameworkVersion=v4.7.1 /p:DSP=Microsoft.Data.Tools.Schema.Sql.SqlAzureV12DatabaseSchemaProvider
- name: Upload artifact
uses: actions/upload-artifact@v3.1.2
with:
name: SQLDB
path: ${{ github.workspace }}/DBADirectory/bin/debug/5. Make sure if you are creating a new Building specifically for Azure SQL then use below argument(s).
/p:TargetFrameworkVersion=v4.7.1 /p:DSP=Microsoft.Data.Tools.Schema.Sql.SqlAzureV12DatabaseSchemaProviderand if you are using Azure SQL Data warehouse/Azure Synapse then argument would be given below.
/p:ExtractAllTableData=true /p:IgnorePermissions=True /p:IgnoreUserLoginMappings=True6. One of the basic principal whenever you create your Build download generated artifact and check if it has generated right dacpac image and it has everything.
Deployment/Release steps:
- Select the path of the package and it will be in ‘bin/debug’ folder.
- Pass some of the mandatory arguments such as ‘Connection-String’, ‘path of the dacpac file’, ‘action’ and finally ‘any special arguments’.
- Also keep an eye on this extension as Microsoft frequently make changes related with arguments, versions etc. you can find more information about this extension at : https://github.com/marketplace/actions/azure-sql-deploy
4. Our Deployment steps
deploy:
needs: build
environment: DEV
runs-on: windows-latest
steps:
- name: login to az
shell: bash
run: |
echo "$CERT" > spncertificate.pem
az login --service-principal -u $CLIENT_ID -p spncertificate.pem --tenant $TENANT_ID
- name: download artifact containing dacpac
uses: actions/download-artifact@v3.0.2
with:
name: SQLDB
- name: Azure SQL Deploy
uses: Azure/sql-action@v2.1
with:
connection-string: '${{ secrets.CONNECTION_STRING }}'
path: './DBADirectory.dacpac'
action: publish
arguments: '/p:DropObjectsNotInSource=true'After deploy step execution you can validate the same in output console, here you can see tables are getting created (line #25 onwards).
Our final GitHub action file is given below.
name: Database Deployment
on:
workflow_dispatch:
inputs:
environment:
description: 'Environment'
type: environment
push:
branches:
- feature/*
env:
SQL_SERVER_NAME: ${{ secrets.SQL_SERVER_NAME }}
CONNECTION_STRING: ${{ secrets.CONNECTION_STRING }}
CERT: ${{ secrets.CERT }}
WORKING_DIRECTORY: DBADirectory
CLIENT_ID: ${{ secrets.CLIENT_ID }}
TENANT_ID: ${{ secrets.TENANT_ID }}
RESOURCE_GROUP: ${{ secrets.RESOURCE_GROUP }}
jobs:
build :
runs-on: windows-latest
steps:
- name: Checkout
uses: actions/checkout@v2
- name: Navigate to Workspace
run: |
cd DBADirectory
pwd
- name: Setup MSBuild
uses: microsoft/setup-msbuild@v1.3.1
- name: Build DACPAC
run: msbuild 'DBADirectory/DBADirectory.sln' /p:TargetFrameworkVersion=v4.7.1 /p:DSP=Microsoft.Data.Tools.Schema.Sql.SqlAzureV12DatabaseSchemaProvider
- name: Upload artifact
uses: actions/upload-artifact@v3.1.2
with:
name: SQLDB
path: ${{ github.workspace }}/DBADirectory/bin/debug/
deploy:
needs: build
environment: DEV
runs-on: windows-latest
steps:
- name: login to az
shell: bash
run: |
echo "$CERT" > spncertificate.pem
az login --service-principal -u $CLIENT_ID -p spncertificate.pem --tenant $TENANT_ID
- name: download artifact containing dacpac
uses: actions/download-artifact@v3.0.2
with:
name: SQLDB
- name: Azure SQL Deploy
uses: Azure/sql-action@v2.1
with:
connection-string: '${{ secrets.CONNECTION_STRING }}'
path: './DBADirectory.dacpac'
action: publish
arguments: '/p:DropObjectsNotInSource=true'