Amazon RDS - MySQL for .NET Developers
Tags: aws, dotnet, database, mysql
Published: 2022-05-27 21:07:23

This is the first of a series on how to take advantage of Amazon RDS databases as a .NET developer. Let’s start with one of the oldest open-source relational databases out there, [MySQL](https://dev.mysql.com/). Originally released in 1995, MySQL has gone through a series of “owners” since then, with it currently being primarily developed by Oracle. As mentioned, MySQL is free and open-sourced under the terms of the GNU General Public License (GPL). That AWS does not have to pay any licensing fee is one of the primary reasons that the cost for MySQL on Amazon RDS is the lowest in the chart; you are only paying for hardware and management rather than hardware, licensing, and management.

MySQL may not be as fully featured or high-powered as some of the commercial systems such as SQL Server and Oracle, however, that does not mean that it is not of use to a .NET developer. One of the reasons that MySQL became popular was because of its relative simplicity and ease of use, so if all you are looking for is the ability to easily persist data in a relational database then more than likely MySQL will support your need at a fraction of the cost of SQL Server.

## MySQL and .NET

Before we dig into the RDS support for [MySQL](https://aws.amazon.com/rds/mysql/), let us first briefly go over using MySQL during .NET development. The primary use case when thinking about .NET and database products is the support for Entity Framework, .NET’s default object-relational mapping (ORM) system. If there is support for that database, then using that database in your .NET application will come down to the features of the database rather than its interaction with .NET. With that in mind, let’s look at what you need to do to use MySQL in your application.

The first thing you need to do is to include the necessary NuGet package, [**MySql.EntityFrameworkCore**](https://www.nuget.org/packages/MySql.EntityFrameworkCore/). Once you have the package, next is configuring your application to use MySQL. You do this by calling the **UseMySQL** method when overriding the _OnConfiguring_ method in the context class as shown below:

```
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseMySQL("connection string here");
}
```

A connection string for MySQL has four required fields:

- _server_ – server with which to connect
- _uid_ – user name 
- _pwd_ - password
- _database_ – database with which to connect

From here on out, it is just like working with Entity Framework and SQL Server.  Kinda anti-climactic, isn't it? Let’s now go create a MySQL database.

## Setting up a MySQL Database on Amazon RDS

Now that we know how to setup our .NET application to access MySQL, let’s go look at setting up MySQL. Log into the console, go to RDS, select _Create database_. On the Create database screen, select _Standard create_ and then _MySQL_. Doing this will show you that there is only one _Edition_ that you can select, MySQL Community. You then have a lot of different release versions that you can select from, however, the NuGet packages that we used in our earlier example require a reasonably modern version of MySQL, so unless you have any specific reason to use an older version you should always use the default, most updated version.

Once you have defined the version of MySQL that you will use, your next option is to select the _Template_ that you would like to use. You have three different templates to choose from:

- Production – defaults are set to support high availability and fast, consistent performance.
- Dev/Test – defaults are set in the middle of the range.
- Free tier – defaults are set to the minimal, free version.

We are going to select the Free tier version to limit our costs for this walkthrough! This will create many of the default system values that you will see during the server configuration, more on these later.

The next section is Settings. Here you will create the _DB instance identifier_ and the _Master username_ and _Master password_, the login credentials as shown in Figure 1. Note that the DB instance identifier needs to be unique across all the account’s DB instances in this region, not just MySQL database instances. We used “prodotnetonaws” as both the instance identifier and the Master username. If you choose to _Auto generate a password_, you will get an opportunity to access that password immediately after the database is created.

<img src="https://billthevestguy.com/wp-content/uploads/2022/05/image-12.png"/>
_Figure 1. Naming the DM instance and creating the master user_

Scrolling down to the next section, **DB instance class**, will show that the instance class being used is the _db.t2.micro_ (or comparable, depending upon when you are reading this) which is the free tier-compatible instance type. The next section down the page, **Storage**, is also filled out with the free version of storage, defaulting to 20 GiB of _Allocated storage_. Do not change either of these values to stay within the “free” level.

There are four additional sections. The first of these is **Availability & durability**, where you can create a replica in a different availability zone. Amazon RDS will automatically fail over to the standby in the case of a planned or unplanned outage of the primary. If you selected the “Free” template, then this whole section will be greyed out and you will not be able to create a _Multi-AZ deployment_. The second section is **Connectivity**. This is where you assign your _Virtual private cloud_ (VPC) and _Subnet group_, determine whether your RDS instance has _Public access_, and assign a _VPC security group_. You can also select an _Availability zone_ if desired. We left all these values at their default.

The third section is **Database authentication**. You have three options in this section, with the first being _Password authentication_ (the default value) where you manage your database user credentials through MySQL’s native password authentication features. The second option in this section is _Password and IAM database authentication_ where you use both MySQL’s password authentication features and IAM users and roles, and the last is _Password and Kerberos authentication_, where you use both MySQL’s password authentication features and an AWS Managed Microsoft Active Directory (AD) created with AWS Directory Service.

The last section when creating an RDS database is **Additional configuration**. This allows you to add any _Database options_, configure _Backup_, add _Monitoring_, and configure _Logging_, _Maintenance_, and to turn on _Deletion protection_. When deletion protection is enabled, you are not able to delete the database without first editing the database to turn off that setting. Select the **Create database** button when completed. This will bring you back to the Databases screen where you will likely see a notice that the database is being created as shown in Figure 2.

<img src="https://billthevestguy.com/wp-content/uploads/2022/05/image-13.png"/>
_Figure 2. Notice that an RDS database is being created_

If you had selected to auto generate a password, the button shown in Figure 2 also shows the _View credential details_ button that you would need to click to see the generated value. Once the database is _Available_, you can interact with it like any other database, using endpoint and port values that are shown in the Connectivity & security tab in the database details, as shown in Figure 3, for the connection string in your .NET application.

<img src="https://billthevestguy.com/wp-content/uploads/2022/05/image-14.png"/>
_Figure 3. MySQL database details screen showing endpoint and port_

MySQL is the most used open-source relational database in the world. However, as mentioned earlier, Oracle took control of the project in 2010 and this created some angst amongst MySQL users. This led to a forking of the source code by some of the original MySQL developers and a creation of a new open-source relational database based upon the MySQL code, MariaDB. We will look at that next.