Sharding database settings

When the AddSharding enum member is added to the AuthP's option's TenantType then the way it accesses the multi-tenant databases changes. Without AddSharding being set, then a single database is used, usually defined in the "ConnectionStrings" section of the appsettings file, and provided via dependency injection of the database options. But when the AddSharding enum member is added to the AuthP's options, then you need to dynamically create a connection string to get the a) the correct database server, and b) the correct database on that database server. This uses two parts:

1. Getting the database server from the appsettings file (overridden in Azure)
2. Getting the database name from the shardingsettings.json file.

Note: A detailed overview of this design was used can be found in issue #29.

1. Getting the database server

In AddSharding mode the appsettings file is used to define a connection string without a database name. Its job is to hold the database server, plus any other information needed to use a database in that server. This is very likely to contain parts like username / password which must be kept secret. There are various ways to hide connection strings, such as ASP.NET Core secrets and have Azure override your connection strings.

When using Azure you can override the connection strings via the App Service -> Configuration tab. This overrides any value the ConnectionStrings" section of the appsettings file - see the screenshot from an Azure App Service page.

IMPORTANT NOTE: Do NOT use Azure Key Vault provider because it has a limit of 200 requests / second. Because each tenant user accessing the data has to read the connection string every time, which means if your have lots of simultaneous users that would slow down your application.

NOTE: You do need a database to hold the AuthP admin data (and possibly tenant data, but I don't recommend that) and typically is defined by the "DefaultConnection" connection string, which does include a database name.

2. Getting the database name

In sharding mode you need a extra configuration file called shardingsettings.json. This contains a array of information for each database - known as database information. This contains four properties

• Name: This name is used as reference to database information. This name is held in the Tenant information and ends up in a claim.
• ConnectionName: This contains the name of the connection string the the "ConnectionStrings" section that contains the information to use a database server.
• DatabaseName: This holds the name of the database. If null, then it uses the database in the connection string.
• DatabaseType: This holds the database type, e.g. SqlServer, Postgres.

Here is an example shardingsettings.json (NOTE: If no shardingsettings.json is found, then the code provides single database information called "Default Database" which is linked to the "DefaultConnection" connection string).

{
  "ShardingDatabases": [
    {
      "Name": "DatabaseWest1",
      "DatabaseName": "asp.net-Example6.Sharding_West1",
      "ConnectionName": "WestCoastServer",
      "DatabaseType": "SqlServer"
    },
    {
      "Name": "DatabaseCentral1",
      "DatabaseName": "asp.net-Example6.Sharding_Central1",
      "ConnectionName": "CentralServer",
      "DatabaseType": "SqlServer"
    },
    {
      "Name": "DatabaseEast1",
      "DatabaseName": "asp.net-Example6.Sharding_East1",
      "ConnectionName": "EastCoastServer",
      "DatabaseType": "SqlServer"
    }
  ]
}

NOTE: You need to set the shardingsettings.json file to NOT be copied to your production system when you deploy your application. You need to do that because you don't want the file overridden when you deploy a new version of your application. To do this go the properties of the shardingsettings.json file and set the following settings.

• Build Action to "None"
• Copy to Output Directory to "Do not copy"

See SupportCode -> Managing sharding databases for a service that can edit / create a shardingsettings.json file.

The ShardingConnections service

This service is automatically registered if the TenantType options property contains the AddSharding enum member. The services main job is to create a connection string every time the tenant DbContext is created - see the Example 6 DbContext where the IGetShardingDataFromUser service called the ShardingConnections's FormConnectionString(databaseInfoName).

Other methods within the ShardingConnections code contains various methods to obtain information from the shardingsettings.json file and their use.

The method GetDatabaseInfoNamesWithTenantNamesAsync is useful because it provides a view of how the databases are used telling if the database is empty, has a single Sharding tenant, or a database which can contain multiple (non-sharing) tenants. This is used in the an implmemation of the IGetDatabaseForNewTenant service (see Get new sharding database) when using the "sign up / versioning" feature.

The GetDatabaseInfoNamesWithTenantNamesAsync method is also useful to the admin user to display is a list of databases showing what tenants are in each database and whether the tenant is using Sharding, i.e. they have their own database. The screenshot was taken from Example 6 project (code is found in the TenantController class of Example 6).

This is useful as the "Sharding?" column tells you the sharding setting for each database:

• False: The database is holding tenant(s) that aren't using sharding, i.e. HasOwnDb is false
• True: The database is holding one tenant that is using sharding, i.e. HasOwnDb is true
• Empty: The database hasn't got any tenants in it, i.e. HasOwnDb is null

NOTE: This method will always False for the the database with default connection string as that database contains AuthP's data.

Additional resources

• Managing sharding databases
• ??