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.
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
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 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."
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.
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
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
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?
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
Thank
To>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...
> 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.
> 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.
>
> 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
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."
> 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.)
>
> 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.
>
> 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.
> 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?
> 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.
> Thanks
> Tom
>|||perhaps you can shoot the guy who came up with a design like that.
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...
> 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.
> 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.
>
> 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
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."
> 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.)
>
> 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.
>
> 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.
> 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?
> 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.
> Thanks
> Tom
>|||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
So If anyone has any answers to my questions I would really appreciate it
Thank
To
-- Quentin Ran wrote: --
>I HAVE NO CONTROL OVER HOW THE APPLICATION WAS WRITTEN
You don't. However, you have the right, even the duty, to tell your bos
that this is going to blow up within two years, don't you? Tell them no wa
to upgrade the application if they do not want to start upgrading/lookin
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 wit
the questions
May not be much of help, but is this not the way to go
Quenti
"tototom" <anonymous@.discussions.microsoft.com> wrote in messag
news:A24BF31D-FEED-4A53-A52E-10B15ADF11E1@.microsoft.com..
> My Company is evaluating an upgrade to an existing product that has bee
converted to SQL server. I HAVE NO CONTROL OVER HOW THE APPLICATION WA
WRITTEN. They have developed a application that creates a new database fo
every single section of work being processed. This means that we will b
starting off with over 10,000 databases and within 1-1/2 to 2 years we coul
be over 40,000 databases. Most databases will be small (4-5Mb in size), an
there is not suppose to be a lot of activity going on in each database.
would expect that no more than 150 will be accessed at any given moment
>> We were hoping to put this on our existing SQL Server (2000 Standar
edition, Dual 1.13Ghz Processors, 2Gb of RAM). This would be on a separat
instance of SQL Server. Also at the moment at least we don't have any right
to this instance. (after setting up the instance per their instructions
ran their install program and it removed all rights. All I can do is star
and stop the instance) Also the Client portion of this program installs MSD
on the workstations. They have removed all rights for this as well
>> 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 /3G
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 th
Master database when the instance starts. They say this will lower th
memory requirements. Each database is supposed to still be attached but no
loaded into memory until it is used the first time after startup. I can fin
very little information on this Trace Flag except "Recovers master databas
only. Skips automatic recovery (at startup) for all databases except th
master database.
>> Does anyone really know how this Trace Flag operates
>> If it does not do the recovery process for any database except for th
master couldn't this cause problems if something happened to the system?
say lost power, etc.
>> 4. How much memory is used per database to just load a database int
memory? (I can not find any documentation on this) Assuming the Trace Fla
works as they say I could still expect to have 2000 databases loaded int
memory at one time and wonder if I will experience memory problems
>> 5. Without rights to the SQL Instance or the MSDE Instances on the Client
what problems might I face? Some of the possibilities we thought of were
Unable to patch or update the instance, Unable to run diagnostics o
utilities, Unable to do backups or restores (Though we don't want to do thi
manually on thousands of databases any way) And then the same issues on th
MSDE workstations.
>> 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?
>> 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.
>> Thanks
>> Tom
>|||The solid fact is that the whole application will crash within 2 years. Is
that not hard enough?
"tototom" <anonymous@.discussions.microsoft.com> wrote in message
news:1FC01620-C793-4F9F-A6EE-2B51FD9C1564@.microsoft.com...
> 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.
> So If anyone has any answers to my questions I would really appreciate it.
> Thanks
> Tom
>
> -- Quentin Ran wrote: --
> >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...
> > 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.
> >> 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.
> >> 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
> 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."
> >> 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.)
> >> 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.
> >> 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.
> >> 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?
> >> 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.
> >> Thanks
> >> Tom
> >|||There are a bunch of benefits for our users in program
functionality.
What are they. Perhaps we can find some holes.
>--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.
>So If anyone has any answers to my questions I would
really appreciate it.
>Thanks
>Tom
>
> -- Quentin Ran wrote: --
> >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...
> > 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.
> >> 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.
> >> 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
> 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."
> >> 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.)
> >> 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.
> >> 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.
> >> 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?
> >> 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.
> >> Thanks
> >> Tom
> >
>.
>|||Tom,
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:
>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.
>So If anyone has any answers to my questions I would really appreciate it.
>Thanks
>Tom
>
> -- Quentin Ran wrote: --
> >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...
> > 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.
> >> 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.
> >> 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
> 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."
> >> 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.)
> >> 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.
> >> 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.
> >> 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?
> >> 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.
> >> Thanks
> >> Tom
> >
>|||If nothing else just the sheer overhead of having to keep that many pointers
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...
> 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.
> 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.
>
> 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
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."
> 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.)
>
> 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.
>
> 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.
> 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?
> 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.
> Thanks
> Tom
>|||tototom,
"tototom" <anonymous@.discussions.microsoft.com> wrote in message
news:A24BF31D-FEED-4A53-A52E-10B15ADF11E1@.microsoft.com...
> 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.
The classic handcuffs scenario. Been there. Got the T-shirt.
>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.
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.
> 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.
> 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."
> Does anyone really know how this Trace Flag operates?
>
Yes, as others pointed out, it's basically an emergency recovery flag.
> 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.
> 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.
> 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.
> 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'.
> 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...
> 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.
> 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.
>
> 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
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."
> 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.)
>
> 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.
>
> 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.
> 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?
> 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.
> Thanks
> Tom
>|||Simple Maths Time...
Smallest Database you can create - 2mb
So already there is 80000 mb of databases before anyone
starts development on it..
Keeping 1 pointer to one database (help me out here I may
be wrong) 16k
You could autoclose it, but with the number of database
you do not want to be doing that otherwise it will take 10
minutes to re-open.
16k * 40000 = 640,000 k of ram (you will need at least one
to keep it open).
CPU - Well I am not even going there but good luck ;)
These connections will be current on your server, so your
network will be rather large.
Solution - Supercomputer or at least a different
architecture to support it, which means paying for extra
hardware. Try that approach if your company is rather
cheap.
PS the design has been done by gibbons.
J
>--Original Message--
>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.
>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.
>
>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 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."
>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.)
>
>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.
>
>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.
>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?
>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.
>Thanks
>Tom
>.
>|||Try opening Enterprise Manager with 10'000 databases.
Got a day or so to wait?
--
--
Mike Epprecht, Microsoft SQL Server MVP
Epprecht Consulting (PTY) LTD
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.NOSPAMepprecht.net
Specialist SQL Server Solutions and Consulting
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:036a01c3daf8$39cd5b50$a601280a@.phx.gbl...
> There are a bunch of benefits for our users in program
> functionality.
> What are they. Perhaps we can find some holes.
> >--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.
> >
> >So If anyone has any answers to my questions I would
> really appreciate it.
> >
> >Thanks
> >
> >Tom
> >
> >
> > -- Quentin Ran wrote: --
> >
> > >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...
> > > 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.
> > >> 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.
> > >> 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
> > 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."
> > >> 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.)
> > >> 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.
> > >> 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.
> > >> 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?
> > >> 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.
> > >> Thanks
> > >> Tom
> > >
> >.
> >|||"tototom" <anonymous@.discussions.microsoft.com> wrote in message
news:A24BF31D-FEED-4A53-A52E-10B15ADF11E1@.microsoft.com...
> 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.
After installing the instance, you should start up a Profiler trace, then
run their install program, and afterwards look at the Profiler results. It
should show you what they are doing to secure the instance, and give you
some clues on how to get back in.|||i understand what it can be like trying to explain to non-technical
management how unreasonable this architecture is.
i would do the following:
1) try and get MS tech say that use of this trace flag for normal prod work
is unsupported or at least a 'bad idea' (and get concrete reasons)
2) print out these responses from folks on this newgroup and show them to
your boss.
3) nobody can give you concrete reasons as to why this many dbs is a bad
idea becuase nobody has ever done it
4) you will be pushing the envelope on how this product is meant to be used
which means you might be hitting snags that are showstoppers that the
developers of sql/server did not (and will not address)
5) document your concerns in an email
good luck,
steve
"tototom" <anonymous@.discussions.microsoft.com> wrote in message
news:A24BF31D-FEED-4A53-A52E-10B15ADF11E1@.microsoft.com...
> 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.
> 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.
>
> 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
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."
> 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.)
>
> 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.
>
> 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.
> 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?
> 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.
> Thanks
> Tom
>|||In addition to most of the posts, some other things to question:
There will doubtless be an upgrade to the app that requires databse =table changes. So if you ad a column to a table you will need to do it =40000 times. Ok you can script this but the time to do it will be =ridiculous.
backup/restore - Even if you manage to get a backup solution to work, =testing it and devising a recovery plan will be a monumental task.
What on earth will happen to the directory size just keeping directory =entries for 40000 files is an overhead - ie the OS overhead to locatethe =file before SQl Server even gets hold of it. Ask the vendor what they =are proposing with respect to spreading the MDF and LDF files across =multiple directories to keep the directory overhead reasonable.
In summary - Not a bright idea!
Mike John
"tototom" <anonymous@.discussions.microsoft.com> wrote in message =news:A24BF31D-FEED-4A53-A52E-10B15ADF11E1@.microsoft.com...
> 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. > > 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.
> > > 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 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." > > 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.)
> > > > 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.
> > > 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.
> > 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? > > 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.
> > Thanks
> > Tom
>|||The first thing that popped into my mind was, how are you going to manage
the backup strategy for 40,000 databases? That could be a bit problematic.
:-)
--
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
Please reply to the newsgroups only, thanks.
"tototom" <anonymous@.discussions.microsoft.com> wrote in message
news:1FC01620-C793-4F9F-A6EE-2B51FD9C1564@.microsoft.com...
> 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.
> So If anyone has any answers to my questions I would really appreciate it.
> Thanks
> Tom
>
> -- Quentin Ran wrote: --
> >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...
> > 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.
> >> 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.
> >> 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
> 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."
> >> 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.)
> >> 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.
> >> 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.
> >> 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?
> >> 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.
> >> Thanks
> >> Tom
> >|||To clarify a couple of issues. The application is a totally re-written and updated version of an existing application that we have been using for a couple of years. It integrates with a couple of other applications written by the same company. All of these are major applications for our company. The backend on the current version does not use SQL technology at all. They said they wrote the application this way so that they could use MSDE on the server as well as the client to get around the 2Gb database limitation of MSDE. This way they could sell it to small firms as well as larger firms like mine. Because they say a small firm would not want to pay for SQL server
The MSDE on the workstations are two purposes. 1. They "Check Out" the data, work on it then check it back in. 2. So they can work on it at a clients office, then come back to the office and synchronize with the main database on the server
I have brought up most of these issues but they say everything is great. They are telling my boss they have had over 60,000 databases running on MSDE without any problems. (not clear on if multiple instances on one server or multiple servers
I just credibility problems with the thoroughness of their testing etc based on past experiences with the current version. So I have got to find facts to support my concerns
Oh just as an FYI another firm in the same industry who is also looking at going to this product projects they would be at 240,000 databases by this summer.
To
-- tototom wrote: --
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.
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
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 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."
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.
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
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.
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?
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.
Thanks
Tom|||HI,
"tototom" <anonymous@.discussions.microsoft.com> wrote in message
news:E8A51823-F1B7-4CF5-A96F-9CE006CEAAA9@.microsoft.com...
> To clarify a couple of issues. The application is a totally re-written and
updated version of an existing application that we have been using for a
couple of years. It integrates with a couple of other applications written
by the same company. All of these are major applications for our company.
The backend on the current version does not use SQL technology at all. They
said they wrote the application this way so that they could use MSDE on the
server as well as the client to get around the 2Gb database limitation of
MSDE. This way they could sell it to small firms as well as larger firms
like mine. Because they say a small firm would not want to pay for SQL
server.
I don't think legally they can do that. Isn't there a 5 connection limit on
MSDE. I also beleave there is some enterprise garb in the MSDE license (not
sure)
> The MSDE on the workstations are two purposes. 1. They "Check Out" the
data, work on it then check it back in. 2. So they can work on it at a
clients office, then come back to the office and synchronize with the main
database on the server.
> I have brought up most of these issues but they say everything is great.
They are telling my boss they have had over 60,000 >databases running on
MSDE without any problems. (not clear on if multiple instances on one server
or multiple servers)
I doubt they have this tested in a user load testing situation. Even if you
could load that many DB and get the system working how many users did they
have running in the test?
> I just credibility problems with the thoroughness of their testing etc
based on past experiences with the current version. So I have got to find
facts to support my concerns.
> Oh just as an FYI another firm in the same industry who is also looking
at going to this product projects they would be at 240,000 databases by this
summer.
Tom, what you need to do is get Microsoft involved in this. Contact your
Microsoft office and get there SQL Tech out to analyse the system and give
back a report/recommendation on the long term viability of this system given
you comapanies growth. Sure it will cost you money (most likely a week of
consulting) but the money it will save later on will justfy it. Tackle it
from a risk management point of view, go to management and say it will
reduce the risk of total system failure. If they don't believe the report
from Microsoft (the makers of the software) then pack your bags matey this
is going to end bad.
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
> Tom
>
>
>
> -- tototom wrote: --
> 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.
> 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.
>
> 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 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."
> 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.)
>
> 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.
>
> 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.
> 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?
> 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.
> Thanks
> Tom
>|||Steve Evans wrote:
>i understand what it can be like trying to explain to non-technical
>management how unreasonable this architecture is.
>i would do the following:
>1) try and get MS tech say that use of this trace flag for normal prod work
>is unsupported or at least a 'bad idea' (and get concrete reasons)
>
You can add to this that no matter how much they've tested it, because
this trace flag is not documented for use in production, it could stop
working at any time. A security hotfix could bring the system down, for
example, and MS will not provide a hotfix or any other help.
SK
>2) print out these responses from folks on this newgroup and show them to
>your boss.
>3) nobody can give you concrete reasons as to why this many dbs is a bad
>idea becuase nobody has ever done it
>4) you will be pushing the envelope on how this product is meant to be used
>which means you might be hitting snags that are showstoppers that the
>developers of sql/server did not (and will not address)
>5) document your concerns in an email
>good luck,
>steve
>"tototom" <anonymous@.discussions.microsoft.com> wrote in message
>news:A24BF31D-FEED-4A53-A52E-10B15ADF11E1@.microsoft.com...
>
>>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.
>
>>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.
>
>>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
>>
>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."
>
>>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.)
>
>>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.
>
>>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.
>
>>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?
>
>>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.
>
>>Thanks
>>Tom
>>
>
>
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment