Skip to content

Azure SQL Database Incremental Deployment

Jump to bottom
Adrien Siffermann edited this page Aug 22, 2016 · 6 revisions

Icon

With this task, you can deploy an Azure SQL Database using multiple DACPAC and performing incremental deployments based on current Data-Tier Application version, using an Azure Resource Manager Service Endpoint.

It will :

  1. Take a list of DACPAC files as an input, and read their version numbers by reading the DacMetadata.xml file inside
  2. Get the version of the target Azure SQL Database by executing select type_version from master.dbo.sysdac_instances on the SQL Server (or default to 0.0.0.0)
  3. Deploy all DACPAC files, ordered by their version number, with version greater than the current database version using SQLPackage.exe. It will automatically add the /p:RegisterDataTierApplication=True to register the new version on the target server.

Requirements

You should have Setup an Azure Resource Manager Endpoint to use this task.

Your Build Agent should have the SQLPS capability, so you have to Install SQLPS on your Build Agent.

The task will automatically find the installation path of SQLPackage.exe (installed with SQL Server, or DAC Framework, or Visual Studio).

Add the task to your Build or Release Definition

Add-Tasks

You will find the task in the "Deploy" category.

Configuration

Configure-ExecuteSql

General

  • Azure RM Subscription - Specifies the Azure Resource Manager Service Endpoint to use.
  • DACPAC Files - Location of the DACPAC files on the automation agent or on a UNC path accessible to the automation agent like, \\BudgetIT\Web\Deploy*.dacpac. Predefined system variables like, $(Agent.ReleaseDirectory) can be also used here.

Target

  • Azure SQL Server Name - Azure SQL Server name like, FabrikamSQL.database.windows.net,1433 or FabrikamSQL.database.windows.net.
  • Database Name - Name of the Azure SQL Database.
  • Login - The Azure SQL Server administrator login (the login must have access to the master database of the target server).
  • Password - Password for the Azure SQL Server login provided. It can accept variable defined in Build / Release Definitions as $(PasswordVariable). You may mark variable type as "secret" to secure it.
  • Publish Profile - Publish profile provides fine-grained control over Azure SQL Database creation or upgrades. Specify the path to the Publish profile XML file on the automation agent or on a UNC share. Predefined system variables like, $(Agent.BuildDirectory) or $(Agent.ReleaseDirectory) can be also used here.
  • Additional SqlPackage.exe Arguments - Additional SqlPackage.exe arguments that will be applied when creating or updating the Azure SQL Database like, /p:DropObjectsNotInSource=True /p:DoNotDropObjectTypes=users /p:ExcludeObjectTypes=RoleMembership;users /p:BlockOnPossibleDataLoss=False.​

Firewall

  • Specify Firewall Rules Using - For the script to execute, the IP Address of the automation agent has to be added to the 'Allowed IP Addresses' in the Azure SQL Server's Firewall. Provide the IP Address range of the automation agents, or select auto-detect (it will find the external IP of your agent).
  • Start IP Address - If you choose 'Allowed IP Addresses', the starting IP Address of the automation agent machine pool like 196.21.30.50.
  • End IP Address - If you choose 'Allowed IP Addresses', the ending IP Address of the automation agent machine pool like 196.21.30.65.
  • Delete Rule After Task Ends - 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.
Clone this wiki locally