Microsoft Release Management- Database Deployment Using DACPAC

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.