[SQLProjects] Add support for .dacpac / SqlProject deployment

[yorek]

Related to an existing integration?

Yes

Existing integration

CommunityToolkit.Aspire.Hosting.SqlDatabaseProjects

Overview

Right now, as per #529 the CommunityToolkit.Aspire.Hosting.SqlDatabaseProjects does not deploy the generate .dacpac to Azure, during the deployment phase. It would be great it that could be extended to add support for having .dacpac deployend in Azure when running azd up for an Aspire project.

One way that could be done is running SqlPackage in an Azure Container Apps (which is already supported by Aspire), after the .dacpac has been generated, in the same container, via MSBuild.

The created application would be a Worker service, just like it is recommended for EF Migration deployment (https://learn.microsoft.com/en-us/dotnet/aspire/database/ef-core-migrations#create-the-migration-service)

Usage example

No changes from today's code, except that azd init and azd up will generate an additional Azure Container Apps container where the database project is built and deployed

Breaking change?

Yes

Alternatives

Today one has to manually deploy the .dacpac to Azure SQL

Additional context

No response

Help us help you

Yes, I'd like to be assigned to work on this item

[ErikEJ]

@yorek So each Aspire user would have to create this additional project? (Maybe based on some template?)

[ErikEJ]

@jmezach FYI!

[ErikEJ]

.dacpac bundle? https://devblogs.microsoft.com/dotnet/introducing-devops-friendly-ef-core-migration-bundles/

[yorek]

I think that if during development we can just spin up an ACA container where a Worker Service is run that uses SqlPackage behind the scenes, that would be enough. @dzsquared what do you think?

[ErikEJ]

@yorek The development story is already taken care of with a much more lightweight approach.

This is about production, I assumed?

[ErikEJ]

And I am a big fan of as little code as possible.

[yorek]

@yorek The development story is already taken care of with a much more lightweight approach.

This is about production, I assumed?

Correct

[jmezach]

I guess we could generate a container image that would contain the .dacpac along with a version of sqlpackage which would be the entry point of the container image?

Perhaps this could even be tied into the .NET SDK Container publish stuff and be a part of MSBuild.Sdk.SqlProj?

[ErikEJ]

@jmezach sounds cool. Not sure I know enough about azd up to understand how that would fit with this.

[jmezach]

@ErikEJ Neither do I. Don't have a lot of experience with AZD. But from what I understand from @yorek there is the ability to run arbitrary containers, so if we can make an executable container as a deliverable from MSBuild.Sdk.SqlProj we are at least halfway there ;).

[ErikEJ]

@jmezach Yes, read a little about it and eventually azd calls

dotnet publish /t:PublishContainter

https://learn.microsoft.com/en-us/dotnet/aspire/deployment/azure/aca-deployment-azd-in-depth?tabs=windows#how-azure-developer-cli-integration-works

So adding that to MsBuild.Sdk.SqlProj would be a good first step, could also be useful in other contexts.

[ErikEJ]

@jmezach I just confirmed with the .NET Aspire team that supporting dotnet publish /t:PublishContainter is a future proof building block

@yorek In the meantime, we could perhaps add some docs to our readme, and point to that in the logs with an link? (aka.ms??)

Docs could show snippets for GitHub actions and Azure DevOps tasks for

• publish the .dacpac artifact
• deploy the artifact to Azure SQL Database

Maybe @dzsquared aleready has some good samples that we could link to?

[jmezach]

@ErikEJ Cool. I think adding that support is generally useful, not only when using Aspire. So maybe we should open an issue on MSBuild.Sdk.SqlProj as a feature. Not sure what would be involved just yet, but I'll try to figure that out when I have some time

[github-actions]

We have noticed this issue has not been updated within 21 days. If there is no action on this issue in the next 14 days, we will automatically close it. You can use /stale-extend to extend the window.