MSDE Concurrent Connections

MSDE Concurrent Connections

Post by Rahul Sharm » Tue, 22 Oct 2002 15:24:33



We have run into the MSDE "8" max concurrent connection limit.  We are using
MSDE as a local cache on the web server to read the configuration data which
rarely changes.  If it at all changes, the application then fires a stored
procedure that syncs the local cache with the back-end database that could
be either Oracle or MS SQL Server.

Because of the "8" connections limit, once the connections exceed, the
performance degrades.  Hence we are looking for alternatives now because of
two reasons:
a) The connection limitations,
b) MSDE won't work on Unix boxes.

Does anybody has any recommendations ?  MySQL ? PostgresSQL ? DB2 Everyplace
?  I don't think that there is a connection limitation in any of these and
they are cheap as well but am note sure about the linked server support in
them to pull data from heterogeneous back-end databases.

If anyone has any insight on this, it will be highly appreciated.

Thanks.

 
 
 

MSDE Concurrent Connections

Post by Bill Hollinshead [MS » Tue, 22 Oct 2002 18:29:55


Hi Rahul,

I will have to assume that you confirmed MSDE hit that limit via DBCC
CONCURRENCYVIOLATION. If that is not how you confirmed, then please keep in
mind that there are an overwhelmingly larger number of other causes for
poor database performance, that just seeing 8 spids in sysprocesses does
not mean that MSDE's governor had kicked in, and keep in mind that multiple
instances of MSDE can be setup. I am not aware of any other product that
uses linked servers. But in any case, I will not recommend competitor's
products <g>. For similar reasons, I would expect this newsgroup to not
offer you this type of recommendation, unless that response was from
another party that was interested in selling you a (or perhaps their)
competing product <g>. From your description of your process, it appears
that even MSDE may not be necessary, although I will assume some MSDE
development costs have already been expended.. Perhaps instead simply write
a multi-threaded C++ application that persists to a local store. If data
access is a problem, then a B-Tree structure will perhaps speed data
access. I do not know whether an OLE DB application can be compiled in
UNIX. I do not see lacks of support for alterative platforms to be a
problem (by itself) since all applications are limited by the platforms for
which they are, or are not, compiled. Thus, I belie it is more important to
choose an application for the features it supports as opposed to
constraining choices by first specifying a shopping list of required
platforms. Given the long-term costs of development and its subsequent
management, I believe the initial cost of any product is an inconsequential
consideration <g>.

Thanks,

Bill Hollinshead
Microsoft, SQL Server

This posting is provided "AS IS" with no warranties, and confers no
rights. Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

 
 
 

MSDE Concurrent Connections

Post by Greg Obleshchu » Wed, 23 Oct 2002 08:43:46


Bill,
Well written, very Microsoft of you. :) Lots of asumptions ;)
Rahual,
Bill has some points , I would look at why you are using 8 conncurrent
connections from one application.  Why not disconnect and or pool the
connection objects?  You haven't stated what application language you are
using so I won't give examples.  Disconnecting or closing the connections is
perferrible than holding connection open, for local as well as remote
servers.

MSDE is great but it's a beast of a thing for readonly data.  There are
other system my personal fav is SQLite.  This is a single DLL database that
lack some of the features of MSDE and SQL Server , but is very quick and is
perfect for client side stores.  It is faster than MySQL and ProtgressSQL
and support over 80% of SQL92  for more information look here
http://www.hwaci.com/sw/sqlite/
Oh and by the way it is free.  There are many different drivers (ODBC, VB
wrappers,..) or if you are using C your can compile the library right in.

--
I hope this helps
regards
Greg O MCSD
Document any SQL server database
version 2.0, the best just got better
AGS SQL Scribe http://www.ag-software.com/ags_scribe_index.asp



Quote:> Hi Rahul,

> I will have to assume that you confirmed MSDE hit that limit via DBCC
> CONCURRENCYVIOLATION. If that is not how you confirmed, then please keep
in
> mind that there are an overwhelmingly larger number of other causes for
> poor database performance, that just seeing 8 spids in sysprocesses does
> not mean that MSDE's governor had kicked in, and keep in mind that
multiple
> instances of MSDE can be setup. I am not aware of any other product that
> uses linked servers. But in any case, I will not recommend competitor's
> products <g>. For similar reasons, I would expect this newsgroup to not
> offer you this type of recommendation, unless that response was from
> another party that was interested in selling you a (or perhaps their)
> competing product <g>. From your description of your process, it appears
> that even MSDE may not be necessary, although I will assume some MSDE
> development costs have already been expended.. Perhaps instead simply
write
> a multi-threaded C++ application that persists to a local store. If data
> access is a problem, then a B-Tree structure will perhaps speed data
> access. I do not know whether an OLE DB application can be compiled in
> UNIX. I do not see lacks of support for alterative platforms to be a
> problem (by itself) since all applications are limited by the platforms
for
> which they are, or are not, compiled. Thus, I belie it is more important
to
> choose an application for the features it supports as opposed to
> constraining choices by first specifying a shopping list of required
> platforms. Given the long-term costs of development and its subsequent
> management, I believe the initial cost of any product is an
inconsequential
> consideration <g>.

> Thanks,

> Bill Hollinshead
> Microsoft, SQL Server

> This posting is provided "AS IS" with no warranties, and confers no
> rights. Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

 
 
 

1. MSDE concurrent connections

I would like to move from Access to MSDE and I am interested in hearing from
anyone that has implemented the move sucessfully.

How many concurrent connections will MSDE support?   From what I read
Microsoft states 5 is the number.    I am hoping that the number is higher.

Any info on this topic is greatly appreciated.

Steve

2. odbc connection to oracld

3. Concurrent Batches = Concurrent Connections?

4. going from FPD26 to VFP5

5. Maximum concurrent connections using connection pool

6. Sockets to SQL

7. MSDE - 5 concurrent users

8. Help please! dts primary key constraint errors

9. Number of MSDE concurrent users

10. Concurrent Licensing for MSDE

11. MSDE Concurrent Users

12. MSDE: Little Concurrent Execution Limits Test

13. Number of MSDE concurrent users