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. In this case you need a service as it will be called to obtain the DatabaseInfoName for the new tenant. The signature of the 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);

There are so many options on how you would pick a the right database to use that there isn't a standard solution. The demo code shown below gives an example of what you might do. In this demo it tries to put all the non-sharding tenants into databases that already have non-sharding tenants, but won't add more than 50 non-sharding tenants into one database.

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

NOTE: In this demo code it returns an error if no suitable database can be found. But you could add extra code to create a new database and also update the database information in the shardingsettings.json file.