SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SQL Server Administration (2012)
 500 small databases or 1 large database?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ferrethouse
Constraint Violating Yak Guru

340 Posts

Posted - 04/04/2013 :  13:27:36  Show Profile  Reply with Quote
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

USA
5072 Posts

Posted - 04/04/2013 :  14:07:04  Show Profile  Visit russell's Homepage  Reply with Quote
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.
Go to Top of Page

ferrethouse
Constraint Violating Yak Guru

340 Posts

Posted - 04/04/2013 :  15:18:18  Show Profile  Reply with Quote
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.
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 04/04/2013 :  15:22:10  Show Profile  Visit russell's Homepage  Reply with Quote
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.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15663 Posts

Posted - 04/04/2013 :  15:25:55  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000