Convert a single SQL Server client database to a single tenant database

Question

We currently have a system in which each of our users receives a database. Now we move on to a scheme with several tenants with one database, so a single database can contain many clients.

A few questions:

• Is it a conversion tool for multiple tenants? Or is it just the process of creating a Tenant table and adding TenantID to each other table?

• Is there an easy way to implement multi-user mode without having to reorganize our code that communicates with the database?

  We have Odata.svc that does all the talking with the database (our client interfaces range from .net interfaces to iOS devices). I read a little about using federation to filter in the TenantID predicate, so the code does not need to be changed at all. Is it possible?

• Is there a recommended limit on the number of tenants in the database?

I am collecting this silly question (how long is a piece of string). We will most likely host the final solution on Azure.

Look forward to any advice anyone can give me. We are making fundamental changes in our processes, so I want to be at the top until we are under it.

Answer 1

Automation?

In theory, it should be possible to create a tool that facilitates the implementation of this complex operation (the transition from one tenant to multiple tenant). However, I do not think that such a tool exists, given the limited audience of such a product. It would be very nice if you surfaced.

Manual Conversion Ideas

Start by developing a new multi-tenant database schema. (This means combining all the database schemas with one tenant with any shared schemas that you have). I would like to make it as if it was designed without any outdated considerations.

You obviously need a Tenant table that many of your existing tenant tables with the Tenant_id column will have to Tenant_id . For example, a user table will require that it uniquely associate users with the tenant.

In the case of a simple Products table (with Product_id as the primary key), it should be possible to add the Tenant_id column, resulting in a table with a composite key ( Tenant_id and Product_id ). But if you wrote the application from scratch, I believe that the Product table without a reference to the tenant is the right way. It also allows tenants to share products rather than add duplicates. Since one tenant can have products with Product_id 1,2,3 and 1,2 more, you cannot simply join the tables, because you cannot use the same identifier twice - you need unique primary key values. One way to solve this problem is to write a program (in Java or another high-level language) that reads all the data from the tenant's database into objects in memory, and then writes the data to a circuit with several tenants. The process is repeated for the next tenant database, etc. That way you will have Product_id values ​​1,2,3,4,5. A quick, dirty way would be to add a number, say 1000, 2000, and so on, to all the identifier values ​​in each circuit and just cross your fingers so that there are no conflicts.

The code that communicates with the database

You will need to rewrite most of the database queries in order to take into account the fact that the database is now multi-user. This is a difficult task, especially considering the consequences of introducing an error that allows one tenant to bother with other data of the tenant. However, some methods may make this task easier. For example, the Tenant View Filter can significantly reduce the amount of work required.

Limit the number of tenants

I have never seen a recommendation to limit the number of tenants in a multi-tenant structure. On the contrary, the strength of the multi-user approach is its scalability. Today, you can easily create database server clusters or use cloud solutions to add additional hardware if necessary.

Interesting links

• Convert single-tenant apps to multi-story apps
• Multi-Tenant Data Architecture

Answer 2

Honestly, in my experience you cannot automate this. You are moving very important data from your infrastructure to your data model. Each request was written under the assumption that the tenant is already installed. Therefore, each request and SP will be replaced by a link back to your tenants table and parameterized.

You ask in Q1 if you just add the tenantID to each table. That would be one approach, but not the one I defended. This leads you to open access to incorrect data (without observing that the children have the same tenantIDs as the parent, or even that they are all the same!) You need to add the tenant table, and then carefully select which tables should refer to it. It will not be everyone. Some of them will require this, some of them may want to put it there for performance reasons. If you decide on the latter, you will undoubtedly need additional verification mechanisms to make your data significant.

If you were in Oracle, then what you can do is to convert each table into a view (still doing all of the above), and then put the tenantID on the session and make some Fine Grained Access on it to hide most of the detail from the client. Hard to do well, although I'm not sure what the SQL equivalent is. It may be worth some research.

What is the reason for merging the database? Do you need a cross-DB report or something else? Otherwise, one tenant has many advantages (multiple updates and downtime, performance may be better depending on how [de] normalized, ease of retrieval / reporting of individual tenants, ease of removal when you lose a tenant). A cloud solution and one tenant can potentially work in your favor here.