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?

  • The third option is the one I usually use and a pretty common solution for deploying static reference/config data as part of a database project. Is there a reason you don't like it or it doesn't work for you? – HandyD Apr 21 '20 at 3:13
  • @HandyD well this isn't my typical line of work, so I don't know if it's bad or not, but it just seems wrong when I have a 600mb sql file that slows the computer trying to open it. Is that really the common practice? For the other tables, I really liked the approach, it just felt like I was doing something wrong. Is there a common practice to compress (zip?) and decompress the SQL file during the operation? – Alex K Apr 21 '20 at 3:38
  • How you deploy might open other options, for example, if you're deploying through an orchestration tool like Azure DevOps etc then you could add post deployment steps to import the data from a CSV file via sqlcmd but that depends on how you're deploying and other things. – HandyD Apr 21 '20 at 3:55

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:

enter image description here

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

enter image description here

  • 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:

enter image description here

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

enter image description here

  • Let's say I have a database called MyDb. My first project has all of the schema things in MyDb plus references the dacpac I generate from my second project. When I try to publish (Upgrade Data-tier Application) the second dacpac, that contains the giant SQL script with inserts and the *.PostDeployment.sql file (~8mb now), SQL tells me it's going to drop pretty much all of the tables in MyDb. I created a *.publish.xml file and modified the advanced settings and unchecked the drop statements on the drop tab? I feel like I'm missing publish settings or doing something wrong? – Alex K Apr 21 '20 at 20:13
  • @AlexK That's odd, that's not the behavior I'm seeing. Is "Drop objects in target but not in source" (at the top of the "Drop" tab) checked, or unchecked? Unchecked is the default, and should prevent the schema compare from producing "DROP" statements for your tables. – Josh Darnell Apr 22 '20 at 12:18
  • If I take the produced dacpac in SQL and do Upgrade data-tier application it does it, but if I publish directly from VS, it does not drop or prompt to drop anything. Any idea why that would be? I may have registered something as a data-tier application, as I don't fully understand what that is and in VS it's just a checkbox that says "register as data-tier app"...so I probably clicked it. – Alex K Apr 22 '20 at 16:27
  • @AlexK Honestly, I've never used the "Upgrade data tier application" feature in SSMS. It doesn't sound like it would really jive with automation. You'd probably be better off using sqlpackage.exe or dbatools.io to deploy the dacpac. – Josh Darnell Apr 22 '20 at 20:52
  • I think there's some disconnect between my publish profile, my dacpac, DevOps, etc. If I do things manually it works correctly so I'm just going to debug those things. Thanks for the detail! – Alex K Apr 22 '20 at 22:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.