verted to SQL server. I HAVE NO CONTROL OVER HOW THE APPLICATION WAS WRITTEN
. They have developed a application that creates a new database for every si
ngle section of work being
processed. This means that we will be starting off with over 10,000 database
s and within 1-1/2 to 2 years we could be over 40,000 databases. Most databa
ses will be small (4-5Mb in size), and there is not suppose to be a lot of a
ctivity going on in each da
tabase. I would expect that no more than 150 will be accessed at any given m
oment.
We were hoping to put this on our existing SQL Server (2000 Standard edition
, Dual 1.13Ghz Processors, 2Gb of RAM). This would be on a separate instance
of SQL Server. Also at the moment at least we don't have any rights to this
instance. (after setting u
p the instance per their instructions I ran their install program and it rem
oved all rights. All I can do is start and stop the instance) Also the Clien
t portion of this program installs MSDE on the workstations. They have remov
ed all rights for this as w
ell.
I was hoping to ask a few questions about what they are doing.
1. Has any one had any experience with a system with this many databases?
What were your experiences?
(I know about the 32,767 databases per instance limit)
2. We were hoping to install another Gigabyte of RAM and use the /3GB switch
. Are there any problems doing this with SQL 2000 Standard edition?
3. They are using a Trace Flag during startup (-T3608) to only load the Mast
er database when the instance starts. They say this will lower the memory re
quirements. Each database is supposed to still be attached but not loaded in
to memory until it is used
the first time after startup. I can find very little information on this Tra
ce Flag except "Recovers master database only. Skips automatic recovery (at
startup) for all databases except the master database."
Does anyone really know how this Trace Flag operates?
If it does not do the recovery process for any database except for the maste
r couldn't this cause problems if something happened to the system? ( say lo
st power, etc.)
4. How much memory is used per database to just load a database into memory?
(I can not find any documentation on this) Assuming the Trace Flag works as
they say I could still expect to have 2000 databases loaded into memory at
one time and wonder if I wi
ll experience memory problems.
5. Without rights to the SQL Instance or the MSDE Instances on the Clients w
hat problems might I face? Some of the possibilities we thought of were: Una
ble to patch or update the instance, Unable to run diagnostics or utilities,
Unable to do backups or re
stores (Though we don't want to do this manually on thousands of databases a
ny way) And then the same issues on the MSDE workstations.
6. They say they are going to provide us with some sort of a backup & restor
e utility they are working on. If this does not happen any ideas on what cou
ld be done?
7. Any other problems or issues you can think of that I may not have?
This entire situation does not seem very hopeful. I look forward to any feed
back you can give.
Thanks
Tom>I HAVE NO CONTROL OVER HOW THE APPLICATION WAS WRITTEN.
You don't. However, you have the right, even the duty, to tell your boss
that this is going to blow up within two years, don't you? Tell them no way
to upgrade the application if they do not want to start upgrading/looking
for a replacement halfway of this upgrading. After this evaluation is dead,
you will look at a whole new picture. Probably no need of bothering with
the questions.
May not be much of help, but is this not the way to go?
Quentin
"tototom" <anonymous@.discussions.microsoft.com> wrote in message
news:A24BF31D-FEED-4A53-A52E-10B15ADF11E1@.microsoft.com...
quote:
> My Company is evaluating an upgrade to an existing product that has been
converted to SQL server. I HAVE NO CONTROL OVER HOW THE APPLICATION WAS
WRITTEN. They have developed a application that creates a new database for
every single section of work being processed. This means that we will be
starting off with over 10,000 databases and within 1-1/2 to 2 years we could
be over 40,000 databases. Most databases will be small (4-5Mb in size), and
there is not suppose to be a lot of activity going on in each database. I
would expect that no more than 150 will be accessed at any given moment.
quote:
> We were hoping to put this on our existing SQL Server (2000 Standard
edition, Dual 1.13Ghz Processors, 2Gb of RAM). This would be on a separate
instance of SQL Server. Also at the moment at least we don't have any rights
to this instance. (after setting up the instance per their instructions I
ran their install program and it removed all rights. All I can do is start
and stop the instance) Also the Client portion of this program installs MSDE
on the workstations. They have removed all rights for this as well.
quote:
>
> I was hoping to ask a few questions about what they are doing.
> 1. Has any one had any experience with a system with this many databases?
> What were your experiences?
> (I know about the 32,767 databases per instance limit)
> 2. We were hoping to install another Gigabyte of RAM and use the /3GB
switch. Are there any problems doing this with SQL 2000 Standard edition?
quote:
> 3. They are using a Trace Flag during startup (-T3608) to only load the
Master database when the instance starts. They say this will lower the
memory requirements. Each database is supposed to still be attached but not
loaded into memory until it is used the first time after startup. I can find
very little information on this Trace Flag except "Recovers master database
only. Skips automatic recovery (at startup) for all databases except the
master database."
quote:
> Does anyone really know how this Trace Flag operates?
> If it does not do the recovery process for any database except for the
master couldn't this cause problems if something happened to the system? (
say lost power, etc.)
quote:
>
> 4. How much memory is used per database to just load a database into
memory? (I can not find any documentation on this) Assuming the Trace Flag
works as they say I could still expect to have 2000 databases loaded into
memory at one time and wonder if I will experience memory problems.
quote:
>
> 5. Without rights to the SQL Instance or the MSDE Instances on the Clients
what problems might I face? Some of the possibilities we thought of were:
Unable to patch or update the instance, Unable to run diagnostics or
utilities, Unable to do backups or restores (Though we don't want to do this
manually on thousands of databases any way) And then the same issues on the
MSDE workstations.
quote:
> 6. They say they are going to provide us with some sort of a backup &
restore utility they are working on. If this does not happen any ideas on
what could be done?
quote:
> 7. Any other problems or issues you can think of that I may not have?
>
> This entire situation does not seem very hopeful. I look forward to any
feedback you can give.
quote:|||perhaps you can shoot the guy who came up with a design like that.
> Thanks
> Tom
>
Anyhow I wouldn't recommend this, and definitely not on a standard edition
with that amount of cpu and ram
Regards,
Dandy Weyn
MCSE, MCSA, MCDBA, MCT
www.dandyman.net
"tototom" <anonymous@.discussions.microsoft.com> wrote in message
news:A24BF31D-FEED-4A53-A52E-10B15ADF11E1@.microsoft.com...
quote:
> My Company is evaluating an upgrade to an existing product that has been
converted to SQL server. I HAVE NO CONTROL OVER HOW THE APPLICATION WAS
WRITTEN. They have developed a application that creates a new database for
every single section of work being processed. This means that we will be
starting off with over 10,000 databases and within 1-1/2 to 2 years we could
be over 40,000 databases. Most databases will be small (4-5Mb in size), and
there is not suppose to be a lot of activity going on in each database. I
would expect that no more than 150 will be accessed at any given moment.
quote:
> We were hoping to put this on our existing SQL Server (2000 Standard
edition, Dual 1.13Ghz Processors, 2Gb of RAM). This would be on a separate
instance of SQL Server. Also at the moment at least we don't have any rights
to this instance. (after setting up the instance per their instructions I
ran their install program and it removed all rights. All I can do is start
and stop the instance) Also the Client portion of this program installs MSDE
on the workstations. They have removed all rights for this as well.
quote:
>
> I was hoping to ask a few questions about what they are doing.
> 1. Has any one had any experience with a system with this many databases?
> What were your experiences?
> (I know about the 32,767 databases per instance limit)
> 2. We were hoping to install another Gigabyte of RAM and use the /3GB
switch. Are there any problems doing this with SQL 2000 Standard edition?
quote:
> 3. They are using a Trace Flag during startup (-T3608) to only load the
Master database when the instance starts. They say this will lower the
memory requirements. Each database is supposed to still be attached but not
loaded into memory until it is used the first time after startup. I can find
very little information on this Trace Flag except "Recovers master database
only. Skips automatic recovery (at startup) for all databases except the
master database."
quote:
> Does anyone really know how this Trace Flag operates?
> If it does not do the recovery process for any database except for the
master couldn't this cause problems if something happened to the system? (
say lost power, etc.)
quote:
>
> 4. How much memory is used per database to just load a database into
memory? (I can not find any documentation on this) Assuming the Trace Flag
works as they say I could still expect to have 2000 databases loaded into
memory at one time and wonder if I will experience memory problems.
quote:
>
> 5. Without rights to the SQL Instance or the MSDE Instances on the Clients
what problems might I face? Some of the possibilities we thought of were:
Unable to patch or update the instance, Unable to run diagnostics or
utilities, Unable to do backups or restores (Though we don't want to do this
manually on thousands of databases any way) And then the same issues on the
MSDE workstations.
quote:
> 6. They say they are going to provide us with some sort of a backup &
restore utility they are working on. If this does not happen any ideas on
what could be done?
quote:
> 7. Any other problems or issues you can think of that I may not have?
>
> This entire situation does not seem very hopeful. I look forward to any
feedback you can give.
quote:|||I have expressed plenty of concerns but I am just one opinion. Without answe
> Thanks
> Tom
>
rs to my questions and solid facts to back them up my opinions will not be e
nough. There are a bunch of benefits for our users in program functionality.
But the backend is terribl
e.
So If anyone has any answers to my questions I would really appreciate it.
Thanks
Tom
-- Quentin Ran wrote: --
quote:
>I HAVE NO CONTROL OVER HOW THE APPLICATION WAS WRITTEN.
You don't. However, you have the right, even the duty, to tell your boss
that this is going to blow up within two years, don't you? Tell them no way
to upgrade the application if they do not want to start upgrading/looking
for a replacement halfway of this upgrading. After this evaluation is dead,
you will look at a whole new picture. Probably no need of bothering with
the questions.
May not be much of help, but is this not the way to go?
Quentin
"tototom" <anonymous@.discussions.microsoft.com> wrote in message
news:A24BF31D-FEED-4A53-A52E-10B15ADF11E1@.microsoft.com...
quote:
> My Company is evaluating an upgrade to an existing product that has been
converted to SQL server. I HAVE NO CONTROL OVER HOW THE APPLICATION WAS
WRITTEN. They have developed a application that creates a new database for
every single section of work being processed. This means that we will be
starting off with over 10,000 databases and within 1-1/2 to 2 years we could
be over 40,000 databases. Most databases will be small (4-5Mb in size), and
there is not suppose to be a lot of activity going on in each database. I
would expect that no more than 150 will be accessed at any given moment.
quote:|||The solid fact is that the whole application will crash within 2 years. Is
edition, Dual 1.13Ghz Processors, 2Gb of RAM). This would be on a separate
instance of SQL Server. Also at the moment at least we don't have any rights
to this instance. (after setting up the instance per their instructions I
ran their install program and it removed all rights. All I can do is start
and stop the instance) Also the Client portion of this program installs MSDE
on the workstations. They have removed all rights for this as well.[QUOTE]
> What were your experiences?
> (I know about the 32,767 databases per instance limit)
switch. Are there any problems doing this with SQL 2000 Standard edition?[QUOTE]
Master database when the instance starts. They say this will lower the
memory requirements. Each database is supposed to still be attached but not
loaded into memory until it is used the first time after startup. I can find
very little information on this Trace Flag except "Recovers master database
only. Skips automatic recovery (at startup) for all databases except the
master database."[QUOTE]
master couldn't this cause problems if something happened to the system? (
say lost power, etc.)[QUOTE]
memory? (I can not find any documentation on this) Assuming the Trace Flag
works as they say I could still expect to have 2000 databases loaded into
memory at one time and wonder if I will experience memory problems.[QUOTE]
what problems might I face? Some of the possibilities we thought of were:
Unable to patch or update the instance, Unable to run diagnostics or
utilities, Unable to do backups or restores (Though we don't want to do this
manually on thousands of databases any way) And then the same issues on the
MSDE workstations.[QUOTE]
restore utility they are working on. If this does not happen any ideas on
what could be done?[QUOTE]
feedback you can give.[QUOTE]
>
that not hard enough?
"tototom" <anonymous@.discussions.microsoft.com> wrote in message
news:1FC01620-C793-4F9F-A6EE-2B51FD9C1564@.microsoft.com...
quote:
> I have expressed plenty of concerns but I am just one opinion. Without
answers to my questions and solid facts to back them up my opinions will not
be enough. There are a bunch of benefits for our users in program
functionality. But the backend is terrible.
quote:
> So If anyone has any answers to my questions I would really appreciate it.
> Thanks
> Tom
>
> -- Quentin Ran wrote: --
>
> You don't. However, you have the right, even the duty, to tell your
boss
quote:
> that this is going to blow up within two years, don't you? Tell them
no way
quote:
> to upgrade the application if they do not want to start
upgrading/looking
quote:
> for a replacement halfway of this upgrading. After this evaluation
is dead,
quote:
> you will look at a whole new picture. Probably no need of bothering
with
quote:
> the questions.
> May not be much of help, but is this not the way to go?
> Quentin
> "tototom" <anonymous@.discussions.microsoft.com> wrote in message
> news:A24BF31D-FEED-4A53-A52E-10B15ADF11E1@.microsoft.com...
been[QUOTE]
> converted to SQL server. I HAVE NO CONTROL OVER HOW THE APPLICATION
WAS
quote:
> WRITTEN. They have developed a application that creates a new
database for
quote:
> every single section of work being processed. This means that we will
be
quote:
> starting off with over 10,000 databases and within 1-1/2 to 2 years
we could
quote:
> be over 40,000 databases. Most databases will be small (4-5Mb in
size), and
quote:
> there is not suppose to be a lot of activity going on in each
database. I
quote:
> would expect that no more than 150 will be accessed at any given
moment.
quote:
Standard[QUOTE]
> edition, Dual 1.13Ghz Processors, 2Gb of RAM). This would be on a
separate
quote:
> instance of SQL Server. Also at the moment at least we don't have any
rights
quote:
> to this instance. (after setting up the instance per their
instructions I
quote:
> ran their install program and it removed all rights. All I can do is
start
quote:
> and stop the instance) Also the Client portion of this program
installs MSDE
quote:
> on the workstations. They have removed all rights for this as well.
databases?[QUOTE]
/3GB[QUOTE]
> switch. Are there any problems doing this with SQL 2000 Standard
edition?
quote:
load the[QUOTE]
> Master database when the instance starts. They say this will lower
the
quote:
> memory requirements. Each database is supposed to still be attached
but not
quote:
> loaded into memory until it is used the first time after startup. I
can find
quote:
> very little information on this Trace Flag except "Recovers master
database
quote:
> only. Skips automatic recovery (at startup) for all databases except
the
quote:
> master database."
the[QUOTE]
> master couldn't this cause problems if something happened to the
system? (
quote:
> say lost power, etc.)
into[QUOTE]
> memory? (I can not find any documentation on this) Assuming the Trace
Flag
quote:
> works as they say I could still expect to have 2000 databases loaded
into
quote:
> memory at one time and wonder if I will experience memory problems.
the Clients[QUOTE]
> what problems might I face? Some of the possibilities we thought of
were:
quote:
> Unable to patch or update the instance, Unable to run diagnostics or
> utilities, Unable to do backups or restores (Though we don't want to
do this
quote:
> manually on thousands of databases any way) And then the same issues
on the
quote:
> MSDE workstations.
backup &[QUOTE]
> restore utility they are working on. If this does not happen any
ideas on[QUOTE]
> what could be done?
have?[QUOTE]
to any[QUOTE]
> feedback you can give.|||There are a bunch of benefits for our users in program
functionality.
What are they. Perhaps we can find some holes.
quote:
>--Original Message--
>I have expressed plenty of concerns but I am just one
opinion. Without answers to my questions and solid facts
to back them up my opinions will not be enough. There are
a bunch of benefits for our users in program
functionality. But the backend is terrible.
quote:
>So If anyone has any answers to my questions I would
really appreciate it.
quote:
>Thanks
>Tom
>
> -- Quentin Ran wrote: --
>
WRITTEN.[QUOTE]
> You don't. However, you have the right, even the
duty, to tell your boss
quote:
> that this is going to blow up within two years,
don't you? Tell them no way
quote:
> to upgrade the application if they do not want to
start upgrading/looking
quote:
> for a replacement halfway of this upgrading. After
this evaluation is dead,
quote:
> you will look at a whole new picture. Probably no
need of bothering with
quote:
> the questions.
> May not be much of help, but is this not the way to
go?
quote:
> Quentin
> "tototom" <anonymous@.discussions.microsoft.com>
wrote in message
quote:
> news:A24BF31D-FEED-4A53-A52E-
10B15ADF11E1@.microsoft.com...
quote:
product that has been[QUOTE]
> converted to SQL server. I HAVE NO CONTROL OVER HOW
THE APPLICATION WAS
quote:
> WRITTEN. They have developed a application that
creates a new database for
quote:
> every single section of work being processed. This
means that we will be
quote:
> starting off with over 10,000 databases and within 1-
1/2 to 2 years we could
quote:
> be over 40,000 databases. Most databases will be
small (4-5Mb in size), and
quote:
> there is not suppose to be a lot of activity going
on in each database. I
quote:
> would expect that no more than 150 will be accessed
at any given moment.
quote:
Server (2000 Standard[QUOTE]
> edition, Dual 1.13Ghz Processors, 2Gb of RAM). This
would be on a separate
quote:
> instance of SQL Server. Also at the moment at least
we don't have any rights
quote:
> to this instance. (after setting up the instance per
their instructions I
quote:
> ran their install program and it removed all rights.
All I can do is start
quote:
> and stop the instance) Also the Client portion of
this program installs MSDE
quote:
> on the workstations. They have removed all rights
for this as well.
quote:
they are doing.[QUOTE]
with this many databases?[QUOTE]
limit)[QUOTE]
RAM and use the /3GB[QUOTE]
> switch. Are there any problems doing this with SQL
2000 Standard edition?
quote:
T3608) to only load the[QUOTE]
> Master database when the instance starts. They say
this will lower the
quote:
> memory requirements. Each database is supposed to
still be attached but not
quote:
> loaded into memory until it is used the first time
after startup. I can find
quote:
> very little information on this Trace Flag
except "Recovers master database
quote:
> only. Skips automatic recovery (at startup) for all
databases except the
quote:
> master database."
operates?[QUOTE]
database except for the[QUOTE]
> master couldn't this cause problems if something
happened to the system? (
quote:
> say lost power, etc.)
load a database into[QUOTE]
> memory? (I can not find any documentation on this)
Assuming the Trace Flag
quote:
> works as they say I could still expect to have 2000
databases loaded into
quote:
> memory at one time and wonder if I will experience
memory problems.
quote:
MSDE Instances on the Clients[QUOTE]
> what problems might I face? Some of the
possibilities we thought of were:
quote:
> Unable to patch or update the instance, Unable to
run diagnostics or
quote:
> utilities, Unable to do backups or restores (Though
we don't want to do this
quote:
> manually on thousands of databases any way) And then
the same issues on the
quote:
> MSDE workstations.
some sort of a backup &[QUOTE]
> restore utility they are working on. If this does
not happen any ideas on
quote:|||Tom,
> what could be done?
that I may not have?[QUOTE]
hopeful. I look forward to any[QUOTE]
> feedback you can give.
>.
>
I would argue against using a product that relies on a non-documented
trace flag, for one (it's documented by MS only to use temporarily to
move the msdb and model databases). What other non-documented things do
they use that they aren't telling you about? If you would really be
installing instances of MSDE and SQL Server that you can't upgrade or
patch, what will happen when the next Slammer rolls around? Whenever I
see a vendor attempt to hide everything, I assume the worst - kludges,
no attention to security, no hope if the software breaks and the vendor
closes shop. What access will they provide for a security analysis, for
example? And are they providing the product with the usual as-is
disclaimer, with no responsibility for anything? Sounds bad to me.
SK
tototom wrote:
quote:
>I have expressed plenty of concerns but I am just one opinion. Without answers to my questi
ons and solid facts to back them up my opinions will not be enough. There are a bunch of ben
efits for our users in program functionality. But the backend is terrib
le.
quote:|||If nothing else just the sheer overhead of having to keep that many pointers
>So If anyone has any answers to my questions I would really appreciate it.
>Thanks
>Tom
>
> -- Quentin Ran wrote: --
>
> You don't. However, you have the right, even the duty, to tell your b
oss
> that this is going to blow up within two years, don't you? Tell them
no way
> to upgrade the application if they do not want to start upgrading/look
ing
> for a replacement halfway of this upgrading. After this evaluation is
dead,
> you will look at a whole new picture. Probably no need of bothering w
ith
> the questions.
> May not be much of help, but is this not the way to go?
> Quentin
> "tototom" <anonymous@.discussions.microsoft.com> wrote in message
> news:A24BF31D-FEED-4A53-A52E-10B15ADF11E1@.microsoft.com...
> converted to SQL server. I HAVE NO CONTROL OVER HOW THE APPLICATION WA
S
> WRITTEN. They have developed a application that creates a new database
for
> every single section of work being processed. This means that we will
be
> starting off with over 10,000 databases and within 1-1/2 to 2 years we
could
> be over 40,000 databases. Most databases will be small (4-5Mb in size)
, and
> there is not suppose to be a lot of activity going on in each database
. I
> would expect that no more than 150 will be accessed at any given momen
t.
> edition, Dual 1.13Ghz Processors, 2Gb of RAM). This would be on a sepa
rate
> instance of SQL Server. Also at the moment at least we don't have any
rights
> to this instance. (after setting up the instance per their instruction
s I
> ran their install program and it removed all rights. All I can do is s
tart
> and stop the instance) Also the Client portion of this program install
s MSDE
> on the workstations. They have removed all rights for this as well.
> switch. Are there any problems doing this with SQL 2000 Standard editi
on?
> Master database when the instance starts. They say this will lower the
> memory requirements. Each database is supposed to still be attached bu
t not
> loaded into memory until it is used the first time after startup. I ca
n find
> very little information on this Trace Flag except "Recovers master dat
abase
> only. Skips automatic recovery (at startup) for all databases except t
he
> master database."
> master couldn't this cause problems if something happened to the syste
m? (
> say lost power, etc.)
> memory? (I can not find any documentation on this) Assuming the Trace
Flag
> works as they say I could still expect to have 2000 databases loaded i
nto
> memory at one time and wonder if I will experience memory problems.
> what problems might I face? Some of the possibilities we thought of we
re:
> Unable to patch or update the instance, Unable to run diagnostics or
> utilities, Unable to do backups or restores (Though we don't want to d
o this
> manually on thousands of databases any way) And then the same issues o
n the
> MSDE workstations.
> restore utility they are working on. If this does not happen any ideas
on
> what could be done?
> feedback you can give.
>
to databases in memory will steal way, way too much memory. If you use
autoclose to get around this then you get a perf hit every time a database
needs to be opened. If you want to email me we can dig into this further -
rwaymi@.microsoft.com.
Richard Waymire, MCSE, MCDBA
This posting is provided "AS IS" with no warranties, and confers no rights.
"tototom" <anonymous@.discussions.microsoft.com> wrote in message
news:A24BF31D-FEED-4A53-A52E-10B15ADF11E1@.microsoft.com...
quote:
> My Company is evaluating an upgrade to an existing product that has been
converted to SQL server. I HAVE NO CONTROL OVER HOW THE APPLICATION WAS
WRITTEN. They have developed a application that creates a new database for
every single section of work being processed. This means that we will be
starting off with over 10,000 databases and within 1-1/2 to 2 years we could
be over 40,000 databases. Most databases will be small (4-5Mb in size), and
there is not suppose to be a lot of activity going on in each database. I
would expect that no more than 150 will be accessed at any given moment.
quote:
> We were hoping to put this on our existing SQL Server (2000 Standard
edition, Dual 1.13Ghz Processors, 2Gb of RAM). This would be on a separate
instance of SQL Server. Also at the moment at least we don't have any rights
to this instance. (after setting up the instance per their instructions I
ran their install program and it removed all rights. All I can do is start
and stop the instance) Also the Client portion of this program installs MSDE
on the workstations. They have removed all rights for this as well.
quote:
>
> I was hoping to ask a few questions about what they are doing.
> 1. Has any one had any experience with a system with this many databases?
> What were your experiences?
> (I know about the 32,767 databases per instance limit)
> 2. We were hoping to install another Gigabyte of RAM and use the /3GB
switch. Are there any problems doing this with SQL 2000 Standard edition?
quote:
> 3. They are using a Trace Flag during startup (-T3608) to only load the
Master database when the instance starts. They say this will lower the
memory requirements. Each database is supposed to still be attached but not
loaded into memory until it is used the first time after startup. I can find
very little information on this Trace Flag except "Recovers master database
only. Skips automatic recovery (at startup) for all databases except the
master database."
quote:
> Does anyone really know how this Trace Flag operates?
> If it does not do the recovery process for any database except for the
master couldn't this cause problems if something happened to the system? (
say lost power, etc.)
quote:
>
> 4. How much memory is used per database to just load a database into
memory? (I can not find any documentation on this) Assuming the Trace Flag
works as they say I could still expect to have 2000 databases loaded into
memory at one time and wonder if I will experience memory problems.
quote:
>
> 5. Without rights to the SQL Instance or the MSDE Instances on the Clients
what problems might I face? Some of the possibilities we thought of were:
Unable to patch or update the instance, Unable to run diagnostics or
utilities, Unable to do backups or restores (Though we don't want to do this
manually on thousands of databases any way) And then the same issues on the
MSDE workstations.
quote:
> 6. They say they are going to provide us with some sort of a backup &
restore utility they are working on. If this does not happen any ideas on
what could be done?
quote:
> 7. Any other problems or issues you can think of that I may not have?
>
> This entire situation does not seem very hopeful. I look forward to any
feedback you can give.
quote:|||tototom,
> Thanks
> Tom
>
"tototom" <anonymous@.discussions.microsoft.com> wrote in message
news:A24BF31D-FEED-4A53-A52E-10B15ADF11E1@.microsoft.com...
quote:
> My Company is evaluating an upgrade to an existing product that has been
converted to >SQL server. I HAVE NO CONTROL OVER HOW THE APPLICATION WAS
quote:
>WRITTEN.
The classic handcuffs scenario. Been there. Got the T-shirt.
quote:
>They have developed a application that creates a new database for every
single section of
quote:
>work being processed. This means that we will be starting off with over
10,000 databases and within 1-1/2 to 2 years we could be over 40,000
databases. Most databases will be >small (4-5Mb in size), and there is not
suppose to be a lot of activity going on in each >database. I would expect
that no more than 150 will be accessed at any given moment.
That is patently ridiculous.
Their architecture people should be fired, blacklisted, shipped to a Gulag
in Ukraine, and forced to write in COBOL against ISAM files for eternity.
quote:
> We were hoping to put this on our existing SQL Server (2000 Standard
edition, Dual 1.13Ghz Processors, 2Gb of RAM). This would be on a separate
instance of SQL Server. Also at the moment at least we don't have any rights
to this instance. (after setting up the instance per their instructions I
ran their install program and it removed all rights. All I can do is start
and stop the instance) Also the Client portion of this program installs MSDE
on the workstations. They have removed all rights for this as well.
Your existing SQL Server will be summarily slaughtered.
quote:
> 3. They are using a Trace Flag during startup (-T3608) to only load the
Master database when the instance starts. They say this will lower the
memory requirements. Each database is supposed to still be attached but not
loaded into memory until it is used the first time after startup. I can find
very little information on this Trace Flag except "Recovers master database
only. Skips automatic recovery (at startup) for all databases except the
master database."
quote:
> Does anyone really know how this Trace Flag operates?
>
Yes, as others pointed out, it's basically an emergency recovery flag.
quote:
> If it does not do the recovery process for any database except for the
master couldn't this cause problems if something happened to the system? (
say lost power, etc.)
No way to tell. But, it sounds very bad.
quote:
> 4. How much memory is used per database to just load a database into
memory? (I can not find any documentation on this) Assuming the Trace Flag
works as they say I could still expect to have 2000 databases loaded into
memory at one time and wonder if I will experience memory problems.
I don't know but I imagine the thread load would have to be pretty big
managing all those files.
Also, if all of these databases are in use frequently, your disks will get
hammered, because there's no way that much data will reside in hot cache. I
can only imagine the overhead on the buffer management.
quote:
> 5. Without rights to the SQL Instance or the MSDE Instances on the Clients
what problems might I face? Some of the possibilities we thought of were:
Unable to patch or update the instance, Unable to run diagnostics or
utilities, Unable to do backups or restores (Though we don't want to do this
manually on thousands of databases any way) And then the same issues on the
MSDE workstations.
Yes, all of that.
quote:
> 6. They say they are going to provide us with some sort of a backup &
restore utility they are working on. If this does not happen any ideas on
what could be done?
Ya, I'd trust a backup utility from somebody with this much 'design
expertise'.
quote:
> This entire situation does not seem very hopeful. I look forward to any
feedback you can give.
No. If I were you, I'd probably get that laserjet busy firing out resumes.
James Hokes|||Hi,
1. Having so many databases for an application show that the designer has a
lack of understanding for Database Design. The only reason I can see for
doing that is they want to separate data, which means that the designer has
never heard of Parent/Child relationships
2. Not sure on this one I thought you needed Advanced Server for 4GB
support?
3. I totally agree with Steve using an undocumented feature is not the way
to go. Again they are trying to create solutions to problems that their
design has created. The real solution is for them to fix the design.
4. Sorry, I'm sure it depends on objects and user per DB
5. If you are ADMIN of the Local machine then you are a member of the SYSTEM
Owner group. So perhaps try that. If not then get there script and hack
through that until you find where they change the password or remove
Administrators from the SQL instance.
6. If they don't you can use Maintenance plans and create a backup job for
user databases.
7. Well
There is typically one reason that you backup a system and that is to
restore it after a disaster. Lots of luck restoring 40,000 DB
Typically you would want to do transactional backups as well say every hour,
in this way you can recover to the last hour instead of the last day. So
say you are going to have 7 TLOG backups * 40000 DB = 280,000 files to TLOG
to restore plus the 40,000 backups (it just gets better)
Say you want to create your own report (down the track, after these people
have your companies money and have skipped to Jamaica ) just to get
together all the records in the application you will need to union 40,000
DB's!
Lots of luck
I hope this helps
regards
Greg O MCSD
http://www.ag-software.com/ags_scribe_index.aspx. SQL Scribe Documentation
Builder, the quickest way to document your database
http://www.ag-software.com/ags_SSEPE_index.aspx. AGS SQL Server Extended
Property Extended properties manager for SQL 2000
http://www.ag-software.com/IconExtractionProgram.aspx. Free icon extraction
program
http://www.ag-software.com. Free programming tools
"tototom" <anonymous@.discussions.microsoft.com> wrote in message
news:A24BF31D-FEED-4A53-A52E-10B15ADF11E1@.microsoft.com...
quote:
> My Company is evaluating an upgrade to an existing product that has been
converted to SQL server. I HAVE NO CONTROL OVER HOW THE APPLICATION WAS
WRITTEN. They have developed a application that creates a new database for
every single section of work being processed. This means that we will be
starting off with over 10,000 databases and within 1-1/2 to 2 years we could
be over 40,000 databases. Most databases will be small (4-5Mb in size), and
there is not suppose to be a lot of activity going on in each database. I
would expect that no more than 150 will be accessed at any given moment.
quote:
> We were hoping to put this on our existing SQL Server (2000 Standard
edition, Dual 1.13Ghz Processors, 2Gb of RAM). This would be on a separate
instance of SQL Server. Also at the moment at least we don't have any rights
to this instance. (after setting up the instance per their instructions I
ran their install program and it removed all rights. All I can do is start
and stop the instance) Also the Client portion of this program installs MSDE
on the workstations. They have removed all rights for this as well.
quote:
>
> I was hoping to ask a few questions about what they are doing.
> 1. Has any one had any experience with a system with this many databases?
> What were your experiences?
> (I know about the 32,767 databases per instance limit)
> 2. We were hoping to install another Gigabyte of RAM and use the /3GB
switch. Are there any problems doing this with SQL 2000 Standard edition?
quote:
> 3. They are using a Trace Flag during startup (-T3608) to only load the
Master database when the instance starts. They say this will lower the
memory requirements. Each database is supposed to still be attached but not
loaded into memory until it is used the first time after startup. I can find
very little information on this Trace Flag except "Recovers master database
only. Skips automatic recovery (at startup) for all databases except the
master database."
quote:
> Does anyone really know how this Trace Flag operates?
> If it does not do the recovery process for any database except for the
master couldn't this cause problems if something happened to the system? (
say lost power, etc.)
quote:
>
> 4. How much memory is used per database to just load a database into
memory? (I can not find any documentation on this) Assuming the Trace Flag
works as they say I could still expect to have 2000 databases loaded into
memory at one time and wonder if I will experience memory problems.
quote:
>
> 5. Without rights to the SQL Instance or the MSDE Instances on the Clients
what problems might I face? Some of the possibilities we thought of were:
Unable to patch or update the instance, Unable to run diagnostics or
utilities, Unable to do backups or restores (Though we don't want to do this
manually on thousands of databases any way) And then the same issues on the
MSDE workstations.
quote:
> 6. They say they are going to provide us with some sort of a backup &
restore utility they are working on. If this does not happen any ideas on
what could be done?
quote:
> 7. Any other problems or issues you can think of that I may not have?
>
> This entire situation does not seem very hopeful. I look forward to any
feedback you can give.
quote:
> Thanks
> Tom
>
No comments:
Post a Comment