This article aims at providing guide lines to automate Databases release to SQL Server using MS Release Management tool and Data-tier applications in Visual Studio. If you are not familiar with Release Management, you can make use of my article to configure the RM environment.
Database integration is a main headache in continuous integration of Software Applications. Integrating Databases manually is very painful and required lot of effort to keep track of the schema changes, updating scripts etc. Release Management for Visual Studio 2013 is a very useful tool to achieve continuous integration of applications.
DAC is an entity, which defines all SQL Server DB object like tables, views etc. Database developer can use 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 below article url for a better understanding on DAC.
Few installations need to be done/verified on the target DB server before going forward with DACPAC deployment through RM Client.
- MS Deployment agent should be installed, configured and its identity should have access to 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:WindowsMicrosoft.NETassemblyGAC_MSILMicrosoft.SqlServer.TransactSql.ScriptDomv4.0_18.104.22.168__89845dcd8080cc91Microsoft.SqlServer.TransactSql.ScriptDom.dll
Main activities to deploy a DACPAC file to SQL Server database instance can be categorised in to two.
- Create SQL Server Data Tools database project
- Create Component & Release Template in RM Client
- Trigger the Release
Create SQL Server Data Tools database project
Here you are going to create a DB project for an SQL Server DB which is 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.
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.
DB snap shot should be created and this action will provide a file with .dacpac extension. Right click on the project and create DB snap shot as shown below.
“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 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 MS Deployment Agent(Provide read permission for the account running MS Deployment Agent).
2. Create Component & 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.
2.1. Create Component
Open RM client and go to Components under Configure Apps tab. Click on new button. Provide component name and select Build Externally option. Input folder of the dacpac file should be provided here. See the below screen for the clarity.
Select Deployment tab, choose DACPAC Database Deployer(red marked below) and click save & Close.
2.1 Create Release Template
Create a new Release template, provide name and select release path as shown below. Since our build source is a shared location, no need to select TFS build definition here.
We need to use the component created in 2.1 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 Link button which is shown below.
As you may know, first we need to drop destination server in to the Release Template. You can drag and drop it from the left panel.
Now the component should be dropped in the server box. Double click on the component box and provide the parameters, which is required.
3. Trigger the Release
Create a new Release, provide name and select newly created Release Template here. Click on Start button and trigger the release.
You can see the status of the release as shown in the below. 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 while creating the release template. Check the DB schema and verify the release status.
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 different environments. MS Release Management tool help us to make use of the DACPAC in a very effitient way to relase DB and this can be done along with application release as well.