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:
Please click the Install button to begin the installation. A successful installation is seen below:
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”:
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
-- Create a new database called 'SQLSampleBooks_Prod' -- Connect to the 'master' database to run this snippet USE master GO -- Create the new database if it does not exist already IF NOT EXISTS ( SELECT [name] FROM sys.databases WHERE [name] = N'SQLSampleBooks_Prod' ) CREATE DATABASE SQLSampleBooks_Prod GO |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
USE SQLSampleBooks_Prod -- Create a new table called '[Book]' in schema '[dbo]' -- Drop the table if it already exists IF OBJECT_ID('[dbo].[Book]', 'U') IS NOT NULL DROP TABLE [dbo].[Book] GO -- Create the table in the specified schema CREATE TABLE [dbo].[Book] ( [BookId] INT NOT NULL PRIMARY KEY, -- Primary Key column [Title] NVARCHAR(50) NOT NULL, [Author] NVARCHAR(50) NOT NULL ); GO -- Insert rows into table 'Book' in schema '[dbo]' INSERT INTO [dbo].[Book] ( -- Columns to insert data into BookId,Title,Author ) VALUES ( -- First row: values for the columns in the list above 1,N'Learn SQL from Experts','Asif' ), ( -- First row: values for the columns in the list above 2,N'How to build a database from a database','Peter' ), ( -- First row: values for the columns in the list above 3,N'Test Driven Database Development for Beginners','Sarah' ) GO |
Please refresh the Databases node in the Connection pane to see the newly created database.
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:
|
1 2 3 4 5 |
-- View Book table (dev database) SELECT B.[BookId] ,B.[Title] ,B.[Author] FROM [dbo].[Book] B |
The output is as follows:
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:
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:
Select the location where you would like to preserve (save) the DACPAC file and click Next:
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:
In less than a minute or so the database DACPAC file is ready, saved in the desired location.
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:
Next, select the first option that lets you deploy a data-tier application to an instance of SQL Server and press the Next button:
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:
In the final step, click Deploy:
Refresh the Databases node once again to see the newly created dev database:
Let us view the Book table in the dev database by running the following script against the SQLSampleBooks_Dev database:
|
1 2 3 4 5 |
-- View Book table (dev database) SELECT B.[BookId] ,B.[Title] ,B.[Author] FROM [dbo].[Book] B |
The results are as follows:
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:
- Dev database
- Test database
- 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 |
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
- How to create and query the Python PostgreSQL database - August 15, 2024
- SQL Machine Learning in simple words - May 15, 2023
- MySQL Cluster in simple words - February 23, 2023