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.
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.
Figure 2: Create table and stored procedure in the 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”.
Figure 4: Set the target platform and DACPAC properties
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.
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.
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.
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.
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 (*).
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.
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”.
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.
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”
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 https://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.
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.
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 https://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.
This article has been editorially reviewed by Suprotim Agarwal.
C# and .NET have been around for a very long time, but their constant growth means there’s always more to learn.
We at DotNetCurry are very excited to announce The Absolutely Awesome Book on C# and .NET. This is a 500 pages concise technical eBook available in PDF, ePub (iPad), and Mobi (Kindle).
Organized around concepts, this Book aims to provide a concise, yet solid foundation in C# and .NET, covering C# 6.0, C# 7.0 and .NET Core, with chapters on the latest .NET Core 3.0, .NET Standard and C# 8.0 (final release) too. Use these concepts to deepen your existing knowledge of C# and .NET, to have a solid grasp of the latest in C# and .NET OR to crack your next .NET Interview.
Click here to Explore the Table of Contents or Download Sample Chapters!
Was this article worth reading? Share it with fellow developers too. Thanks!
Subodh is a Trainer and consultant on Azure DevOps and Scrum. He has an experience of over 33 years in team management, training, consulting, sales, production, software development and deployment. He is an engineer from Pune University and has done his post-graduation from IIT, Madras. He is a Microsoft Most Valuable Professional (MVP) - Developer Technologies (Azure DevOps), Microsoft Certified Trainer (MCT), Microsoft Certified Azure DevOps Engineer Expert, Professional Scrum Developer and Professional Scrum Master (II). He has conducted more than 300 corporate trainings on Microsoft technologies in India, USA, Malaysia, Australia, New Zealand, Singapore, UAE, Philippines and Sri Lanka. He has also completed over 50 consulting assignments - some of which included entire Azure DevOps implementation for the organizations.
He has authored more than 85 tutorials on Azure DevOps, Scrum, TFS and VS ALM which are published on
www.dotnetcurry.com.Subodh is a regular speaker at Microsoft events including Partner Leadership Conclave.You can connect with him on
LinkedIn .