MultiTenant versus Multiple DBs

I am developing a custom CRM solution which will be sold via the Web/SaaS model. I anticipate tens or hundreds of clients using this solution. I will be using MS SQL as the db engine.

Option 1 is to have a single DB, and include a TenantId column on tables, a suitable index and use 'where tenantId={...}' on each db access.

Option 2 is to have an individual DB for each client, avoiding the need for the TenantId and where clauses.

I anticipate that each client will have hundreds of thousands of records, not millions.

As I see it, there will be a total number of data pages whichever option I go for. The decision seems centered on whether SQL is better at managing multiple DBs, or a single DB with TenantId 开发者_如何学Cand index. Initially the solution will run on a single DB server, but will eventually move to SAN.

Does anyone have any views on this?

There is an interesting MSDN article, titled Multi-Tenant Data Architecture, which you may want to check out. The authors make a brief analysis on where a certain approach might be more appropriate than another:

The number, nature, and needs of the tenants you expect to serve all affect your data architecture decision in different ways. Some of the following questions may bias you toward a more isolated approach, while others may bias you toward a more shared approach.

• How many prospective tenants do you expect to target? You may be nowhere near being able to estimate prospective use with authority, but think in terms of orders of magnitude: are you building an application for hundreds of tenants? Thousands? Tens of thousands? More? The larger you expect your tenant base to be, the more likely you will want to consider a more shared approach.

• How much storage space do you expect the average tenant's data to occupy? If you expect some or all tenants to store very large amounts of data, the separate-database approach is probably best. (Indeed, data storage requirements may force you to adopt a separate-database model anyway. If so, it will be much easier to design the application that way from the beginning than to move to a separate-database approach later on.)

• How many concurrent end users do you expect the average tenant to support? The larger the number, the more appropriate a more isolated approach will be to meet end-user requirements.

• Do you expect to offer any per-tenant value-added services, such as per-tenant backup and restore capability? Such services are easier to offer through a more isolated approach.

Note that the "shared approach" is option 1, and the "isolated approach" is option 2, in your case. You are not biased on either side when it comes to the first two points, so I think I would base my decision on the last two points.

If you dont have to link data between the tenants, your go best with having multiple databases. Maintenance is easier, setup is easier and performance will be much better. When having data from multiple tenants in one table, table locks and search querys over big tables can and most likely will slow down your solution.

The only reason to share one db i would see if you have very much clients and very low number of rows per client.