Azure SQL Database Deployment task

  • 4 minutes to read

Azure Pipelines

Use this task in a build or release pipeline to deploy to Azure SQL DB using a DACPAC or run scripts using SQLCMD.

YAML snippet

# Azure SQL Database deployment
# Deploy an Azure SQL Database using DACPAC or run scripts using SQLCMD
- task: SqlAzureDacpacDeployment@1
  inputs:
    #azureConnectionType: 'ConnectedServiceNameARM' # Optional. Options: connectedServiceName, connectedServiceNameARM
    #azureClassicSubscription: # Required when azureConnectionType == ConnectedServiceName
    #azureSubscription: # Required when azureConnectionType == ConnectedServiceNameARM
    #authenticationType: 'server' # Options: server, aadAuthenticationPassword, aadAuthenticationIntegrated, connectionString
    #serverName: # Required when authenticationType == Server || AuthenticationType == AadAuthenticationPassword || AuthenticationType == AadAuthenticationIntegrated
    #databaseName: # Required when authenticationType == Server || AuthenticationType == AadAuthenticationPassword || AuthenticationType == AadAuthenticationIntegrated
    #sqlUsername: # Required when authenticationType == Server
    #sqlPassword: # Required when authenticationType == Server
    #aadSqlUsername: # Required when authenticationType == AadAuthenticationPassword
    #aadSqlPassword: # Required when authenticationType == AadAuthenticationPassword
    #connectionString: # Required when authenticationType == ConnectionString
    #deployType: 'DacpacTask' # Options: dacpacTask, sqlTask, inlineSqlTask
    #deploymentAction: 'Publish' # Required when deployType == DacpacTask. Options: publish, extract, export, import, script, driftReport, deployReport
    #dacpacFile: # Required when deploymentAction == Publish || DeploymentAction == Script || DeploymentAction == DeployReport
    #bacpacFile: # Required when deploymentAction == Import
    #sqlFile: # Required when deployType == SqlTask
    #sqlInline: # Required when deployType == InlineSqlTask
    #publishProfile: # Optional
    #additionalArguments: # Optional
    #sqlAdditionalArguments: # Optional
    #inlineAdditionalArguments: # Optional
    #ipDetectionMethod: 'AutoDetect' # Options: autoDetect, iPAddressRange
    #startIpAddress: # Required when ipDetectionMethod == IPAddressRange
    #endIpAddress: # Required when ipDetectionMethod == IPAddressRange
    #deleteFirewallRule: true # Optional

Arguments

ArgumentDescription
Azure Connection Type(Optional)
Azure Classic Subscription(Required) Target Azure Classic subscription for deploying SQL files
Azure Subscription(Required) Target Azure Resource Manager subscription for deploying SQL files
Authentication Type(Required) Type of database authentication, can be SQL Server Authentication, Active Directory - Integrated, Active Directory - Password, or Connection String. Integrated authentication means that the agent will access the database using its current Active Directory account context.
Azure SQL Server(Required except when Authentication Type is Connection String) Azure SQL Server name, like Fabrikam.database.windows.net,1433 or Fabrikam.database.windows.net.
Database(Required) Name of the Azure SQL Database, where the files will be deployed.
Login(Required when Authentication Type is SQL Server Authentication or Active Directory - Password) Specify the Azure SQL Server administrator login or Active Directory user name.
Password(Required when Authentication Type is SQL Server Authentication or Active Directory - Password) Password for the Azure SQL Server administrator or Active Directory user.
It can accept variables defined in build/release pipelines as '$(passwordVariable)'.
You may mark the variable type as 'secret' to secure it.
Connection String(Required when Authentication Type is Connection String) The connection string, including authentication information, for the Azure SQL Server.
Deploy Type(Optional) Specify the type of artifact, SQL DACPAC file, SQL Script file, or Inline SQL Script.
DACPAC File(Required when Deploy Type is SQL DACPAC file) Location of the DACPAC file on the automation agent or on a UNC path accessible to the automation agent like, \BudgetIT\Web\Deploy\FabrikamDB.dacpac. Predefined system variables like, $(agent.releaseDirectory) can also be used here.
SQL Script(Required when Deploy Type is SQL Script file) Location of the SQL script file on the automation agent or on a UNC path accessible to the automation agent like, \BudgetIT\Web\Deploy\FabrikamDB.sql. Predefined system variables like, $(agent.releaseDirectory) can also be used here.
Inline SQL Script(Required when Deploy Type is Inline SQL Script) Enter the SQL script to execute on the Database selected above.
Publish Profile(Optional) Publish profile provides fine-grained control over Azure SQL Database creation or upgrades. Specify the path to the publish profile XML file on the agent machine or a UNC share. If the publish profile contains secrets like credentials, upload it to the secure files library where it is securely stored with encryption. Then use the Download secure file task at the start of your pipeline to download it to the agent machine when the pipeline runs and delete it when the pipeline is complete. Predefined system variables like $(agent.buildDirectory) or $(agent.releaseDirectory) can also be used in this field.
Additional SqlPackage.exe Arguments(Optional) Additional SqlPackage.exe arguments that will be applied when deploying the Azure SQL Database, in case DACPAC option is selected like, /p:IgnoreAnsiNulls=True /p:IgnoreComments=True. These arguments will override the settings in the publish profile XML file (if provided).
Additional Invoke-Sqlcmd Arguments(Optional) Additional Invoke-Sqlcmd arguments that will be applied when executing the given SQL query on the Azure SQL Database like, -ConnectionTimeout 100 -OutputSqlErrors.
Additional Invoke-Sqlcmd Arguments(Optional) Additional Invoke-Sqlcmd arguments that will be applied when executing the given SQL query on the Azure SQL Database like, -ConnectionTimeout 100 -OutputSqlErrors
Specify Firewall Rules Using(Required) For the task to run, the IP Address of the automation agent has to be added to the 'Allowed IP Addresses' in the Azure SQL Server's Firewall. Select auto-detect to automatically add firewall exception for range of possible IP Address of automation agent or specify the range explicitly.
Start IP Address(Required) The starting IP Address of the automation agent machine pool like 196.21.30.50.
End IP Address(Required) The ending IP Address of the automation agent machine pool like 196.21.30.65.
Delete Rule After Task Ends(Optional) If selected, then after the task ends, the IP Addresses specified here are deleted from the 'Allowed IP Addresses' list of the Azure SQL Server's Firewall.
Control options

Open source

This task is open source on GitHub. Feedback and contributions are welcome.