AWS RDS and .NET: Step-by-Step Guide for SQL Server Setup

Amazon Relational Database Service (RDS) is a managed database service on AWS Cloud.

RDS provides different database engines; this post will focus on SQL Server.

We will set up a Microsoft SQL Server database on RDS and connect to it from a .NET application.

RDS for SQL Server makes setting up, operating, and scaling SQL Server deployments in AWS easy.

AWS sponsors this post, which is part of my SQL Series.

Setting Up AWS RDS SQL Server Instance

Let's create our first RDS instance running SQL Server.

In the Amazon Console, navigate to the RDS section and select the 'Create database' button.

The Create database takes you to a new page where you can configure the details of the RDS instance.

Let's choose the Microsoft SQL Server as our Database Engine.

The Standard option gives you full control over setting up the options, the SQL Server version, instance size, and other details.

The Easy option requires lesser manual configuration and uses recommended best-practice configurations.

It automatically suggests two database instance sizes depending on the workloads you are running—Production or Dev/Test.

Under Settings provide the DB instance identifier and the master credentials required to connect to the SQL instance.

You can manage the credentials in AWS Secrets Manager or self-manage.

The console also provides an option to auto-generate a password, which will appear on the summary page upon creation.

The Standard Create option lets you choose the Instance machine and storage configurations.

You can choose the instance size and the amount of storage required based on your application needs.

You can increase the DB instance storage if you need additional storage.

The Connectivity section lets you choose the VPC configuration and firewall rules and controls access to the instance.

For production workloads, you should restrict access to your database instance to only the applications that require access.

For this post, I will set the public access to 'Yes' since I need to connect to it from a .NET application running outside the VPC.

This covers the main settings you must know when creating an RDS SQL Server instance.

Create the database!

Connecting To AWS RDS SQL Server using Azure Data Studio

To interact with RDS SQL Server instance you can use various IDEs, such as SSMS, Azure Data Studio, Jetbrains DataGrip, etc.

You can use the server connection details from the RDS instance in the AWS Console.

Below, I am using Azure Data Studio to connect to it, and here's how adding a new connection looks.

Connecting to AWS RDS SQL Server from .NET Application

To connect to the RDS instance from a .NET application, we can use the SqlConnection class.

I also use Dapper, a lightweight ORM framework that provides extension methods on the SqlConnection class to translate the SQL query result into first-class C# objects.

The code below shows the updated ASP NET API Controller, which talks to the RDS database to retrieve weather data information.

string connectionString = builder.Configuration.GetConnectionString("RDSConnectionString");

app.MapGet("/weatherforecast", async () =>
    {
        using var connection = new SqlConnection(connectionString);
        var forecasts = await connection
            .QueryAsync<WeatherForecast>(
                 "SELECT Date, TemperatureC, Summary FROM WeatherForecasts");
        
        return Results.Ok(forecasts);
    })
    .WithName("GetWeatherForecast")
    .WithOpenApi();

I created a database and a table with some dummy weather forecast data for this to work!