Haroon Ashraf
SQL Server Dacpac extension showing up in the search box in Extensions in Azure Data Studio

How to use SQL Server DACPAC extensions in Azure Data Studio

April 29, 2021 by

This article is focused on installing and using SQL Server DACPAC Extension in Azure Data Studio.

Additionally, the readers of this article are going to get a conceptual understanding of this extension along with its implementation in the light of a professional life scenario. At the end of the article, you will also find some handy tips about the approach supported by this extension.

About SQL Server DACPAC Extension

It is always good to first get familiar with this extension before we start installing and using it.

What is SQL Server DACPAC Extension?

This extension helps in database import and export operations primarily built for managing data-tier applications.

What is a data-tier application (DAC)?

According to Microsoft documentation, a data-tier application is a logical database management entity that defines all the SQL Server objects like tables, views associated with a user’s database.

What is a DACPAC?

A DACPAC is a data-tier application package in the form of a windows file containing all the database structure into one unit.

What is the purpose of DACPAC?

DACPAC helps developers and DBAs to package their database into a single unit to be either handed over to the team responsible for deploying the database to target environments in a manual or automated fashion.

Are there any requirements for standard data-tier application management via DACPAC?

The database must be registered as a data-tier application to be managed via standard DACPAC deployments in a commercial development environment.

In simple words, we can use this extension in Azure Data Studio to manage specific database deployments strategy (through DACPAC packages) that is going to help us to simplify the DLM (Database Lifecycle Management).

Installing SQL Server DACPAC Extension

In order to use the extension, we have to install it first, so let us do that. Please open Azure Data Studio and switch to Extensions.

As soon as you type “SQL server dacpac” in the search box you are going to see the desired extension appearing in the search box:

SQL Server Dacpac extension showing up in the search box in Extensions in Azure Data Studio

Please click the Install button to begin the installation. A successful installation is seen below:

Extension successfully installed in Azure Data Studio

Now, switch to the Connections and connect to your local/remote SQL Server instance.

Once connected right click on the connected Server to bring up the context menu. At the bottom of the context menu you should be able to locate a new option “Data-tier Application wizard”:

Data-tier Application wizard context menu option

We are good to go as the next step is to use the extension to see how it works particularly in the context of database deployments.

Using SQL Server DACPAC Extension

Let us understand the use of this handy extension in the light of a professional life scenario.

Scenario: Creating Dev Database from Production Database

We must create a fresh dev database complying with the most recent production database structure.

This is not a very common scenario for a database developer or DBA or even for a database infrastructure engineer who has a production database but wants to set up a dev database for the developers to work on.

There may be many reasons for this (having a production database without a dev database) and it is fine to have such a situation although in a typical database development scenario we normally do not expect a dev database without a production database.

DACPAC extension can help us to solve this problem (of creating a fresh dev database) in a very safe and sound manner without any external interference.

Replicating Production Database

The first step in this regard is to replicate the production database.

Create a sample database called BooksSample_Prod in Azure Data Studio by running the following script against the master database while connected to the local/remote SQL Server instance in Azure Data Studio:

Now refresh the Database node in the Connection pane and create a new table called Book in the database followed by adding some rows in it by running the following query:

Please refresh the Databases node in the Connection pane to see the newly created database.

Production database sample created

Quick Data Check

Let us view the rows in the Book table of the sample database we created to replicate the production database by running the following script against it:

The output is as follows:

Book table rows in the Production sample database

Extracting and Preserving DACPAC of the Production Database

Now once we have the production database sample, the next step is to extract and preserve the DACPAC of this database and this is where the DACPAC extension comes into action.

Right-click on SQLSampleBooks_Prod and click Data-tier Application wizard:

Opening Data-tier Application wizard in Azure Data Studio

Now select the second option in the next which says Extract a data-tier application from an instance of SQL Server to a DACPAC file followed by clicking Next:

Selecting the option to extract a DACPAC file of the production database

Select the location where you would like to preserve (save) the DACPAC file and click Next:

Selecting location of the DACPAC file

Do remember this location is vital and in a professional life scenario, it can be a shared storage accessible by all the team. One very important point to be mentioned here that since DACPAC only contains the structure of the database so there is no concern of data privacy as no data will be transferred to the dev environment through this.

In the final step, click on the Extract button:

Clicking Extract button in Azure Data Studio

In less than a minute or so the database DACPAC file is ready, saved in the desired location.

DACPAC file successfully created

Deploying Production Database DACPAC to Create Dev Database

Let us now create a dev database from the production database using the DACPAC file that contains the structure of the database.

Click the Connections icon on the left navigation bar (if you have not already opened it) and right-click on the Databases folder followed by clicking Data-tier Application wizard:

Opening Data-tier Application wizard

Next, select the first option that lets you deploy a data-tier application to an instance of SQL Server and press the Next button:

Selecting the option to deploy DACPAC file to a target database

In the next step, select the location where you stored the DACPAC file and choose the New Database option next Target Database followed by naming the database as SQLSampleBooks_Dev and clicking Next:

Creating a new database (dev) SQLSampleBooks_Dev based on DACPAC file saved earlier

In the final step, click Deploy:

Final step to deploy changes

Refresh the Databases node once again to see the newly created dev database:

dev database successfully created in Azure Data Studio

Let us view the Book table in the dev database by running the following script against the SQLSampleBooks_Dev database:

The results are as follows:

Checking dev database structure which matches with production database

Congratulations, you have successfully created a dev database from the production database using SQL Server DAPCAC extension in Azure Data Studio.

A Word of Advice

It is very important to understand the implications (likely outcomes) of your chosen approach before implementing it on your production server/system. However, the risk of accidental data disclosure when using DACPAC extension to extract the structure of the database is nearly none.

In other words, the data privacy concerns should not be an issue when using the DACPAC approach and that makes it a very safe and sound method to create a similar database for different environments.

For example, you can create the following databases from the production database using this extension:

  1. Dev database
  2. Test database
  3. QA database

One of the possible use cases of creating dev from production database is when your development team has just got hold of a production database that was previously developed using an approach that does not comply with your teams’ current tools and technologies and as a result of that, they decide to bring on their development version and continue from there.

Another use case is when you are creating a developer sandbox environment by replicating the production database in your local dev environment without needing the traditional development database historical changes made over the time by different developers saved in source control.

This extension can also be used in conjunction with the DB Snapshot Creator extension to ensure that you have a rollback strategy if a pre-populated dev database gets undesirably overwritten by this approach.

Summary

To sum up, we created a sample production database to use as a source to build a dev database (from it) by using SQL Server DACPAC Extension in Azure Data Studio.

Table of contents

Two ways to build SQL Database Projects in Azure Data Studio
How to use SQL Server DACPAC extensions in Azure Data Studio
Declarative Database Development in Azure Data Studio
Using the DB Snapshot Creator Extension in Azure Data Studio
Migrating on-premises databases to Azure SQL Database in Azure Data Studio

Haroon Ashraf
Azure, Development, SQL Azure

About Haroon Ashraf

Haroon’s deep interest in logic and reasoning at an early age of his academic career paved his path to become a data professional. He holds BSc and MSc Degrees in Computer Science and also received the OPF merit award. He began his professional life as a computer programmer more than a decade ago, working on his first data venture to migrate and rewrite a public sector database driven examination system from IBM AS400 (DB2) to SQL Server 2000 using VB 6.0 and Classic ASP along with developing reports and archiving many years of data. His work and interest revolves around Database-Centric Architectures and his expertise include database and reports design, development, testing, implementation and migration along with Database Life Cycle Management (DLM). He has also received passing grade to earn DevOps for Databases verified certificate, an area in which he finds particular interest and potential. View all posts by Haroon Ashraf

3,549 Views