Quickstart: SQL Server Aspire Integration

Feature: SQL Server Aspire Integration
Date: 2025-10-17
Purpose: Developer quickstart guide for working with Aspire-managed SQL Server

Prerequisites

.NET 10 SDK installed
Docker Desktop running (for containerized SQL Server in development)
Visual Studio 2025 or VS Code with C# Dev Kit
Existing Visage solution cloned

Quick Start (5 minutes)

1. Update AppHost (2 minutes)

Add the SQL Server hosting package and register SQL Server with databases:

cd Visage.AppHost
dotnet add package Aspire.Hosting.SqlServer

Update Visage.AppHost/Program.cs:

var builder = DistributedApplication.CreateBuilder(args);

// Register SQL Server as a managed resource
var sqlServer = builder.AddSqlServer("sql")
    .WithHealthCheck();

// Add databases for each service
var registrationDb = sqlServer.AddDatabase("registrationdb");
var eventingDb = sqlServer.AddDatabase("eventingdb");

// Update service registrations to reference databases and wait for them
var registrations = builder.AddProject<Projects.Visage_Services_Registrations>("registrations")
    .WithReference(registrationDb)
    .WaitFor(registrationDb);

var eventing = builder.AddProject<Projects.Visage_Services_Eventing>("eventing")
    .WithReference(eventingDb)
    .WaitFor(eventingDb);

builder.Build().Run();

2. Update Registration Service (1 minute)

Add the Aspire EF Core SQL Server package:

cd services/Visage.Services.Registrations
dotnet add package Aspire.Microsoft.EntityFrameworkCore.SqlServer

Update Program.cs to use Aspire-injected connection:

var builder = WebApplication.CreateBuilder(args);

// Add service defaults (already exists)
builder.AddServiceDefaults();

// Replace manual DbContext configuration with Aspire-managed connection
builder.AddSqlServerDbContext<RegistrantDB>("registrationdb");

var app = builder.Build();

// Apply migrations on startup
using (var scope = app.Services.CreateScope())
{
    var dbContext = scope.ServiceProvider.GetRequiredService<RegistrantDB>();
    await dbContext.Database.MigrateAsync();
}

app.MapDefaultEndpoints();
// ... rest of app configuration

Remove connection string from appsettings.json (Aspire manages it now).

3. Update Eventing Service (1 minute)

Add the Aspire EF Core SQL Server package:

cd services/Visage.Services.Eventing
dotnet add package Aspire.Microsoft.EntityFrameworkCore.SqlServer

Update Program.cs (same pattern as Registration service):

var builder = WebApplication.CreateBuilder(args);

builder.AddServiceDefaults();
builder.AddSqlServerDbContext<EventDB>("eventingdb");

var app = builder.Build();

using (var scope = app.Services.CreateScope())
{
    var dbContext = scope.ServiceProvider.GetRequiredService<EventDB>();
    await dbContext.Database.MigrateAsync();
}

app.MapDefaultEndpoints();
// ... rest of app configuration

Remove connection string from appsettings.json.

4. Run and Verify (1 minute)

cd Visage.AppHost
dotnet run

Open the Aspire dashboard (URL shown in console output, typically https://localhost:17073/).

Verify:

✅ SQL Server resource shows as "Healthy" (green status)
✅ Both registrationdb and eventingdb are listed as child resources
✅ Registration and Eventing services show as "Healthy"
✅ Services started in correct order (SQL Server → Databases → Services)

Development Workflow

Running the Solution

# From repository root
cd Visage.AppHost
dotnet run

The Aspire dashboard opens automatically, showing all resources and their health status.

Accessing the Aspire Dashboard

URL: Shown in console output (typically https://localhost:17073/)
View SQL Server health status
Monitor service dependencies and startup order
View connection strings (in dev environment)
Check logs and traces for all resources

Database Management

View Database Schema

# SQL Server is containerized - connect via Aspire-provided connection string
# Get connection string from Aspire dashboard under SQL Server resource

Use SQL Server Management Studio or Azure Data Studio to connect to localhost with the credentials shown in the Aspire dashboard.

Run Migrations Manually

Migrations run automatically on service startup, but you can also run them manually:

cd services/Visage.Services.Registrations
dotnet ef migrations add <MigrationName>
dotnet ef database update

Reset Database (Development)

# Stop the AppHost
# Delete Docker container:
docker ps
docker stop <sql-container-id>
docker rm <sql-container-id>

# Restart AppHost - fresh SQL Server container will be created
cd Visage.AppHost
dotnet run

Troubleshooting

Services won't start

Symptom: Services stuck in "Starting" state

Solution:

Check Aspire dashboard for SQL Server health status
Ensure Docker Desktop is running
Check service logs for connection errors
Verify .WaitFor() is configured correctly in AppHost

Migration errors

Symptom: Service unhealthy with migration failure logs

Solution:

Review migration error in service logs (Aspire dashboard → Service → Logs)
Fix migration code
Restart AppHost
If needed, manually rollback: dotnet ef database update <PreviousMigration>

Connection string not found

Symptom: Service error about missing connection string

Solution:

Verify WithReference(database) is called for the service in AppHost
Check that database resource name matches ("registrationdb" or "eventingdb")
Ensure AddSqlServerDbContext<T>() uses the same name

Testing

Integration Tests with Aspire

Create tests using Aspire.Hosting.Testing:

[TestClass]
public class SqlServerIntegrationTests
{
    [TestMethod]
    public async Task Registration_Service_Connects_To_Database()
    {
        // Arrange
        await using var app = await DistributedApplicationTestingBuilder
            .CreateAsync<Projects.Visage_AppHost>();
        
        await app.StartAsync();
        
        // Act
        var httpClient = app.CreateHttpClient("registrations");
        var response = await httpClient.GetAsync("/health");
        
        // Assert
        response.Should().BeSuccessful();
        response.StatusCode.Should().Be(HttpStatusCode.OK);
    }
}

Run tests:

cd tests/Visage.Tests.Integration
dotnet test

Configuration

Development Configuration

Aspire automatically configures SQL Server for development:

Server: Containerized SQL Server on localhost
Authentication: SQL authentication with generated credentials
Databases: registrationdb and eventingdb auto-created
Health Checks: Enabled by default

No manual configuration required!

Production Configuration

For production, use Aspire parameters:

// In Visage.AppHost/Program.cs
var sqlPassword = builder.AddParameter("sql-password", secret: true);
var sqlConnectionString = builder.AddParameter("sql-connection-string");

var sqlServer = builder.AddSqlServer("sql", password: sqlPassword)
    .WithConnectionStringParameter(sqlConnectionString);

Set values in:

Azure: App Service configuration or Key Vault
Local production testing: User secrets (dotnet user-secrets set sql-password "YourPassword")

Connection Pooling

Aspire automatically configures connection pooling for SQL Server. Default pool size is 100 connections.

Production Tuning:

// In Visage.AppHost/Program.cs for production
var sqlServer = builder.AddSqlServer("sql")
    .WithConnectionStringParameter("Server=...;Min Pool Size=10;Max Pool Size=200;...");

Recommended settings:

Min Pool Size: 10-20 (maintains warm connections)
Max Pool Size: 100-500 (based on load testing, depends on concurrent requests)
Monitor connection pool exhaustion via Application Insights
Run load tests (NBomber) to determine optimal pool size for your workload

Environment-Specific Settings

Aspire supports multiple environments:

# Development (default)
dotnet run

# Staging
dotnet run --environment Staging

# Production
dotnet run --environment Production

Configure environment-specific Aspire parameters in appsettings.{Environment}.json in the AppHost project.

Next Steps

Review data-model.md for database architecture details
See plan.md for full implementation plan
Proceed to /speckit.tasks to generate implementation tasks
Review Aspire documentation: .NET Aspire SQL Server integration

Common Commands Reference

# Run the entire solution
cd Visage.AppHost && dotnet run

# Add a new migration (Registration service)
cd services/Visage.Services.Registrations
dotnet ef migrations add <MigrationName>

# Add a new migration (Eventing service)
cd services/Visage.Services.Eventing
dotnet ef migrations add <MigrationName>

# Run integration tests
cd tests/Visage.Tests.Integration && dotnet test

# View Docker containers
docker ps

# Stop all Docker containers
docker stop $(docker ps -q)

# Remove SQL Server container (for fresh start)
docker rm <container-id>

FAQ

Q: Do I need to install SQL Server locally?
A: No! Aspire automatically provisions a containerized SQL Server for development.

Q: How do I see the database schema?
A: Connect to localhost using the connection string from the Aspire dashboard with SQL Server Management Studio or Azure Data Studio.

Q: Can I use a different database for local development?
A: Yes, but it's not recommended. Aspire's containerized SQL Server ensures consistency across all developers.

Q: What happens to my existing connection strings?
A: Remove them from appsettings.json - Aspire manages connection strings automatically.

Q: How do I deploy to Azure?
A: Use azd up (Azure Developer CLI) - Aspire generates infrastructure-as-code automatically. See Aspire deployment docs for details.

Q: Do migrations run in production?
A: Yes, by default migrations run on service startup. For zero-downtime deployments, you may want to run migrations as a separate job before deploying new service versions.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Quickstart: SQL Server Aspire Integration

Prerequisites

Quick Start (5 minutes)

1. Update AppHost (2 minutes)

2. Update Registration Service (1 minute)

3. Update Eventing Service (1 minute)

4. Run and Verify (1 minute)

Development Workflow

Running the Solution

Accessing the Aspire Dashboard

Database Management

View Database Schema

Run Migrations Manually

Reset Database (Development)

Troubleshooting

Services won't start

Migration errors

Connection string not found

Testing

Integration Tests with Aspire

Configuration

Development Configuration

Production Configuration

Connection Pooling

Environment-Specific Settings

Next Steps

Common Commands Reference

FAQ

FilesExpand file tree

quickstart.md

Latest commit

History

quickstart.md

File metadata and controls

Quickstart: SQL Server Aspire Integration

Prerequisites

Quick Start (5 minutes)

1. Update AppHost (2 minutes)

2. Update Registration Service (1 minute)

3. Update Eventing Service (1 minute)

4. Run and Verify (1 minute)

Development Workflow

Running the Solution

Accessing the Aspire Dashboard

Database Management

View Database Schema

Run Migrations Manually

Reset Database (Development)

Troubleshooting

Services won't start

Migration errors

Connection string not found

Testing

Integration Tests with Aspire

Configuration

Development Configuration

Production Configuration

Connection Pooling

Environment-Specific Settings

Next Steps

Common Commands Reference

FAQ