SQL Database Health Check route in ASP.NET Core

In this article, we shall check how to implement SQL Database Health Check route in ASP.NET Core API or MVC applications.

Implementing a health check is mandatory in many stages of the application development life cycle.

It helps you to keep track of the health of numerous resources, databases, and services that your business application relies on in real-time.

This enables you to quickly troubleshoot and resolve technical or environmental issues related to Database.

Today in this article, we will cover below aspects,

Health check services and middleware in ASP.Net core allow us to validate APIs by allowing us to create health check routes.

It also allows us to validate the health of other resources such as external service connections, external databases, and so on.

SQL Database Health Check – Getting started

If you already have an API, then please follow below two steps,

Step1 – Implement SQL IHealthCheck interface

Here please implement the IHealthCheck interface and override the CheckHealthAsync method.

Here we are defining Redis Cache health check using a Redis connection.

We shall use the “OpenAsync” command to check the connectivity with the database and later execute a simple query or command to validate the success or failure of the query.

Let’s now define and override the CheckHealthAsync which is the IHealthCheck interface method implemented for SQL connection.

Below is the implementation of the method,

HealthCheckResult lets you return health as Healthy, Degraded, or Unhealthy statuses.

CheckHealthAsync method is defined as below,

public async Task<HealthCheckResult> CheckHealthAsync(HealthCheckContext context, CancellationToken cancellationToken = default(CancellationToken))
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                try
                {
                    await connection.OpenAsync(cancellationToken);
                    var command = connection.CreateCommand();
                    command.CommandText = "select 1";
                    await command.ExecuteNonQueryAsync(cancellationToken);
                }
                catch (DbException ex)
                {
                    return new HealthCheckResult(status: context.Registration.FailureStatus, exception: ex);
                }
            }
            return HealthCheckResult.Healthy();
        }

In the above method, for any connection failure, you shall get an exception that can be used to return the failure status of the method.

For More details on SQL CRUD operations like Create, Update, Read, or Delete operations, Please visit the below article for more details,

• How to Use SqlClient in ASP.NET Core

Step2 – Register SQL Database health check services

In Startup.ConfigureServices please add the HealthChecks as below,

 public void ConfigureServices(IServiceCollection services)
        {
            services.AddControllers();
            services.AddScoped<IEmployeeRepository, EmployeeRepository>();
            services.AddHttpContextAccessor();
            services.AddDbContext<TheCodeBuzzContext>(options =>
            {
                options.UseSqlServer(Configuration.GetConnectionString("SQLSettings: Connection"));
            });
            services.AddHealthChecks().AddCheck<SqlConnectionHealthCheck>("SQLDBConnectionCheck");
        }

Step3 – Enable health check for SQL DB Middleware in API pipeline

Below is a sample Configure method that enables health checks for SQL DB Middleware in the API pipeline.

public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }
            app.UseHttpsRedirection();
            app.UseHealthChecks("/healthcheck");
            app.UseRouting();
            app.UseAuthorization();
            app.UseEndpoints(endpoints =>
            {
                endpoints.MapControllers();
            });
        }

Let’s execute the route “/health check” and we shall get the SQL Health check success as below,

You shall be able to verify Healthy, Degraded, or Unhealthy status accordingly.

References:

• Getting Started with EFCore in ASP.NET Core

• EFCore Repository implementation in .NET Core with example

Do you have any comments or ideas or any better suggestions to share?

Please sound off your comments below.

Happy Coding !!

---

Please bookmark this page and share it with your friends. Please Subscribe to the blog to receive notifications on freshly published(2024) best practices and guidelines for software design and development.

---