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.

How is this used?

When sharding is turned on the AuthP's Tenant entity class has its DatabaseInfoName property set to the name of database information in the shardingsettings.json. When a user linked to a tenant logs in a "DatabaseInfoName" claim is added to the user containing Tenant's DatabaseInfoName property.

When a instance of the tenant data's DbContext is needed it will get the "DatabaseInfoName" claim is used to obtain the database information in the shardingsettings.json with that name. Then the ShardingConnections service uses that DatabaseInfoName" claim value to build the correct connection string to give the the DbContext.