First, I am going to suggest that you may want to post follow-up questions to dba.se. Multi-tenant applications are tricky and different RDBMSs have different tools for solving some of the problems. Without knowing exactly what your environment is, specific answers are often impossible. So I am going to look at conceptual problems and sample answers here.
For customization of cardholder data, my recommendation generally is to store the extra fields in something like XML or JSON format because this gives you a basic framework for storage which is flexible and gives your application a way to handle the data without eav pains. Some db's may even be able to index xpath search results against xml records.
Multi-tenant security is a very different matter and it is hard. The traditional standby is to use a view which can be used to filter out rows only to those which are authorized. Oracle has some ways to bolt these on to the table directly. PostgreSQL has some ways to make sure that other functions applied don't leak information.
If this becomes large, some sort of division of storage is going to be needed. How this gets divided by partitioning tables will be something you will have to decide based on your observed workload. If you are on Oracle thinking about being able to move to RAC as needed may be helpful, and if you are on PostgreSQL, Postgres-XC is the technology to keep in mind. However your current requirements make a normal sharding scheme somewhat questionable and it isn't clear what your usage patterns are going to be. How many cross-tenant cardholders are there likely to be? How much tenant vs cardholder queries are there going to be? These are the sorts of decisions which really should be made when they become problems and premature optimization here will make thins hard.
I am asking myself the exact same question at the moment.
I am leaning towards the multi-instance single tenancy solution but have not taken a definitive decision yet. Let me share some of my thoughts :
The main historical advantage of the multi-tenant architecture is a better use of infrastructure resources, by mutualisation (single OS, single Database, single application layer) and better occupying of said resources (when one user is away another can use the same resource).
It also greatly simplifies software lifecycle : you deploy new versions to you one instance, all the customers are updated at the same time.
It seems however, that recent advancements in cloud technology make the first class of advantages largely available in a multi-instance (instance-per-customer) architecture (I am thinking specifically of a platform like Jelastic here but I am sure there are others that provide the same features):
- Container-based PaaS
- Provisioning and auto-scaling of containers (elastic containers)
So hardware and platform management is not the Software provider's concern any more. Resources are mutualised much more efficiently than before at the infrastructure and plaform levels.
There will still be an overhead for multi-instance (some app and middleware will be ran N times instead of just one), but much lower than when using a separate (virtual) machine per instance. The database could be shared anyway (one schema per instance, several schemas per DB server)
Also :
- Automation of creation of new instances is possible via PaaS API
- Automation of deployment of new versions is possible via PaaS API, with zero downtime (takes some work to put in place)
- Scaling is always out, never up. We don't have to worry about huge datasets at the instance level.
Of course, we would need some kind of central service that manages all this automatically (e.g. creation of instance when a new user creates an account). This would also manage payment and licensing issues, interaction between instances etc. This central service might be quite complex and hard to develop, but the good thing is that we don't have to implement it upfront (now that we don't have much resource) whereas multi-tenant would need to be baked into the app from the start.
Which brings me to the final advantages of developing single-tenant for a very early stage (pre-invesment) startup project :
- Same (or almost same) version of the app can be deployed on-premises either as a virtual appliance or docker container or even on customer-managed machine (some companies are still reluctant towards the Cloud and it may help an early stage startup to not push out important early adopters)
- Faster to get a product out with limited resources (the application layer and database schema is quite less complex), can get a "dumb" single instance single tenant product out first (MVP) for early adopters and to show the business value of the app to potential investors, and add all the cloud automation later on
- Can be seen as a selling argument for customers worried about data security : the data is better encapsulated since every customer has his own schema or even database. Much less risk of "spillage"
NB: I am obviously thinking here of a business app where customers would be businesses (each with multiple individual users) and not individuals. It would not make any sense to run a separate instance of an app for each individual user (or would it ?)
Best Answer
There is no one "correct" way to do anything in programming, even with a cloud-computing specific architecture such as Windows Azure.
A cursory search of the MSDN documentation reveals that Azure does in fact have some useful functionality to help you separate your client's data. It appears as if you could decide to give each client their own SQL server instance, or you could instead use a common instance that you either partition directly or leverage some built-in functionality to partition for you.
Each of those solutions have their own pros and cons, which you will need to evaluation on your own. Some conceptual guidance as to the selection:
As for the question of "how do you move data from single-instance to multi-instance", I imagine it would largely be a matter of constructing your new architecture and then importing the old data. You'll want to know how to do that for future clients, anyway.