Multitenancy is when several customers share the same resources.

In this article I will focus on the scenario where you want to keep each of your customers in a separate database, but you want to minimize the effort to maintain your code. Using a shared DbContext also minimize the risk of bugs. Many of the principles are valid no matter the platform you use (Azure, AWS etc.), but I will mostly use my knowledge of Azure and Microsoft technologies. This article will contain knowledge I wish existed (or was easier to find) when I started working on a huge project many years ago.

Why would you separate the customers in different databases?

1. Data Isolation and Security

• Improved Data Security: With separate databases, you can ensure that each customer's data is completely isolated. This reduces the risk of data leakage or cross-tenant data exposure, as a breach in one database won't affect others.

• Compliance Requirements: Certain industries or regions have strict data handling regulations (e.g., GDPR, HIPAA) that might require customer data to be stored separately for legal reasons.

2. Scalability and Performance

• Individual Performance Scaling: If a customer’s data or traffic grows significantly, you can scale their database independently without affecting other customers. This flexibility prevents one large customer from slowing down the entire system by using all of the available resources.

• Azure elastic pool: If you have several small customers, or with a sporadic usage pattern, you can use an elastic pool to share the resources and save money on not having to commit individual resources to each customer.

• Database Optimization: You can fine-tune the database configuration (e.g., indexes, partitioning) for each tenant's specific needs, leading to better performance and more efficient resource usage.

3. Customization and Flexibility

• Customer-Specific Requirements: Some customers may have unique needs for data storage, like custom schema extensions, different data types, or retention policies. With separate databases, you can accommodate these differences without impacting other tenants.

• Custom Backup and Restore: Separate databases allow individual backup strategies and the ability to restore a specific customer's data without disrupting others.

4. Improved Fault Tolerance

• Fault Isolation: If one database encounters corruption, downtime, or performance issues, it affects only that tenant. Other tenants can continue operating normally. This increases overall system resilience.

• Easier Disaster Recovery: With separate databases, it’s easier to restore a specific customer’s data from backups in case of accidental deletion or corruption.

5. Data Sovereignty and Regional Hosting

• Regional Data Residency: For global customers, you might need to store data in specific geographical regions to comply with data sovereignty laws. Separate databases make it easier to host customer data in the appropriate region.

• Tailored Maintenance Windows: You can perform maintenance, updates, or migrations on one database at a time, giving individual tenants more control over their service windows.

6. Simpler Migration and Exit Strategy

• Easier Tenant Migration: If a customer decides to leave or move to a different system, exporting their data is much easier when it's stored in a separate database. Similarly, onboarding new customers can be simpler if they have their own database.

• Vendor Lock-In Reduction: Separate databases provide flexibility, reducing the risk of locking in customers. If they outgrow the multitenant platform, migrating their data elsewhere is less cumbersome.

7. Different SLAs for Different Customers

• Tailored Service-Level Agreements (SLAs): High-value or large customers may demand different SLAs, such as higher availability or more frequent backups. With separate databases, you can cater to these needs more easily, offering tiered service levels.

8. Easier Auditing and Monitoring

• Isolated Auditing: Separate databases allow more granular auditing of each tenant’s data and actions. This can be particularly important for industries that require detailed logging for compliance.

• Simplified Monitoring: Tracking performance, errors, and data growth on a per-customer basis is easier when customers are isolated in their own databases.

Separating customers into individual databases can lead to greater control, customization, and security, even though it comes with trade-offs like increased management complexity. Whether you choose a shared or separate model depends largely on your application’s use case, the nature of your customer base, and performance/security requirements. If a customer ever needs access to ALL of their data, it is super simple.

Template database

1. Easier onboarding

To make onboarding easier I have found it very helpful to keep a template database. This way you can either just copy the template database manually and set it up, or you can automate the process and for example use an Azure Resourcemanager Library. This is a choice you have to make depending on your requirements. If you will only have a new customer once in a while you can probably get by with the manual approach. But if you are like me, you will want to automate this process. This way you are better prepared for scaling your business.

2. Easier database migrations

Updating: When you have multiple databases, you also need a way to make sure all databases follow the correct schema. If there are missing tables, columns or views, your application might not work at all. With the template database always updated to the current production version of your scheme you can always either copy it or compare it to a specific customer database. Because, and be honest now, mistakes do happen. And sometimes you might have a customer database out of sync with current scheme. Maybe you had to restore the database from a backup? There are great tools out there to do the job for you. A great example is RedGate SQL Compare.

3. Pre-defined settings/data

If there is a need to populate the databases of newly created customers with certain data you can just add it to the template database. It could be settings, or a list of some data. Drawback with this way of maintaining pre-defined settings/data is that your existing customers won't get new data you add to your template database. This can of course be remedied easily with a database script that you run on all existing databases, or through code, if that is your choice.

(Customer)DbContext

Setting up a single DbContext with your DI (Dependency Injection) is easy.

• First you need to create your Context, let us call it SharedContext.cs:

using Microsoft.EntityFrameworkCore;
namespace Test.Contexts;
public class SharedContext : DbContext
{
   // Imagine that we have a class/entity named Document (Id: int, Title: string, Content: string)
   public DbSet<Document> Documents { get; set;

   public SharedContext(DbContextOptions<SharedContext> options) : base(options)
   {
   }
}

• In your program.cs or startup.cs (depending on your project type)

var connectionstring = Config["MyConnectionString"];
// could also be builder.services.AddDbContext...
services.AddDbContext<SharedContext>(options =>
        {
            options.UseSqlServer(connectionstring, o => o.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery));
        });

• Usage example from DocumentManager.cs

namespace Test.Managers;
public class DocumentManager
{
  private readonly SharedContext _sharedContext;
  public DocumentManager(
     SharedContext sharedContext
  )
  {
     _sharedContext = sharedContext;
  }

  public async Task<List<Document>> GetDocuments()
  {
    return _sharedContext.Documents.ToListAsync();
  }
}

Right now the SharedContext only connects with one database. Our goal is to be able to choose which database it should connect to depending on the user. Injecting the connectionstring at runtime is not very difficult, but it takes some planning to do it in a simple way. I have found that keeping a main or master database where I keep track of my customers, users, user-customer relationship and other useful data that you need to keep track of for your application is very helpful.

If a user can only be associated with one customer at any time, you can resolve which customer database to reach from the logged in user in your application.

If a user can be associated with mulitiple customers you will also need to send a value in the request that identifies which customer the user is currently interacting with.

I will normally have an endpoint the user can reach where they can retreive their customer associations/logins. This way my frontend application can have some sort of mechanism to switch between different customers. My frontend will send an Http header - SelectedCustomerId, so that I can resolve which customer the user is interacting with.

ALWAYS validate the SelectedCustomerId against the users allowed logins in the backend, not doing so could open your application/api to severe security vulnerabilities. So let us make some changes.

• We need access to info about the current user:

public interface ICurrentUserDataAccessor
{
    Task<CurrentUserData> GetCurrentUserDataAsync();
}

public class CurrentUserDataAccessor(IHttpContextAccessor httpContextAccessor, MainContext mainContext) : ICurrentUserDataAccessor
{
    public async Task<CurrentUserData> GetCurrentUserDataAsync()
    {
        // Get the User ID from the current user, adjust to your needs
        var userId = httpContextAccessor.HttpContext.User.FindFirst(ClaimTypes.NameIdentifier)?.Value;

        var customerId = getSelectedCustomerId();

        // Gets the customer relation. 
        // Throws an exception if none or more than one is found. 
        // If customerId is NULL, but user can only access one Customer we can still resolve the current customer. 
        var customer = await mainContext.UserCustomers
            .Where(uc => uc.UserId == userId && (!customerId.HasValue || uc.CustomerId == customerId))
            .Select(uc => uc.Customer)
            .SingleAsync();


        return new CurrentUserData
        {
            UserId = userId,
            CustomerId = customer.Id,
            DatabaseName = customer.DatabaseName,
            // Add the other properties you need to fit your use case
            ServerName = customer.ServerName,
            DatabaseUsername = customer.DatabaseUsername
        };
    }

    private int? getSelectedCustomerId()
    {
        var context = httpContextAccessor.HttpContext;
        // Check if the user is authenticated
        if (context == null || !context.User.Identity.IsAuthenticated)
        {
            throw new KeyNotFoundException("Userinfo data could not be resolved. User not authenticated.");
        }

        // Get the selected customer ID from the request headers
        context.Request.Headers.TryGetValue("selected-customer-id", out var selectedCustomerId);

        // Try to parse the selected customer ID, and return it if successful
        if (int.TryParse(selectedCustomerId, out var currentCustomerId))
        {
            return currentCustomerId;
        }

        return null;
    }
}

• We need an accessor to retrieve a SharedContext. In this example we connect to a Sqlite database, but there are extension methods for lots of other types as well.

public interface ISharedContextAccessor
{
    Task<SharedContext> GetSharedContextAsync();
}
public class SharedContextAccessor : ISharedContextAccessor
{
    private readonly ICurrentUserDataAccessor _currentUserDataAccessor;
    private readonly Lazy<Task<SharedContext>> _sharedContext;


    public SharedContextAccessor(
        ICurrentUserDataAccessor currentUserDataAccessor
    )
    {
        _currentUserDataAccessor = currentUserDataAccessor;

        // This is a lazy initialization of the SharedContext
        this._sharedContext = new Lazy<Task<SharedContext>>(async () =>
        {
            var currentUserData = await _currentUserDataAccessor.GetCurrentUserDataAsync();
            if (currentUserData == null)
            {
                throw new Exception("CustomerContext can't be created. No customer selected/ or user has no access to any customer");
            }

            if (string.IsNullOrEmpty(currentUserData.DatabaseName))
            {
                throw new Exception("CustomerContext can't be created. Missing database information");
            }

            var connectionstring = ConnectionStringHelper.GetConnectionString(
                currentUserData.DatabaseName
                );

            var optionsBuilder = new DbContextOptionsBuilder<SharedContext>();
            // This is the important part, it tells your code which database to use
            optionsBuilder.UseSqlite(connectionstring, o => o.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery));
            return new SharedContext(optionsBuilder.Options);
        });
    }

    public async Task<SharedContext> GetSharedContextAsync()
    {
        return await _sharedContext.Value;
    }
}

• A helper class for getting the connection string

public class ConnectionStringHelper
{
    // This method is used to create the connection string
    // Right now it is set up for Sqlite3, but you can adjust it to your needs.
    public static string GetConnectionString(
            string databaseName
        ) =>
            $"Data Source=Databases/{databaseName};";
}

• We need a centralized place to keep track of customers, users and the relationship between customers and their users.

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

    public DbSet<User> Users { get; set; }
    public DbSet<Customer> Customers { get; set; }
    public DbSet<UserCustomer> UserCustomers { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<UserCustomer>()
            .HasKey(uc => new { uc.UserId, uc.CustomerId });
    }
}

• The entity classes (just simple examples)

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string DatabaseName { get; set; }
    public string ServerName { get; set; }
    public string DatabaseUsername { get; set; }
}

public class User
{
    public string Id { get; set; } = Guid.NewGuid().ToString();
    public string Username { get; set; }
    public string Email { get; set; }
    public string PasswordHash { get; set; }
    public string SecurityStamp { get; set; }
    public string ConcurrencyStamp { get; set; }
}

// A simple join entity between User and Customer
public class UserCustomer
{
    public string UserId { get; set; }
    public User User { get; set; }
    public int CustomerId { get; set; }
    public Customer Customer { get; set; }
}

After making these changes we could still use the SharedContext the same way in the DocumentManager.cs from above. As long as the user is authenticated and there is a selected-customer-id header set in the Http-Request you will get data from the correct database. You will of course need to maintain your Customers, Users and their relationships in some way. The code above is in many cases simplified and would not satisfy requirements for a production environment, but it is meant to hopefully give a basic understanding of how multi tenancy can be solved.

If you are interested in seeing a more complete code example you can head over to my GitHub.

I hope you found this helpful.

Powered by Synscribe