One Application Multiple Instances and Different DB

Question

I am building an case management application and one of the requirements is that each client gets their own Database with their own URL. However, it is starting to be a nightmare to maintain multiple instances of the application when upgrading. I am using IIS 7 ASP.NET MVC. I would like to have one application and have the application be aware of which database to get the data from depending on the User authentication. Is there a viable alternative?

Answer 1

Yes its better to have one instance of the application if possible otherwise it gets complicated. If the features for different clients are different then use feature flags to turn-on/turn-off features for each client/user. See below article by Martin Fowler about it. Feature flags can be per user or client. Facebook and few other major sites use feature flags effectively.

http://martinfowler.com/bliki/FeatureToggle.html

Regarding the database, I think you can have a Common database which has all basic information including all clients and then have other databases specific to each client which has all other data for the client.

When any user hits a client specific URL in the Session_Start method of Global.asax add logic to fetch the appropriate connection string and store it in session (Session["ClientDbConnectionString"]) so that you can use it whenever you need data from client DB.

I would suggest you to store all the connection strings in a table in Common database (with a key identifying each client) so that you can add new connection string row when you want to on-board a new client. Whenever you do a new release I would suggest to update all the client databases together instead of just updating one client DB otherwise it will become unmanageable after a while.

Answer 2

Your question is really just a tip-of-the-iceberg question. Multi-tenancy is a complex topic. Having a per-client connection to the tenant database is just one aspect. There are other aspects to consider:

• balancing of tenants: how do you ensure that tenants that grow much faster than the average get adequate resources and do not overwhelm other tenants that are collocated on the same storage (eg. same instance). How do you move a tenant that had grown? How do you aggregate small tenants with low activity?
• isolation of resources: a tenant can consume a large percent of resources (eg. it can run a query in the database that take sup all the CPU and starves all the other queries), how do you ensure fairness to other tenants that are collocated?
• shared data. Replicating changes to shared data (data that is common among all tenants, eg. lookup tables) can be problematic.
• schema changes. This is one of the trickiest topics. Database schema and code are usually changed in-sync (code expects a certain schema) and deploying a schema change to all tenants can be problematic.

I recommend you go over the Multi-Tenant Data Architecture white paper. This presents three basic approaches:

• Separate Databases
• Shared Database, Separate Schemas
• Shared Database, Shared Schema

In addition, I would add the SQL Azure Federations option (which was not available at the time the white paper was written). The paper discuss pros and cons of these approaches from database/storage point of view, considering things like:

• storage cost
• security
• availability
• scalability
• ease of schema change (app upgrade)
• extensibility

From the client side, I'm not aware of any MVC extension that helps for the multi-tenant case, something along the line of the act_as_tenant gem in Rails.

is starting to be a nightmare to maintain multiple instances of the application when upgrading.

This is actually one of the biggest problems in multi-tenant architectures. Even if you have eliminated the friction of actually doing the upgrade in a DB, it is still a difficult problem to solve. Unless you can afford downtime on all tenants and take the entire system offline, upgrade all tenants databases, deploy new code that understands new schema, then bring all tenants online, doing it online is challenging because the app code (the ASP/MVC code) must be able to understand both versions (old and new) at once. Is not impossible to solve, but is difficult and must be coded carefully.

That being said, an important part if 'eliminating the friction of actually upgrading'. I do no what procedure you employ to deploy an upgrade. It is critical that the upgrade is automated and scripted, without any manual intervention. Diff based tools are sometimes used, like SQL Compare from Red-Gate or even Visual Studio's vsdbcmd.exe. My favorite approach though is using upgrade scripts and metadata versioning in the application. For more details see Version Control and your Database. For reference, this approach is basically the Rails Migrations approach.