Number of Databases

I have started administrating a system that has 44 databases running on the
same server. That is 44 user databases.
This is running under NT4 SP 6a and SQL 7 SP3
I am sure that SQL was not meant to support this number of databases on the
same server but unable to find any documentation to support this.
Does any one have any experience of working with this number of databases or
point me in the right direction to documentation on the number of supported
databases.
Any help would be appreciatedSQL 7.0 and 200 support up to 32.767 databases
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8
_ar_ts_8dbn.asp). But personally I prefer to limit the number per instance;
however, it really depends on application(s). If everything is working and
users are not complaining, why worry? If you have performance problems and
you have applications using different databases, then it really might be the
simplest solution to install another SQL Server on another box and transfer
half of the databases.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Mark Jones" <mark.jones@.nospam.kimdata.plus.com> wrote in message
news:FR%ab.8119$vX3.1275275@.wards.force9.net...
> I have started administrating a system that has 44 databases running on
the
> same server. That is 44 user databases.
> This is running under NT4 SP 6a and SQL 7 SP3
> I am sure that SQL was not meant to support this number of databases on
the
> same server but unable to find any documentation to support this.
> Does any one have any experience of working with this number of databases
or
> point me in the right direction to documentation on the number of
supported
> databases.
> Any help would be appreciated
>|||How many databases your application can or should support involves far too
many variables for someone to answer "44 is too many" or "44 is fine."
> point me in the right direction to documentation on the number of
supported
> databases.
This shows some of the fixed limits in the product, however there is really
no way for there to be an "official" declaration of how many databases
*your* unique application can and should support.
http://www.aspfaq.com/2345
Follow-ups set only to server.|||We regularly host over 50 databases on a server. You can have many more if
the databases are quiet. But then, you may have to settle for far fewer on a
server if the databases are heavy CPU/Disk/Memory or Nework consumers.
In general, I wouldn't consider 44 databases as too many. The number of
databases is often not the most important factor. Ultimately, it's a
resource issue (CPU, memory, disk, or Network, etc)
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Mark Jones" <mark.jones@.nospam.kimdata.plus.com> wrote in message
news:FR%ab.8119$vX3.1275275@.wards.force9.net...
> I have started administrating a system that has 44 databases running on
the
> same server. That is 44 user databases.
> This is running under NT4 SP 6a and SQL 7 SP3
> I am sure that SQL was not meant to support this number of databases on
the
> same server but unable to find any documentation to support this.
> Does any one have any experience of working with this number of databases
or
> point me in the right direction to documentation on the number of
supported
> databases.
> Any help would be appreciated
>|||I'm not sure about SQL Server 7.0 at this point, the comments below really
apply to SQL Server 2000.
There was a white paper at www.microsoft.com that discussed a scenario in
which a single instance supported several hundred databases. It was the
architecture that Microsoft used for a bCentral's hosted small business
financials offering (before they acquired Great Plains and decided to drop
the hosted offering). Unfortunately last time I looked I was unable to find
the white paper.
There are really two limits on the PRACTICAL number of databases that a
single instance can support. The first is a limit on the number of file
handles that Windows allows a process to have open at any one time. In the
hundreds of databases example it is clear that only a small subset are
actually in use at any given time and so setting the databases to autoclose
when not in use solves the handle problem. The second problem is that SQL
Enterprise Manager has some algorithms that don't scale to hundreds of
databases. Basically, it loads metadata for all of the databases whenever
you connect to the instance. With hundreds of databases this can take a
long time (I've heard someone mention an example where it took over an hour
to start Enterprise Manager). The solution here is to do all management
from the command line and not use Enterprise Manager to manage hundreds of
databases. Neither of these problems apply when you are only talking about
44-50 databases. That should work just fine without any special handling.
--
Hal Berenson, SQL Server MVP
True Mountain Group LLC
"Mark Jones" <mark.jones@.nospam.kimdata.plus.com> wrote in message
news:FR%ab.8119$vX3.1275275@.wards.force9.net...
> I have started administrating a system that has 44 databases running on
the
> same server. That is 44 user databases.
> This is running under NT4 SP 6a and SQL 7 SP3
> I am sure that SQL was not meant to support this number of databases on
the
> same server but unable to find any documentation to support this.
> Does any one have any experience of working with this number of databases
or
> point me in the right direction to documentation on the number of
supported
> databases.
> Any help would be appreciated
>|||Greg;
My guess is that you'd probably run into other PRACTICAL issues before you
run into the file handle issue. I'd generally advise against using
auto-close for any serious production database. The option has the potential
to cause too many maintenance problems, and has the performance issue.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Hal Berenson" <haroldb@.truemountainconsulting.com> wrote in message
news:uPr1iW9fDHA.2152@.tk2msftngp13.phx.gbl...
> I'm not sure about SQL Server 7.0 at this point, the comments below really
> apply to SQL Server 2000.
> There was a white paper at www.microsoft.com that discussed a scenario in
> which a single instance supported several hundred databases. It was the
> architecture that Microsoft used for a bCentral's hosted small business
> financials offering (before they acquired Great Plains and decided to drop
> the hosted offering). Unfortunately last time I looked I was unable to
find
> the white paper.
> There are really two limits on the PRACTICAL number of databases that a
> single instance can support. The first is a limit on the number of file
> handles that Windows allows a process to have open at any one time. In
the
> hundreds of databases example it is clear that only a small subset are
> actually in use at any given time and so setting the databases to
autoclose
> when not in use solves the handle problem. The second problem is that SQL
> Enterprise Manager has some algorithms that don't scale to hundreds of
> databases. Basically, it loads metadata for all of the databases whenever
> you connect to the instance. With hundreds of databases this can take a
> long time (I've heard someone mention an example where it took over an
hour
> to start Enterprise Manager). The solution here is to do all management
> from the command line and not use Enterprise Manager to manage hundreds of
> databases. Neither of these problems apply when you are only talking
about
> 44-50 databases. That should work just fine without any special handling.
> --
> Hal Berenson, SQL Server MVP
> True Mountain Group LLC
>
> "Mark Jones" <mark.jones@.nospam.kimdata.plus.com> wrote in message
> news:FR%ab.8119$vX3.1275275@.wards.force9.net...
> > I have started administrating a system that has 44 databases running on
> the
> > same server. That is 44 user databases.
> >
> > This is running under NT4 SP 6a and SQL 7 SP3
> >
> > I am sure that SQL was not meant to support this number of databases on
> the
> > same server but unable to find any documentation to support this.
> >
> > Does any one have any experience of working with this number of
databases
> or
> > point me in the right direction to documentation on the number of
> supported
> > databases.
> >
> > Any help would be appreciated
> >
> >
>|||Sorry, I meant, Hal (not Greg).
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message
news:e%2398WpAgDHA.3200@.tk2msftngp13.phx.gbl...
> Greg;
> My guess is that you'd probably run into other PRACTICAL issues before you
> run into the file handle issue. I'd generally advise against using
> auto-close for any serious production database. The option has the
potential
> to cause too many maintenance problems, and has the performance issue.
> --
> Linchi Shea
> linchi_shea@.NOSPAMml.com
>
> "Hal Berenson" <haroldb@.truemountainconsulting.com> wrote in message
> news:uPr1iW9fDHA.2152@.tk2msftngp13.phx.gbl...
> > I'm not sure about SQL Server 7.0 at this point, the comments below
really
> > apply to SQL Server 2000.
> >
> > There was a white paper at www.microsoft.com that discussed a scenario
in
> > which a single instance supported several hundred databases. It was the
> > architecture that Microsoft used for a bCentral's hosted small business
> > financials offering (before they acquired Great Plains and decided to
drop
> > the hosted offering). Unfortunately last time I looked I was unable to
> find
> > the white paper.
> >
> > There are really two limits on the PRACTICAL number of databases that a
> > single instance can support. The first is a limit on the number of file
> > handles that Windows allows a process to have open at any one time. In
> the
> > hundreds of databases example it is clear that only a small subset are
> > actually in use at any given time and so setting the databases to
> autoclose
> > when not in use solves the handle problem. The second problem is that
SQL
> > Enterprise Manager has some algorithms that don't scale to hundreds of
> > databases. Basically, it loads metadata for all of the databases
whenever
> > you connect to the instance. With hundreds of databases this can take a
> > long time (I've heard someone mention an example where it took over an
> hour
> > to start Enterprise Manager). The solution here is to do all management
> > from the command line and not use Enterprise Manager to manage hundreds
of
> > databases. Neither of these problems apply when you are only talking
> about
> > 44-50 databases. That should work just fine without any special
handling.
> >
> > --
> > Hal Berenson, SQL Server MVP
> > True Mountain Group LLC
> >
> >
> > "Mark Jones" <mark.jones@.nospam.kimdata.plus.com> wrote in message
> > news:FR%ab.8119$vX3.1275275@.wards.force9.net...
> > > I have started administrating a system that has 44 databases running
on
> > the
> > > same server. That is 44 user databases.
> > >
> > > This is running under NT4 SP 6a and SQL 7 SP3
> > >
> > > I am sure that SQL was not meant to support this number of databases
on
> > the
> > > same server but unable to find any documentation to support this.
> > >
> > > Does any one have any experience of working with this number of
> databases
> > or
> > > point me in the right direction to documentation on the number of
> > supported
> > > databases.
> > >
> > > Any help would be appreciated
> > >
> > >
> >
> >
>