Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
 Posted - 2013-04-04 : 13:27:36
|
| Currently all of our clients are in a single database separated by clientid. We are undertaking a rewrite and are considering a separate database for each client (roughly 500). I'm curious about the impact on performance. Since each database is much smaller and the indexes can be tailored to each client you might get better performance. But on the other hand I'm not sure how sql server handles CPU and memory management in these scenarios.Possible benefits...1. Improved separation in terms of security and bugs resulting in cross client communication.2. Easier to move databases to different hardware if required.3. Fewer locks???Downside...1. A bit harder to maintain (need to execute change scripts across hundreds of databases.2. Not sure of impact on CPU and memory management.Thoughts? |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2013-04-04 : 14:07:04
|
| Depending on the nature of your business, some clients and industries require separate databases.Change management can become a nightmare if you aren't meticulous with your update process. And you'll want to automate it.In general, I'd prefer one database, but I'd need to know a lot more about the business and db architecture to say for certain. |
 |
|
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
Posted - 2013-04-04 : 15:18:18
|
quote:
Originally posted by russell
Depending on the nature of your business, some clients and industries require separate databases.Change management can become a nightmare if you aren't meticulous with your update process. And you'll want to automate it.In general, I'd prefer one database, but I'd need to know a lot more about the business and db architecture to say for certain.
Thanks for the response. We are a learning management system. The data in question is generally not overly sensitive. Any of our clients who are very concerned about security typically opt for the an install behind their firewall instead of SaaS.In terms of DB architecture it is a pretty standard relational database. We have about 100 tables, 100 functions/stored procs. It is fairly normalized. The main instance of the database is currently about 200 gigs with about 1/3 of that being indexes. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2013-04-04 : 15:22:10
|
| In that case I don't see any point in splitting it out into many databases. That would be a ton of work for little to no benefit. And any benefit gained is easily offset in the complexity added to management. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2013-04-04 : 15:25:55
|
| Having experienced a 20-different-databases scenario, I agree with Russell, 1 database is the way to go. Change management will royally suck with 500 databases no matter how strict and automated you are. And the minute they're separated someone will want something different for a client, then someone else different for another, and then you'll have 500 different schemas.None of the benefits you listed are likely with either design. There's a limited amount of memory for locks on the server, multiple databases are more likely to starve it. If tables are large and need particular indexing, look at partitioning and/or filtered indexes. Splitting to 500 databases could hurt performance, as you'd have 500 independent I/O threads vs. 1. There's also the duplication of reference data in each database, e.g. system codes, that will waste buffer and disk space. |
|
|
|
|
|
|
|
|
500 small databases or 1 large database?