[SQLProjects] Deployment stuck in "Pending" state when using Aspire.Hosting.Azure.Sql locally

[yorek]

Describe the bug

When using AddAzureSqlServer("").RunAscontainer() using the Aspire.Hosting.Azure.Sql package, the deployment just hangs. No error, no logs...just hanging, and deployment reports "Pending" as a status.

Regression

No response

Steps to reproduce

var sqlSrv = builder.AddAzureSqlServer("sql")
    .RunAsContainer();    

var sqlDb = sqlSrv.AddDatabase("aspiretodo");

var dbPrj = builder.AddSqlProject<Projects.TodoDB>("tododb")    
    .WithReference(sqlDb)    
    .WaitFor(sqlDb);

Expected behavior

Deploy the database project

Screenshots

No response

IDE and version

Other

IDE version

VS Code

Nuget packages

<PackageReference Include="Aspire.Hosting.AppHost" Version="9.1.0" />
<PackageReference Include="Aspire.Hosting.Azure.Sql" Version="9.1.0" />
<PackageReference Include="Aspire.Hosting.NodeJs" Version="9.1.0" />
<PackageReference Include="Aspire.Hosting.SqlServer" Version="9.1.0" />
<PackageReference Include="CommunityToolkit.Aspire.Hosting.Azure.DataApiBuilder" Version="9.2.0" />
<PackageReference Include="CommunityToolkit.Aspire.Hosting.NodeJS.Extensions" Version="9.2.0" />
<PackageReference Include="CommunityToolkit.Aspire.Hosting.SqlDatabaseProjects" Version="9.2.1" />

Additional context

No response

Help us help you

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

[aaronpowell]

Related to #434 I believe

[ErikEJ]

@yorek What happens if you remove usage of SQL Projects?

Can you share a dashboard screen shot?

@aaronpowell Yes, sounds related to #434

https://github.com/CommunityToolkit/Aspire/blob/main/src/CommunityToolkit.Aspire.Hosting.SqlDatabaseProjects/SqlProjectBuilderExtensions.cs#L197

[aaronpowell]

That looks like a good catch. I wonder how we can best tackle it, do we create a separate Hosting.SqlDatabaseProjects.Azure integration or roll it into the existing. Rolling with the existing does mean that if you're not using Azure you still end up with Azure in the dependency stack, but a separate integration feels like major overkill

[ErikEJ]

What is the difference between

builder.AddAzureSqlServer("sql")
.RunAsContainer();

And

.AddSqlServer

Is it deployment support or anything else?

[ErikEJ]

if you're not using Azure you still end up with Azure in the dependency stack,

@aaronpowell If you use Microsoft.Data.SqlClient you end up with a lot of Azure "stuff" anyway currently.

[ErikEJ]

It was local deployment: https://github.com/dotnet/aspire/blob/main/src/Aspire.Hosting.Azure.Sql/AzureSqlExtensions.cs#L167

[ErikEJ]

What if the Resource classes here were in a seperate package?

https://github.com/dotnet/aspire/tree/main/src/Aspire.Hosting.Azure.Sql

[aaronpowell]

Thinking through this, the question, in my mind, is "what happens if it's an Azure SQL resource?", as in, not configured to be a container locally, just purely an Azure SQL resource - should (can?) it deploy to Azure SQL?

Since the SqlDatabaseProjects package doesn't produce anything in the manifest, it means we have to be careful on how the support works. Take this code for example:

var sqlSrv = builder.AddAzureSqlServer("sql");    

var sqlDb = sqlSrv.AddDatabase("aspiretodo");

var dbPrj = builder.AddSqlProject<Projects.TodoDB>("tododb")    
    .WithReference(sqlDb)    
    .WaitFor(sqlDb);

What happens when you use Aspire to deploy? Well, the AddSqlProject is ignored from the manifest, but you have the expectation that something should have happened because running locally it did deploy to the Azure resource. There's an obvious argument against using the Azure SQL database for local development, but it's something people can, and may need to, do.

So back to the problem at hand, I think this works (I haven't tested):

var dbPrj = builder.AddSqlProject<Projects.TodoDB>("tododb");

var sqlSrv = builder.AddAzureSqlServer("sql")
    .RunAsContainer(resource => {
      // find databases from the resource, or maybe via the builder
    });    

var sqlDb = sqlSrv.AddDatabase("aspiretodo");

The callback provided to RunAsContainer receives the IResourceBuilder<SqlServerServerResource> which does have the names of all the databases on it, and you can use that to get the IResourcesCollection from the builder and then grab it. It wouldn't be "pretty", but it'd be doable.

[yorek]

Hey both, from my understanding the RunAsContainer creates a local container to simulate Azure SQL db locally. I would expect during the deployment phase that an Azure SQL DB is created instead of having SQL Server deployed in a container in Azure.

[yorek]

"what happens if it's an Azure SQL resource?", as in, not configured to be a container locally, just purely an Azure SQL resource - should (can?) it deploy to Azure SQL?

I think it should, right? Conceptually it would be equivalent to run EF Core migrations against Azure SQL instead of SQL Server and that is expected to work right away

[aaronpowell]

"what happens if it's an Azure SQL resource?", as in, not configured to be a container locally, just purely an Azure SQL resource - should (can?) it deploy to Azure SQL?

I think it should, right? Conceptually it would be equivalent to run EF Core migrations against Azure SQL instead of SQL Server and that is expected to work right away

Yep, I'd expect so (I'm just unfamiliar with the tooling that powers the integration).

Deployment is more complex though, as you need to run the dacpac deployment as part of the Bicep files, which I guess you'd do as a custom script in Bicep, but you'd need to somehow upload the dacpac to somewhere that it can be downloaded in the script run. This is why it's not currently enabled for deployment - deployment makes it complex.

[ErikEJ]

@yorek @aaronpowell

the RunAsContainer creates a local container to simulate Azure SQL db locally.

Yeah, I dug into the source - since there is no "Azure SQL Container" it spins up a plain SQL Server 2022 container...

Deployment is more complex though, as you need to run the dacpac deployment as part of the Bicep files...

Yeah, exactly! And I absolutely agree that this is a noble end goal!

I am not an expert, but maybe the following options are worth investigating:

• The owner of the ARM provider for Azure SQL could potentially add support for continous deployment of a .dacpac, similar to the initial "sampleName" property that is available today. Then that would also become available in Bicep - but as Aaron mentioned, there are probably some challenges around storage/upload of the .dacpac and maybe execution of it (or perhaps that is what "sampleName" already does?
• I understand that 'azd' has an extensibility model, but have not dug further into it.

[yorek]

Yeah, I dug into the source - since there is no "Azure SQL Container" it spins up a plain SQL Server 2022 container...

Yep, that is fine and expected

[ErikEJ]

@yorek We seem to be discussing at least two topics here

• local deployment support
• general production deployment support for SQL Projects

Maybe you should open a new issue for the second topic?

Did you try what @aaronpowell suggested above?

@jmezach Any thoughts on this issue - it would be a great scenario to support.

[jmezach]

@ErikEJ I would definitely want to support the ability to deploy a .dacpac to an Azure SQL-like container during local development. One might argue that at least for local-dev there really isn't a distinction between Azure SQL and SQL Server.

Not sure why it gets stuck into a pending state though. If the target resource is not a SqlServerDatabaseResource, which I'm assuming is the case here, we start deploying as soon as all resources have been created (AfterResourcesCreatedEvent).