top of page

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

Updated: Jul 10

In C# ASP.NET Core, you can implement load balancing for your service while utilizing app-layer sharding with MySql. This approach showcases important concepts that can also be applied to other databases like MongoDB, among others.

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

Microservice offers numerous advantages, one of which is the ability to scale independently. This comprehensive article explores the benefits and challenges associated with scaling a specific microservice and its corresponding database.


To illustrate the concepts discussed, a practical example application will be created, accompanied by a manual implementation of application-layer sharding. The article will demonstrate how to select an appropriate shard key based on the use-cases and data model, thus enabling the application of similar principles to DBMS solutions with built-in scaling capabilities, such as MongoDB, among others.


This article consists of two parts. In the first part, the focus will be on implementing the microservice and utilizing a sharded database. The second part will delve into scaling and running multiple container instances of both the microservice and databases. Docker Compose and a load balancer will be employed to facilitate this process. Finally, JMeter load tests will be conducted to evaluate the application's scalability when varying the number of instances.


1. Use cases and Data model

The example application comprises two microservices: User and Post. These microservices communicate with each other through messages.


The User microservice is responsible for adding and modifying users, while the Post microservice handles the tasks of viewing and adding posts. As the interaction with the Post microservice is more frequent, it becomes the primary candidate for scaling when the application's load increases.

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

Within the PostService bounded context, the Post microservice manages the author's name. On the other hand, adding and modifying authors is handled by the User microservice. Whenever a new user is added or a username changes, the User microservice emits relevant events.

The logical data model of the PostService is as follows:

Users have the ability to write posts categorized under specific categories. Users can also read posts based on categories, including the author's name. The newest posts appear at the top, and the categories themselves are relatively stable and undergo infrequent changes.


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

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, the decision was made to implement sharding based on the category of posts.

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


2. Implement the Microservice

Here are the step-by-step instructions to implement the microservice:

STEP 1: Install Visual Studio Community, which is a free development environment, including the ASP.NET and web development workload.


STEP 2: Create a solution and add an ASP.NET Core 5 Web API project named "PostService". Disable HTTPS and activate OpenAPI Support.


STEP 3: Install the following NuGet packages:

  • Microsoft.EntityFrameworkCore.Tools

  • MySql.EntityFrameworkCore

  • Newtonsoft.Json


STEP 4: Create the following entities in the "Entities" folder:


Post.cs:

using Microsoft.EntityFrameworkCore;
using System.ComponentModel.DataAnnotations;

namespace PostService.Entities
{    
    [Index(nameof(PostId), nameof(CategoryId))]
    public class Post    
    {
        public int PostId { get; set; }
        public string Title { get; set; }
        public string Content { get; set; }
        
        public int UserId { get; set; }
        public User User { get; set; }        
        
        [Required]
        public string CategoryId { get; set; }
        public Category Category { get; set; }    
    }
}

User.cs:

namespace PostService.Entities
{
    public class User    
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public int Version { get; set; }    
    }
}


Category.cs:

namespace PostService.Entities
{
    public class Category    
    {
        public string CategoryId { get; set; }    
     }
}

STEP 5: Create the "PostServiceContext.cs" file in the "Data" folder:

using Microsoft.EntityFrameworkCore;
namespace PostService.Data
{
    public class PostServiceContext : DbContext    
    {
        private readonly string _connectionString;
        
        public PostServiceContext(string connectionString)        
        {
            _connectionString=connectionString;        
            }
            
            protected override void OnConfiguring ( 
                            DbContextOptionsBuilder optionsBuilder)        
            {                                                         
                optionsBuilder.UseMySQL(_connectionString);        
            }
            
            public DbSet<PostService.Entities.Post> Post { get; set; }
            public DbSet<PostService.Entities.User> User { get; set; }
            public DbSet<PostService.Entities.Category> Category { 
                                                        get; set; }    
     }
 }

STEP 6: Add the connection strings for the shards in "appsettings.Development.json". Here's an example with two shards:

{
    "Logging": {
        "LogLevel": {
            "Default": "Information",
            "Microsoft": "Warning",
            "Microsoft.Hosting.Lifetime": "Information"    
        }  
    },
    "PostDbConnectionStrings": {
    "Shard0": "server=localhost; port=3310; database=post; user=root; 
       password=pw; Persist Security Info=False; Connect Timeout=300",
    "Shard1": "server=localhost; port=3311; database=post; user=root; 
        password=pw; Persist Security Info=False; Connect Timeout=300"  
   }
}


STEP 7: Add the following data access code in the "DataAccess.cs" file within the "Data" folder:

The GetConnectionString(string category) method calculates the hash of the CategoryId. It then uses the first part of the hash modulo the number of configured shards (connection strings) to determine the shard for the given category.


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


The other methods in the file are responsible for creating and loading posts.

using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using PostService.Entities;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;

namespace PostService.Data
{
    public class DataAccess    
    {
        private readonly List<string>  _connectionStrings = new 
                                        List<string>();
        public DataAccess(IConfiguration configuration)        
        {
            var connectionStrings = configuration.GetSection 
                                        ("PostDbConnectionStrings");
            foreach ( var connectionString in 
                                    connectionStrings.GetChildren())            
            {
                Console.WriteLine("ConnectionString: "+ 
                                        connectionString.Value);
                _connectionStrings.Add(connectionString.Value);            
                }        
         }
         
         public async Task<ActionResult<IEnumerable<Post>>> 
                     ReadLatestPosts(string category, int count)        
         {
             using var dbContext = new 
                    PostServiceContext(GetConnectionString(category));
             return await dbContext.Post.OrderByDescending(p => 
                 p.PostId).Take(count).Include(x => x.User).Where(p => 
                     p.CategoryId == category).ToListAsync();        
         }
         
         public async Task<int> CreatePost(Post post)        
         {
             using var dbContext = new 
             PostServiceContext(GetConnectionString(post.CategoryId));
             dbContext.Post.Add(post);
             return await dbContext.SaveChangesAsync();        
         }
         
         public void InitDatabase(int countUsers, int countCategories)        
         {
             foreach (var connectionString in _connectionStrings)            
             {
                 using var dbContext = new 
                             PostServiceContext(connectionString);
                 dbContext.Database.EnsureDeleted();
                 dbContext.Database.EnsureCreated();
                 for (int i=1; i<=countUsers; i++)                
                 {
                     dbContext.User.Add(new User { Name="User"+i, 
                                             Version=1 });
                     dbContext.SaveChanges();                
                 }
                 for (int i=1; i<=countCategories; i++)                
                 {
                     dbContext.Category.Add(new Category 
                                     { CategoryId="Category"+i });
                     dbContext.SaveChanges();                
                }            
         }        
    }
    
    private string GetConnectionString(string category)        
    {
        using var md5 = MD5.Create();
        var hash = md5.ComputeHash(Encoding.ASCII.GetBytes(category));
        var x = BitConverter.ToUInt16(hash, 0) % 
                            _connectionStrings.Count;
        return _connectionStrings[x];        
     }    
  }
}

STEP 8: Register the DataAccess class as a singleton in "Startup.cs":

public class Startup    
{        
    ...
    
    public void ConfigureServices(IServiceCollection services)        
    {
        services.AddControllers();
        services.AddSwaggerGen(c=>            
        {
            c.SwaggerDoc("v1", new OpenApiInfo 
                    { Title="PostService", Version="v1" });            
        });
        services.AddSingleton<DataAccess>();        
    }        
    
    ---


STEP 9: Create the "PostsController.cs" file in the "Controllers" folder:

It uses the DataAccess class

using Microsoft.AspNetCore.Mvc;
using PostService.Data;
using PostService.Entities;
using System.Collections.Generic;
using System.Threading.Tasks;

namespace PostService.Controllers
{    
    [Route("api/[controller]")]    
    [ApiController]
    public class PostsController : ControllerBase    
    {
        private readonly DataAccess_dataAccess;
        
        public PostsController(DataAccess dataAccess)        
        {
            _dataAccess=dataAccess;        
        }        
        
        [HttpGet]
        public async Task<ActionResult<IEnumerable<Post>>> GetLatestPosts(string category, int count)        
        {
            return await _dataAccess.ReadLatestPosts(category, count);        
        }        
        
        [HttpPost] 
        public async Task<ActionResult<Post>> PostPost(Post post)        
        {
            await _dataAccess.CreatePost(post);
            return NoContent();        
        }        
        
        [HttpGet("InitDatabase")]
        public void InitDatabase([FromQuery] int countUsers, 
                        [FromQuery] int countCategories)        
        {
            _dataAccess.InitDatabase(countUsers, countCategories);        
        }    
    }
}


3. Access a Database from the PostService

To access the database from the PostService, follow these steps:

STEP 1: Install Docker Desktop on your machine.


STEP 2: Create two MySQL containers using the following commands:

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

STEP 3: Start the Post service in Visual Studio. The browser will open at http://localhost:5001/swagger/index.html.


STEP 4: Use the Swagger UI to interact with the service:


Initialize the databases with 100 users and 10 categories.

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

Add a post to "Category1" by sending a POST request with the following JSON payload:

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

Read the top 10 posts in "Category1" to see your new post.

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

STEP 5: To connect to the MySQL containers, use the following command:

docker container exec -it mysql1 /bin/sh

STEP 6: Login to MySQL with the password "pw" and read the posts.

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

STEP 7: Repeat steps 5 and step 6 for the second container to verify that it doesn't contain any posts.

docker container exec -it mysql2 /bin/sh
How to use Database Sharding and Scale an ASP.NET Core Microservice Architecture


Congratulations! You have successfully created a working application and implemented application-layer sharding using shard keys. This allows for efficient scaling and management of the database.


Resource: Medium, ITNEXT

0 comments
bottom of page