Managing sharding databases

The AuthP library has a AuthPermissions.SupportCode.ShardingServices namespace which contains services for managing multi-tenant applications that use Sharding. There are two services: AccessDatabaseInformation and IGetDatabaseForNewTenant.

AccessDatabaseInformation service

This service provides methods to a) obtain information about the databases using an a Sharding application, and b) methods to add, update, and remove the database information held in the shardingsettings.json file in development and production.

NOTE: You need to manually register this service as its not part of the AuthP setup. See this code from the application's Program class.

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

a) Method to obtain data about the databases

The ReadShardingSettingsFile method reads the information in the shardingsettings.json file. This is used internally but is also useful for the admin user to see what the shardingsettings.json 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 shardingsettings.json file containing all the database information.

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

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>();

The signature of the FindBestDatabaseInfoNameAsync method is shown below:

/// <summary>
/// This will look for a database for a new tenant when <see cref="TenantTypes.AddSharding"/> is on
/// The job of this method that will return a DatabaseInfoName for the database to use, or an error if can't be found
/// </summary>
/// <param name="hasOwnDb">If true the tenant needs its own database. False means it shares a database.</param>
/// <param name="region">If not null this provides geographic information to pick the nearest database server.</param>
/// <param name="version">Optional: provides the version name in case that effects the database selection</param>
/// <returns>Status with the DatabaseInfoName, or error if it can't find a database to work with</returns>
Task<IStatusGeneric<string>> FindBestDatabaseInfoNameAsync(bool hasOwnDb, string region, string version = null);

Example of the IGetDatabaseForNewTenant service

There are so many options on how you would pick a the right database that there isn't a standard solution. The demo code shown below gives an example 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)

NOTE: The demo code can be found at DemoGetDatabaseForNewTenant.

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;
}