Bearbeiten

Freigeben über

.NET Aspire SQL Server integration

  • Artikel

In this article, you learn how to use the .NET Aspire SQL Server integration. The Aspire.Microsoft.Data.SqlClient library:

  • Registers a scoped Microsoft.Data.SqlClient.SqlConnection factory in the DI container for connecting Azure SQL, MS SQL database.
  • Automatically configures the following:
    • Connection pooling to efficiently managed HTTP requests and database connections
    • Automatic retries to increase app resiliency
    • Health checks, logging and telemetry to improve app monitoring and diagnostics

Prerequisites

Get started

To get started with the .NET Aspire SQL Server integration, install the Aspire.Microsoft.Data.SqlClient NuGet package in the client-consuming project, i.e., the project for the application that uses the SQL Server client.

dotnet add package Aspire.Microsoft.Data.SqlClient

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

Example usage

In the Program.cs file of your client-consuming project, call the AddSqlServerClient extension to register a SqlConnection for use via the dependency injection container.

builder.AddSqlServerClient("sqldb");

To retrieve your SqlConnection object an example service:

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

After adding a SqlConnection, you can get the scoped SqlConnection instance using DI.

App host usage

To model the SqlServer resource in the app host, install the Aspire.Hosting.SqlServer NuGet package in the app host project.

dotnet add package Aspire.Hosting.SqlServer

In your app host project, register a SqlServer database and consume the connection using the following methods:

var builder = DistributedApplication.CreateBuilder(args);

var sql = builder.AddSqlServer("sql");
var sqldb = sql.AddDatabase("sqldb");

var myService = builder.AddProject<Projects.MyService>()
                       .WithReference(sqldb);

When you want to explicitly provide a root SQL password, you can provide it as a parameter. Consider the following alternative example:

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

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

var myService = builder.AddProject<Projects.MyService>()
                       .WithReference(sqldb);

For more information, see External parameters.

Configuration

The .NET Aspire SQL Server integration provides multiple configuration approaches and options to meet the requirements and conventions of your project.

Use a connection string

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

builder.AddSqlServerClient("myConnection");

The connection string is retrieved from the ConnectionStrings configuration section:

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

For more information, see the ConnectionString.

Use configuration providers

The .NET Aspire SQL Server supports Microsoft.Extensions.Configuration. It loads the MicrosoftDataSqlClientSettings from configuration files such as appsettings.json by using the Aspire:SqlServer:SqlClient key. If you have set up your configurations in the Aspire:SqlServer:SqlClient section, you can just call the method without passing any parameter.

The following example shows an appsettings.json file that configures some of the available options:

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

Use inline configurations

You can also pass the Action<MicrosoftDataSqlClientSettings> delegate to set up some or all the options inline, for example to turn off the DisableMetrics:

builder.AddSqlServerClient(
    static settings => settings.DisableMetrics = true);

Configuring connections to multiple databases

If you want to add more than one SqlConnection you could use named instances. The json configuration would look like:

{
  "Aspire": {
    "SqlServer": {
      "SqlClient": {
        "INSTANCE_NAME": {
          "ServiceUri": "YOUR_URI",
          "DisableHealthChecks": true
        }
      }
    }
  }
}

To load the named configuration section from the json config call the AddSqlServerClient method by passing the INSTANCE_NAME.

builder.AddSqlServerClient("INSTANCE_NAME");

Configuration options

Here are the configurable options with corresponding default values:

Name Description
ConnectionString The connection string of the SQL Server database to connect to.
DisableHealthChecks A boolean value that indicates whether the database health check is disabled or not.
DisableTracing A boolean value that indicates whether the OpenTelemetry tracing is disabled or not.
DisableMetrics A boolean value that indicates whether the OpenTelemetry metrics are disabled or not.

Health checks

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

By default, the .NET Aspire Sql Server integration handles the following:

  • Adds the SqlServerHealthCheck, which verifies that a connection can be made commands can be run against the SQL Database.
  • 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 SqlClient.

Tracing

The .NET Aspire SQL Server integration will emit 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

See also