Re: Maximum practical number of databases per SQL Server 2000 Instance

From: Mike Epprecht \(SQL MVP\) (mike_at_epprecht.net)
Date: 12/15/04 

Date: Wed, 15 Dec 2004 23:07:34 +0100

Hi

There are some KB articles on what needs to changed in SQL Server for it to
work well with more than 1'000 DB's. It comes down to having enough memory
available for the file structures. I don't have the KB at hand.

The more DB's you have, the more difficult it is to manage them with
Enterprise Manager. And one big issue, they all share TempDB, so if the
applications are written badly, you have a nice bottleneck.

I have tested to 600 DB's. Shutdown does take a while, and so does startup.
Enterprise Manager took 32 seconds to render the screen.

If I were you, and it is possible in your scenario, I would run multiple
instances with about 100 DB's per instance. Then, applying a SP or a hotfix
does not affect every DB, neither does a failure.

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

IM: mike@epprecht.net

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

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

"Bob Castleman" <nomail@here> wrote in message
news:#xuo4wu4EHA.2124@TK2MSFTNGP15.phx.gbl...
> We have hundreds of databases on our cluster. It creates some interesting
> issue (starting and stopping the service takes a looooong time, for
> example). But it runs pretty smoothly, all things considered.
>
> Bob Castleman
> SuccessWare Software
> "Cesar" <Cesar@discussions.microsoft.com> wrote in message
> news:1FCBB476-FEC0-493C-AA99-D7BF6E867B84@microsoft.com...
> > Does anyone know what the maximum practical number of databases that
could
> > be
> > created and used per a SQL Server 2000 Enterprise Instance. I know that
> > the
> > theoretical limit in BooksOnLine is 32K. But my application is dying
way
> > before that.
> >
> > Thanks in advance
> >
> > Cesar
>
>

Relevant Pages

  • Hide Databases
    ... I´m sharing a SQL Server 2000 SP3a with some users. ... These users are using Enterprise Manager and Query ... Analyzer at client side. ... How can I hide Databases ...
    (microsoft.public.sqlserver.security)
  • Re: Enterprise Manager data entry limit
    ... grid control that is used? ... insert such huge values through Enterprise Manager? ... Mark Allison, SQL Server MVP ... Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.server)
  • Re: MYSQL what should I know
    ... MSDE is Microsoft Desktop Engine (I think that's ... size (but you can have many databases in one instance, and you can have up to 16 ... It supports Views, Stored Procedures, everything that SQL Server does. ... supposedly you can buy Enterprise manager and Query analyzer for $75 or so from ...
    (microsoft.public.vb.general.discussion)
  • Re: Hide Databases
    ... This is not possible in SQL2000 Enterprise Manager. ... pretty lax.You can choose to hide System databases if you wish, ... Jasper Smith (SQL Server MVP) ... "Alex Decarli" wrote in message ...
    (microsoft.public.sqlserver.security)
  • Re: Very slow Responding SQL2000
    ... Can you install the SQL Server client tools remotely and connect ... using both Enterprise manager as well as Query ANalyzer and check the Query ... One more thing is uncheck the AUTOCLOSE option for all databases ...
    (microsoft.public.sqlserver.server)