How to use Database Sharding and Scale an  ASP.NET Core Microservice Architecture
Tags: microservices, architecture, dotnet, webdev
Published: 2021-06-13 19:37:25

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

![](https://cdn-images-1.medium.com/max/2000/1*qfNgu11fAZHUkZeohvUzzA.png)

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](https://dev.to/alex_barashkov/microservices-vs-monolith-architecture-4l1m) and [Database Sharding Explained- 2021 Database Scaling Tutorial](https://dev.to/renaissanceengineer/database-sharding-explained-2021-database-scaling-tutorial-5cej)*

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**:

![](https://cdn-images-1.medium.com/max/2000/1*DN2QzWP1oFpvFM0fh9gKrQ.png)

*See also my previous article [How to Build an Event-Driven ASP.NET Core Microservice Architecture](https://dev.to/christianzink/how-to-build-an-event-driven-asp-net-core-microservice-architecture-4fnh)*

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*

![](https://cdn-images-1.medium.com/max/2000/1*N-i4KoRko3WrIakewYLykQ.png)

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:**

![](https://cdn-images-1.medium.com/max/2056/1*vBwiakd1uV_MnOKckNe-8A.png)

## 2. Implement the Microservice

**Install [Visual Studio Community](https://visualstudio.microsoft.com/en/vs/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:

{% gist https://gist.github.com/Chriz76/a13e630c3ddd5f178d9d4df72f94a702
%}

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

{% gist https://gist.github.com/Chriz76/99be5e410edf379c0f0fd9c16bfa21db
%}

{% gist https://gist.github.com/Chriz76/a9d290ce76ccb4caee9fa5dd3fd41091
%}

**Create the PostServiceContext**

{% gist https://gist.github.com/Chriz76/35412fb32d4e0d90b1d94307beee4cae
%}

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

{% gist https://gist.github.com/Chriz76/9ba14c4d3e54947835e22117daee4d15
%}

**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.

{% gist https://gist.github.com/Chriz76/d0d12470046f503cad10954fe6474af3
%}

**Register DataAccess as a singleton in Startup.cs**

{% gist https://gist.github.com/Chriz76/1f1f7f674fbf1a33fec9070fb950890d
%}

**Create the PostController**

It uses the *DataAccess* class

{% gist https://gist.github.com/Chriz76/796ae650b31a067b6a1f34f7b36ee943
%}

## 3. Access a Database from the PostService

**Install [Docker Desktop](https://hub.docker.com/editions/community/docker-ce-desktop-windows)**

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:

![](https://cdn-images-1.medium.com/max/2000/1*fBl-wzNlTuKb9U2O9eJuPA.png)

**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:

![](https://cdn-images-1.medium.com/max/2000/1*qrfNkc28IBcL-Lp7Qg3ONQ.png)

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:

![](https://cdn-images-1.medium.com/max/2000/1*di3f8KF_WZBu0NY59Mno8w.png)

The second instance does not contain any post:

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

![](https://cdn-images-1.medium.com/max/2000/1*GVUXZpvsfZIjDcSTr8JM2Q.png)

## 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**](https://dev.to/christianzink/how-to-scale-an-asp-net-core-microservice-and-sharded-database-load-test-with-jmeter-9mm), you will **scale** and [**run multiple container instances**](https://dev.to/christianzink/how-to-scale-an-asp-net-core-microservice-and-sharded-database-load-test-with-jmeter-9mm) 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.**