Database per Microservice Using MS SQL and Cosmos DB in .NET Microservices: Product and Order Services

In a microservices architecture, it is often recommended to have a database per microservice approach to ensure that each service is isolated and independent. This isolation allows services to evolve independently, without affecting other services. However, this also brings challenges, such as managing data consistency across services. In this blog, we’ll explore how to implement a database-per-microservice strategy using MS SQL for one service and Cosmos DB for another in a .NET microservices architecture, specifically for our Product and Order services.

Embark on a journey of continuous learning and exploration with DotNet-FullStack-Dev. Uncover more by visiting our https://dotnet-fullstack-dev.blogspot.com reach out for further information.

Why Database Per Microservice?

• Decentralized Data Management: Each microservice manages its own database, allowing teams to choose the database technology that best fits their service’s requirements.
• Scalability: Services can scale independently, including their databases.
• Flexibility: Different services can use different types of databases (relational, NoSQL, etc.) based on their data storage needs.
• Fault Isolation: A failure in one service’s database does not affect other services.

Architecture Overview

• Product Service: Uses MS SQL as its database. MS SQL is a relational database that provides strong consistency and transaction support.
• Order Service: Uses Cosmos DB as its database. Cosmos DB is a globally distributed NoSQL database designed for high availability and low latency.

Step 1: Setting Up MS SQL for Product Service

1.1 Install and Configure MS SQL Server

First, ensure you have MS SQL Server installed and running. You can use SQL Server Management Studio (SSMS) to create a database for the Product Service.

1.2 Create the Database and Tables

Create a database named ProductDB and a table for storing product information:

CREATE DATABASE ProductDB;
GO

USE ProductDB;
GO

CREATE TABLE Products (
    Id INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(100) NOT NULL,
    Description NVARCHAR(255) NULL,
    Price DECIMAL(18, 2) NOT NULL
);

1.3 Configure Entity Framework Core in Product Service

In the Product Service, configure Entity Framework Core to use MS SQL Server.

Install EF Core SQL Server Package:

dotnet add package Microsoft.EntityFrameworkCore.SqlServer

Add the DbContext:

Create a ProductDbContext class:

public class ProductDbContext : DbContext
{
    public ProductDbContext(DbContextOptions<ProductDbContext> options) : base(options)
    {
    }

    public DbSet<Product> Products { get; set; }
}

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public decimal Price { get; set; }
}

Configure the DbContext in Program.cs:

builder.Services.AddDbContext<ProductDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("ProductDbConnection")));

builder.Services.AddControllers();

Add Connection String in appsettings.json:

{
    "ConnectionStrings": {
        "ProductDbConnection": "Server=your_server;Database=ProductDB;User Id=your_user;Password=your_password;"
    }
}

Apply Migrations:

dotnet ef migrations add InitialCreate
dotnet ef database update

Step 2: Setting Up Cosmos DB for Order Service

2.1 Configure Azure Cosmos DB

First, ensure you have an Azure Cosmos DB account. You can create a new database and container for storing orders.

2.2 Create the Database and Containers

In Azure Cosmos DB, create a database named OrderDB and a container named Orders with OrderId as the partition key.

2.3 Configure Cosmos DB in Order Service

Install Cosmos DB Package:

dotnet add package Microsoft.Azure.Cosmos

Add the Cosmos DB Client:

Create a CosmosDbContext class:

public class CosmosDbContext
{
    private CosmosClient _cosmosClient;
    private Container _container;

    public CosmosDbContext(CosmosClient cosmosClient, string databaseName, string containerName)
    {
        _cosmosClient = cosmosClient;
        _container = _cosmosClient.GetContainer(databaseName, containerName);
    }

    public async Task AddOrderAsync(Order order)
    {
        await _container.CreateItemAsync(order, new PartitionKey(order.OrderId));
    }

    public async Task<Order> GetOrderAsync(string orderId)
    {
        try
        {
            ItemResponse<Order> response = await _container.ReadItemAsync<Order>(orderId, new PartitionKey(orderId));
            return response.Resource;
        }
        catch (CosmosException)
        {
            return null;
        }
    }
}

public class Order
{
    public string OrderId { get; set; }
    public string ProductId { get; set; }
    public int Quantity { get; set; }
    public DateTime OrderDate { get; set; }
}

Configure the Cosmos DB Client in Program.cs:

builder.Services.AddSingleton<CosmosClient>(serviceProvider =>
{
    var configuration = serviceProvider.GetRequiredService<IConfiguration>();
    var cosmosDbSettings = configuration.GetSection("CosmosDbSettings").Get<CosmosDbSettings>();
    return new CosmosClient(cosmosDbSettings.ConnectionString);
});

builder.Services.AddSingleton<CosmosDbContext>(serviceProvider =>
{
    var cosmosClient = serviceProvider.GetRequiredService<CosmosClient>();
    return new CosmosDbContext(cosmosClient, "OrderDB", "Orders");
});

builder.Services.AddControllers();

Add Cosmos DB Settings in appsettings.json:

{
    "CosmosDbSettings": {
        "ConnectionString": "your_cosmos_db_connection_string"
    }
}

Step 3: Implementing CRUD Operations

3.1 Product Service CRUD Operations

Get Products:

[HttpGet]
public async Task<IActionResult> GetProducts()
{
    var products = await _context.Products.ToListAsync();
    return Ok(products);
}

Create Product:

[HttpPost]
public async Task<IActionResult> CreateProduct([FromBody] Product product)
{
    _context.Products.Add(product);
    await _context.SaveChangesAsync();
    return CreatedAtAction(nameof(GetProductById), new { id = product.Id }, product);
}

3.2 Order Service CRUD Operations

Create Order:

[HttpPost]
public async Task<IActionResult> CreateOrder([FromBody] Order order)
{
    await _cosmosDbContext.AddOrderAsync(order);
    return CreatedAtAction(nameof(GetOrderById), new { id = order.OrderId }, order);
}

Get Order:

[HttpGet("{id}")]
public async Task<IActionResult> GetOrderById(string id)
{
    var order = await _cosmosDbContext.GetOrderAsync(id);
    if (order == null) return NotFound();
    return Ok(order);
}

Conclusion

In this blog, we’ve explored the database-per-microservice approach using MS SQL and Cosmos DB in a .NET microservices architecture. The Product Service used a relational database (MS SQL), while the Order Service used a NoSQL database (Cosmos DB). This approach provides flexibility, allowing each service to choose the best storage technology based on its needs. While this adds complexity in terms of data consistency and management, it offers significant benefits in terms of scalability, flexibility, and fault isolation. By following this guide, you can effectively implement a database-per-microservice pattern in your .NET microservices architecture.