[Feature] Allow setting DacDeployOptions in DacpacDeployer when publishing SqlServer porjects from dacpac

[nnitkasw]

Background
I'm working in a project where we have a base dacpac with common schema elements for multiple instances of different databases building on top of the common schema elements. The dacpac is referenced as a PackageReference in the SqlServer project and normally published with the IncludeCompositeObjects=true option to deploy alle schema elements required.

As we attempt to adopt .Net Aspire, we are not easily able to do this without re-implementing the functionality of the CommunityToolkit.Aspire.Hosting.SqlDatabaseProjects package only to override the use of new DacDeployOptions() in DacPacDeployer.Deploy().

It seems like a very usable feature to allow modifying the many DacDeployOptions properties passed to the Deploy operation for the dacpac in question.

Proposal
I thus propose an overload of the WithDacpac extension method for SqlProjectResource resource builders to set a new DacDeployOptionsMetaDataAnnotation which can be picked up when deploying the dacpac in the SqlProjectPublishService class.

Alternatively, the DacDeployOptions can be created and populated with supported property values from the sqlproj file in a new GetDacDeployOptions method in the SqlProjectResource class similar to how the SqlTargetPath and TargetPath are attempted read in the GetDacpacPath method.

Help us help you
Yes, I'd like to be assigned to work on this item, but am new to contributing on github.

[aaronpowell]

@jmezach

[jmezach]

I agree that this would be a very useful feature to have and should be fairly easy to add. Not sure when I'll have time to add this, but if anyone wants to take a stab at it feel free to create a PR

[nnitkasw]

I can give it a go.

Any comments as to the design?
I do not see any good ways to incorporate the settings into the fluent syntax.
I think an overload with an DacDeployOptions parameter for the settings works best.
And given that Microsoft.SqlServer.DacFx package is already referenced, I prefer using the existing options class rather than adding and then maintaining some form of copy with similar values.

I would also like to try parsing mirrored settings values from the sqlproj file as is done for the dacpacpath.
But I think that this could a breaking change in the current behavior for some.
I would appreciate any comments as to how to approach this. Otherwise I will leave it out for now.

[jmezach]

Since we need an instance of DacDeployOptions regardless I'm wondering if we should provide a WithConfigureDacDeployOptions method that gets passed an Action<DacDeployOptions> delegate and we'll then call that delegate before performing the deployment.

[nnitkasw]

Since we need an instance of DacDeployOptions regardless I'm wondering if we should provide a WithConfigureDacDeployOptions method that gets passed an Action<DacDeployOptions> delegate and we'll then call that delegate before performing the deployment.

I like this, alternatively adding a overload with a Action<DacDeployOptions> configureDacDeployOptions parameter to WithDacpac, similar to the configureContainer parameters for RunAsContainer available on some of the integrations.

My thinking behind using a parameter approach is that future usages of AddSqlProject might not be dacpac deployment oriented.
WithConfigureDacDeployOptions seem to only be relevant when used with WithDacpac.
So an extension method might then introduce a future need for breaking change where WithDacpac is updated to return a new SqlProjectDacpPacResource type and then WithConfigureDacDeployOptions updated to work on this (having a Parent property to navigate back to the SqlProjectResource in the call chain).

But that is just speculation. Script based deployments are already possible with the core SqlServer integration.
I'm not sure what other usages than dacpac deployment the SqlDatabaseProjects library would serve.

[nnitkasw]

Since we need an instance of DacDeployOptions regardless I'm wondering if we should provide a WithConfigureDacDeployOptions method that gets passed an Action<DacDeployOptions> delegate and we'll then call that delegate before performing the deployment.

I like this, alternatively adding a overload with a Action<DacDeployOptions> configureDacDeployOptions parameter to WithDacpac, similar to the configureContainer parameters for RunAsContainer available on some of the integrations.

My thinking behind using a parameter approach is that future usages of AddSqlProject might not be dacpac deployment oriented. WithConfigureDacDeployOptions seem to only be relevant when used with WithDacpac. So an extension method might then introduce a future need for breaking change where WithDacpac is updated to return a new SqlProjectDacpPacResource type and then WithConfigureDacDeployOptions updated to work on this (having a Parent property to navigate back to the SqlProjectResource in the call chain).

But that is just speculation. Script based deployments are already possible with the core SqlServer integration. I'm not sure what other usages than dacpac deployment the SqlDatabaseProjects library would serve.

I just realized that WithConfigureDacDeployOptions would be needed to support when .WithDap when `WithDacpac is not used to specify a local path. So this indeed seems the best way to go. I will create a PR and any implementation comments can then be done via the PR.

[cx-seibel]

Deploy dacpac is very easy. As Someone mentioned, you need the Microsoft.SqlServer.DacFx NuGet package.
Then this code works:

using Microsoft.SqlServer.Dac;

// Display the command line arguments using the args variable.
foreach (var arg in args)
{
    Console.WriteLine($"Argument={arg}");
}

foreach (var env in Environment.GetEnvironmentVariables())
{
    Console.WriteLine($"Environment={env}");
}

DacServices dacServices = new("Server=localhost;Integrated Security=true;");

// Create a new DacPackage object by loading the .dacpac file.
DacPackage dacpac = DacPackage.Load("C:\\Users\\johndoe\\Documents\\Visual Studio 2019\\Projects\\MyDatabase\\MyDatabase\\bin\\Debug\\MyDatabase.dacpac");

// Deploy the .dacpac file to the database.
dacServices.Deploy(dacpac, "MyDatabase", upgradeExisting: true, options: new DacDeployOptions
{
    BlockOnPossibleDataLoss = false,
    CreateNewDatabase = true
});

Having ".WithDacPac()" in SqlServer ApplicationBuilder would execute the deployment every time.
I would rather create a migration Console Project which do not start in aspire.
Only when I click the run button, it will do the create / migration stuff.
I could not figure out how to add a project in stopped state in aspire.

But the biggest issue is how to pass the dacpac.
Normally you use a SSDT project which produces the dacpac and also the Pre/Postpublish Scripts.
And you configure the DacDeployOptions inside the SSDT profile as well.
It would be awesome just Add the SSDT Project to Aspire and connect my sqlServer database with SSDT.
You can add the SSDT Project to Aspire Host but as soon as you start Aspire, a connect dialog will appear. Closing the dialog closes aspire as well.
Maybe someone has an idea how to solve this issue?
When you set the SSDT Project as Start project in Visual Studio and start debug, you will have the same result.

Conclusion:
In think the best way to go is to connect the sqlServer database and a migration Project which itself has a reference to SSDT would be the best way to go.

[nnitkasw]

Having ".WithDacPac()" in SqlServer ApplicationBuilder would execute the deployment every time.

.WithDacPac already exists in the CommunityToolkit and works very much like the code example you give. (Hint: See the DacPacDeployer calss in the sources). The proposed feature above is specifically about allowing to specify DacDeployOptions as you also use in your example. Specifically to allow deploying composite dacpacs referenced by PackageReferences.

Our organisations "normal", actually does include always deploying updated dacpacs to publish the latest schema differences every time.
We use safeguards such as setting BlockOnPossibleDataLoss = true to guard against problematic schema changes through our dev and test environments. This has been our deployment strategy for years and ahve yet to experience any problems.
We actually see this as one of the core strengths of using dacpacs over manual migration scripts. And it works great for us.

We do have the same annoyance with the Publish dialog which appears when you launch the Apphost project from the VS menu.
If you right clik the AppHost project and select Debug -> New instance (or without debugging) the AppHost will start as expected without the publish database dialog. I encourage you to file a seperate issue on this problem; and perhaps help solve the problem with a PR.
The problem is not specifically related to the proposed feature above.

Another problem we have, is when using the new beta Microsoft.Build.Sql SDK for the database project.
We experience that the .WithDacPac method fails to automatically get the dacpac file path from the .sqlproj properties.
We have this problem when running in VS. The problem seems to be an error in resolving the Microsoft.Build.Sql sdk.
We have gotten around this issue by specifying the dacpac path explicitly.
We were planning to log a seperate issue and perhaps contribute with solving the problem; if we at some point are able to identify the root cause. For now we get by fine using an explicit path.

Generally, what you want to achieve with manual scripts is actually possible using the core .Net Aspire packages.
You do not nescesarily need to use the Aspire CommunityToolkit for that. You can hook up your scripts by mounting them in the container and customize the entrypoint. I believe that the many .Net Aspire database samples includes such a scenario.