Managing sharding databases

When you add the enum TenantTypes.AddSharding to the options.TenantType during registation of the the AuthP library various sharding services such as the code to read the sharding settings file to create the correct connection string for each tenant user access. But there are two services within the AuthPermissions.SupportCode.ShardingServices namespace that updates the sharding settings file and this document describes what they do and how to register them. The two services are:

• AccessDatabaseInformation service: This service provides methods to read, add, update, and remove the database information held in the sharding settings.json file
• IGetDatabaseForNewTenant service (optional): If you are using the Sign up for a new tenant, with versioning feature in your sharding multi-tenant application you need to create some code that selects / creates a database to hold the new tenant's data.

The rest of this document explains how to register and use each service.

AccessDatabaseInformation service

Setup / registration of this service

First, you need to manually register this service as its not part of the AuthP setup. See this code from Example6's ASP.NET Core Program class.

//manually add services from the AuthPermissions.SupportCode project
builder.Services.AddTransient<IAccessDatabaseInformation, AccessDatabaseInformation>();

Second, you need to ensure that the sharding settings file isn't overwritten when the application is deployed, because the sharding settings file will already contain data that you want to keep. There are two things you should do to ensure the

First, since AuthP version 3.4.0, then you can alter the sharding settings file by adding the Environment.EnvironmentName to make the sharding settings file's name to be unique to each environment (e.g. Debug, Staging, Production). So for production the sharding settings file name would be "shardingsettings.Production.json". See the setup part of the Sharding database settings for how you can do that.

But the second step is the most important step (but easy to forget), which is to set the file’s “Copy to Output Director” property to “Do not copy”. This stops the database json file being copied in your deployment. You can manually set this via file properties, but I prefer to add a ItemGroup to the ASP.NET Core .csproj file, as shown below.

<ItemGroup>
	<Content Update="shardingsettings.Production.json">
		<CopyToOutputDirectory>Never</CopyToOutputDirectory>
	</Content>
</ItemGroup>

a) Method to obtain data about the databases

The ReadShardingSettingsFile method reads the information in the sharding settings file. This is used internally but is also useful for the admin user to see what the sharding settings file. The screenshot below comes from Example6.MvcWebApp.Sharding web application. If you log in as an application admin user and click the Auth Admin -> List database info.

The code for these can be found in the ShardingController class of Example 6.

b) Methods to add, update, and remove the database information

These are simple to understand. They alter the sharding settings file containing all the database information.

• AddDatabaseInfoToJsonFile(info) - Adds a new database information to the sharding settings file.
• UpdateDatabaseInfoToJsonFile(info) - Updates the database information with the given Name.
• RemoveDatabaseInfoToJsonFileAsync(name) - Removes the database information with the given Name.

NOTE: Since AuthP version 3.4.0 any change is executed within a distributed lock, which ensures that simultaneous updates don't write over each over and loose data. See the section in my article that gives you more information on the distributed lock.

IGetDatabaseForNewTenant service

This service has one method called FindBestDatabaseInfoNameAsync which is used in the Sign up for a new tenant, with versioning feature when the multi-tenant application is using sharding and its job is to return the name of the DatabaseInformation which defines the database the tenant should created in. This is a complex calculation because there many options:

• Does the tenant need a dedicated database, or does it go into a database that has multiple tenants in it? (see this diagram)
• Are you using multiple database servers spread of geographically? In this case you want to pick the nearest server / database.

There is no one solution to this as it depends on your application, so the AuthP library defines the IGetDatabaseForNewTenant interface and you need to implement and then manually register your service, e.g.

//manually add services from the AuthPermissions.SupportCode project
builder.Services.AddTransient<IGetDatabaseForNewTenant, YourGetDatabaseCode>();

Example of the IGetDatabaseForNewTenant service

As explained, there isn't a standard solution so you need to implement your own, but help you there is some demo code with examples of what you might do. The demo code supports the hybrid approach (Shading and non-sharding tenants) but doesn't have multiple database servers spread of geographically. Its goals are:

• Pack all the non-sharding tenants into a database, but not more than 50 tenants.
• Sharding tenants take any empty database.
• If no databases are available it returns an error (you could create a new database and also update the database information in the shardingsettings.json file, but that depends on your production system)

Here is the code from the can be found at DemoGetDatabaseForNewTenant class.

public async Task<IStatusGeneric<string>> FindBestDatabaseInfoNameAsync(bool hasOwnDb, string region, string version = null)
{
    var status = new StatusGenericHandler<string>();

    //This gets the databases with the info on whether the database is available
    var dbsWithUsers = await _shardingService.GetDatabaseInfoNamesWithTenantNamesAsync();

    var foundDatabaseInfoName = hasOwnDb
        ? // this will find the first empty database
          dbsWithUsers
            .FirstOrDefault(x => x.hasOwnDb == null).databaseInfoName
        : // this will find the first database that can be used for non-sharding tenants
        dbsWithUsers
            .Where(x => (x.hasOwnDb == null || x.hasOwnDb == false)
                        // This means there is a limit of 50 shared tenants in any one database
                        && x.tenantNames.Count < 50)
            //This puts the databases that can only contain shared databases first
            .OrderByDescending(x => x.hasOwnDb)
            //This then orders the database with least tenants first
            .ThenBy(x => x.tenantNames.Count)
            .FirstOrDefault().databaseInfoName;

    if (foundDatabaseInfoName == null)
        //This returns an error, but you could create a new database if none are available.
        status.AddError(
            "We cannot create the tenant at this time. Please contact the support team with the code: no db available.");

    return status;
}

Additional resources

• Sharding database settings
• [Part of article explaining how sharding works]