Tuesday, 22 March 2016

Deploy dacpac with MSDeploy in VS Release Management

To deploy a dacpac against a target database, MSDeploy can be used. In powershell, following command allows to deploy a dacpac.
Invoke-Expression "& 'WebDeployFolderPath\msdeploy.exe' --% -verb:sync -source:dbDacFx=`'SourceDACPAC`' -dest:dbDacFx=`'DestinationDBConnection`',commandTimeout=100"  -Verbose -ErrorAction "Stop"
Example
.\DeployDacpacMSDeploy.ps1 -WebDeployFolder "C:\Program Files (x86)\IIS\Microsoft Web Deploy V3" -SourceDACPAC "C:\temp\MyDb.dacpac" -DestinationDBConnection "Server=tcp:qa-db.database.windows.net,1433;Database=mydb;User ID=dbadmin@qa-db;Password=pwd;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30" -CommandTimeout 100
A poweshell script can be developed, to use in a Release Management tool.image
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
param(
    [Parameter(mandatory=$true)]
    [string]$WebDeployFolder,
    [Parameter(mandatory=$true)]
    [string]$SourceDACPAC,
    [Parameter(mandatory=$true)]
    [string]$DestinationDBConnection,
    [Parameter(mandatory=$true)]
    [string]$CommandTimeout
  )

$ErrorActionPreference = "Stop"

Invoke-Expression "& '$WebDeployFolder\msdeploy.exe' --% -verb:sync -source:dbDacFx=`'$SourceDACPAC`' -dest:dbDacFx=`'$DestinationDBConnection`',commandTimeout=$CommandTimeout"  -Verbose -ErrorAction "Stop" | Out-Host

if ($lastexitcode -ne 0) 
    {
        throw "Dacpac deploy failed."
    }
Using this poweshell script a tool in RM can be created as shown below.01
Using the tool, create an action component.02
This component can be used in a release template.03
Database updates can be pushed to Azure SQL or any other target SQL server.04

1 comment:

Chaminda Chandrasekara said...

Resolving ERROR_EXECUTING_METHOD with the tool is explained here .