Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

For one of our systems, we have sensitive client data and store each client's data in a separate database. We have about 10-15 clients for that system.

However, we're developing a new system that will have 50-100 clients, maybe more. I am thinking it might be unfeasible to have one database per client in this instance (to store sensitive records and audit history). However I don't know if this is perfectly normal or not, or if there's another way of maintaining security.

Any thoughts on this?

share|improve this question
    
I do not know the pros-cons of having multiple databases per server (I never had any issue with that) but the many schemas concept technet.microsoft.com/en-us/library/dd207005.aspx within the same database, offers both isolation and security. So, you can try this architecture as well. –  Alexandros Mar 19 at 12:25
2  
@Alexandros schema separation offers a little, but it doesn't allow you to use separate recovery models, back up on different schedules, restore one client to a specific point in time, remove one client easily, move one client easily, etc. –  Aaron Bertrand Mar 19 at 13:31
3  
I've seen systems with 3,000+ databases (1 per client) on a single server. I wouldn't worry too much - just make sure you plan resources carefully and monitor usage as the client count goes up. –  Max Vernon Mar 19 at 18:03
    
You might read this, specifically note the dates and the ops comments: stackoverflow.com/questions/5596755/… –  Chris Lively Mar 19 at 22:08

3 Answers 3

up vote 36 down vote accepted

Managing 100 or 500 databases is really not all that different from managing 5 or 10 - you just have to embrace automation and have a scalability plan in place (and don't plan to use high-cost-per-database features like mirroring across all clients).

At my previous job we used this architecture and I would never once have ever thought of merging two clients into a single database, even though some of the challenges can be "hard."

The big benefits are independent recovery models (a can be simple, b can be full, etc.), the ability to restore to a point in time (or remove entirely) a client without disrupting others, the ability to seamlessly move a resource-heavy client to its own storage or to a completely different server with very little in the way of transparency (you update a config file or table that tells the app where to find that client).

I address several of the objections, and/or how to approach the problems, in these posts:

That all said, I don't think any of us can tell you the point at which management becomes impractical for you - just know that whatever specific challenges you come across, you can ask about those problems individually.

share|improve this answer
    
+1 Excellent points ! –  Kin Mar 19 at 13:57
5  
You will also need a good naming convention, applied consistently. –  Greenstone Walker Mar 19 at 20:03
    
Thanks those are some great links. It's not really a management issue (at the moment), I was just concerned things might grind to a halt. But it seems that I shouldn't worry about that and just ensure I can manage it all. So thanks! –  SLC Mar 20 at 9:32

I recommend you read Multi-Tenant Data Architecture, a white-paper that discusses the options you have, and pros and cons. To summarize, it gives three options:

  • separate DBs
  • separate schemas
  • shared schema

You are now at separate DBs stage, which offers the best separation (isolation between tenants), but is the most difficult to manage. As you grow into hundreds of tenants you'll realize that the logistics of administering 100s of DBs are far from trivial. Think backup-restore (location of backed up files, jobs, schedules etc). Think how will you monitor and manage file allocation, disk space used and database growth across hundreds of DBs. Think what will be your High-Availability/Disaster-Recoverability scenario in a near future with 1000 tenant? 1000 mirrored DBs, 1000 log shipping sessions? Think what if in 6 months your dev team comes to you and say "I know how give this awesome feature to our product, we'll use Transactional Replication!", what will you say? "sure, let me set up 500 publishers, it will be fun"! Is not impossible to manage hundreds of DBs, but if you plan to, you better polish up your PowerShell skills and stop using the UI management tools right now.

Additionally you need to consider that multiple (hundreds) DBs have measurable impact on performance and cost:

  • physical disk space is less efficiently used (every database must have some spare room, you'll have that spare room multiplied by the number of DBs)
  • There is no way you can create a dedicate log disk for write intensive tasks, you'll will have to move all those LDFs onto one (or more) SSD storage
  • log writes will be less efficient on frequent commits as they spread out across many individual log block records vs. aggregate into one (you'll get underused log blocks). See What is an LSN: Log Sequence Number to understand what I'm talking about.

Separated DBs come with some advantages though due to isolation, the main advantage being independent backup/restore.

Scenario like yours though are a perfect candidate for SQL Azure databases. No administration of disk space, no need to provide HA/DR, grow to hundreds/thousands of DBs etc.

share|improve this answer
    
Thanks this is some good advice, especially possibly switching to a cloud model. I will have to give some serious thought to how I'm going to handle backing things up. –  SLC Mar 20 at 9:33
    
And once your automation is sufficiently well developed to support separate databases for each client, it isn't a giant leap from there to provision separate VMs for each client. This, after all, is what "cloud" hosting companies do. Agree that this is possibly a good use case for SQL Azure –  Gavin Campbell Mar 20 at 9:55

At my previous job, we hosted not just one database per client -- in most instances, it was more than that! As I left, there were over 4,500 databases running in one MariaDB cluster, nearly 7,000 in another (ironically smaller) cluster, and 4 "shards" (completely separate, independent web and database servers, even in a wholly separate data center) each hosting 200-500 databases in a single MySQL server. And that company is still growing at a good clip.

The long and the short is that the success of that company proves that such an architecture is indeed feasible. (Caveat: Contrary to the apparent gains in isolation by using separate databases, all data was accessed through a trio of tightly coupled applications that all used the same database user/pass! I suspect performance may have suffered ever-so-slightly if each client had a separate user/pass -- but only slightly.)

From my experiences working closely with the sys admins (technically I was a programmer with the company, but in reality I was the best DBA they had, and the only person they had who knew how to set up a firewall!), performance-related concerns boiled down to concurrent accesses, query complexity/time, index performance, etc. -- all the usual suspects, in other words, and the number of databases on the server played no discernible part, a conclusion affirmed by the highly-paid specialist consultants we consulted regularly.

The bottom line is that you should focus your concerns on your application, on your infrastructure, and not on the number of databases you happen to have. All those other factors will be more than enough to keep you busy resolving performance problems and bottlenecks.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.