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.
Main activities to deploy a DACPAC file to SQL Server database instance can be categorised in to two.
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.