Handling growing number of Tenants in Multi-tenant Database Architecture

Question

Handling a modest number of customers (tenants) in a common server with separate databases for each tenant's instance of the application is relatively straightforward and is normally the correct way to do this. Currently I am looking at the architecture for an application where each tenant has their own database instance.

However, the problem is that this application will have a large number of tenants (5,000-10,000) with a substantial number of users, perhaps 2,000 for a single tenant. We will need to support growing the system by several tenants every week.

In addition, all tenants and their users will be presented with a common login process (i.e. each tenant cannot have their own URL). To do this, I need a centralised login process and a means to dynamically add databases to the system and register users.

• How could the registration and database creation process be automated robustly?

• Is the process of creating and registering tenants' databases on the system likely to cause performance or locking issues. If you think this could be an issue, can anyone suggest ways to mitigate it?

• How can I manage central authentication in a way where user credentials will be associated with a particular tenant's database but the user can log in through a common page (i.e. all through the same login URL, but their home application will be on some specific tenant's database). The tenants will have to be able to maintain their own logins and permissions, but a central login system must be aware of these. Can anyone suggest a way to do this?

• If I need to 'scale out' by adding multiple database servers, can anyone suggest what issues I might have to deal with in managing user identies across servers (impersonation etc.) and some way to mitigate those issues?

Answer 1

At the lower end (500 tenants / 10000 users) this is how I did it. First, you have a "control" database that is global, central and contains all of the information about tenants and users (I really don't think you want to manage these as SQL auth logins). So imagine a database called "Control" with the following tables:

CREATE TABLE dbo.Instances
(
  InstanceID INT PRIMARY KEY,
  Connection VARCHAR(255)
  --, ...
);

INSERT dbo.Instances SELECT 1, 'PROD1\Instance1';
INSERT dbo.Instances SELECT 1, 'PROD2\Instance1';
-- ...

CREATE TABLE dbo.Tenants
(
  TenantID INT PRIMARY KEY,
  Name NVARCHAR(255) NOT NULL UNIQUE,
  InstanceID INT -- Foreign key tells which instance this tenant's DB is on
  --, ...
);

INSERT dbo.Tenants SELECT 1, 'MyTenant', 1;
-- ...

CREATE TABLE dbo.Users
(
  UserID INT PRIMARY KEY,
  Username VARCHAR(320) NOT NULL UNIQUE,
  PasswordHash VARBINARY(64), -- because you never store plain text, right?
  TenantID INT -- foreign key
  --, ...
);

INSERT dbo.Users SELECT 1, '[email protected]', 0x43..., 1;

In our case when we added a new tenant we would build the database dynamically, but not when the admin user clicked OK in the UI... we had a background job that pulled new databases off a queue every 5 minutes, set model to single_user, and then created each new database serially. We did this to (a) prevent the admin user from waiting for database creation and (b) to avoid two admin users trying to create a database at the same time or otherwise getting denied the ability to lock model (required when creating a new database).

Databases were created with the name scheme Tenant000000xx where xx represented Tenants.TenantID. This made maintenance jobs quite easy, instead of having all kinds of databases named BurgerKing, McDonalds, KFC etc. Not that we were in fast food, just using that as an example.

The reason we didn't pre-allocate thousands of databases as the comment suggested is that our admin users usually had some idea of how big the tenant would become, whether they were high priority, etc. So they had basic choices in the UI that would dictate their initial size and autogrowth settings, which disk subsystem their data/log files would go to, their recovery settings, backup schedule to hinge off of, and even smarts about which instance to deploy the database to in order to best balance usage (though our admins could override this). Once the database is created, the tenant table was updated with the chosen instance, an admin user was created for the tenant, and our admins were e-mailed the credentials to pass along to the new tenant.

If you're using a single point of entry, it is not feasible to allow multiple tenants to have users with the same username. We opted to use e-mail address, which - if all users work for the company and use their corporate e-mail address - should be fine. Though our solution eventually became more complex for two reasons:

1. We had consultants that worked for more than one of our clients, and needed access to multiple
2. We had tenants who themselves were actually comprised of multiple tenants

So, we ended up with a TenantUsers table that allowed one user to be associated with multiple tenants.

Initially when a user logs in, the app will know the connection string for the control database only. When a login is successful, it can then build a connection string based on the information it found. E.g.

SELECT i.Connection
  FROM dbo.Instances AS i
  INNER JOIN dbo.Tenants AS t
  ON i.InstanceID = t.InstanceID
  INNER JOIN dbo.TenantUsers AS u
  ON i.TenantID = u.TenantID
  WHERE u.UserID = @UserID;

Now the app could connect to the user's database (each user had a default tenant) or the user could select from any of the tenants they could access. The app would then simply retrieve the new connection string, and redirect to the home page for that tenant.

If you get into this 10MM user area you propose, you'll definitely need this to be balanced better. You may want to federate the application so that they have different points of entry connecting to different control databases. If you give each tenant a subdomain (e.g. TenantName.YourApplicationDomain.com) then you can do this behind the scenes with DNS/routing without interrupting them when you need to scale out further.

There is a lot more to this - like @Darin I am only scratching the surface here. Let me know if you need a non-free consult. :-)

Answer 2

You have yourself quite an interesting project. I've never directly seen anyone try to implement something that large, at least on SQL Server. The more I read your post, the more questions I come up with...

Worst case scenario infrastructure-wise (which is actually the best case scenario, business-wise), you need 10K databases times 2k users. That's 20,000,000 users. You aren't going to be successful in trying to manage 20 M SQL Server logins. IMO. Just the sheer number of them, dealing with moving them from server to server, watching out for ID collisions and mismatched IDs, plus I'm not sure how SQL Server would behave with 20 M rows in sys.server_principals. Additionally, your web app is probably going to want to connect as a single, or very low number of, users. IIS can't pool connections unless their DSN strings are identical. One of the attributes of a DSN string is the user name. Different users means no pooling.

You will need to roll your own user credential scheme. It will have to be able figure out what tenant a user belongs to and then your web code will need to select the proper database. That user metadata is critical, it going to need to be stored somewhere, it's going to need to be clustered or mirrored, it's going to need to be fast and it's going to need to be well-protected (from a security perspective. IOW, encrypt it.). Assuming that SQL is even a good idea here, I would keep this database away from the instances that server tenants. This helps from a security standpoint and from a load standpoint, though I would guess that once a users is validated and the web app is steered to the correct database on another instance, there will not be any more querying of this user metadata related to that user.

Quick question: should two different users, who belong to two different tenants, be allowed to have the same user name?

Another quick question: If I tell you that I work for FuBar, Inc., how do you know that? Is FuBar going to give you a list of users and you give them back a list of user names, or are they going to self-provision?

You are going to need to go multi-instance. If even a fraction of those users decide to hit the application at once, a single instance will melt. It won't have enough worker threads to run all of those requests at once. If only 1000 users hit your instance at the same time, it will probably run out of worker threads and request will start to stack up and wait. I've seen this happen; the proximate symptom is that new connections will not be able to log into the instance because there are no available worker threads to service them. If this is very short-lived behavior, you app might survive. If not, or your app is fussy, users will get errors.

Even if you will not have many tenants to start, you should start thinking about the future and automation because when you see that your server is bogged down and there are 10 new tenants to bring online, it's pretty much too late and your service (and your clients, and your soon-to-be-ex-clients) will suffer until you write your way out of the problem.

You are going to need a way to move databases around, from overloaded servers to lightly loaded (or new) servers. Whether or not you can get a window of downtime will depend on your SLA.

Are you providing a specific application, like SalesForce, or are these databases just containers for whatever your tenants want to put in?

How big are the databases? If they aren't very big, you could just restore from a backup file that provides a template. (This isn't much different than what the model database does, but I haven't seen anyone really use model in a good way since my days with SQL 6.5.) Once the template has been restored to the new database name, you could then customize the new database as necessary for a particular tenant. You can't do the customization before you have the tenant, obviously. If the database is big, you might follow the same basic procedure except you do the restore ahead of time, before any new tenant needs the space. You might keep a couple of these databases around, maybe one per instance. If you keep too many around, this will force you to maybe buy more hardware and/or storage than you need, plus you will have make allowances in your backup/reindex/checkdb maintenance window (or change your code to avoid databases that are 'deployed', but not 'in use'.).

If this is your own app, how are you going to handle updates to the schemas? How are you going to keep versions of the database straight with versions of the code, if you are using a single URL that gets to your web app?

How do you detect and destroy databases that aren't in use anymore? Do you wait until your A/R group says that someone hasn't paid their bill for three months?

If tenants are managing permissions, that implies that they have some understanding of the inner workings of the app, or that your app has a very simple role structure. Using something like Blogger as a rough example, users can (read posts), (read posts and make comments), (... and create posts), (... and edit other's posts), (... and can reset other users passwords), or (... and whatever). Having a role for each of those different sets of rights and assigning a user to one role or another shouldn't be too hard, but you don't want your app running 'GRANT' statements. Watch out for roles that have a hierarchy and depend on inheritance, it can get confusing. If you are promoting or demoting a user, I'd say pull them out of all of the associated roles and then add them back to the one role that they need. Oh, and since you probably can't use SQL's native credential scheme, you are going to have roll your own code here.

I think that I've only scratched the surface here, and this post is already too long. What you really need is a book, or at least a whitepaper from someone who has done this. Most of those guys won't be talking, if they view it as a competitive advantage.