Many databases vs. 1 database

[mike@rumblegroup.com]

Hi,

I am trying to determine what the overhead is per database in SQL
Server 2000 Standard. I have the option to put several customers in one
database, or give each customer their own database. I would like to put
each customer in their own database to simplify maintenance and
strengthen security.

I have found the following document which shows the memory used by
various objects in SQL Server:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp

Based on this info I get the following *additional* memory requirements
per database:

Open Database (1 file, 1 filegroup): 6k
Open Objects (250 objects, 30 indexes): 692k
Total: 698k

Is this an accurate calculation of the overhead? Is there something
else that would affect the overhead that I am overlooking? Are there
any other downsides to having many databases versus a few databases?

Thanks,
Mike

[Mike Epprecht \(SQL MVP\)]

Re: Many databases vs. 1 database

Hi

Having 100's of databases does slow EM down as it need to list them all.
That is about it.

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mike@epprecht.n et

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

<mike@rumblegro up.com> wrote in message
news:1112124274 .309091.156730@ f14g2000cwb.goo glegroups.com.. .[color=blue]
> Hi,
>
> I am trying to determine what the overhead is per database in SQL
> Server 2000 Standard. I have the option to put several customers in one
> database, or give each customer their own database. I would like to put
> each customer in their own database to simplify maintenance and
> strengthen security.
>
> I have found the following document which shows the memory used by
> various objects in SQL Server:
>
>[/color]
http://msdn.microsoft.com/library/de...ar_ts_8dbn.asp[color=blue]
>
> Based on this info I get the following *additional* memory requirements
> per database:
>
> Open Database (1 file, 1 filegroup): 6k
> Open Objects (250 objects, 30 indexes): 692k
> Total: 698k
>
> Is this an accurate calculation of the overhead? Is there something
> else that would affect the overhead that I am overlooking? Are there
> any other downsides to having many databases versus a few databases?
>
> Thanks,
> Mike
>[/color]

[Thomas R. Hummel]

Re: Many databases vs. 1 database

You said that you want to have many databases to simplify maintenance.
I'm not sure that I understand that. Especially with regards to change
control. With multiple databases you run the risk of one or more
databases becoming out of sync, either intentionally or
unintentionally . This can turn into a real headache if you aren't very
careful.

Also, will you ever want to research information on your customers as a
whole? You didn't include any information as far as what these
databases actually hold (this would have been useful to know), but
assuming that they hold sales data as an example... if you wanted to
find your total sales across all customers then you would have to
select across many databases. If you got a new customer you would now
have to change any queries that select across these databases to
include the new database.

Good luck,
-Tom.

[Erland Sommarskog]

Re: Many databases vs. 1 database

(mike@rumblegro up.com) writes:[color=blue]
> I am trying to determine what the overhead is per database in SQL
> Server 2000 Standard. I have the option to put several customers in one
> database, or give each customer their own database. I would like to put
> each customer in their own database to simplify maintenance and
> strengthen security.[/color]

Putting all customers in the same database may be a good idea if
the customers does not access the data themselves.

But since you say "security", I assume that the customers will access
the databases.

One can handle security for customers in a shared database, so that
they only see their own data, but:

o If there is a slip somewhere, a customer can by mistake get access
to someone else's data.
o Even if correctly implemented, "Row-level security" is not waterproof,
since the views that typically implement such scheme can be provoked
to leak information.

And if the overhead of many databases are your only concern, there is
no reason for doubt. As Mike said, the overhead is negligible. You
will have to automate backups and all that, but that is not a major
issue.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Erland Sommarskog]

Re: Many databases vs. 1 database

Thomas R. Hummel (tom_hummel@hot mail.com) writes:[color=blue]
> You said that you want to have many databases to simplify maintenance.
> I'm not sure that I understand that. Especially with regards to change
> control. With multiple databases you run the risk of one or more
> databases becoming out of sync, either intentionally or
> unintentionally . This can turn into a real headache if you aren't very
> careful.[/color]

Version control and a automated way of propagating changes.

Note also that this cuts both ways. If you have one single database and
you want to upgrade from version 1.2 to 1.3 and big-whiz says no? Or what
of big-whiz wants special features that are useless to most other
customers? With one big database, how do you beta-test? And what if
you find that the server does not cut it anymore, and you want to
scale out? Move a bunch to another server, easy as a piece of cake
with multiple databases. The monolith is more difficult to deal with.
[color=blue]
> Also, will you ever want to research information on your customers as a
> whole? You didn't include any information as far as what these
> databases actually hold (this would have been useful to know), but
> assuming that they hold sales data as an example... if you wanted to
> find your total sales across all customers then you would have to
> select across many databases. If you got a new customer you would now
> have to change any queries that select across these databases to
> include the new database.[/color]

Views with a whole bunch of unions can easily be build dynamically on
demand.

But I the most decisive factor in this question is security. A multi-
customer database is more or less destined to leak data among customers.
Whether this is acceptable or completely unacceptable could be different
from business to business case.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp