DotNetCurry Logo

Deploy DACPAC to SQL Server on Azure VM using VSTS

Posted by: Subodh Sohoni , on 8/21/2016, in Category Visual Studio, VSTS & TFS
Views: 13475
Abstract: Use VSTS to create a DACPAC and deploy it on a VM in Azure Resource Group to make it immediately available to the application

In the last 15 years, with Internet and Mobile computing becoming popular, a number of web applications have grown exponentially. Many of these applications store their data in SQL Server that is deployed on or along with the web server that hosts the application. These machines are located at a remote location. Similar remote environments also exist for System Integration Testing, and usually, that means SQL Server is not available to developers for making frequent changes.

 

What does the development team want as part of database programming?

Developers would like to frequently test changes in database schema and programs like stored procedure, on these remote servers. What they need is to deploy these changes on remote machines as soon as they are checked-in to the source control. We need to trigger a build that will create a package of database deployment. This package will be deployed on the remote database server so that changes made in database schema will be implemented, and the developers and testers can access those in the shortest possible time. It will be interesting if we can use Visual Studio Team Services (VSTS) to create such a package and deploy it on a VM that is in a resource group created using Azure Resource Manager (ARM).

Components of the solution

To bring this desired scenario to reality, we will require a solution with the following components:

1. Database Project that is included with SQL Server Data Tools (SSDT)

2. VSTS Account to add the database project to source control

3. Build definition to create a DACPAC and put it in an artifact

4. Release definition to copy the DACPAC on the target VM and then execute that DACPAC to implement the database changes.

Database Project in Visual Studio 2015

Let us begin with creating a database project. The project template for this task is SQL Server Database Project.

database-project-vs2015

Figure 1: Create a Database Project in Visual Studio 2015

I have retained the name of our database project as suggested by the wizard, Database1. You may go ahead and give a more appropriate context sensitive name if you do want to. We can now add the tables, stored procedures and other schema items in the designer that is provided in the Database Project.

create-table-database-project

Figure 2: Create table and stored procedure in the database project

stored-procedure-database-project

Figure 3: Stored procedure in database project

The next important task is to set the Target Platform, which is the version of SQL Server on which this database is to be implemented. This information will be stored in the package that will be created in the build. The package has the name as project name with extension “.dacpac”.

target-platform-dacpac-properties

Figure 4: Set the target platform and DACPAC properties

dacpac-local-build

Figure 5: DACPAC created by local build

VSTS for Source Control of Database Project

Now we can check-in this project to source control. We are going to use Visual Studio Team Services (VSTS) for our source control. If we do not have an existing account, we can create a new account on http://www.visualstudio.com. If a new team project is needed, then that also gets created. I already have created a team project named RMDemo under my account.

checkin-database-project-source-control-vsts

Figure 6: Checking in the database project to source control in VSTS

Build the Database Project on VSTS

We will now create a build definition that will create the DACPAC package for us. This build definition must have a task for “Build Solution” that will compile and package the database project in a DACPAC file and other projects in their appropriate form. It should also have a task to publish that DACPAC in an artifact on the hosted build agent. As you will observe, the value of Contents parameter is “**\*.dacpac”. These settings will ensure that any file that has extension .dacpac and is in any folder under the Build Directory on hosted agent will be published in the artifact named “DACPAC”. That name is for convenience only, and you can name the artifact as per your convenience.

publish-dacpac-build-artifact-vsts

Figure 7: Publish the DACPAC in the build artifact on VSTS

We can trigger this build to create the Database1.dacpac and publish it in the artifact named DACPAC. After the build is triggered and completed, we can view the file in the Artifacts Explorer, which can be opened by clicking on the Artifacts tab.

dacpac-file-build-artifact

Figure 8: DACPAC file under the build artifact

 

Release and Deploy the DACPAC using Release Management

Now, we will create a release definition that will deploy the Database1.dacpac on a target VM. We are targeting a VM in Azure Resource Group created using Azure Resource Manager (ARM). Our earlier article Continuous Integration and Deployment of a Web application from Visual Studio Team Services to an Azure VM explains how we can link our Azure account with the VSTS account by creating Azure Service Endpoint (SPN).

As a first step in the release process, we will need to select the Azure Resource Group. In the task, we will use the created SPN. In the next task, we need to copy the Database1.dacpac file from the DACPAC artifact which is on the hosted build agent to a folder on the target virtual machine. We will use the Azure VMs File Copy task for doing that.

azure-vms-file-copy-task

Figure 9: Copy DACPAC to target server using Release Management Azure VMs File Copy Task

You will observe that we have used a variable named “SSPwd” for password. Value of that variable can be given in the variables section of the global properties of the release. By clicking on the lock icon on the right of the textbox, we can make that value appears as password character (*).

variable-hidden-value-release

Figure 10: Set a variable with the hidden value in Release Management

After the Database1.dacpac file is copied, we need to execute that on the target machine. Microsoft has created a release management task to run the DACPAC on the target machine. We can download and install it from Visual Studio Marketplace. To connect to the marketplace you can use the Marketplace icon that is in the top right corner of your VSTS page.

azure-marketplace-link

Figure 11: Locate the Marketplace icon

Microsoft has published a set of tasks for IIS Web Deployment using WinRM that contains the task of using DACPAC to install SQL Database on a VM in Azure. Search and get those set of tasks by searching for “IIS Web App Deployment”.

rm-tasks-winrm

Figure 12: Set of tasks that use WinRM

As a part of that process, you will need to confirm the account for which you need to save those set of tasks.

confirm-linking-vsts-winrm-tasks

Figure 13: Account Confirmation for WinRM Tasks

The wizard will take you through three steps to complete this confirmation process. Once it is installed, we can add that to our release definition. You will find it as the task named as “WinRM – SQL Server DB deployment”

winrm-sqldb-deployment-task

Figure 14: SQL Server DACPAC Deployment Task in Release Management

The first parameter it requires is the Fully Qualified Domain Name (FQDN) of the target machine. One issue that we will face is that when this task, during execution, will try to run a built-in PowerShell script on that remote machine using WinRM, it will try to connect to that machine using the given FQDN. However, WinRM by default on that machine runs with the certificate that contains the IP address of the machine, instead of FQDN. This will throw an exception and abort the deployment. I have already written a workaround for this issue in the article http://www.dotnetcurry.com/windows-azure/1289/configure-winrm-execute-powershell-remote-azure-with-arm.

For the parameter DACPAC File, we shall provide the local path on the target machine where we have copied the Database1.dacpac. It should be same as the destination folder property of the earlier task (Azure VMs File Copy task). In our case, that folder was “C:\DACPAC”, so the value of parameter DACPAC file in our case will be “C:\DACPAC\Database1.dacpac”. Server name and Database name parameters will be as shown in the figure 15.

parameters-dacpac-deployment-task

Figure 15: Parameter details for Deploy SQL DACPAC task in Release Management

It is now possible to create this release and deploy it immediately. After the deployment, we can connect to the VM, start SQL Server Management Studio on it and check that the deployment has succeeded. It will create the database, table, and the stored procedure as we had configured in our database project.

dacpac-database-created

Figure 16: Database created on target server by DACPAC run by Release Management

Automate the build and release using CICD triggers

If we want the process of build, release creation and database deployment to be automated on the event of check-in into the database project, we can set the trigger of “Continuous Integration” to the build we created and of “Continuous Deployment” to the release. Process of setting those is described in an article we had published earlier - Continuous Integration and Deployment of a Web application from Visual Studio Team Services to an Azure VM http://www.dotnetcurry.com/visualstudio/1276/continuous-integration-deployment-from-vsts-azure-vm .

Now a developer from our team can “Get Latest” the database project, make changes in the database schema and check-in those changes. The project will get built on VSTS, and the dacpac will get recreated. As soon as the build is over, the release will be created, and then deployment will take place in the first environment (may be a testing environment). The Developer can check the validity of the changes and then signal to testers to start testing on the new database.

If we have multiple environments and the name of the database server is going to be different in each environment, then the application also needs to have a contextual connection string to it. You can find how to deploy with different connections strings in an article at Microsoft Release Management – Deploy to multiple environments with appropriate configurations (https://blogs.msdn.microsoft.com/mvpawardprogram/2016/05/17/microsoft-release-management-deploy-to-multiple-environments-with-appropriate-configurations/ )

Summary

Creating and deploying a database automatically, is a frequently needed task for database developers. In this article, we have seen how the development team can use Database Project to create or change the database schema and then use VSTS to create a DACPAC and further deploy it on a VM in Azure Resource Group so that it is immediately available to the application that the database supports.

Was this article worth reading? Share it with fellow developers too. Thanks!
Share on LinkedIn
Share on Google+
Further Reading - Articles You May Like!
Author
Subodh is a consultant and corporate trainer. He has overall 28+ years of experience. His specialization is Application Lifecycle Management and Team Foundation Server. He is Microsoft MVP – VS ALM, MCSD – ALM and MCT. He has conducted more than 300 corporate trainings and consulting assignments. He is also a Professional SCRUM Master. He guides teams to become Agile and implement SCRUM. Subodh is authorized by Microsoft to do ALM Assessments on behalf of Microsoft. Follow him on twitter @subodhsohoni


Page copy protected against web site content infringement 	by Copyscape




Feedback - Leave us some adulation, criticism and everything in between!