Deploy SQL Dacpac Machine Group
The Deploy SQL Dacpac Machine Group task is used to deploy SQL Server database to an existing SQL Server instance, and the underlying technologies used by the task are DACPAC and SqlPackage.exe. DACPACs and SqlPackage.exe provide fine-grained control over database creation and upgrades, including upgrades for schema, triggers, stored procedures, roles, users, extended properties etc. Using the task, around eighty different properties can be set to ensure that the database is created or upgraded properly like:
The task runs on the automation agent machine, and connects to the target machine(s) using [Windows Remote Management] (WinRM), and launches a bootstrapping executable program (VisualStudioRemoteDeployer.exe) on the target machine(s), and the bootstrap executable invokes the PowerShell scripts to locate the sqlpackage.exe on the machine, and creates or updates the SQL Server database using sqlpackage.exe. As the execution happens within the target machine(s), it is important to have the pre-requisites described below, installed properly on the target machine(s).
Please contact the alias RM_Customer_Queries at microsoft dot com, if you are facing problems in making this task work. Also, share feedback about the task, and the new features that you would like to see in it.
Pre-requisites for the task
The following pre-requisites need to be setup in the target machine(s) for the task to work properly.
There should be a SQL Server instance that is already installed and configured on the pre-existing machines or virtual machines. The task deploys SQL Server database but does not install or configure SQL Server.
SqlPackage.exe is used to create or upgrade the database and it is installed during the installation of SQL Server 2008 R2/2012/2014/2016. If the SQL Server Database deployment task is targeting these versions of SQL Server, then there is no need to install SqlPackage.exe separately. However, the latest version of SqlPackage.exe ships with SQL Server 2014, and is also available as a web download, and installs when the products listed below are installed on a machine. The latest version of SqlPackage.exe can target database deployments from SQL Server 2005 onwards and it is advisable to install that on the deployment machine. If the deployment of the SQL Server database is happening on the Web Server which is targeting a database on a separate Database Server that is behind firewall in a DMZ zone, then SqlPackage.exe needs to be installed on the Web Server:
The task PowerShell on Target Machines can be used to deploy SqlPackage.exe to Azure virtual machines or domain-joined/workgroup machines.
Specifying Machine Details in the Task
Specify the machine details, wiz. the IP Address or the FDQN, administrator's login, password, WimRM HTTP/HTTPS protocol, and Test Certificate in the task itself. The difference between using the domain-joined/workgroup on-premises physical or virtual machines and the Azure virtual machines is that copying files to them is done by separate tasks. The Windows Machine File Copy is used for the domain-joined/workgroup machines and the Azure File Copy is used for the Azure virtual machines. Note that the WinRM - SQL Server Database Deployment task expects the web application's package zip files to be available on the target machines or on a UNC path that is accessible by the target machine administrator's login.
Parameters of the task:
The parameters of the task are described in details, including examples, to show how to input the parameters. The parameters listed with a * are required parameters for the task:
Deploy SQL Server Database
This section of the task is used to deploy SQL Server Database to an existing SQL Server using sqlpackage.exe.
These arguments will override the settings in the Publish profile XML file (if provided). A full list of the arguments that can provided is listed in the ' Properties' sub-section of the ' Publish Parameters, Properties, and SQLCMD Variables' in the SqlPackage.exe documentation. The SQLCMD variables can be also specified here. This is an optional parameter.