How to use Database Sharding and Scale an ASP.NET Core Microservice Architecture

Load Balance a C# ASP.NET Core Service and Use MySql App-Layer Sharding. Shows the Concepts, Which Also Apply to MongoDB, etc.

One of the big advantages of microservices is, that they can be scaled independently. This article shows the benefits and challenges of scaling one microservice and its database.

You will create a working example application and manually implement application-layer sharding. It shows how to choose a shard key based on the use-cases and data model. This helps to apply the same principles to DBMS with integrated scaling like MongoDB, etc.

Further reading: Microservices vs. Monolith Architecture and Database Sharding Explained- 2021 Database Scaling Tutorial

This is the first of two parts. You will implement the microservice and use a sharded DB.

In the second part, you will scale and run multiple container instances of the microservice and databases. You will use docker compose and a load balancer. Finally, you run JMeter load tests to see how the application scales when using a different number of instances.

1. Usecases and Datamodell

The example application consists of a user and a post microservice. They communicate via messages:

See also my previous article How to Build an Event-Driven ASP.NET Core Microservice Architecture

The User microservice handles adding and modifying users. The Post microservices handles viewing and adding posts. There is far more interaction with the Post microservice. So when the load to the app increases the Post microservice will be the first microservice that needs to scale.

The name of the author is part of the PostService bounded context and therefore the Post microservice. Adding and modifying authors is done in the User microservice. The User microservice sends events when a new user is added or a username changes.

Logical Data Model of the PostService

Users can write posts in categories. They can also read the posts by category including the author name. Newest posts are on top. The categories are fixed and change seldom.

Based on these use-cases I decided to shard by category:

2. Implement the Microservice

Install Visual Studio Community (it’s free) with the ASP.NET and web development workload.

Create a solution and add an ASP.NET Core 5 Web API project with the name “PostService”. Disable HTTPS and activate OpenAPI Support.

Install the following NuGet packages:

• Microsoft.EntityFrameworkCore.Tools

• MySql.EntityFrameworkCore

• Newtonsoft.Json

Create the Entities

The index of the Post entity should speed up the retrieval of the latest posts in a category:

The Version in the User entity will later help to handle out-of-order messages:

Create the PostServiceContext

Add connection strings for the shards in appsettings.Development.json (you will use two shards during debugging)

Add the DataAccess Code

GetConnectionString(string category) calculates the hash of the CategoryId. The first part of the hash modulo the number of configured shards (connection strings) determines the shard for the given category.

InitDatabase drops and recreates all tables in all shards and inserts dummy users and categories.

The other methods create and load posts.

Register DataAccess as a singleton in Startup.cs

Create the PostController

It uses the DataAccess class

3. Access a Database from the PostService

Install Docker Desktop

Create two MySql Containers (each command as one line)

C:\dev>docker run -p 3310:3306 --name=mysql1 -e MYSQL_ROOT_PASSWORD=pw -d mysql:5.6

C:\dev>docker run -p 3311:3306 --name=mysql2 -e MYSQL_ROOT_PASSWORD=pw -d mysql:5.6

Start the Post service in Visual Studio. The browser opens at http://localhost:5001/swagger/index.html

Use the swagger UI to interact with the service:

Init the Databases with 100 users and 10 categories:

Add a post to “Category1”:

{
  "title": "MyTitle",
  "content": "MyContent",
  "userId": 1,
  "categoryId": "Category1"
}

Read the top 10 posts in “Category1” to see your new post:

Connect to the database containers and verify which database contains the new post.

C:\dev>docker container exec -it mysql1 /bin/sh

Login to MySql with the password “pw” and read the posts:

The second instance does not contain any post:

C:\dev>docker container exec -it mysql2 /bin/sh

4. Final Thoughts and Outlook

You created a working application and implemented application-layer sharding and used the concept of shard keys.

In the second part, you will scale and run multiple container instances of the microservice and database. You will use docker compose and a load balancer. You will then run JMeter load tests to see how the application scales when using a different number of instances. Finally, you will simulate user events from the User microservice via RabbitMQ.

Please contact me if you have any questions, ideas, or suggestions.