Changes needed to the sharding feature to work in production

[JonPSmith]

Version 3.0.0 introduced a sharding approach which allows a multi-tenant application to use multiple databases to place tenant data into. The version 3.0.0 used the "ConnectionStrings" section of the appsettings file, using the names of the connection strings, e.g. "DefaultConnection" to pick the database.

However, when I tried to run the sharding example app on Azure I found some issues that would make updating the database information while the application was running pretty difficult. These issues have made me change the way of handing multiple databases, and especially how you would add (or remove) databases in production. This issue explains the problem and then describes the solution (which will be in version 3.2.0).

The Problem

Connection strings contain secrets, e.g. user name and password for the database server. While can define the connection strings when you deploy, then recommended way in Azure is to set up the connection strings in the Azure App Service via the Settings -> Configuration -> Connection strings feature.

There is a way to add/change a connection string via an API in Azure, but that only works for Azure and I want the library to be used in any web server, e.g. AWS, Docker etc.

The assumptions / rules

So, I assume any web server / database server will have a way to hold the database connection string in a private way. I also assume that if you are going to use multiple databases (for a sharding multi-tenant application) that one database server will have multiple databases. But I also allow for multiple database servers, say if you want some servers / database geographically spread.

The solution

1. I use the "ConnectionStrings" section of the ASP.NET Core appsettings file to hold the private information for a database server. Azure has a good approach for that.
2. I create a second section called "ShardingData" in an extra appsettings file which holds a array with each database that can be used by the sharding multi-tenant app - known as database information. The information for a database has the following information:
   • Name: This is a reference to this 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 mentions in 1.
   • 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, etc.

So when a user that it linked to a tenant logs in, then the Name of the database information is added to their claims. When the user wants to access the data in their parts of the database, then the following steps are taken:

1. The Name claim is used to find the database information in the configuration
2. It then gets the connection string from the "ConnectionStrings" section
3. Then using the correct SQL connection string builder (based on the DatabaseType) to create a connection string with the database server information form the "ConnectionStrings", but with the database name set from the DatabaseName parameter in the database information.

All of these steps are handled by the IShardingConnections service. It is possible that someone's application might need something changed, so I have added a way to replace the default ShardingConnections code. NOTE: Until version 3.2.0 is out the links to IShardingConnections and ShardingConnections are using the version 3.0.0 code.

Implementation specifics

• I use IOptionsSnapshot<T> to access the "ConnectionStrings" and "ShardingData" sections. This means if the data is changed, then the latest data is used. The IOptionsSnapshot<T> method is very fast.
• I create a separate file called shardingsettings.json and registered to ASP.NET Core's Configuration. Having its own file has two benefits:
  • You want the shardingsettings.json file in production created by some admin code, and you definitely don't it overwritten when you deploy an updated version of your application - see NOTE1 and NOTE2 for how you do this.
  • It makes it much easier to update the file because it only contains the database infomation.

NOTE1: You need to set the shardingsettings.json file properties shown below to stop the file from sent to the web server when you deploy your application:

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

NOTE2: If no shardingsettings.json is found, then it sets up a default database, which is linked directly to the "DefaultConnection" connection string.

[JonPSmith]

This change was implemented in version 3.2.0.