Microsoft Release Management- Database Deployment Using DACPAC

Introduction

The purpse of this article is to provide guidelines for automating the release of databases to SQL Server using the Microsoft Release Management tool and Data-tier applications in Visual Studio. If you are not familiar with Release Management, you can use my article to configure the RM environment.

Background

Database integration is a big headache in continuous integration of Software Applications. Integrating Databases manually is very painful and requires a lot of effort to keep track of the schema changes, updating scripts and so on. Release Management for Visual Studio 2013 is a very useful tool to do continuous integration of applications.

Data-tier Applications

DAC is an entity that defines all SQL Server DB objects, like tables, views and so on. Database developers can use a SQL Server Data Tools database project to design a database in Visual Studio. A successful build of this project will generate a DACPAC file with .dacpac extension. Go through the following article URL for a better understanding of DAC.

http://msdn.microsoft.com/en-IN/library/ee210546.aspx

Prerequisites

A few installations need to be done/verified on the target DB server before proceeding with DACPAC deployment through RM Client.

• Microsoft Deployment agent should be installed, configured and its identity should have access to a SQL Server DB instance. The user must be a member of the dbmanager role or assigned CREATE DATABASE permissions to create a database, including creating a database by deploying a DAC package. The user must be a member of the dbmanager role.
  
• .Net 4.0 should be installed.
  
  
• Microsoft SQL Server 2012 Transact-SQL Script Dom should have been installed. You can verify the installation by looking for: C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.SqlServer.TransactSql.ScriptDom\v4.0_11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.TransactSql.ScriptDom.dll

DACPAC Deployment

The main activities to deploy a DACPAC file to a SQL Server database instance can be categorised into the following two:

1. Create SQL Server Data Tools database project
   
2. Create Component and Release Template in RM Client
   
3. Trigger the Release

1. Create SQL Server Data Tools database project
   
   Here you will create a DB project for a SQL Server DB already available in your development environment. Connect to the development DB instance through SQL Server Object Explorer in Visual Studio 2013. Right-click on the DB and choose "Create New Project" as shown below.
   
   
   
   Provide a name for the project, location to store and click Start.
   
   
   
   The import database progress will be shown in the next screen as shown below. Once done, click Finish.
   
   
   
   Your DB project will be created at this time and you can check it under Solution Explorer as shown below.
   
   
   
   The DB snap shot should be created and this action will provide a file with .dacpac extension. Right-click on the project and create a DB snapshot as shown below.
   
   
   
   A "Snapshots" folder will be created under the solution and I renamed it as DemoDBProject.dacpac file will be created under this folder.
   
   
   
   Here I am explaining the build externally method of RM component to pick the input package. You need to move this DemoDBProject.dacpac into a shared folder, which should be accessible by the Microsoft Deployment Agent (provide read permission for the account running Microsoft Deployment Agent).
   
   
2. Create Component and Release Template in RM Client
   
   You need to create a new component and release template to trigger a new release. Basic RM configurations should be done prior to this.
   
   
   1. Create Component
      
      Open the RM client and go to Components under the Configure Apps tab. Click on the new button. Provide the component name and select the Build Externally option. The Input folder of the dacpac file should be provided here. See the following screen for clarity.
      
      
      
      Select the Deployment tab, choose DACPAC Database Deployer (marked in Red below) and click Save and Close.
      
      
      
      
   2. Create Release Template
      
      Create a new Release template, provide a name and select the release path as shown below. Since our build source is a shared location, there is no need to select a TFS build definition here.
      
      
      
      We need to use the component which is already created in our release template. To add the component here, right-click on the components in the left panel as shown below and click on Add.
      
      
      
      From the components list, choose the newly created component for DACPAC deployment and click on the Link button as shown below.
      
      
      
      As you may know, first we need to drop the destination server in to the Release Template. You can drag and drop it from the left panel.
      
      
      
      Now the component should be dropped into the server box. Double-click on the component box and provide the parameters, which are required.
      
      
3. Trigger the Release
   
   Create a new Release, provide a name and select newly created Release Template here. Click on the Start button and trigger the release.
   
   
   
   You can see the status of the release as shown in the following. If the release is successful, the status will be Done as shown here.
   
   
   
   Connect to the target SQL Server DB instance and open the DB, both you have provided as parameters for the component when creating the release template. Check the DB schema and verify the release status.

Conclusion

DACPAC is a very useful utility for the DB deployment which avoids the headache to synchronise the DB schema against new enhancements and fixes of a software system in various environments. Microsoft Release Management tool help us to use DACPAC in a very efficient way to release a DB and this can be done along with the application release as well.

Thank you.