Product articles SQL Compare SQL Compare
Automating DacPac deployments using the…

9 February 2017

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.

9 February 2017

Automating DacPac deployments using the SQL Compare command line and PowerShell

How to use PowerShell and the SQL Compare command line to automate the generation of a deployment script, when comparing to a DacPac.

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.

My previous article, How to compare two DacPacs using SQL Compare, described how to use the SQL Compare GUI to compare two DacPacs, and generate a synchronization script. This article takes it one step further, showing how to use PowerShell and the SQL Compare command line to automatically generate a deployment script, to build or upgrade a database, starting from a DacPac.

As a DBA, you can easily be given a DacPac by a development team, encapsulating the latest database changes, which they wish you to release to a production database. This is fine, but you’re probably accustomed to generating your deployment scripts using SQL Compare, inspecting the script, tweaking it, trying it out on staging, running tests to ensure that any problems you get will be subtle or unusual. You may need to run it past other people to get buy-in, such as the Technical Architects, or Governance, for compliance purposes.

In short, you need to generate a deployment script from a DacPac. Fortunately, all you need to do is to unpack the DacPac and then compare it to an empty database, or even an empty file, to generate a build script, or to the current production database, to generate an upgrade script.

Create the DacPac

The first thing we need to do is to download and install the latest version of SQL Server Data Tools (SSDT) in order to get the command-line file sqlpackage.exe. In this case, I’m using DACFx 17.0.

Having done that, we can create a DacPac. As described in my previous article, a DacPac file is simply a binary file, a zipped directory, containing definitions of objects in a SQL Server database.

We can rename the extension to .zip to open the file and view the contents. Significantly, among other things, it contains an XML ‘model’ of the database’s metadata (model.xml).

Listing 1 shows the PowerShell script to extract a DacPac of AdventureWorks2014. If you already have an example DacPac, then you can leave out this stage.

Listing 1: Extract a DacPac from the AdventureWorks database

In Listing 1, make sure you have the correct server name, database name and path to the sqlpackage file. Also, keep in mind that the DacPac will be saved in the AppData folder of the current user; the PowerShell output will remind you of the exact location, like this:

Connecting to database 'AdventureWorks2014' on server …..
Extracting schema
Extracting schema from database
Resolving references in schema model
Successfully extracted database and saved it to file 'C:\Users\UserName\AppData\Local\Temp\AdventureWorks2014.DacPac'.

Unpack the DacPac

Now we have a DacPac we can use, we’ll unpack it so that we can get at the model.sql file that is created as part of the unpacking process.

Listing 2: Unpack the AdventureWorks2014 DacPac

If you take a look at the contents of the model.sql file (in my case, created in the folder “C:\Users\Bob\AppData\Local\Temp\unpacked“), it looks rather like a plain build script:

plain-build-script

Figure 1

However, if you attempt to run the script, you’ll see all kinds of errors since it doesn’t have the objects in the correct dependency order.

So while the model.sql file does contain the schema definition of our database, we need to use SQL Compare to turn it into a runnable deployment script.

Generating a database build script from a DacPac

In Listing 3, we first create a blank (empty) .sql file, called DummyDest.sql, and then get SQL Compare to compare this dummy file to our model.sql file.

It will cheerfully do this, and produce a synchronization script to make the database represented by the blank file into the database represented by the build script, but with all the transaction rollback on error and other things you would expect from a good deployment script.

In short, what we end up with in the ReleaseCandidate.sql file is a really good build script.

Listing 3: Create a build script from a DacPac by comparing to an empty file

If you’re unsure where to find one of the files produced by this script, just highlight the appropriate path, such as “/scriptFile:’$FolderName\$MySourceScript.sql'” (including the quotes, and then execute just that section in PowerShell.

PS Z:\> "/scriptFile:'$FolderName\$MySourceScript.sql'"
/scriptFile:'C:\Users\Username\AppData\Local\Temp\ReleaseCandidate.sql'
PS Z:\>

Generating a database upgrade script from a DacPac

It isn’t that often you’ll be given a DacPac and asked to create a new database. More often, you’ll be asked to make changes to an existing database. It turns out that this is even easier.

If you can access the production server, then simply provide the name of the instance and database instead of the file directory "/scr2:$DestFolderName, as shown in Listing 4.

Listing 4: Create an upgrade script from a DacPac by comparing to a target database

Alternatively, you can compare the DacPac to a build script for the production database, simply by comparing the blank dummydest.sql, in Listing 3, with the build script for the existing version of the database.

SQL Compare will give you the synchronization script that will modify the database represented by that build script so that its schema is identical to that of the database in the DacPac.

Summary

It is actually rather easy to use PowerShell together with Redgate’s SQL Compare and DacFx to automate DacPac deployments. Once the solution shown in this article is fitted and tested, it can effortlessly be applied to large database server environments, thus saving endless production DBA hours (and sleepless nights).

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

You may also like