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 sharding settings json file.

Note: I have written an article called "How to turn an ASP.NET Core appsettings.json file into a fast-read database" which shows how the connection string is created (see this section of the article for description on how this works). The diagram below comes from that article and shows how the final connection string is created by accessing two appsettings files.

NOTE: With the release of AuthP version 5.0.0 you the ability to change parts of how sharding is implemented. For instance you could change where sharding information is stored from a json file to a database. To do this you should copy the SetupMultiTenantSharding extension method in this class and create a new extension method where you replace the AccessDatabaseInformationJsonFile and ShardingConnectionsJsonFile services with your implementations of the two interfaces.

Register of AuthP library

The most important step is to add the AddSharding enum member to the AuthP's option's TenantType along with the type of tenant you want, i.e. single level or hierarchical. This does lots of things like:

• Register the sharding settings file as a Configuration file
• Configures the classes to access the Configuration data via a IOptionsSnapshot<T> service.
• Register all the services to read the settings files to create the correct sharding connection string to a user (see digram above).

NOTE: What it doesn't register is the code to update the sharding setting file. See Managing sharding databases about registering and using services to update the sharding setting file.

The other important thing is to ensure the sharding settings file isn't overwritten on deployment, because the sharding settings file is being used as a database, not a set of configuration settings. This is explained in Managing sharding databases -> setup document.

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 this Azure document which shows how you can configure connections strings (NOTE: Its even easier by using Visual Studio's Publish feature).

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 sharding settings 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 sharding settings json (NOTE: If no sharding settings 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"
    }
  ]
}

See SupportCode -> Managing sharding databases for a service that can edit / create a sharding settings 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 sharding settings 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 implementation 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
• Get new database (sharding)