2

I building a software that is going to have a lot of users (hopefully) I will have users that dont have much records or exceed normal data and I would also have the big users which will have millions of data rows in many tables. What would it be better putting each user in a separate database or having a master database for all users even thought data could get very large in records. I am worried about performance, would SQL server still be able to perform as normal even after millions of records for each user? The structure of the data would be the same since they will be all talking to the same software.

Any ideas? Thanks.

3 Answers3

4

You really need to find a good DBA who can more carefully evaluate your particular requirements and make an informed recommendation. That said, I'd worry about making it work before worrying about scaling excessively, and put it all in one DB to start.

There are advanced techniques to divide data so it's not all on the same server, without compromising the single database view of the data structure. Again, getting a good DBA to make a recommendation, they would know this stuff.

Chris S
  • 77,945
  • 11
  • 124
  • 216
  • +1 get advice from an expert. Though as a non-DBA myself, I can't help but wonder if each customer's data would be more secure in its own database. – Kara Marfia Oct 06 '10 at 13:49
  • I would think separate db's would help down the road with scaling and availability, and reduce chance of corruption...but I'm not a DBA either. – Bart Silverstrim Oct 06 '10 at 13:56
  • Separate DBs per customer probably wouldn't be more secure, as the application would have access to both, and would be the likely place where data would leak anyway. Also separate DBs for scalability isn't guaranteed in the slightest, and for HA complicates the mirroring scheme used. In both cases, I don't know enough to say which one is right for this particular situation; getting back around to *find a DBA*... – Chris S Oct 06 '10 at 16:02
2

Consulting with a DBA sounds like a must.

If you have few users now but hope to scale up to many it does not make a lot of sense for you to create individual databases for each user; whatever performance you may gain with individual databases will almost certainly be negated by the overhead of scaling. NOTE: max number of databases in a SQL server instance is 32,767

A good DBA should be able to help normalize your tables within a single database in such a way that will address your performance concerns.

shiitake
  • 379
  • 1
  • 7
0

If you have a lot of tables & data per user, IMHO a separate database would be the preferred solution.

You can also start with a single database instance, and when specific users' data grow beyond the average, split them out of the main DB and have them work on a separate instance.

In addition, if the data is private, and security is a major issue, separate instances will limit the options of data leaks to the wrong users.

Am.
  • 165
  • 2
  • 7