Jim Dettman
jimdettman at verizon.net
Fri Sep 10 14:46:17 CDT 2010
Dan, That is more or less true, but the real bottle neck with a JET based DB has never been .LDB file operations. With JET, all processing is on the client side. The server acts as nothing more then a file share. The trick with keeping a connection open to the BE avoids the repeated closing/opening of the LDB and DB files and all the associated overhead with removing/adding an active user under JET. Some apps benefit from that, other not because they already maintain a connection one way or another. The problem can be further compounded if the server has OPLOCKS on (which allows client side caching of files). JET doesn't need this as the cache is already on the client side. So it's simply a wasted effort on the servers part. And by default, OPLOCKs is enabled on Windows servers. The real restriction of JET is just one of stability. With no server side process to perform a rollback if a disconnect occurs, anytime that happens in the middle of write operations your fair game for corruption. So by the time you get past 30 or 40 stations, it's just hard to keep the environment stable. To prove that point, you can easily run a read-only/reporting JET based app with 200+ users without issues. It's not an issue of performance, but one of stability. Jim. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dan Waters Sent: Friday, September 10, 2010 2:44 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] SQL Server Express - true skinny Hi John, The trick is binding a form to a table. What's the plan to do that? For Access user limits, it's my understanding that the throttle (governor) is the .ldb file on the BE .mdb. This .ldb file is being continuously 'managed' by all the users currently logged on - as you increase the number of users, the .ldb file management slows down from the perspective of a single user. As the server's CPU speed increases and as its memory increases, the slowdown will begin to happen with a higher number of users. Of course, SQL Server doesn't have an .ldb file, and it will manage concurrency internally and more quickly. I have an Access Que 2003 book which states that developers expect to see a slowdown at about the 10 to 30 user point. But that was written eight years ago when CPU's were slower and memory was less available. HTH! Dan -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: Friday, September 10, 2010 1:09 PM To: Access Developers discussion and problem solving; Sqlserver-Dba; VBA Subject: [AccessD] SQL Server Express - true skinny Does anyone know the true skinny on the limitations placed on SQL Server express in order to "throttle" it.? I found a blog that claims that outside of CPU / Memory / DB size limitations, there is not other "governor". So it appears that the limitations are: 10 gig db file size. This does not discuss additional database files. 1 CPU. However it appears that it can use 4 cores if available 1 gig of memory. This appears to be the killer. But there appears to be no "user" limitations etc. So the question is, would SQL Server express be capable of replacing Jet for a complex bound application for 30 users? -- John W. Colby www.ColbyConsulting.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com