SQL Deployment with ARM Templates including schema and table (dacpac, PowerShell)

Question

I want to deploy a Azure SQL Database with schema and table using PowerShell, Azure Resource Manager Templates and dacpac. How can I do that?

Answer 1

Prerequisites

• Azure Subscription
• Visual Studio with the workloads Azure-Development and Datastorage- and processing installed
• An Azure Key Vault available

Create dacpac package with the table

• Create a new SQL Server Database Project
• Add a new solution folder
• Add a new schema and table SQL script
• Adjust the target platform to Microsoft Azure SQL Platform V12 if necessary [2]
• Then build the solution -> this will create the dacpac package that can be used for the deployment (see .\bin\debug\xxx.dacpac)

Deploy SQL Server with database

• Create a new Azure Resource Group project
• Modify the parameter and template file
  • During the deployment a firewall rule is set to allow the deployment of the dacpac. Adjust the IP address accordingly respectively when working with VSTS follow the instructions here [1]
• Then the server, database, schema and table can be deployed to Azure

To consider

• For being able to use the dacpac package it was necessary to get the DLLs from the following source:
  • NuGet Microsoft.SqlServer.DacFx.x64
• When having issues while deploying the DACPAC package
  • Check firewall settings of the Azure SQL DB
  • Sometimes PowerShell seems to cache old values without overwriting them with new assigned values. When having a file not found error and you can't find the error try to restart the PowerShell environment

Resources

[1] Deploy Dacpac packages via power shell script to Azure SQL Server

[2] How to: Change Target Platform and Publish a Database Project