Product articles
SQL Toolbelt
Database Builds and Deployments
Comparing SQL Server Databases using…

Comparing SQL Server Databases using DacPacs and SQL Compare

How to compare SQL databases using DACPACs, script out differences and then run a database deployment task to synchronize the schema of the target database.

Guest post

This is a guest post from Feodor Georgiev. Feodor has worked with SQL Server for over 15 years, specializing in database architecture, Microsoft SQL Server data platform, data model design, database design, integration solutions, business intelligence, reporting, as well as performance optimization and systems scalability.

In the past 3 years, he has expanded his focus to coding in R for assignments relating to data analytics and data science. Outside of his day-to-day schedule, he blogs, shares tips on forums and writes articles on his Data Messenger blog.

I’ll show you how to use Data-Tier Application Packages (DacPacs), together with the Data-Tier Application Framework (DacFx), as an alternative way of comparing databases schemas, scripting out a database, and deploying database changes, in cases where SQL Compare can’t get direct access to the production database.

What is a DacPac?

A DacPac file is a binary file, a zipped directory, containing definitions of all the objects in a SQL Server database. It is very useful for sharing and migrating database definitions in a very compact way and is compatible with SQL Server Data Tools and Azure Data Studio.

Since a DacPac is just a zipped bundle of files, we can simply rename the extension to .zip to open the file and see what is in it. As you can see, among other things, it contains an XML ‘model’ of the database’s metadata (model.xml).

What is in a DacPac

Figure 1

Why compare databases using DacPacs?

Often during development work, especially at the proof-of-concept stage, you need a quick and easy way to save your current progress, for example by updating a set of object build scripts in a directory. You also need a way to compare your current development database or script directory, to a target production database, and generate a synchronization script that would align the two. My previous articles have shown several ways to achieve this, by comparing directly to the production database, using SQL Compare GUI or command line.

In certain environments, however, security regulations prohibit direct access to the production database, via tools like SQL Compare. In these cases, the database administrator will often use DacPacs, together with DacFx, as an alternative way of scripting out a database, and deploying database changes.

If the DBA can provide the latest DacPac for the production database, you can still use SQL Compare to compare and synchronize two DacPacs, and can also use SQL Compare command line, or DacFx, to extract a database build script from the DacPac.

How to create a DacPac

To create a DacPac, we use DacFx, which is already installed together with SQL Server, so all we need to do is right-click on the database and navigate Tasks | Extract Data-tier Application.

How to create a DacPac

Figure 2

This will start a wizard that will guide you through the process of creating the DacPac and saving it to disk.

Saving a DacPac

Figure 3

Let’s suppose that AdventureWorks2014 is the Production database, and that I need to compare it with my proof-of-concept database, called AdventureWorks2014PoC, and deploy changes from this to the production database.

To do that, we’ll use SQL Compare to synchronize the two DacPacs, so generate a DacPac for the POC database as described above.

How to unpack a DacPac

Since DacPacs are simply a zipped bundle of files, we need to unpack the DacPac before we can do the compare. To unpack each Dacpac, simply right-click on the file and select Unpack….

Unpack a DacPac

Figure 4

After unpacking both DacPacs, we get two folders (respectively for each database) which contain the object scripts.

SQL Server object scripts in a DacPac

Figure 5

How to compare two databases using DacPacs

To compare SQL databases using DacPacs, all we need to do is start SQL Compare, select the source and target script folders, and hit Compare now.

Compare DacPacs with SQL Compare

Figure 6

After this we can pick and choose which changes we want to include in the synchronization script, just as we would do with any other compare task using SQL Compare.

Summary

SQL Compare’s ability to compare DacPacs offers a very effective way to synchronize a database schema in two environments, if you don’t have direct access to the target. Therefore, it is especially useful in environments with restrictive security requirements.

Next step:

Automating DacPac deployments using the SQL Compare command line and PowerShell

Tools in this post

SQL Compare

Compare and synchronize SQL Server database schemas

Find out more

SQL Toolbelt

The industry-standard tools for SQL Server development and deployment.

Find out more