Multi-rental: an individual database for each tenant

Question

We are developing a multi-user application. In terms of architecture, we developed a common middle tier for business logic and one database for each tenant to store data. Stating that the business layer will establish a set of connections (connection pool) with the database server for each tenant. This means that the application supports a separate connection pool for each tenant. If we expect about 5,000 tenants, then this solution requires a high load of resources (connections between the application server and the database server for each tenant), which leads to performance problems.

We decided that we support a common connection pool. To support a single connection pool in different databases, we created a new database called "App-master". Now we always connect to the App-master database, and then change the database to a specific tenant database. This solved the connection pool problem.

This solution works fine with the database server at the local level. But it does not work with Azure Sql because it does not support the change database.

Please rate in advance to suggest how to maintain a connection pool or better use an approach / best practice for solving this scenario with multiple tenants.

Answer 1

I have seen this problem before with multiple rental schemes with separate databases. There are two problems of overlap; the number of web servers per tenant and the total number of tenants. Firstly, this is a big problem - if you cache database connections through the ADO.net connection pool, the likelihood that any particular client connection entering the web server that has an open connection to their database is back in proportion to the number of web servers you have. The more you scale, the more any client will notice a delay in one call (not initial registration), since the web server makes the initial connection to the database on their behalf. Every call made to a non-sticky, highly scalable web server tier is less likely to detect an existing open database connection that can be reused.

The second problem is that you have so many connections in your pool, and the likelihood of this creates memory pressure or poor performance.

You can "solve" the first problem by installing a limited number of database application servers (simple WCF endpoints) that communicate with the database on behalf of your web server. Each WCF database application server serves a well-known pool of client connections (the Eastern region goes to server A, the Western region goes to server B), which means a very high probability of getting a connection pool for any given request. It also allows you to scale access to the database separately to access the HTML rendering web servers (the database is the most critical performance bottleneck, so it might not be bad).

The second solution is to use content routing through the NLB router. This routing traffic is content-based and allows you to segment your web server level by client groups (West region, East region, etc.). Therefore, each set of web servers has a much smaller number of active connections with a corresponding increase in the probability of getting open and unused compound.

Both of these problems are caching problems in general, the more you scale as a completely “fuzzy” architecture, the less likely that any call will hit the cached data - whether it be a cached database connection or cached data. Managing user connections to maximize the chance of getting into the cache would be useful to maintain high performance.

Answer 2

Another method to limit the number of connection pools per application server is to use Application Request Routing (ARR) to separate your tenants and assign them to subsets of the web tier. This lends itself to a more scalable pod architecture, where pod is a small collection of web application servers connected to a subset of databases. A good article on this approach is given here: http://azure.microsoft.com/blog/2013/10/31/application-request-routing-in-csf/

If you are building an Azure multi-tenant DB application, you should also check out the new Elastic Scale client libraries, which simplify data-dependent routing and facilitate interlaced queries and management operations. http://azure.microsoft.com/en-us/documentation/articles/sql-database-elastic-scale-documentation-map/