|
|
Posted by Erland Sommarskog on 06/20/06 22:14
TC (golemdanube@yahoo.com) writes:
> I need to design a system which represents multiple "projects" in SQL
> Server. Each project has the same data model, but is independent of all
> others. My inclination is to use one database to store all projects.
> Looking at the numbers involved, however, I wonder if I would get
> better performance by storing each project in its own database.
>
> Suppose I have 50 projects, each with two users and 10,000 rows; it
> seems to me I'd rather have 50 x 2 users working in a table with 10,000
> rows than 1 x 100 users working in a table with 500,000 rows.
>
> On the other hand, the single database approach seems more elegant from
> a design perspective. I wouldn't be creating multiple copies of an
> identical data model, and I wouldn't be creating new databases as a
> business procedure, every time a new project is required.
>
> Here are my questions:
> 1. For the scenario described above, am I correct to assume I will get
> better performance by using multiple databases, or does SQL Server have
> some clever way of achieving the same performance in a single database?
> 2. Is the multiple database approach common? If anyone has tried it,
> please tell me about how it works in practice.
Whether to use one or many databases has nothing to do with performance
whatsoever. If performance is the only motive for you to consider
separate databases, just forget about it given the volumes you indicated.
There may be other reasons for using separate databases. One project
says "oops, we deleted our data". With a separate database, a restore
is a quick thing. Or some projects may start to call for diverging
requirements, so that they no longer fit into the same model. There
can also be security considerations.
But all of that business requirements that are unknown to me. Since
maintaining 50 databases with the same model requires more overhead,
a single database with a good data model is a good way to start.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|