Advice or strategy for merging multiple layered dacpacs into one

[tristanbarcelon]

Hi @dzsquared. We are trying to solve a problem similar to a previously filed issue. Specifically, we have 15 layered and independently versioned dacpacs deployed in sequence to a target database. Each of these dacpacs primarily focuses on a specific database schema and its sqlproject is contained in the same repo as the api/app. During deployment, our CD pipeline checks whether the target database has had this dacpac and version deployed already. If it has, then it skips to the next dacpac. However, the pipeline in its current state could use some improvements. Specifically, we'd like to be able to preview changes (breaking or otherwise) that will be made against production database before approving it. We thought of using sqlpackage's deployreport action to generate an xml report which we can parse for breaking changes and prevent deployment from going through without additional approval from DBAs. With 15 dacpacs to process, this would be difficult and time consuming. Hence, we looked for ways to merge all 15 dacpacs into 1 when building the "overall" package/release and that's how we stumbled onto Ed Elliot's sample project for merging dacpacs to 1. There are some limitations with the same project such as the inability to combine dacpac refs, sqlcmd vars, refactor logs, just to name a few. I've forked Ed's repo in order to make it compile as 64 bit, migrate packages.config to PackageReference, and remove 2 sample dacpac projects I'm unable to find.

Each of our dacpacs can come with:

• an optional pre-predeployment phase containing 0, 1, or more idempotent scripts for handling breaking changes before model comparison process is initiated by sqlpackage. This is something we handle separately and can merge since they fall outside of a dacpac.
• an optional post-postdeployment phase containing files to bcp data into the db if/when specific tables are empty. This is only used for really large pre-seeded table. Bacpac is available but we've never tried whether it works in an idempotent manner when deploying against a database that has data in the target tables already.

We have made sure the database properties of all 15 dacpacs are identical. During a merge, we expect to only inherit the database properties coming from the first dacpac provided (out of the 15). Any advice you could provide to address some of the limitations would be appreciated.

[ErikEJ]

Why do you need everything in a single database?

I did some work to improve the dacpac merger in EF Core Power Tools.

[tristanbarcelon]

@ErikEJ. Good question.

The last of these dacpacs we deploy contain mostly table valued functions and views and thus have references to tables from other dacpacs deployed previously. We get this sqlproj to validate the model at compile time using dacpac references. Likewise, there is an expectation on the system that when database backup is done, all the tables from various apps have the same level of consistency. If they were spread out into 15 different databases, then they'd have different levels of consistency even if we were to perform 15 database backups in parallel. All client apps connecting to the single database also use EF with snapshot isolation on.

Regarding the improvements you've made to dacpac merging, are they all contained here?

[ErikEJ]

@tristanbarcelon yes, they are.

[StingyJack]

Why do you need everything in a single database?

@ErikEJ - For me, it made the deployment significantly faster - like 20 minutes to publish 6 layered dacpacs vs 3 minutes to publish the composite dacpac

@tristanbarcelon - The solution I found for this was to add a task to the release packaging build pipeline of the final/topmost layer that running a publish of each dacpac, in order, to a database that was newly created by the first dacpac publish in the set, and then extracting a new composite dacpac from that after they were all done. I think I had to do some special handling of the Pre and Post deployment scripts in order to get the default reference data that was part of each constituent dacpac to be used correctly in the composite one. It was a few years ago (and a few employers ) so details are kinda fuzzy on that,

We thought of using sqlpackage's

/Action:Script is so much more useful than those two reports.

[StingyJack]

@dzsquared this issue converted into a discussion is hard to read now.