.NET Aspire SQL Server integration

Article
11/21/2024

Includes: Hosting integration and Client integration

SQL Server is a relational database management system developed by Microsoft. The .NET Aspire SQL Server integration enables you to connect to existing SQL Server instances or create new instances from .NET with the mcr.microsoft.com/mssql/server container image.

Hosting integration

The SQL Server hosting integration models the server as the SqlServerServerResource type and the database as the SqlServerDatabaseResource type. To access these types and APIs, add the 📦 Aspire.Hosting.SqlServer NuGet package in the app host project.

.NET CLI
PackageReference

dotnet add package Aspire.Hosting.SqlServer

<PackageReference Include="Aspire.Hosting.SqlServer"
                  Version="*" />

For more information, see dotnet add package or Manage package dependencies in .NET applications.

Add SQL Server resource and database resource

In your app host project, call AddSqlServer to add and return a SQL Server resource builder. Chain a call to the returned resource builder to AddDatabase, to add SQL Server database resource.

var builder = DistributedApplication.CreateBuilder(args);

var sql = builder.AddSqlServer("sql")
                 .WithLifetime(ContainerLifetime.Persistent);

var db = sql.AddDatabase("database");

builder.AddProject<Projects.ExampleProject>()
       .WithReference(db)
       .WaitFor(db);

// After adding all resources, run the app...

Note

The SQL Server container is slow to start, so it's best to use a persistent lifetime to avoid unnecessary restarts. For more information, see Container resource lifetime.

When .NET Aspire adds a container image to the app host, as shown in the preceding example with the mcr.microsoft.com/mssql/server image, it creates a new SQL Server instance on your local machine. A reference to your SQL Server resource builder (the sql variable) is used to add a database. The database is named database and then added to the ExampleProject. The SQL Server resource includes default credentials with a username of sa and a random password generated using the CreateDefaultPasswordParameter method.

When the app host runs, the password is stored in the app host's secret store. It's added to the Parameters section, for example:

{
  "Parameters:sql-password": "<THE_GENERATED_PASSWORD>"
}

The name of the parameter is sql-password, but really it's just formatting the resource name with a -password suffix. For more information, see Safe storage of app secrets in development in ASP.NET Core and Add SQL Server resource with parameters.

The WithReference method configures a connection in the ExampleProject named database.

Tip

If you'd rather connect to an existing SQL Server, call AddConnectionString instead. For more information, see Reference existing resources.

Add SQL Server resource with data volume

To add a data volume to the SQL Server resource, call the WithDataVolume method on the SQL Server resource:

var builder = DistributedApplication.CreateBuilder(args);

var sql = builder.AddSqlServer("sql")
                 .WithDataVolume();

var db = sql.AddDatabase("database");

builder.AddProject<Projects.ExampleProject>()
       .WithReference(db)
       .WaitFor(db);

// After adding all resources, run the app...

The data volume is used to persist the SQL Server data outside the lifecycle of its container. The data volume is mounted at the /var/opt/mssql path in the SQL Server container and when a name parameter isn't provided, the name is generated at random. For more information on data volumes and details on why they're preferred over bind mounts, see Docker docs: Volumes.

Warning

The password is stored in the data volume. When using a data volume and if the password changes, it will not work until you delete the volume.

Add SQL Server resource with data bind mount

To add a data bind mount to the SQL Server resource, call the WithDataBindMount method:

var builder = DistributedApplication.CreateBuilder(args);

var sql = builder.AddSqlServer("sql")
                 .WithDataBindMount(source: @"C:\SqlServer\Data");

var db = sql.AddDatabase("database");

builder.AddProject<Projects.ExampleProject>()
       .WithReference(db)
       .WaitFor(db);

// After adding all resources, run the app...

Important

Data bind mounts have limited functionality compared to volumes, which offer better performance, portability, and security, making them more suitable for production environments. However, bind mounts allow direct access and modification of files on the host system, ideal for development and testing where real-time changes are needed.

Data bind mounts rely on the host machine's filesystem to persist the SQL Server data across container restarts. The data bind mount is mounted at the C:\SqlServer\Data on Windows (or /SqlServer/Data on Unix) path on the host machine in the SQL Server container. For more information on data bind mounts, see Docker docs: Bind mounts.

Add SQL Server resource with parameters

When you want to explicitly provide the password used by the container image, you can provide these credentials as parameters. Consider the following alternative example:

var builder = DistributedApplication.CreateBuilder(args);

var password = builder.AddParameter("password", secret: true);

var sql = builder.AddSqlServer("sql", password);
var db = sql.AddDatabase("database");

builder.AddProject<Projects.ExampleProject>()
       .WithReference(db)
       .WaitFor(db);

// After adding all resources, run the app...

For more information on providing parameters, see External parameters.

Connect to database resources

When the .NET Aspire app host runs, the server's database resources can be accessed from external tools, such as SQL Server Management Studio (SSMS) or Azure Data Studio. The connection string for the database resource is available in the dependent resources environment variables and is accessed using the .NET Aspire dashboard: Resource details pane. The environment variable is named ConnectionStrings__{name} where {name} is the name of the database resource, in this example it's database. Use the connection string to connect to the database resource from external tools. Imagine that you have a database named todos with a single dbo.Todos table.

SQL Server Management Studio
Azure Data Studio

To connect to the database resource from SQL Server Management Studio, follow these steps:

Open SSMS.
In the Connect to Server dialog, select the Additional Connection Parameters tab.
Paste the connection string into the Additional Connection Parameters field and select Connect.
If you're connected, you can see the database resource in the Object Explorer:

For more information, see SQL Server Management Studio: Connect to a server.

Hosting integration health checks

The SQL Server hosting integration automatically adds a health check for the SQL Server resource. The health check verifies that the SQL Server is running and that a connection can be established to it.

The hosting integration relies on the 📦 AspNetCore.HealthChecks.SqlServer NuGet package.

Client integration

To get started with the .NET Aspire SQL Server client integration, install the 📦 Aspire.Microsoft.Data.SqlClient NuGet package in the client-consuming project, that is, the project for the application that uses the SQL Server client. The SQL Server client integration registers a SqlConnection instance that you can use to interact with SQL Server.

.NET CLI
PackageReference

dotnet add package Aspire.Microsoft.Data.SqlClient

<PackageReference Include="Aspire.Microsoft.Data.SqlClient"
                  Version="*" />

Add SQL Server client

In the Program.cs file of your client-consuming project, call the AddSqlServerClient extension method on any IHostApplicationBuilder to register a SqlConnection for use via the dependency injection container. The method takes a connection name parameter.

builder.AddSqlServerClient(connectionName: "database");

Tip

The connectionName parameter must match the name used when adding the SQL Server database resource in the app host project. In other words, when you call AddDatabase and provide a name of database that same name should be used when calling AddSqlServerClient. For more information, see Add SQL Server resource and database resource.

You can then retrieve the SqlConnection instance using dependency injection. For example, to retrieve the connection from an example service:

public class ExampleService(SqlConnection connection)
{
    // Use connection...
}

For more information on dependency injection, see .NET dependency injection.

Add keyed SQL Server client

There might be situations where you want to register multiple SqlConnection instances with different connection names. To register keyed SQL Server clients, call the AddKeyedSqlServerClient method:

builder.AddKeyedSqlServerClient(name: "mainDb");
builder.AddKeyedSqlServerClient(name: "loggingDb");

Important

When using keyed services, it's expected that your SQL Server resource configured two named databases, one for the mainDb and one for the loggingDb.

Then you can retrieve the SqlConnection instances using dependency injection. For example, to retrieve the connection from an example service:

public class ExampleService(
    [FromKeyedServices("mainDb")] SqlConnection mainDbConnection,
    [FromKeyedServices("loggingDb")] SqlConnection loggingDbConnection)
{
    // Use connections...
}

For more information on keyed services, see .NET dependency injection: Keyed services.

Configuration

The .NET Aspire SQL Server integration provides multiple options to configure the connection based on the requirements and conventions of your project.

Use a connection string

When using a connection string from the ConnectionStrings configuration section, you can provide the name of the connection string when calling the AddSqlServerClient method:

builder.AddSqlServerClient(connectionName: "sql");

Then the connection string is retrieved from the ConnectionStrings configuration section:

{
  "ConnectionStrings": {
    "database": "Data Source=myserver;Initial Catalog=master"
  }
}

For more information on how to format this connection string, see the ConnectionString.

Use configuration providers

The .NET Aspire SQL Server integration supports Microsoft.Extensions.Configuration. It loads the MicrosoftDataSqlClientSettings from configuration by using the Aspire:Microsoft:Data:SqlClient key. The following snippet is an example of a appsettings.json file that configures some of the options:

{
  "Aspire": {
    "Microsoft": {
      "Data": {
        "SqlClient": {
          "ConnectionString": "YOUR_CONNECTIONSTRING",
          "DisableHealthChecks": false,
          "DisableMetrics": true
        }
      }
    }
  }
}

For the complete SQL Server client integration JSON schema, see Aspire.Microsoft.Data.SqlClient/ConfigurationSchema.json.

Use inline delegates

Also you can pass the Action<MicrosoftDataSqlClientSettings> configureSettings delegate to set up some or all the options inline, for example to disable health checks from code:

builder.AddSqlServerClient(
    "database",
    static settings => settings.DisableHealthChecks = true);

Client integration health checks

By default, .NET Aspire integrations enable health checks for all services. For more information, see .NET Aspire integrations overview.

The .NET Aspire SQL Server integration:

Adds the health check when MicrosoftDataSqlClientSettings.DisableHealthChecks is false, which attempts to connect to the SQL Server.
Integrates with the /health HTTP endpoint, which specifies all registered health checks must pass for app to be considered ready to accept traffic.

Observability and telemetry

.NET Aspire integrations automatically set up Logging, Tracing, and Metrics configurations, which are sometimes known as the pillars of observability. For more information about integration observability and telemetry, see .NET Aspire integrations overview. Depending on the backing service, some integrations may only support some of these features. For example, some integrations support logging and tracing, but not metrics. Telemetry features can also be disabled using the techniques presented in the Configuration section.

Logging

The .NET Aspire SQL Server integration currently doesn't enable logging by default due to limitations of the Microsoft.Data.SqlClient.

Tracing

The .NET Aspire SQL Server integration emits the following tracing activities using OpenTelemetry:

OpenTelemetry.Instrumentation.SqlClient

Metrics

The .NET Aspire SQL Server integration will emit the following metrics using OpenTelemetry:

Microsoft.Data.SqlClient.EventSource
- active-hard-connections
- hard-connects
- hard-disconnects
- active-soft-connects
- soft-connects
- soft-disconnects
- number-of-non-pooled-connections
- number-of-pooled-connections
- number-of-active-connection-pool-groups
- number-of-inactive-connection-pool-groups
- number-of-active-connection-pools
- number-of-inactive-connection-pools
- number-of-active-connections
- number-of-free-connections
- number-of-stasis-connections
- number-of-reclaimed-connections

Share via