isn't easy, and one of the particularly challenging aspects is what to
do with the database. In Ayende's excellent posts on multi tenancy,
he's pretty clear that he feels a database per tenant is the way to go . There are two major, reasonable approaches that I know of: First, to have a database per tenant. (This is the approach discussed
by Ayende in his multi tenancy series) A second common alternative is to have
a tenant id column in all tables that require differentiation by
client. My team was in a situation requiring multitenancy, and needed
to select an approach. Wary of the idea of multiple databases, the
team immediately decided to go with the tenant column approach.
back, I think Ayende was right. The one advantage of the tenantid
column system is not having to deal with multiple databases. There are
many disadvantages. I'll discuss the advantages, and then give some thoughts on how to deal wwith the complexity of working with multiple databases.
Advantages of multiple databases:
my mind the most important developer-centric advantage of having
multiple databases is the ability to refactor the database. Many
times, developers will design a system a system with certain
assumptions, then realize those assumptions are wrong. Other times, a
major new feature would be much easier and cleaner with certain system
changes. If the database is only talking to one system, then when the
developer comes upon these situations, s/he can also refactor the
database to reflect that understanding, and keep the mapping between
database simple, clean and easy to understand. When the code is pushed
live, db update scripts also get pushed live. However, if multiple
systems (in this case tenants) talk to the same database, this kind of
db structure change just can't happen-pushing a db change would alter
everyone's db, meaning everyone's code would also have to be updated at
once. In many situations, that kind of simultaneous mass upgrade would
be totally unacceptable, meaning that once this kind of multitenancy
system is put in place, the single db can never again be altered.
advantage of multiple databases is the ability to easily load-balance
by moving individual tenants to different db servers as necessary. If
the databases are architected to be isolated, this isn't a
problem-change a few connection strings, and you're done. In a shared
system, all tenants suffer if one tenant is overloading the server.
a related note, though nobody likes to think about it, clients
sometimes leave, and when they do, they will want to be able to take
their own data with them. If they have their own database, it's as
easy as zipping it up and getting it to them. If all tenants are on
the same db, it could be a much more difficult exercise involving
picking through every table and selecting out the tenant-specific
data. Hopefully you dont give them anything that isn't theirs. Which
cleads to the next point.
From a business perspective, data
separation is probably the most important reason to go with separate
dbs. With a multitenant system, it's impossible for one tenant to end
up with anyone else's data. Other tenants' data is in a completely
different system, so there's no worry about messing up the tenantid and
passing a client someone else's data. Depending on the situation, this
could just be embarrassing; in other situations, you might be looking
at a lawsuit.
Lastly, having separated databases makes many
things in the application more complicated, for the life of the
application. Every join, every query, every new table, every mapping
has to deal with the tenant id. If the strategy is to use the tenant
id to make a composite key on the tables, then you've got that much
additional complexity-and if you're using a tool like NHibernate,
you'll soon find out that working with composite keys is a LOT less
seamless than a single autogenerated key. Eliminate the tenantid, and
a whole lot of things become forever simpler, clearer and have a lot
less potential for error.
How to deal with multiple databases?
trouble with multiple databases is that now you have 20 or even 100
databases to manage instead of one. Managing this manually would be a
difficult, error-prone task that could become overwhelming. In
addition, any shared data can become out of sync. The solution? Don't
do *anything* manually with these databases.
each time you create a new version of your application, create a db
script to update the db from the prior version from the last version to
the newest one, and create a script to roll the database back from the
newest version to the previous version. There are tools to help
produce this kind of script... If you're programming in .Net, Visual
Studio Database edition has a tool to help do this for SQL Server, and
Red Gate has one as well called SQL Compare. Bundle this script in
with the deploy of the code, and push it all out together, to each
tenant as they get upgraded.
For shared data: 99% of the
time, shared data is of the sort where it needs to be updated soon, but
not necessarily real-time. An example of this is cross-client data for
statistical computation. To allow this kind of computation in a
multiclient situation, create a process that goes to each system and
collects the appropriate data, possibly in varying ways based on the
tenant's installed version, then pulls that data into a single system.
The combined reporting system can be optimized and tweaked for
reporting needs without affecting the designs of the individual
tenants. Conversely, if there's any reference data they all need to
share (dropdown data, etc) this information can be coordinated with a
similar process that does a data push to the different systems, based
on application version.
This kind of automation is some up-front
work, but once it's done, it's done, and it allows tremendous
application flexibility... and once it's set up, it's easy-there's no
reason it couldn't become fairly invisible from a management
perspective.. In addition, it's really a very separate task from the
business application itself-it's actually so separate that the syncing
application can be written by a completely different team that knows
little to nothing about the main application. For that matter, I'm not
sure if there's a tool out there that does all of these things
already(I bet there is, come to think of it), but there's no reason
such a tool couldn't exist.
04-24-2009 12:49 AM