SQL Database Health check in ASP.NET core

In this article we will understand how to add a health check for SQL server database from .NET core web api application.

In this article we will perform below activities.

• Create a .NET core web api project
• Add a new api endpoint to add employee details to SQL server database
• Add a health check to check the connectivity of the SQL server database from the application

1. Create a .NET core web api application.
   • Open a new terminal in visual studio code
   
   • Navigate to the desired directory.
   • Run the command "dotnet new webapi" to create the web api application
   

2. Create an Employee table in azure SQL database
   • Create a SQL server and a database under that in azure
   
   • Create a new table "Employee" in azure SQL database using the below query

     "Create TABLE Employee (Id int PRIMARY KEY IDENTITY(1,1), FirstName nvarchar(50), LastName nvarchar(50))"

   
   • Copy the connection string of the database from the azure portal
   

3. Create an Employee model class
   • Add a new folder named "Models" under the parent directory and add a new file named Employee.cs within the Models folder as shown below
   
   • Create an Employee class with first name and last name properties as shown below
   

4. Create a new controller.
   • Create a new file within the Controller folder and name it as "DataController.cs" as shown below.
   
   • Add a new class named DataController within the DataController.cs file within the namespace. Add the using statement as shown below
   
   • Add a new api named "AddEmployee" which accepts an input parameter of type Employee class. Add "HttpPost", Route attribute as shown below
   
   • Add code implementation to add new employee to sql employee database table. Install required nuget packages
   
   • Install below nuget packages
   • Azure.Identity - For authentication and authorization to azure resources
   • System.Data.SQLClient - For SQL DB related operations
   • Azure.Security.KeyVault.Secrets - For interacting with KeyVault

5. Build and run the application. Invoke the api and add an employee.
   • Launch the swagger URL
   
   • Give first name and last name and click on submit. New Employee will be added to employee table
   
   • Employee details in the employee table
   

Now we have an application which depends on SQL DB. This application is said to be helathy if the application can connect to SQL DB. Let's add a health check to verify the connectivity of the SQL DB

1. We have a package "AspNetCore.HealthChecks.SqlServer" available to check the health of SQL server DB.

   Install the package using the command dotnet add package AspNetCore.HealthChecks.SqlServer


2. I am using azure keyvault to save the secrets. The secret identifiers are configured in appsettings file
3. Fetch the SQL DB connection string in the Program.cs file as shown below

4. Add SQL server health check using the "AddSqlServer" extension method as shown below. Passing the previously fetched connection string as input parameter

5. Add a health check endpoint in the Program.cs file

6. Run the application and navigate to "healthz" endpoint

7. I am running the application locally and I am using Azure SQL DB. So to be able to connnect to azure SQL DB from local, I need to add a firewall rule to allow my client IP as shown below. Let's verify the unhealthy scenario by removing the firewall rule or we can also verify by giving the wrong connection string


8. Run the application and navigate to "healthz" endpoint

In this article we have learnt about adding health check for SQL DB in .NET core application. Let me know your thoughts in the comments.
The github repo link for the code changes made in this article is here