One database or many database?

I have a web application which collect data from MANY clients. Data
collected is in the same structure, my questions is: to use one database
store all data or create a database for each client? which way is better?
Thank you
QuinnThere is no right or wrong answer here. If you have one huge DB, then
backup and recovery can be problematic. If you have 100's of small DB's,
then it becomes an administrative challenge, though much admin can be
automated/scripted.
There are also security issues. Some clients refuse to commingle their data
with other companies.
Also, if you needed to separate a client into its own DB on another server,
it's not straightforward. Whereas, if you have each client in it's own DB,
it's just a matter of backup and restore.
If the number of clients that you have is small, then likely you can get
away with a single DB.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Quinn" <qjia@.yahoo.com> wrote in message
news:OLqpwLspHHA.2596@.TK2MSFTNGP06.phx.gbl...
I have a web application which collect data from MANY clients. Data
collected is in the same structure, my questions is: to use one database
store all data or create a database for each client? which way is better?
Thank you
Quinn|||Thank you Tom.
Right now I don't know how many clients yet, but 500+- may close enough.
Quinn
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23G8mcPspHHA.4212@.TK2MSFTNGP04.phx.gbl...
> There is no right or wrong answer here. If you have one huge DB, then
> backup and recovery can be problematic. If you have 100's of small DB's,
> then it becomes an administrative challenge, though much admin can be
> automated/scripted.
> There are also security issues. Some clients refuse to commingle their
> data
> with other companies.
> Also, if you needed to separate a client into its own DB on another
> server,
> it's not straightforward. Whereas, if you have each client in it's own
> DB,
> it's just a matter of backup and restore.
> If the number of clients that you have is small, then likely you can get
> away with a single DB.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Quinn" <qjia@.yahoo.com> wrote in message
> news:OLqpwLspHHA.2596@.TK2MSFTNGP06.phx.gbl...
> I have a web application which collect data from MANY clients. Data
> collected is in the same structure, my questions is: to use one database
> store all data or create a database for each client? which way is better?
>
> Thank you
> Quinn
>|||Then, if you go the one DB per client route, you'll need to automate your
maintenance processes, e.g. backup, reindexing and the like. It's not that
big of a problem; you just need to have it in place.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Quinn" <qjia@.yahoo.com> wrote in message
news:OceHgUspHHA.4772@.TK2MSFTNGP05.phx.gbl...
Thank you Tom.
Right now I don't know how many clients yet, but 500+- may close enough.
Quinn
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23G8mcPspHHA.4212@.TK2MSFTNGP04.phx.gbl...
> There is no right or wrong answer here. If you have one huge DB, then
> backup and recovery can be problematic. If you have 100's of small DB's,
> then it becomes an administrative challenge, though much admin can be
> automated/scripted.
> There are also security issues. Some clients refuse to commingle their
> data
> with other companies.
> Also, if you needed to separate a client into its own DB on another
> server,
> it's not straightforward. Whereas, if you have each client in it's own
> DB,
> it's just a matter of backup and restore.
> If the number of clients that you have is small, then likely you can get
> away with a single DB.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Quinn" <qjia@.yahoo.com> wrote in message
> news:OLqpwLspHHA.2596@.TK2MSFTNGP06.phx.gbl...
> I have a web application which collect data from MANY clients. Data
> collected is in the same structure, my questions is: to use one database
> store all data or create a database for each client? which way is better?
>
> Thank you
> Quinn
>|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uRUj1fspHHA.2156@.TK2MSFTNGP03.phx.gbl...
> Then, if you go the one DB per client route, you'll need to automate your
> maintenance processes, e.g. backup, reindexing and the like. It's not
> that
> big of a problem; you just need to have it in place.
I'm doing this for 400+ databases in two systems, and the maintenance stuff
is a one-time cost. The ability to move a resource-hoggy client to its own
instance or server is well worth the time invested up front to support them
all.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006|||That's a major advantage - the ability to parse off a large client onto
another server as required. Taking it to the next level, let's say you have
those 400+ DB's on 4 servers. You can then move DB's from one box to
another to level the load. Sur, it's manual, but if you take regular
performance stats, you are then in a good position to figure out who goes
where.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:OvZbmStpHHA.3512@.TK2MSFTNGP06.phx.gbl...
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uRUj1fspHHA.2156@.TK2MSFTNGP03.phx.gbl...
> Then, if you go the one DB per client route, you'll need to automate your
> maintenance processes, e.g. backup, reindexing and the like. It's not
> that
> big of a problem; you just need to have it in place.
I'm doing this for 400+ databases in two systems, and the maintenance stuff
is a one-time cost. The ability to move a resource-hoggy client to its own
instance or server is well worth the time invested up front to support them
all.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006