Feedback Edit

Azure SQL Database Deployment task

  • Article
  • 5 minutes to read

Azure DevOps Services

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

Important

This task is supported only in a Windows environment. If you are trying to use Azure Active Directory (Azure AD) integrated authentication, you must create a private agent. Azure AD integrated authentication is not supported for hosted agents.

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, servicePrincipal
    #serverName: # Required when authenticationType == Server || AuthenticationType == AadAuthenticationPassword || AuthenticationType == AadAuthenticationIntegrated || AuthenticationType == servicePrincipal
    #databaseName: # Required when authenticationType == Server || AuthenticationType == AadAuthenticationPassword || AuthenticationType == AadAuthenticationIntegrated || AuthenticationType == servicePrincipal
    #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

Argument Description
ConnectedServiceNameSelector
Azure Connection Type		 (Optional) Argument alias: azureConnectionType
Default value: ConnectedServiceNameARM
ConnectedServiceName
Azure Classic Subscription		 (Required) Target Azure Classic subscription for deploying SQL files
Argument alias: azureClassicSubscription
ConnectedServiceNameARM
Azure Subscription		 (Required) Target Azure Resource Manager subscription for deploying SQL files
Argument alias: azureSubscription
AuthenticationType
Authentication Type		 (Required) Type of database authentication, can be SQL Server Authentication, Active Directory - Integrated, Active Directory - Password, Connection String, or Service Principal. Integrated authentication means that the agent will access the database using its current Active Directory account context.
Default value: server
ServerName
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.
DatabaseName
Database		 (Required) Name of the Azure SQL Database, where the files will be deployed.
SqlUsername
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.
SqlPassword
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.
ConnectionString
Connection String		 (Required when Authentication Type is Connection String) The connection string, including authentication information, for the Azure SQL Server.
TaskNameSelector
Deploy Type		 (Optional) Specify the type of artifact, SQL DACPAC file, SQL Script file, or Inline SQL Script.
Argument alias: deployType
Default value: DacpacTask
DeploymentAction
Action		 (Required) Choose one of the SQL Actions from the list.
  • Publish, Extract, Export, Import, Script, Drift Report, Deploy Report.
    For more details refer link​
    Default value: Publish
    		•
    DacpacFile
    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.
    BacpacFile
    BACPAC File    		 (Required) Location of the BACPAC file on the automation agent or on a UNC path accessible to the automation agent like \BudgetIT\Web\Deploy\FabrikamDB.bacpac. Predefined system variables like, $(agent.releaseDirectory) can also be used here
    SqlFile
    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.
    SqlInline
    Inline SQL Script    		 (Required when Deploy Type is Inline SQL Script) Enter the SQL script to execute on the Database selected above.
    PublishProfile
    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.
    AdditionalArguments
    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).
    SqlAdditionalArguments
    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
    InlineAdditionalArguments
    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
    IpDetectionMethod
    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.
    Default value: AutoDetect
    StartIpAddress
    Start IP Address    		 (Required) The starting IP Address of the automation agent machine pool like 196.21.30.50.
    EndIpAddress
    End IP Address    		 (Required) The ending IP Address of the automation agent machine pool like 196.21.30.65.
    DeleteFirewallRule
    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.

    Open source

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