Which Multi-tenant approach is recommended with SQL Server 2008

2
votes

I have to use ASP.NET MVC 3 or above and SQL Server 2008. As per Multi-Tenant Data Architecture post, there are 3 ways to implement multi-tenancy

  1. Separate Databases

  2. Shared Database, Separate Schemas

  3. Shared Database, Shared Schema

I have following details:

  1. User should be able to backup and restore their data.
  2. No of tenants : 1000 (approx)
  3. Each tenant might belong to different domain(url).
  4. It must support monitoring and management of tenants.
  5. It must support user authentication and authorization for each tenant
  6. It must support tenant customization(enable disable features set)
  7. No of tables in each tenant: 100 (initial)

I would like to know what your experience says about which approach is more suitable for the project considering Economic and Security? Is there any good real time example(open source project) similar to this? I can use one dedicated server for the project.

2
sql-serverasp.net-mvcdatabase-designarchitecturemulti-tenant

2 Answers

1
votes

Your requirement that users should be able to backup its data, can be achivable more easily with approachs 1 and 2... since it will be a native database task.

If you are in approach 3 (shared-shared), you will need to develop the logic to extract all the rows belonging to a single tenant and export it in a xml file or something like that. Then if you need to allow users to restore that backup file, you need to develop a restore logic.

I think this is the only requirement that could make you move away from #3.

Once you set your database using TenantID columns in your table... you can easily use one database for 1 tenant or a small group of tenants if your client is heavily concerned about security. For instance, you could have one database holding tenants that are not paying (free/demo accounts) and paying customers in another one. This way you are using approach #3, but being able to behave as #1 if you need it.

::: BONUS :::

AUTHENTICATION: You will need to extend the SQL Membership and Role Providers used in your MVC3 app... so that a user login is valid only in the Tenant it belongs to.

MULTIPLE DOMAINS Here you can see some approaches using ASP.NET MVC3 Routing: MVC 3 Subdomain Routing

0
votes

I would always use (3) Shared Database, Shared Schema.

If you want an example, how about Wordpress, Joomla, or any other popular open source web-based project?

Creating separate schemas or databases on a per-tenant basis will lead to massive management overhead. Not to mention increased complexity of analysing your data, costs, etc.

The only reason you'd go for (1) (or perhaps 2) is if you were to give your actual tenant direct access to some/all of the database. As you're using ASP.NET MVC 3, this isn't a consideration.