MDAC Roadmap and .mdb files

I know this isn't SQL server related but this seemed to be the only appropriate forum.

I would like to work with Access databases (.mdb files) via (non-managed) C++. At the moment I'm using the ODBC provider for OleDB. However I see from the MDAC "Data Access Technologies Road Map" that this provider is deprecated. The OleDB/ADO alternatives seem to be focussing on SQL server only; I just want a 'local', non-networked desktop system. What technology should I use that isn't deprecated?

Yours,

Peter

If you are using C++ and either OLE DB or ODBC then you might want to consider SQL Server 2005 Express Edition (http://msdn.microsoft.com/sql/express/).

I'd also recommend looking at C++/CLI (there's an Express edition for Visual Studio 2005 also - http://msdn.microsoft.com/vstudio/express/visualc/default.aspx). It's now very straightforward to mix native and .Net code so you can get the best of both worlds - reuse your code and add new features/UI with .Net at your own pace http://msdn.microsoft.com/msdnmag/issues/04/05/VisualC2005/ and http://msdn.microsoft.com/msdnmag/issues/05/01/COptimizations/

|||

Thanks for your answer. That has given me confidence that I can stick with my current system for the time being, but shift to SQL Server in the future, as required. My database needs are pretty simple and I think I could do it without actually changing any code apart from the connection string.

Pete

Maxing out log files when using SIMPLE

We have our databases set to SIMPLE model, but we continue to blow up during DataWarehouse loads because the log file fills. Our understanding is that SIMPLE should have no logging yet it appers to. I have 2 requests.

1) Is there a command to tell the database prior to the DataWarehouse load to not log any transactions?

2) Do we need to do more then set the DB to SIMPLE to get a general "no log" effect?

Thanks,
Todd1) No. Logging is never entirely turned off. The transaction log is simply not saved when you are in SIMPLE recovery mode.

2) Probably. The culprit is ikely to be your code. I would guess there is either:
a) A begin transaction statement somewhere way at the top of the process
or
b) the BCP/DTS load command is not batching the rows it is inserting.|||I know the culprit is the code. There is a join of massive tables. We had gone the batching route but it still was blowing up. We have ben increasing the max size of the log but were hoping we could stop going that direction.

Thanks|||If this is a complete refresh of the table (meaning you can drop it before running the insert), you can try to write the insert as SELECT INTO.|||With SIMPLE recovery, the log is not needed for backup, but SQL Server still needs it for recovery. Therefore every transaction is still logged, causing the log file to expand if there is no free space.

A section of a log file is marked for reuse as soon as all transactions are committed or rolled back and they are not needed for recovery, meaning the data has been written to disk during a checkpoint.|||Thanks for all the input

Maximum Recommended Databases

I've read MSDN that the theoretical maximum number of databases per
SQL server instance is 32,767. But I'd like to know if there is a
real world recommendation from Microsoft. I know this sounds like a
ridiculous question, but I've inherited an instance with approximately
1,000 databases (and growing) and I'm trying to determine when I
should expect major performance issues (don't laugh). I have a plan
for reducing the number of databases, but it won't happen over night.
I know a lot of this depends on database usage, but I'm just looking
for any information I can find. Thanks in advance!
I have a client with over 5000 databases and still growing. The usage
pattern is such that only a few dozen or even a few hundred are heavily used
at any one time. The system is performing quite well.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Chris Reeder" <clreeder@.gmail.com> wrote in message
news:1186429044.319226.206990@.b79g2000hse.googlegr oups.com...
> I've read MSDN that the theoretical maximum number of databases per
> SQL server instance is 32,767. But I'd like to know if there is a
> real world recommendation from Microsoft. I know this sounds like a
> ridiculous question, but I've inherited an instance with approximately
> 1,000 databases (and growing) and I'm trying to determine when I
> should expect major performance issues (don't laugh). I have a plan
> for reducing the number of databases, but it won't happen over night.
> I know a lot of this depends on database usage, but I'm just looking
> for any information I can find. Thanks in advance!
>
|||I don't think I'm going to provide any new information that you don't
already have. But this is kind of like asking how big can a database be, or
how many rows can I get into a table? As the number of databases goes up,
the usage pattern really determines how detrimental each additional database
is to performance (let's leave administration costs out of this for now).
So many factors are involved that it's impossible for anyone to come up with
a real number that would apply to your situation and my situation
identically, even if you could feasibly perform all the real-world tests
required to come up with such a number...
When should you expect performance issues? When your usage patterns exceed
your hardware's capabilities. It is absolutely impossible to tell you that
it will happen when you add 5 or 500 more databases. In fact, it could
conceivably happen after you /*remove*/ databases ... if users of the
remaining databases coincidentally change their usage patterns.
I have relatively high-volume systems with over 500 databases, and they work
very well. Would I want to throw another 500 databases on there? Not in
one shot, I don't think. But it's not the number itself that scares me.
It's the fact that 10 or 15 of those (given my current scenario) would
likely end up high transaction hitters that *could* start to destabilize the
system. On the other hand, if they're all duds, then 500 could be added
easily without any impact to the system.
Aaron Bertrand
SQL Server MVP
"Chris Reeder" <clreeder@.gmail.com> wrote in message
news:1186429044.319226.206990@.b79g2000hse.googlegr oups.com...
> I've read MSDN that the theoretical maximum number of databases per
> SQL server instance is 32,767. But I'd like to know if there is a
> real world recommendation from Microsoft. I know this sounds like a
> ridiculous question, but I've inherited an instance with approximately
> 1,000 databases (and growing) and I'm trying to determine when I
> should expect major performance issues (don't laugh). I have a plan
> for reducing the number of databases, but it won't happen over night.
> I know a lot of this depends on database usage, but I'm just looking
> for any information I can find. Thanks in advance!
>
|||On Mon, 06 Aug 2007 19:37:24 -0000, Chris Reeder <clreeder@.gmail.com>
wrote:

>I've read MSDN that the theoretical maximum number of databases per
>SQL server instance is 32,767. But I'd like to know if there is a
>real world recommendation from Microsoft. I know this sounds like a
>ridiculous question, but I've inherited an instance with approximately
>1,000 databases (and growing) and I'm trying to determine when I
>should expect major performance issues (don't laugh).
The critical problem would probably be that they will never share
pages in a working set, so caching will be less effective. But that's
if they're all active at once, which is unlikely ... isn't it?
A few more open file handles at the OS level, ... nah. Whatever
insight you can get on data locality and access patterns is probably
best, but hey, that's true even if it's all a single database, too!
Josh
|||> The critical problem would probably be that they will never share
> pages in a working set, so caching will be less effective.
Hmm, can you elaborate on this? When you have several databases, the data for each customer (or
whatever differentiate these databases) obviously need to be in separate tables, per customer. So,
data for one customer is on a separate set of pages compared to some other customer meaning data
retrieved for one customer cannot be used when querying from some other customer. But the same thing
would happen if the same set of table were in one database (except for very small tables where mixed
extents come into play). But if this is re-modeled and all customers share the same table, we have a
different situation...

> A few more open file handles at the OS level
Interesting you mention this, since this can impact caching. With many databases, setting auto close
might be needed (especially in 32 bit due to OS limitation and open file handles). And when a
database is auto closed, data in cache is removed (I know for sure this applies to plans, I'd have
to do a test to verify it applies to data).
Perhaps plan caching is a bigger issue? Assuming best practices are followed and stored procedures
are used. Now, we would end up with duplicate procedures, one set for each database. Even if we
don't use stored procedures, we won't be able to re-use cached plans across databases...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:799fb3l6bsc2its7r7mfma3kjqr65gekh1@.4ax.com...
> On Mon, 06 Aug 2007 19:37:24 -0000, Chris Reeder <clreeder@.gmail.com>
> wrote:
>
> The critical problem would probably be that they will never share
> pages in a working set, so caching will be less effective. But that's
> if they're all active at once, which is unlikely ... isn't it?
> A few more open file handles at the OS level, ... nah. Whatever
> insight you can get on data locality and access patterns is probably
> best, but hey, that's true even if it's all a single database, too!
> Josh
>
|||I have a client with 6500+ databases. Biggest issues are rolling out
changes, size of msdb database (make it BIG to begin with to avoid
fragmentation from autogrowths), and most 3rd party tools break down when
you try to use them against the server due to massive amounts of system data
they typically try to read. I had a developer build a multi-threaded
"database shotgun" for doing maintenance stuff. HUGE help!
TheSQLGuru
President
Indicium Resources, Inc.
"Chris Reeder" <clreeder@.gmail.com> wrote in message
news:1186429044.319226.206990@.b79g2000hse.googlegr oups.com...
> I've read MSDN that the theoretical maximum number of databases per
> SQL server instance is 32,767. But I'd like to know if there is a
> real world recommendation from Microsoft. I know this sounds like a
> ridiculous question, but I've inherited an instance with approximately
> 1,000 databases (and growing) and I'm trying to determine when I
> should expect major performance issues (don't laugh). I have a plan
> for reducing the number of databases, but it won't happen over night.
> I know a lot of this depends on database usage, but I'm just looking
> for any information I can find. Thanks in advance!
>
|||> they typically try to read. I had a developer build a multi-threaded
> "database shotgun" for doing maintenance stuff. HUGE help!
I use SQL Farm Combine for this.
http://www.sqlfarms.com/
|||On Tue, 7 Aug 2007 08:41:20 +0200, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:

>Hmm, can you elaborate on this?
Just speculating wildly.
If they are a single database, there is at least a *chance* customers,
users, sessions, etc will share pages!

>Perhaps plan caching is a bigger issue?
Ugh, you're right!
But then, wouldn't this be quickly obvious to anyone running
hundreds++ of databases? And the initial comments mostly seem to be
that it runs pretty well.
Josh
|||> If they are a single database, there is at least a *chance* customers,
> users, sessions, etc will share pages!
Agreed. To give a more precise answer, we would have to know the data model after coalescing into
one database.

> Ugh, you're right!
> But then, wouldn't this be quickly obvious to anyone running
> hundreds++ of databases? And the initial comments mostly seem to be
> that it runs pretty well.
True. But "run pretty well" doesn't mean that it cannot run better... :-)
Of course, this as well depends on the app. If stored procedures are used, then I expect to se some
benefit of having, for a particular proc, only one plan in cache instead of one per database. If
procedures are not used, then chances are that we mostly have exact text matching of the query
string in cache, and re-use would be minimal even if all is in the same database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:9acib3h5ct5mkgtoectjg2s9aqe8k5633k@.4ax.com...
> On Tue, 7 Aug 2007 08:41:20 +0200, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>
> Just speculating wildly.
> If they are a single database, there is at least a *chance* customers,
> users, sessions, etc will share pages!
>
> Ugh, you're right!
> But then, wouldn't this be quickly obvious to anyone running
> hundreds++ of databases? And the initial comments mostly seem to be
> that it runs pretty well.
> Josh
>
>

Maximum Recommended Databases

I've read MSDN that the theoretical maximum number of databases per
SQL server instance is 32,767. But I'd like to know if there is a
real world recommendation from Microsoft. I know this sounds like a
ridiculous question, but I've inherited an instance with approximately
1,000 databases (and growing) and I'm trying to determine when I
should expect major performance issues (don't laugh). I have a plan
for reducing the number of databases, but it won't happen over night.
I know a lot of this depends on database usage, but I'm just looking
for any information I can find. Thanks in advance!I have a client with over 5000 databases and still growing. The usage
pattern is such that only a few dozen or even a few hundred are heavily used
at any one time. The system is performing quite well.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Chris Reeder" <clreeder@.gmail.com> wrote in message
news:1186429044.319226.206990@.b79g2000hse.googlegroups.com...
> I've read MSDN that the theoretical maximum number of databases per
> SQL server instance is 32,767. But I'd like to know if there is a
> real world recommendation from Microsoft. I know this sounds like a
> ridiculous question, but I've inherited an instance with approximately
> 1,000 databases (and growing) and I'm trying to determine when I
> should expect major performance issues (don't laugh). I have a plan
> for reducing the number of databases, but it won't happen over night.
> I know a lot of this depends on database usage, but I'm just looking
> for any information I can find. Thanks in advance!
>|||I don't think I'm going to provide any new information that you don't
already have. But this is kind of like asking how big can a database be, or
how many rows can I get into a table? As the number of databases goes up,
the usage pattern really determines how detrimental each additional database
is to performance (let's leave administration costs out of this for now).
So many factors are involved that it's impossible for anyone to come up with
a real number that would apply to your situation and my situation
identically, even if you could feasibly perform all the real-world tests
required to come up with such a number...
When should you expect performance issues? When your usage patterns exceed
your hardware's capabilities. It is absolutely impossible to tell you that
it will happen when you add 5 or 500 more databases. In fact, it could
conceivably happen after you /*remove*/ databases ... if users of the
remaining databases coincidentally change their usage patterns.
I have relatively high-volume systems with over 500 databases, and they work
very well. Would I want to throw another 500 databases on there? Not in
one shot, I don't think. But it's not the number itself that scares me.
It's the fact that 10 or 15 of those (given my current scenario) would
likely end up high transaction hitters that *could* start to destabilize the
system. On the other hand, if they're all duds, then 500 could be added
easily without any impact to the system.
Aaron Bertrand
SQL Server MVP
"Chris Reeder" <clreeder@.gmail.com> wrote in message
news:1186429044.319226.206990@.b79g2000hse.googlegroups.com...
> I've read MSDN that the theoretical maximum number of databases per
> SQL server instance is 32,767. But I'd like to know if there is a
> real world recommendation from Microsoft. I know this sounds like a
> ridiculous question, but I've inherited an instance with approximately
> 1,000 databases (and growing) and I'm trying to determine when I
> should expect major performance issues (don't laugh). I have a plan
> for reducing the number of databases, but it won't happen over night.
> I know a lot of this depends on database usage, but I'm just looking
> for any information I can find. Thanks in advance!
>|||On Mon, 06 Aug 2007 19:37:24 -0000, Chris Reeder <clreeder@.gmail.com>
wrote:

>I've read MSDN that the theoretical maximum number of databases per
>SQL server instance is 32,767. But I'd like to know if there is a
>real world recommendation from Microsoft. I know this sounds like a
>ridiculous question, but I've inherited an instance with approximately
>1,000 databases (and growing) and I'm trying to determine when I
>should expect major performance issues (don't laugh).
The critical problem would probably be that they will never share
pages in a working set, so caching will be less effective. But that's
if they're all active at once, which is unlikely ... isn't it?
A few more open file handles at the OS level, ... nah. Whatever
insight you can get on data locality and access patterns is probably
best, but hey, that's true even if it's all a single database, too!
Josh|||> The critical problem would probably be that they will never share
> pages in a working set, so caching will be less effective.
Hmm, can you elaborate on this? When you have several databases, the data fo
r each customer (or
whatever differentiate these databases) obviously need to be in separate tab
les, per customer. So,
data for one customer is on a separate set of pages compared to some other c
ustomer meaning data
retrieved for one customer cannot be used when querying from some other cust
omer. But the same thing
would happen if the same set of table were in one database (except for very
small tables where mixed
extents come into play). But if this is re-modeled and all customers share t
he same table, we have a
different situation...

> A few more open file handles at the OS level
Interesting you mention this, since this can impact caching. With many datab
ases, setting auto close
might be needed (especially in 32 bit due to OS limitation and open file han
dles). And when a
database is auto closed, data in cache is removed (I know for sure this appl
ies to plans, I'd have
to do a test to verify it applies to data).
Perhaps plan caching is a bigger issue? Assuming best practices are followed
and stored procedures
are used. Now, we would end up with duplicate procedures, one set for each d
atabase. Even if we
don't use stored procedures, we won't be able to re-use cached plans across
databases...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:799fb3l6bsc2its7r7mfma3kjqr65gekh1@.
4ax.com...
> On Mon, 06 Aug 2007 19:37:24 -0000, Chris Reeder <clreeder@.gmail.com>
> wrote:
>
> The critical problem would probably be that they will never share
> pages in a working set, so caching will be less effective. But that's
> if they're all active at once, which is unlikely ... isn't it?
> A few more open file handles at the OS level, ... nah. Whatever
> insight you can get on data locality and access patterns is probably
> best, but hey, that's true even if it's all a single database, too!
> Josh
>|||I have a client with 6500+ databases. Biggest issues are rolling out
changes, size of msdb database (make it BIG to begin with to avoid
fragmentation from autogrowths), and most 3rd party tools break down when
you try to use them against the server due to massive amounts of system data
they typically try to read. I had a developer build a multi-threaded
"database shotgun" for doing maintenance stuff. HUGE help!
TheSQLGuru
President
Indicium Resources, Inc.
"Chris Reeder" <clreeder@.gmail.com> wrote in message
news:1186429044.319226.206990@.b79g2000hse.googlegroups.com...
> I've read MSDN that the theoretical maximum number of databases per
> SQL server instance is 32,767. But I'd like to know if there is a
> real world recommendation from Microsoft. I know this sounds like a
> ridiculous question, but I've inherited an instance with approximately
> 1,000 databases (and growing) and I'm trying to determine when I
> should expect major performance issues (don't laugh). I have a plan
> for reducing the number of databases, but it won't happen over night.
> I know a lot of this depends on database usage, but I'm just looking
> for any information I can find. Thanks in advance!
>|||> they typically try to read. I had a developer build a multi-threaded
> "database shotgun" for doing maintenance stuff. HUGE help!
I use SQL Farm Combine for this.
http://www.sqlfarms.com/|||On Tue, 7 Aug 2007 08:41:20 +0200, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:

>Hmm, can you elaborate on this?
Just speculating wildly.
If they are a single database, there is at least a *chance* customers,
users, sessions, etc will share pages!

>Perhaps plan caching is a bigger issue?
Ugh, you're right!
But then, wouldn't this be quickly obvious to anyone running
hundreds++ of databases? And the initial comments mostly seem to be
that it runs pretty well.
Josh|||> If they are a single database, there is at least a *chance* customers,
> users, sessions, etc will share pages!
Agreed. To give a more precise answer, we would have to know the data model
after coalescing into
one database.

> Ugh, you're right!
> But then, wouldn't this be quickly obvious to anyone running
> hundreds++ of databases? And the initial comments mostly seem to be
> that it runs pretty well.
True. But "run pretty well" doesn't mean that it cannot run better... :-)
Of course, this as well depends on the app. If stored procedures are used, t
hen I expect to se some
benefit of having, for a particular proc, only one plan in cache instead of
one per database. If
procedures are not used, then chances are that we mostly have exact text mat
ching of the query
string in cache, and re-use would be minimal even if all is in the same data
base.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:9acib3h5ct5mkgtoectjg2s9aqe8k5633k@.
4ax.com...
> On Tue, 7 Aug 2007 08:41:20 +0200, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>
> Just speculating wildly.
> If they are a single database, there is at least a *chance* customers,
> users, sessions, etc will share pages!
>
> Ugh, you're right!
> But then, wouldn't this be quickly obvious to anyone running
> hundreds++ of databases? And the initial comments mostly seem to be
> that it runs pretty well.
> Josh
>
>

Maximum Recommended Databases

I've read MSDN that the theoretical maximum number of databases per
SQL server instance is 32,767. But I'd like to know if there is a
real world recommendation from Microsoft. I know this sounds like a
ridiculous question, but I've inherited an instance with approximately
1,000 databases (and growing) and I'm trying to determine when I
should expect major performance issues (don't laugh). I have a plan
for reducing the number of databases, but it won't happen over night.
I know a lot of this depends on database usage, but I'm just looking
for any information I can find. Thanks in advance!I have a client with over 5000 databases and still growing. The usage
pattern is such that only a few dozen or even a few hundred are heavily used
at any one time. The system is performing quite well.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Chris Reeder" <clreeder@.gmail.com> wrote in message
news:1186429044.319226.206990@.b79g2000hse.googlegroups.com...
> I've read MSDN that the theoretical maximum number of databases per
> SQL server instance is 32,767. But I'd like to know if there is a
> real world recommendation from Microsoft. I know this sounds like a
> ridiculous question, but I've inherited an instance with approximately
> 1,000 databases (and growing) and I'm trying to determine when I
> should expect major performance issues (don't laugh). I have a plan
> for reducing the number of databases, but it won't happen over night.
> I know a lot of this depends on database usage, but I'm just looking
> for any information I can find. Thanks in advance!
>|||I don't think I'm going to provide any new information that you don't
already have. But this is kind of like asking how big can a database be, or
how many rows can I get into a table? As the number of databases goes up,
the usage pattern really determines how detrimental each additional database
is to performance (let's leave administration costs out of this for now).
So many factors are involved that it's impossible for anyone to come up with
a real number that would apply to your situation and my situation
identically, even if you could feasibly perform all the real-world tests
required to come up with such a number...
When should you expect performance issues? When your usage patterns exceed
your hardware's capabilities. It is absolutely impossible to tell you that
it will happen when you add 5 or 500 more databases. In fact, it could
conceivably happen after you /*remove*/ databases ... if users of the
remaining databases coincidentally change their usage patterns.
I have relatively high-volume systems with over 500 databases, and they work
very well. Would I want to throw another 500 databases on there? Not in
one shot, I don't think. But it's not the number itself that scares me.
It's the fact that 10 or 15 of those (given my current scenario) would
likely end up high transaction hitters that *could* start to destabilize the
system. On the other hand, if they're all duds, then 500 could be added
easily without any impact to the system.
--
Aaron Bertrand
SQL Server MVP
"Chris Reeder" <clreeder@.gmail.com> wrote in message
news:1186429044.319226.206990@.b79g2000hse.googlegroups.com...
> I've read MSDN that the theoretical maximum number of databases per
> SQL server instance is 32,767. But I'd like to know if there is a
> real world recommendation from Microsoft. I know this sounds like a
> ridiculous question, but I've inherited an instance with approximately
> 1,000 databases (and growing) and I'm trying to determine when I
> should expect major performance issues (don't laugh). I have a plan
> for reducing the number of databases, but it won't happen over night.
> I know a lot of this depends on database usage, but I'm just looking
> for any information I can find. Thanks in advance!
>|||On Mon, 06 Aug 2007 19:37:24 -0000, Chris Reeder <clreeder@.gmail.com>
wrote:
>I've read MSDN that the theoretical maximum number of databases per
>SQL server instance is 32,767. But I'd like to know if there is a
>real world recommendation from Microsoft. I know this sounds like a
>ridiculous question, but I've inherited an instance with approximately
>1,000 databases (and growing) and I'm trying to determine when I
>should expect major performance issues (don't laugh).
The critical problem would probably be that they will never share
pages in a working set, so caching will be less effective. But that's
if they're all active at once, which is unlikely ... isn't it?
A few more open file handles at the OS level, ... nah. Whatever
insight you can get on data locality and access patterns is probably
best, but hey, that's true even if it's all a single database, too!
Josh|||> The critical problem would probably be that they will never share
> pages in a working set, so caching will be less effective.
Hmm, can you elaborate on this? When you have several databases, the data for each customer (or
whatever differentiate these databases) obviously need to be in separate tables, per customer. So,
data for one customer is on a separate set of pages compared to some other customer meaning data
retrieved for one customer cannot be used when querying from some other customer. But the same thing
would happen if the same set of table were in one database (except for very small tables where mixed
extents come into play). But if this is re-modeled and all customers share the same table, we have a
different situation...
> A few more open file handles at the OS level
Interesting you mention this, since this can impact caching. With many databases, setting auto close
might be needed (especially in 32 bit due to OS limitation and open file handles). And when a
database is auto closed, data in cache is removed (I know for sure this applies to plans, I'd have
to do a test to verify it applies to data).
Perhaps plan caching is a bigger issue? Assuming best practices are followed and stored procedures
are used. Now, we would end up with duplicate procedures, one set for each database. Even if we
don't use stored procedures, we won't be able to re-use cached plans across databases...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:799fb3l6bsc2its7r7mfma3kjqr65gekh1@.4ax.com...
> On Mon, 06 Aug 2007 19:37:24 -0000, Chris Reeder <clreeder@.gmail.com>
> wrote:
>>I've read MSDN that the theoretical maximum number of databases per
>>SQL server instance is 32,767. But I'd like to know if there is a
>>real world recommendation from Microsoft. I know this sounds like a
>>ridiculous question, but I've inherited an instance with approximately
>>1,000 databases (and growing) and I'm trying to determine when I
>>should expect major performance issues (don't laugh).
> The critical problem would probably be that they will never share
> pages in a working set, so caching will be less effective. But that's
> if they're all active at once, which is unlikely ... isn't it?
> A few more open file handles at the OS level, ... nah. Whatever
> insight you can get on data locality and access patterns is probably
> best, but hey, that's true even if it's all a single database, too!
> Josh
>|||I have a client with 6500+ databases. Biggest issues are rolling out
changes, size of msdb database (make it BIG to begin with to avoid
fragmentation from autogrowths), and most 3rd party tools break down when
you try to use them against the server due to massive amounts of system data
they typically try to read. I had a developer build a multi-threaded
"database shotgun" for doing maintenance stuff. HUGE help!
--
TheSQLGuru
President
Indicium Resources, Inc.
"Chris Reeder" <clreeder@.gmail.com> wrote in message
news:1186429044.319226.206990@.b79g2000hse.googlegroups.com...
> I've read MSDN that the theoretical maximum number of databases per
> SQL server instance is 32,767. But I'd like to know if there is a
> real world recommendation from Microsoft. I know this sounds like a
> ridiculous question, but I've inherited an instance with approximately
> 1,000 databases (and growing) and I'm trying to determine when I
> should expect major performance issues (don't laugh). I have a plan
> for reducing the number of databases, but it won't happen over night.
> I know a lot of this depends on database usage, but I'm just looking
> for any information I can find. Thanks in advance!
>|||> they typically try to read. I had a developer build a multi-threaded
> "database shotgun" for doing maintenance stuff. HUGE help!
I use SQL Farm Combine for this.
http://www.sqlfarms.com/|||On Tue, 7 Aug 2007 08:41:20 +0200, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>> The critical problem would probably be that they will never share
>> pages in a working set, so caching will be less effective.
>Hmm, can you elaborate on this?
Just speculating wildly.
If they are a single database, there is at least a *chance* customers,
users, sessions, etc will share pages!
>Perhaps plan caching is a bigger issue?
Ugh, you're right!
But then, wouldn't this be quickly obvious to anyone running
hundreds++ of databases? And the initial comments mostly seem to be
that it runs pretty well.
Josh|||> If they are a single database, there is at least a *chance* customers,
> users, sessions, etc will share pages!
Agreed. To give a more precise answer, we would have to know the data model after coalescing into
one database.
>>Perhaps plan caching is a bigger issue?
> Ugh, you're right!
> But then, wouldn't this be quickly obvious to anyone running
> hundreds++ of databases? And the initial comments mostly seem to be
> that it runs pretty well.
True. But "run pretty well" doesn't mean that it cannot run better... :-)
Of course, this as well depends on the app. If stored procedures are used, then I expect to se some
benefit of having, for a particular proc, only one plan in cache instead of one per database. If
procedures are not used, then chances are that we mostly have exact text matching of the query
string in cache, and re-use would be minimal even if all is in the same database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:9acib3h5ct5mkgtoectjg2s9aqe8k5633k@.4ax.com...
> On Tue, 7 Aug 2007 08:41:20 +0200, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>> The critical problem would probably be that they will never share
>> pages in a working set, so caching will be less effective.
>>Hmm, can you elaborate on this?
> Just speculating wildly.
> If they are a single database, there is at least a *chance* customers,
> users, sessions, etc will share pages!
>>Perhaps plan caching is a bigger issue?
> Ugh, you're right!
> But then, wouldn't this be quickly obvious to anyone running
> hundreds++ of databases? And the initial comments mostly seem to be
> that it runs pretty well.
> Josh
>
>

Maximum practical number of databases per SQL Server 2000 Instance

Does anyone know what the maximum practical number of databases that could be
created and used per a SQL Server 2000 Enterprise Instance. I know that the
theoretical limit in BooksOnLine is 32K. But my application is dying way
before that.
Thanks in advance
Cesar
Exactly what is dying? It's usually not the number of databases but more
what you are doing with them and how you have your resources configured.
Andrew J. Kelly SQL MVP
"Cesar" <Cesar@.discussions.microsoft.com> wrote in message
news:1FCBB476-FEC0-493C-AA99-D7BF6E867B84@.microsoft.com...
> Does anyone know what the maximum practical number of databases that could
> be
> created and used per a SQL Server 2000 Enterprise Instance. I know that
> the
> theoretical limit in BooksOnLine is 32K. But my application is dying way
> before that.
> Thanks in advance
> Cesar
|||We have hundreds of databases on our cluster. It creates some interesting
issue (starting and stopping the service takes a looooong time, for
example). But it runs pretty smoothly, all things considered.
Bob Castleman
SuccessWare Software
"Cesar" <Cesar@.discussions.microsoft.com> wrote in message
news:1FCBB476-FEC0-493C-AA99-D7BF6E867B84@.microsoft.com...
> Does anyone know what the maximum practical number of databases that could
> be
> created and used per a SQL Server 2000 Enterprise Instance. I know that
> the
> theoretical limit in BooksOnLine is 32K. But my application is dying way
> before that.
> Thanks in advance
> Cesar
|||Hi
There are some KB articles on what needs to changed in SQL Server for it to
work well with more than 1'000 DB's. It comes down to having enough memory
available for the file structures. I don't have the KB at hand.
The more DB's you have, the more difficult it is to manage them with
Enterprise Manager. And one big issue, they all share TempDB, so if the
applications are written badly, you have a nice bottleneck.
I have tested to 600 DB's. Shutdown does take a while, and so does startup.
Enterprise Manager took 32 seconds to render the screen.
If I were you, and it is possible in your scenario, I would run multiple
instances with about 100 DB's per instance. Then, applying a SP or a hotfix
does not affect every DB, neither does a failure.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Bob Castleman" <nomail@.here> wrote in message
news:#xuo4wu4EHA.2124@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> We have hundreds of databases on our cluster. It creates some interesting
> issue (starting and stopping the service takes a looooong time, for
> example). But it runs pretty smoothly, all things considered.
> Bob Castleman
> SuccessWare Software
> "Cesar" <Cesar@.discussions.microsoft.com> wrote in message
> news:1FCBB476-FEC0-493C-AA99-D7BF6E867B84@.microsoft.com...
could[vbcol=seagreen]
way
>

Maximum practical number of databases per SQL Server 2000 Instance

Does anyone know what the maximum practical number of databases that could be
created and used per a SQL Server 2000 Enterprise Instance. I know that the
theoretical limit in BooksOnLine is 32K. But my application is dying way
before that.
Thanks in advance
CesarExactly what is dying? It's usually not the number of databases but more
what you are doing with them and how you have your resources configured.
--
Andrew J. Kelly SQL MVP
"Cesar" <Cesar@.discussions.microsoft.com> wrote in message
news:1FCBB476-FEC0-493C-AA99-D7BF6E867B84@.microsoft.com...
> Does anyone know what the maximum practical number of databases that could
> be
> created and used per a SQL Server 2000 Enterprise Instance. I know that
> the
> theoretical limit in BooksOnLine is 32K. But my application is dying way
> before that.
> Thanks in advance
> Cesar|||We have hundreds of databases on our cluster. It creates some interesting
issue (starting and stopping the service takes a looooong time, for
example). But it runs pretty smoothly, all things considered.
Bob Castleman
SuccessWare Software
"Cesar" <Cesar@.discussions.microsoft.com> wrote in message
news:1FCBB476-FEC0-493C-AA99-D7BF6E867B84@.microsoft.com...
> Does anyone know what the maximum practical number of databases that could
> be
> created and used per a SQL Server 2000 Enterprise Instance. I know that
> the
> theoretical limit in BooksOnLine is 32K. But my application is dying way
> before that.
> Thanks in advance
> Cesar|||Hi
There are some KB articles on what needs to changed in SQL Server for it to
work well with more than 1'000 DB's. It comes down to having enough memory
available for the file structures. I don't have the KB at hand.
The more DB's you have, the more difficult it is to manage them with
Enterprise Manager. And one big issue, they all share TempDB, so if the
applications are written badly, you have a nice bottleneck.
I have tested to 600 DB's. Shutdown does take a while, and so does startup.
Enterprise Manager took 32 seconds to render the screen.
If I were you, and it is possible in your scenario, I would run multiple
instances with about 100 DB's per instance. Then, applying a SP or a hotfix
does not affect every DB, neither does a failure.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Bob Castleman" <nomail@.here> wrote in message
news:#xuo4wu4EHA.2124@.TK2MSFTNGP15.phx.gbl...
> We have hundreds of databases on our cluster. It creates some interesting
> issue (starting and stopping the service takes a looooong time, for
> example). But it runs pretty smoothly, all things considered.
> Bob Castleman
> SuccessWare Software
> "Cesar" <Cesar@.discussions.microsoft.com> wrote in message
> news:1FCBB476-FEC0-493C-AA99-D7BF6E867B84@.microsoft.com...
> > Does anyone know what the maximum practical number of databases that
could
> > be
> > created and used per a SQL Server 2000 Enterprise Instance. I know that
> > the
> > theoretical limit in BooksOnLine is 32K. But my application is dying
way
> > before that.
> >
> > Thanks in advance
> >
> > Cesar
>|||Thanks for all of your suggestions.
The error I was getting was something like "Out of Memory". I think I know
how to fix it. I was creating thousands of small DBs and by default they
stay in mostly in memory and eventually my machine runs out of memory. When
I enabled the "AutoClose" property my small DBs, that flushes them from
memory and they reside entirely on the HD. That allowed me to create 10K DBs
in my test environment without any problems or SQL Server process to increase
in memory usage.
Thanks
Cesar
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> There are some KB articles on what needs to changed in SQL Server for it to
> work well with more than 1'000 DB's. It comes down to having enough memory
> available for the file structures. I don't have the KB at hand.
> The more DB's you have, the more difficult it is to manage them with
> Enterprise Manager. And one big issue, they all share TempDB, so if the
> applications are written badly, you have a nice bottleneck.
> I have tested to 600 DB's. Shutdown does take a while, and so does startup.
> Enterprise Manager took 32 seconds to render the screen.
> If I were you, and it is possible in your scenario, I would run multiple
> instances with about 100 DB's per instance. Then, applying a SP or a hotfix
> does not affect every DB, neither does a failure.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Bob Castleman" <nomail@.here> wrote in message
> news:#xuo4wu4EHA.2124@.TK2MSFTNGP15.phx.gbl...
> > We have hundreds of databases on our cluster. It creates some interesting
> > issue (starting and stopping the service takes a looooong time, for
> > example). But it runs pretty smoothly, all things considered.
> >
> > Bob Castleman
> > SuccessWare Software
> > "Cesar" <Cesar@.discussions.microsoft.com> wrote in message
> > news:1FCBB476-FEC0-493C-AA99-D7BF6E867B84@.microsoft.com...
> > > Does anyone know what the maximum practical number of databases that
> could
> > > be
> > > created and used per a SQL Server 2000 Enterprise Instance. I know that
> > > the
> > > theoretical limit in BooksOnLine is 32K. But my application is dying
> way
> > > before that.
> > >
> > > Thanks in advance
> > >
> > > Cesar
> >
> >
>
>

Maximum Number of Processes Reached

I am trying to setup transactional replication between Server A and Server B. There are 265 databases on each server.

I am running SQL Server 2005 on Windows Server 2003. The problem comes in at the 201st database. The message in the SQL Server Agent Error Log is :

Warning,[398] The job (WSSWPG09-EmpirePaint-WSSWPG06-104) has been queued because the maximum number of working threads (400) are already running. This job will be executed as soon as one of the working thread finishes execution.

SQL Server's max worker threads is set to 0. The Distribution, LogReader and T-SQL subsystems have been increased to 200 max_worker_threads.

Is there some other setting (maybe a Windows Registry setting) that can be configured to fix this? Or have I just hit a physical maximum of the processor?

Any help is much appreciated.
Maybe engine forum can answer question about max_worker_threads.|||

That is a lot of databases to syncronize between two servers...

Max worker thread set to 0 means use all available threads.

There are 255 threads per logical CPU.

IF you are running out of threads, you may wish to experiment with using 'fibers' (timeslicing the threads).

Go to the Server Properties Window, and on the CPU tab, check 'Use Windows Fibers'.

I recommend testing any changes.

|||

Check out http://www.microsoft.com/technet/technetmag/issues/2006/10/SQLQA/default.aspx

This talks a bit about your issue.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

Maximum Number of Processes Reached

I am trying to setup transactional replication between Server A and Server B. There are 265 databases on each server.

I am running SQL Server 2005 on Windows Server 2003. The problem comes in at the 201st database. The message in the SQL Server Agent Error Log is :

Warning,[398] The job (WSSWPG09-EmpirePaint-WSSWPG06-104) has been queued because the maximum number of working threads (400) are already running. This job will be executed as soon as one of the working thread finishes execution.

SQL Server's max worker threads is set to 0. The Distribution, LogReader and T-SQL subsystems have been increased to 200 max_worker_threads.

Is there some other setting (maybe a Windows Registry setting) that can be configured to fix this? Or have I just hit a physical maximum of the processor?

Any help is much appreciated.
Maybe engine forum can answer question about max_worker_threads.|||

That is a lot of databases to syncronize between two servers...

Max worker thread set to 0 means use all available threads.

There are 255 threads per logical CPU.

IF you are running out of threads, you may wish to experiment with using 'fibers' (timeslicing the threads).

Go to the Server Properties Window, and on the CPU tab, check 'Use Windows Fibers'.

I recommend testing any changes.

|||

Check out http://www.microsoft.com/technet/technetmag/issues/2006/10/SQLQA/default.aspx

This talks a bit about your issue.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

Maximum number of databases used for each query in 2005?

In MS Sql Server 2000, if you run a stored procedure that query's more
than 8 databases, you get this error gets raised:

Maximum number of databases used for each query has been exceeded. The
maximum allowed is 8.

In 2005, does this maximum number still exist? if so, is it still 8, or
has the number been increased?

Thanks,
MikeHoly Moly, what kind of queries are you writing!?|||Hi, I have the same problem with 2000. I am about to upgrade to 2005 and I am very concerned about this because our database requirements are rapidly growing.

Thanks,
benn

Quote:

Originally Posted by Michael.Suarez@.gmail.com

In MS Sql Server 2000, if you run a stored procedure that query's more
than 8 databases, you get this error gets raised:

Maximum number of databases used for each query has been exceeded. The
maximum allowed is 8.

In 2005, does this maximum number still exist? if so, is it still 8, or
has the number been increased?

Thanks,
Mike

Maximum number of databases on a server

I was wondering if their is a hard limit to the number of databases that can
be attached to a SQL Server 2000 standard install.
Thanks
32,767 databases per instance of SQL Server.
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:E12B7A3A-E7A0-41BA-B74E-3A60A166D5EA@.microsoft.com...
> I was wondering if their is a hard limit to the number of databases that
can
> be attached to a SQL Server 2000 standard install.
> Thanks
|||thanks
"Hassan" wrote:

> 32,767 databases per instance of SQL Server.
> "Ken" <Ken@.discussions.microsoft.com> wrote in message
> news:E12B7A3A-E7A0-41BA-B74E-3A60A166D5EA@.microsoft.com...
> can
>
>
|||May I know how to check the number of databases installed?
I checked through the Enterprise Manager, under the databases tree, there are:
-Databases
+mySys
+master
+model
+msdb
+Northwind
+pubs
+tempdb
+Sys
Can I say there are total 8 databases ?
Thanks !
"Ken" wrote:
[vbcol=seagreen]
> thanks
> "Hassan" wrote:
|||select count(*) from master.dbo.sysdatabases
That will tell you exactly how many databases are on your SQL instance.
Although I guess the more "Microsoft approved" way would be using their
views & procs (so as to not directly query the system tables) so that would
be:
exec sp_helpdb
Cheers,
Mike
"CS" <CS@.discussions.microsoft.com> wrote in message
news:5F09FFC1-5343-46FF-B87C-B92BCD6E10A9@.microsoft.com...[vbcol=seagreen]
> May I know how to check the number of databases installed?
> I checked through the Enterprise Manager, under the databases tree, there
> are:
> -Databases
> +mySys
> +master
> +model
> +msdb
> +Northwind
> +pubs
> +tempdb
> +Sys
> Can I say there are total 8 databases ?
> Thanks !
> "Ken" wrote:

Maximum number of databases

Hello everyone -
Running SQL Server 2000
What is the maximum number of databases that can be had in a single MS SQL installation??
thanks
tonyFrom the article titled "Maximum Capacity Specifications" in BOL: 32,767|||If you get close to that you are probably doing something wrong...

Looking at your next thread.... ;)|||I see you are following my thought processes here...

company is approaching 500 databases in a single sql server

not sure on the direction to take or how soon before the ship takes on too much water

thanks for the replies!!

take care
tony

Maximum number of database on a SQL 2005 EE server

Does the "hard limit" of ~32K databases still apply to 2005 Express?
Tom Maier
Jeppesen Marine
All except CE.
From: http://msdn2.microsoft.com/en-us/library/ms143432.aspx
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Tom Maier" <TomMaier@.discussions.microsoft.com> wrote in message
news:0F28ACF0-8B16-4E69-A6FF-B830D1FA78E4@.microsoft.com...
> Does the "hard limit" of ~32K databases still apply to 2005 Express?
> --
> Tom Maier
> Jeppesen Marine
|||Hi Tom
This is still true of the other editions and I don't think SQL Express is
different see http://msdn2.microsoft.com/en-us/library/ms143432.aspx
John
"Tom Maier" wrote:

> Does the "hard limit" of ~32K databases still apply to 2005 Express?
> --
> Tom Maier
> Jeppesen Marine

Maximum number of database on a SQL 2005 EE server

Does the "hard limit" of ~32K databases still apply to 2005 Express?
--
Tom Maier
Jeppesen MarineAll except CE.
From: http://msdn2.microsoft.com/en-us/library/ms143432.aspx
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Tom Maier" <TomMaier@.discussions.microsoft.com> wrote in message
news:0F28ACF0-8B16-4E69-A6FF-B830D1FA78E4@.microsoft.com...
> Does the "hard limit" of ~32K databases still apply to 2005 Express?
> --
> Tom Maier
> Jeppesen Marine|||Hi Tom
This is still true of the other editions and I don't think SQL Express is
different see http://msdn2.microsoft.com/en-us/library/ms143432.aspx
John
"Tom Maier" wrote:

> Does the "hard limit" of ~32K databases still apply to 2005 Express?
> --
> Tom Maier
> Jeppesen Marine

Maximum number of database on a SQL 2005 EE server

Does the "hard limit" of ~32K databases still apply to 2005 Express?
--
Tom Maier
Jeppesen MarineAll except CE.
From: http://msdn2.microsoft.com/en-us/library/ms143432.aspx
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Tom Maier" <TomMaier@.discussions.microsoft.com> wrote in message
news:0F28ACF0-8B16-4E69-A6FF-B830D1FA78E4@.microsoft.com...
> Does the "hard limit" of ~32K databases still apply to 2005 Express?
> --
> Tom Maier
> Jeppesen Marine|||Hi Tom
This is still true of the other editions and I don't think SQL Express is
different see http://msdn2.microsoft.com/en-us/library/ms143432.aspx
John
"Tom Maier" wrote:
> Does the "hard limit" of ~32K databases still apply to 2005 Express?
> --
> Tom Maier
> Jeppesen Marine

Maximum Memory for standart edition / enterprise

Hello there
I am runing on sql 7 environment and planing to move to 2k environment
(quite late but.....).
for one of the databases, I have a server with 4G of ram but I have only sql
server standart, I am wondering if it will still take 4 G of ram since MS
claims it will take only 2 G?
for some reason I heard that it can utilize the full 4G ?
another thing is: I have licenses for 1 processor, do you guys know if I can
upgrade that to 2?
thanks !
Hi,
SQL Standard edition can only use 2 GB RAM at the max. To utilize more you
have to use SQL Server Enterprise edition.
I have licenses for 1 processor, do you guys know if I can upgrade that to
2?
http://www.microsoft.com/sql/howtobuy/default.asp
Thanks
Hari
MCDBA
"simo sentissi" <simo_sentissi@.skc.edu> wrote in message
news:OVG$ndQZEHA.1652@.TK2MSFTNGP09.phx.gbl...
> Hello there
> I am runing on sql 7 environment and planing to move to 2k environment
> (quite late but.....).
> for one of the databases, I have a server with 4G of ram but I have only
sql
> server standart, I am wondering if it will still take 4 G of ram since MS
> claims it will take only 2 G?
> for some reason I heard that it can utilize the full 4G ?
> another thing is: I have licenses for 1 processor, do you guys know if I
can
> upgrade that to 2?
>
> thanks !
>

Maximum Memory for standart edition / enterprise

Hello there
I am runing on sql 7 environment and planing to move to 2k environment
(quite late but.....).
for one of the databases, I have a server with 4G of ram but I have only sql
server standart, I am wondering if it will still take 4 G of ram since MS
claims it will take only 2 G'
for some reason I heard that it can utilize the full 4G ?
another thing is: I have licenses for 1 processor, do you guys know if I can
upgrade that to 2?
thanks !Hi,
SQL Standard edition can only use 2 GB RAM at the max. To utilize more you
have to use SQL Server Enterprise edition.
I have licenses for 1 processor, do you guys know if I can upgrade that to
2?
http://www.microsoft.com/sql/howtobuy/default.asp
Thanks
Hari
MCDBA
"simo sentissi" <simo_sentissi@.skc.edu> wrote in message
news:OVG$ndQZEHA.1652@.TK2MSFTNGP09.phx.gbl...
> Hello there
> I am runing on sql 7 environment and planing to move to 2k environment
> (quite late but.....).
> for one of the databases, I have a server with 4G of ram but I have only
sql
> server standart, I am wondering if it will still take 4 G of ram since MS
> claims it will take only 2 G'
> for some reason I heard that it can utilize the full 4G ?
> another thing is: I have licenses for 1 processor, do you guys know if I
can
> upgrade that to 2?
>
> thanks !
>

Maximum Memory for standart edition / enterprise

Hello there
I am runing on sql 7 environment and planing to move to 2k environment
(quite late but.....).
for one of the databases, I have a server with 4G of ram but I have only sql
server standart, I am wondering if it will still take 4 G of ram since MS
claims it will take only 2 G'
for some reason I heard that it can utilize the full 4G ?
another thing is: I have licenses for 1 processor, do you guys know if I can
upgrade that to 2?
thanks !Hi,
SQL Standard edition can only use 2 GB RAM at the max. To utilize more you
have to use SQL Server Enterprise edition.
I have licenses for 1 processor, do you guys know if I can upgrade that to
2?
http://www.microsoft.com/sql/howtobuy/default.asp
Thanks
Hari
MCDBA
"simo sentissi" <simo_sentissi@.skc.edu> wrote in message
news:OVG$ndQZEHA.1652@.TK2MSFTNGP09.phx.gbl...
> Hello there
> I am runing on sql 7 environment and planing to move to 2k environment
> (quite late but.....).
> for one of the databases, I have a server with 4G of ram but I have only
sql
> server standart, I am wondering if it will still take 4 G of ram since MS
> claims it will take only 2 G'
> for some reason I heard that it can utilize the full 4G ?
> another thing is: I have licenses for 1 processor, do you guys know if I
can
> upgrade that to 2?
>
> thanks !
>

Maximum databases per server

Dear All, We are planning an application where we need to maintain
separate databases for each customers. Now, the number of customers in
one year would be 50,000. I read that theoretically it is possible to
maintain 32000 databases in a single instance of SQL Server, but I can
guess that it would certainly have performance issues.
What could be the possible architecture? Can having views be a
solution, so even though the database would be one for all the
customers, it would be logically separated. Can this be a possibility?
I do not need to synchronize data between customer databases and I
reckon that the database would be having 30-40 tables.
Kind regards,
Santosh
Can you provide the Sql Server version and other server statistics?
Thanks,
Sree
"santoshmahale@.gmail.com" wrote:

> Dear All, We are planning an application where we need to maintain
> separate databases for each customers. Now, the number of customers in
> one year would be 50,000. I read that theoretically it is possible to
> maintain 32000 databases in a single instance of SQL Server, but I can
> guess that it would certainly have performance issues.
> What could be the possible architecture? Can having views be a
> solution, so even though the database would be one for all the
> customers, it would be logically separated. Can this be a possibility?
> I do not need to synchronize data between customer databases and I
> reckon that the database would be having 30-40 tables.
> Kind regards,
> Santosh
>
|||the version I am planning on is SQL Server 2000 EE.
Are you refering to hardware stats of the server? I am planning 4
processor enabled (2 installed initially), 8 GB RAM and a SAN.
What other stats information you had in mind?
|||Hi santhosh,
For sql Server 2000 The maximum capacity specification says '32767'. More
than that i dont think the single instance will take, i have tried other
maximum capacity specifications like "Maximum columns per select statement =>
4096". If we give 4097 the select statement throws an error. So your design
might fail if you try to make more than 32,767 and you were saying there can
be 50,000 customers a year. Will this count increase subsequent years? If so
you need to purchase servers as the sutomer strength increases, again on
performance side as you have a strong hardware support it wont affect much
but the concern is crossing 32,767 limit.
Why cant you maintain all customers in single DB? you asked for logical
separation between customers which i feel is not a concern. Again users
accessing it can be classified into separate logins no. Single DB is the
right choice, again seeing the application side how will you configure to
connect to each DB for each customer?
Thanks,
Sree
"santoshmahale@.gmail.com" wrote:

> the version I am planning on is SQL Server 2000 EE.
> Are you refering to hardware stats of the server? I am planning 4
> processor enabled (2 installed initially), 8 GB RAM and a SAN.
> What other stats information you had in mind?
>
|||Hi
a single db sounds the best way to go. I did something similar
recently. By adding some form of customer id column to each table, and
setting up a table to control which SQL user accesses which records.
Sreejith G wrote:[vbcol=seagreen]
> Hi santhosh,
> For sql Server 2000 The maximum capacity specification says '32767'. More
> than that i dont think the single instance will take, i have tried other
> maximum capacity specifications like "Maximum columns per select statement =>
> 4096". If we give 4097 the select statement throws an error. So your design
> might fail if you try to make more than 32,767 and you were saying there can
> be 50,000 customers a year. Will this count increase subsequent years? If so
> you need to purchase servers as the sutomer strength increases, again on
> performance side as you have a strong hardware support it wont affect much
> but the concern is crossing 32,767 limit.
> Why cant you maintain all customers in single DB? you asked for logical
> separation between customers which i feel is not a concern. Again users
> accessing it can be classified into separate logins no. Single DB is the
> right choice, again seeing the application side how will you configure to
> connect to each DB for each customer?
> Thanks,
> Sree
>
>
> "santoshmahale@.gmail.com" wrote:
|||santoshmahale@.gmail.com wrote:
> Dear All, We are planning an application where we need to maintain
> separate databases for each customers. Now, the number of customers in
> one year would be 50,000. I read that theoretically it is possible to
> maintain 32000 databases in a single instance of SQL Server, but I can
> guess that it would certainly have performance issues.
> What could be the possible architecture? Can having views be a
> solution, so even though the database would be one for all the
> customers, it would be logically separated. Can this be a possibility?
> I do not need to synchronize data between customer databases and I
> reckon that the database would be having 30-40 tables.
> Kind regards,
> Santosh
No question in my mind. Go for a single DB for all customers. 50,000
databases is madness.
Why do you say you "need" separate databases for each customer?
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

Maximum databases per server

Dear All, We are planning an application where we need to maintain
separate databases for each customers. Now, the number of customers in
one year would be 50,000. I read that theoretically it is possible to
maintain 32000 databases in a single instance of SQL Server, but I can
guess that it would certainly have performance issues.
What could be the possible architecture? Can having views be a
solution, so even though the database would be one for all the
customers, it would be logically separated. Can this be a possibility?
I do not need to synchronize data between customer databases and I
reckon that the database would be having 30-40 tables.
Kind regards,
SantoshCan you provide the Sql Server version and other server statistics?
Thanks,
Sree
"santoshmahale@.gmail.com" wrote:

> Dear All, We are planning an application where we need to maintain
> separate databases for each customers. Now, the number of customers in
> one year would be 50,000. I read that theoretically it is possible to
> maintain 32000 databases in a single instance of SQL Server, but I can
> guess that it would certainly have performance issues.
> What could be the possible architecture? Can having views be a
> solution, so even though the database would be one for all the
> customers, it would be logically separated. Can this be a possibility?
> I do not need to synchronize data between customer databases and I
> reckon that the database would be having 30-40 tables.
> Kind regards,
> Santosh
>|||the version I am planning on is SQL Server 2000 EE.
Are you refering to hardware stats of the server? I am planning 4
processor enabled (2 installed initially), 8 GB RAM and a SAN.
What other stats information you had in mind?|||Hi santhosh,
For sql Server 2000 The maximum capacity specification says '32767'. More
than that i dont think the single instance will take, i have tried other
maximum capacity specifications like "Maximum columns per select statement =
>
4096". If we give 4097 the select statement throws an error. So your design
might fail if you try to make more than 32,767 and you were saying there can
be 50,000 customers a year. Will this count increase subsequent years? If so
you need to purchase servers as the sutomer strength increases, again on
performance side as you have a strong hardware support it wont affect much
but the concern is crossing 32,767 limit.
Why cant you maintain all customers in single DB? you asked for logical
separation between customers which i feel is not a concern. Again users
accessing it can be classified into separate logins no. Single DB is the
right choice, again seeing the application side how will you configure to
connect to each DB for each customer?
Thanks,
Sree
"santoshmahale@.gmail.com" wrote:

> the version I am planning on is SQL Server 2000 EE.
> Are you refering to hardware stats of the server? I am planning 4
> processor enabled (2 installed initially), 8 GB RAM and a SAN.
> What other stats information you had in mind?
>|||Hi
a single db sounds the best way to go. I did something similar
recently. By adding some form of customer id column to each table, and
setting up a table to control which SQL user accesses which records.
Sreejith G wrote:[vbcol=seagreen]
> Hi santhosh,
> For sql Server 2000 The maximum capacity specification says '32767'. More
> than that i dont think the single instance will take, i have tried other
> maximum capacity specifications like "Maximum columns per select statement
=>
> 4096". If we give 4097 the select statement throws an error. So your desi
gn
> might fail if you try to make more than 32,767 and you were saying there c
an
> be 50,000 customers a year. Will this count increase subsequent years? If
so
> you need to purchase servers as the sutomer strength increases, again on
> performance side as you have a strong hardware support it wont affect much
> but the concern is crossing 32,767 limit.
> Why cant you maintain all customers in single DB? you asked for logical
> separation between customers which i feel is not a concern. Again users
> accessing it can be classified into separate logins no. Single DB is the
> right choice, again seeing the application side how will you configure to
> connect to each DB for each customer?
> Thanks,
> Sree
>
>
> "santoshmahale@.gmail.com" wrote:
>|||santoshmahale@.gmail.com wrote:
> Dear All, We are planning an application where we need to maintain
> separate databases for each customers. Now, the number of customers in
> one year would be 50,000. I read that theoretically it is possible to
> maintain 32000 databases in a single instance of SQL Server, but I can
> guess that it would certainly have performance issues.
> What could be the possible architecture? Can having views be a
> solution, so even though the database would be one for all the
> customers, it would be logically separated. Can this be a possibility?
> I do not need to synchronize data between customer databases and I
> reckon that the database would be having 30-40 tables.
> Kind regards,
> Santosh
No question in my mind. Go for a single DB for all customers. 50,000
databases is madness.
Why do you say you "need" separate databases for each customer?
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--