Edit

Share via

Azure SQL database deployment

  • Article
  • 02/04/2025

Azure DevOps Services | Azure DevOps Server 2022 - Azure DevOps Server 2019

You can automatically deploy your database updates to Azure SQL database after every successful build.

Prerequisites

Product Requirements
Azure DevOps - An Azure DevOps project.
- Permissions:
    - To create service connections, you must have the Administrator or Creator role for service connections.

DACPAC

The simplest way to deploy a database is by using a data-tier package or DACPAC. DACPACs allow you to package and deploy schema changes and data. You can create a DACPAC using the SQL Database Project in Visual Studio.

The Azure SQL Database deployment task is the primary mechanism to deploy a database to Azure. This task, as with other built-in Azure tasks, requires an Azure service connection as an input. The Azure service connection stores the credentials to connect from Azure Pipelines to Azure.

The easiest way to get started with this task is to be signed in as a user that owns both the Azure DevOps organization and the Azure subscription. In this case, you won't have to manually create the service connection. Otherwise, to learn how to create an Azure service connection, see Create an Azure service connection.

To learn how to create an Azure service connection, see Create an Azure service connection.

To deploy a DACPAC to an Azure SQL database, use the Azure SQL Database deployment task. Add the following snippet to your YAML file:

- task: SqlAzureDacpacDeployment@1
  displayName: Execute Azure SQL : DacpacTask
  inputs:
    azureSubscription: '<Azure service connection>'
    ServerName: '<Database server name>'
    DatabaseName: '<Database name>'
    SqlUsername: '<SQL user name>'
    SqlPassword: '<SQL user password>'
    DacpacFile: '<Location of Dacpac file in $(Build.SourcesDirectory) after compilation>'

SQL scripts

Alternatively, you can use SQL scripts instead of DACPAC to deploy your database. Below is a simple SQL script that creates an empty database:

  USE [main]
  GO
  IF NOT EXISTS (SELECT name FROM main.sys.databases WHERE name = N'DatabaseExample')
  CREATE DATABASE [DatabaseExample]
  GO

To run SQL scripts from your pipeline, you'll need to add and remove firewall rules in Azure. Without these rules, the Azure Pipelines agent cannot communicate with Azure SQL Database.

Set Azure firewall rules

The following PowerShell script creates firewall rules. Save it as SetAzureFirewallRule.ps1 and add it to your repository:

[CmdletBinding(DefaultParameterSetName = 'None')]
param
(
  [String] [Parameter(Mandatory = $true)] $ServerName,
  [String] [Parameter(Mandatory = $true)] $ResourceGroupName,
  [String] $FirewallRuleName = "AzureWebAppFirewall"
)
$agentIP = (New-Object net.webclient).downloadstring("https://api.ipify.org")
New-AzSqlServerFirewallRule -ResourceGroupName $ResourceGroupName -ServerName $ServerName -FirewallRuleName $FirewallRuleName -StartIPAddress $agentIP -EndIPAddress $agentIP

Remove Azure firewall rules

The following PowerShell script removes firewall rules. Save it as RemoveAzureFirewallRule.ps1 and add it to your repository:

[CmdletBinding(DefaultParameterSetName = 'None')]
param
(
  [String] [Parameter(Mandatory = $true)] $ServerName,
  [String] [Parameter(Mandatory = $true)] $ResourceGroupName,
  [String] $FirewallRuleName = "AzureWebAppFirewall"
)
Remove-AzSqlServerFirewallRule -ServerName $ServerName -FirewallRuleName $FirewallRuleName -ResourceGroupName $ResourceGroupName

Deploy database with SQL scripts

The following example outlines the steps to add firewall rules, deploy your database using SQL scripts, and then remove the firewall rules:

variables:
  AzureSubscription: '<SERVICE_CONNECTION_NAME>'
  ResourceGroupName: '<RESOURCE_GROUP_NAME>'
  ServerName: '<DATABASE_SERVER_NAME>'
  ServerFqdn: '<DATABASE_FQDN>'
  DatabaseName: '<DATABASE_NAME>'
  AdminUser: '<DATABASE_USERNAME>'
  AdminPassword: '<DATABASE_PASSWORD>'
  SQLFile: '<LOCATION_OF_SQL_FILE_IN_$(Build.SourcesDirectory)>'

steps:
- task: AzurePowerShell@5
  displayName: 'Set Azure firewall rules'
  inputs:
    azureSubscription: '$(AzureSubscription)'
    ScriptType: filePath
    ScriptPath: '$(Build.SourcesDirectory)\scripts\SetAzureFirewallRule.ps1'
    ScriptArguments: '-ServerName $(ServerName) -ResourceGroupName $(ResourceGroupName)'
    azurePowerShellVersion: LatestVersion

- task: PowerShell@2
  inputs:
  displayName: 'Install SqlServer module if not present'
    targetType: 'inline'
    script: |
      if (-not (Get-Module -ListAvailable -Name SqlServer)) {
          Install-Module -Name SqlServer -Force -AllowClobber
      }

- task: PowerShell@2
  displayName: 'Deploy database'
  inputs:
    targetType: 'inline'
    script: |
      Invoke-Sqlcmd -InputFile $(SQLFile) -ServerInstance $(ServerFqdn) -Database $(DatabaseName) -Username $(AdminUser) -Password $(AdminPassword)

- task: AzurePowerShell@5
  displayName: 'Remove Azure firewall rules'
  inputs:
    azureSubscription: '$(AzureSubscription)'
    ScriptType: filePath
    ScriptPath: '$(Build.SourcesDirectory)\scripts\RemoveAzureFirewallRule.ps1'
    ScriptArguments: '-ServerName $(ServerName) -ResourceGroupName $(ResourceGroupName)'
    azurePowerShellVersion: LatestVersion

Deploy database conditionally

You can choose to deploy only specific builds to your Azure database, giving you more control over which changes are applied based on criteria like the source branch or build status.

To do this in YAML, you can use one of these techniques:

  • Isolate the deployment steps into a separate job and apply a condition to that job.

  • Add a condition directly to the step.

The example below shows how to deploy only builds from the main branch using conditions:

- task: SqlAzureDacpacDeployment@1
  condition: and(succeeded(), eq(variables['Build.SourceBranch'], 'refs/heads/main'))
  inputs:
    azureSubscription: '<Azure service connection>'
    ServerName: '<Database server name>'
    DatabaseName: '<Database name>'
    SqlUsername: '<SQL user name>'
    SqlPassword: '<SQL user password>'
    DacpacFile: '<Location of Dacpac file in $(Build.SourcesDirectory) after compilation>'

More SQL actions

The SQL Azure Dacpac Deployment task might not cover all the SQL server actions you need to perform. In such cases, you can use PowerShell or command-line scripts to execute the required commands.

This section covers common use cases for invoking the SqlPackage.exe tool. Before running this tool, make sure you're using a self-hosted agent with the tool installed.

Note

If you execute SQLPackage from the folder where it is installed, you must prefix the path with & and wrap it in double-quotes.

Basic Syntax

<Path of SQLPackage.exe> <Arguments to SQLPackage.exe>

You can use any of the following SQL scripts based on the action you wish to perform:

Creates a database snapshot (.dacpac) file from a live SQL server or Microsoft Azure SQL Database.

Command Syntax:

SqlPackage.exe /TargetFile:"<Target location of dacpac file>" /Action:Extract
/SourceServerName:"<ServerName>.database.windows.net"
/SourceDatabaseName:"<DatabaseName>" /SourceUser:"<Username>" /SourcePassword:"<Password>"

or

SqlPackage.exe /action:Extract /tf:"<Target location of dacpac file>"
/SourceConnectionString:"Data Source=ServerName;Initial Catalog=DatabaseName;Integrated Security=SSPI;Persist Security Info=False;"

Example:

SqlPackage.exe /TargetFile:"C:\temp\test.dacpac" /Action:Extract /SourceServerName:"DemoSqlServer.database.windows.net.placeholder"
 /SourceDatabaseName:"Testdb" /SourceUser:"ajay" /SourcePassword:"SQLPassword"

Help:

sqlpackage.exe /Action:Extract /?

Related content

Additional resources

Documentation