Feodor Georgiev

9 February 2017

• SQL Compare
• Database Builds and Deployments

Automating DacPac deployments using the SQL Compare command line and PowerShell

You as a DBA can easily be given a DacPac by a development team, encapsulating the latest database changes, which they wish you to then release to a production database. This is fine, but you are accustomed to generating a database deployment script using SQL Compare. You will then inspect 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 still need to generate the deployment script, but now 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 a migration script.

Create a DacPac using PowerShell

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. Having done that, we can create a DacPac. As described in my previous article, How to compare two DacPacs using SQL Compare, 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 to 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'.

Unpacking a DacPac in PowerShell

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:

Figure 1

However, if you attempt to run the script, you’ll see all kinds of errors since it does not 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 database build and migrations scripts from a DacPac

Using SQL Compare command line and PowerShell, it is easy to automate the generation of a database build script from a DacPac*. Just unpack the DacPac, then compare it to an empty database, or even an empty file. SQL Compare will take the schema definition defined in the DacPac’s model.sql file and use it to generate a build script with all the objects in the correct dependency order!

Similar, for updating an existing production database, SQL Compare will generate a deployment script that will synchronize the schema of the target so that it matches the source. This script will add and remove objects, or alter the definitions existing objects, on the target database so that its scheme then matches than of the source database. It will attempt to preserve existing data as far as possible but issue warning if it’s not.

Generating a database build script from a DacPac

To build a version of a database (from scratch), we compare the source schema objects, as represented in the DacPac, with an ’empty’ target database. This can be an empty live database, or even a blank file!

In Listing 3, first we 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 database 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 good things you would expect from a well-written 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 migration script from a DacPac

It isn’t that often that you’ll be given a DacPac and asked to create a new database. More often, you will 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.

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 it is 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).