What is the best way to include a large amount of configuration data in a SQL project?

Question

I have a BIDS SQL Server Database Project with a database that I publish dynamically to various servers (SQL2016). It initially starts blank and everything is parameterized. Everything is in Azure DevOps and deploys to places.

I want to include/pack in (mostly static) configuration data for a few tables. Initially I used the script wizard to script the tables, then just put that script text in my post-deployment file (InitConfigs.PostDeployment1.sql).

I've come across one config table, when scripted, is a nearly 600mb script. The size isn't the problem, it just seems like there must be a proper/better way to embed it in the project.

Some things I've tried:

• Export to flat file and embed somehow. - Seems messy and I have to figure out a way to import it in my post deployment script
• Export single table bacpac - Doesn't seem to work because it tries to include entire database
• Use Script wizard to generate a giant *.sql file. - I can add to the project and reference with SQLCMD :r. Seems odd.

Is there some sort of data package dacpac/bacpac-style file that can wrap everything up nicely and can be easily imported? Perhaps compressed too?

Answer 1

Your intuition is right here - it's not common to have a 600 MB SQL script as part of your SSDT project in source control.

Especially in the world of distributed version control systems, like git, you're talking about potentially numerous copies of that file, on various machines, and tracking the history of it in detail.

However, there is no "built-in" way of dealing with static data other than post deploy scripts, as you've noticed:

SQL SSDT Database Projects: Source Control Lookup Data

One solution would be to create a one-time-use SSDT project, and all it has is your configuration data scripts. Then you can produce a dacpac file from that (which is really just a compressed folder), and "reference" that dacpac in your main project.

The advantage of this approach is that you get a compressed deploy script, but it's in a format that easily fits with your existing deployment pipeline (rather than just throwing the script file into a .zip archive).

Here's what that looks like.

I have an SSDT project with the AdventureWorks sample database schema in it. The resulting dacpac is 80 KB:

I'll create a second project, and all it contains is a Post Deploy script, which references my static config data script:

• Post_Person.ContactType.sql is a 10 MB script file with a bunch of insert statements
• it's configured as "Build Action" = "None" and "Copy to Output Directory" = "Do Not Copy"

This produces a dacpac file that is 34 KB.

Based on that compression ratio, which depends a lot on your data, your 600 MB could get as small as 2 MB (there is a lot of repetition in a script like this, which lends itself to good compression).

Then you can add the dacpac as a "Database Reference" in your main SSDT project:

At this point, you could discard the "InitScripts" project. Or you could store it in a separate standalone repository, that less people will need, in case you ever need to update and regenerate the dacpac.

Note that, in the end, your build / deploy process will need to deploy both dacpac files - your "main" one, and the "InitScripts" one only on initial setup (or you could deploy it every time, and have the init scripts check for existing data before taking any action).