Guest Blogger:
Feodor Georgiev

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.

Often during my development work, especially at the Proof-of-Concept (PoC) stage, I need a quick and easy way to save my current progress, for example, by updating a set of object build scripts in a directory. Ultimately, I also need a way to compare my current development database or script directory to a target production database, and generate a synchronization script to align the two.

My previous articles have shown several ways to achieve this, by comparing directly to the production database using the SQL Compare GUI or the 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 Data-Tier Application Packages (DacPacs), together with the Data-Tier Application Framework (DacFx), as an alternative way of scripting out a database, and deploying database changes.

Fortunately, as long as the DBA can provide me the latest DacPac for the production database, I can still do my work effectively, since I can use SQL Compare to compare and synchronize two DacPacs. This article will use the SQL Compare GUI only, and a subsequent article will demonstrate how to start automating DacPac synchronization from the SQL Compare command line, along with techniques to extract a build script from a DacPac.

What is a DacPac?

A DacPac file is a binary file, a zipped directory, containing definitions of objects in a SQL Server database. It is very useful for sharing and migrating database definitions in a very compact way.

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

Comparing DacPacs using SQL Compare 1

Figure 1

How to create a DacPac

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

Comparing DacPacs using SQL Compare 2

Figure 2

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

Comparing DacPacs using SQL Compare 3

Figure 3

Let’s suppose AdventureWorks2014 is the Production database, and I need to compare it with my PoC 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, and generate a DacPac for the PoC database, in the same manner 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….

Comparing DacPacs using SQL Compare 4

Figure 4

After unpacking both DacPacs, we get two folders (one for each database) containing the object scripts.

Comparing DacPacs using SQL Compare 5

Figure 5

How to compare two DacPacs

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

Comparing DacPacs using SQL Compare 6

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. It is especially useful in environments with restrictive security requirements.

If you’d like to read more articles by Feodor Georgiev, you can find him on Simple Talk, and at sqlconcept.com.

Tools in this post

SQL Compare

Compare SQL Server schemas and deploy differences fast.

Find out more

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

Related posts

Also in Blog

Masking your on-premises database with SQL Data Mask

Things move fast in Foundry, Redgate’s research and development division. In our last update three weeks ago we announced our intention to build a version of SQL Data Mask that would mask on-premise...

Also in Redgate products

SQL Backup Pro – why we’re going backwards to let our customers go forwards

SQL Backup Pro is one of those tools from Redgate that just works. Everyone needs to back up their SQL Server databases, and SQL Backup Pro lets people do it across different and multiple versions of ...

Also about SQL Compare

How to deploy a database plus static data using SQL Compare and SQL Data Compare

We can use the SQL Compare Pro command line with PowerShell to automate the process of creating a new build script for a version of the database, from the object scripts in the VCS. However, what if w...