Overcoming the connection limitations of MSDE....

I have a Windows application I have developed in VB using a MSDE
database. I will be distributing this to clients, most of which will
have only about 2-4 terminals, but there are many that will have 10-15
terminals running my application. I am afraid of what the performance
is going to be like with clients running a high number of terminals
because of the limitations of MSDE. I looked into the price of the
full version of SQL server for clients with a larger number of
terminals but it would cost way to much so that simply is not an
option for any of them.
I have spoken to other companies who develop products like mine and
they say they have clients running close to 30 terminals off of MSDE
with no slow down. They say they have done this by using connection
pooling. I have looked around on this issue and most people post
articles on connection pooling and the MSDE workload governor but I
have not seen any practical examples on specifically how to setup a
client application to overcome the MSDE workload limit.
As far as connection pooling is concerned, how exactly will this help
in limiting the workload? Does anyone have an specific examples of
how I would do this? I don't want to keep looking around and having
to experiment around with things that may or may not work because I
have no real way of testing this other than giving it to clients to
test. I'd really like to see an actual example of how someone has
done specifically what I'm doing here and has gotten it to work. I'm
surprised there are not more articles on this specific subject, but I
could not find any.
Any help would be appreciated, thanks...
On 15 Apr 2004 08:45:47 -0700, bostonpartykid@.yahoo.com (Ray Lavelle)
wrote:

>I have a Windows application I have developed in VB using a MSDE
>database. I will be distributing this to clients, most of which will
>have only about 2-4 terminals, but there are many that will have 10-15
>terminals running my application. I am afraid of what the performance
>is going to be like with clients running a high number of terminals
>because of the limitations of MSDE. I looked into the price of the
>full version of SQL server for clients with a larger number of
>terminals but it would cost way to much so that simply is not an
>option for any of them.
>I have spoken to other companies who develop products like mine and
>they say they have clients running close to 30 terminals off of MSDE
>with no slow down. They say they have done this by using connection
>pooling. I have looked around on this issue and most people post
>articles on connection pooling and the MSDE workload governor but I
>have not seen any practical examples on specifically how to setup a
>client application to overcome the MSDE workload limit.
>As far as connection pooling is concerned, how exactly will this help
>in limiting the workload? Does anyone have an specific examples of
>how I would do this? I don't want to keep looking around and having
>to experiment around with things that may or may not work because I
>have no real way of testing this other than giving it to clients to
>test. I'd really like to see an actual example of how someone has
>done specifically what I'm doing here and has gotten it to work. I'm
>surprised there are not more articles on this specific subject, but I
>could not find any.
>Any help would be appreciated, thanks...
Don't keep your connection open. Open a connection, get the desired
data and then close the connection. Open a connection, update/insert
the desired data and then close the connection. If your app doesn't
do really intensive data access and you aren't keeping the connection
open, chances of having more than 5 users hit the DB at the same time
are pretty slim.
HTH,
Bryan
__________________________________________________ __________
New Vision Software "When the going gets weird,"
Bryan Stafford "the weird turn pro."
alpine_don'tsendspam@.mvps.org Hunter S. Thompson -
Microsoft MVP-Visual Basic Fear and Loathing in LasVegas
|||hi Ray,
"Ray Lavelle" <bostonpartykid@.yahoo.com> ha scritto nel messaggio
news:d8653140.0404150745.2f9911ec@.posting.google.c om...
> I have a Windows application I have developed in VB using a MSDE
> database. I will be distributing this to clients, most of which will
> have only about 2-4 terminals, but there are many that will have 10-15
> terminals running my application.
> CUT
as Bryan already pointed out, keep your transactions as short as possible...
it's not a matter of connections, becouse users are "usually" idle, and
connection pooling is on by default, but can be not that useful, becouse
connections in the pool can be reused only if the connection string is exact
the same as the last used...
but, again, keep your batches as short as possible.
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Ray I have one question though about your scenario as I am in the same boat as you are...
How do you plan to deploy your app with MSDE. I know Microsoft has dismissed merge modules way and is advising the bootstrapper. I am still investigating the bootstrapper but was wondering how will you be setting up your desktop app (I believe it is des
ktop app) so that the data (MSDE database) is on shared server. Are their any specific customizations that you are doing to the setup. Do you have any code for this which you can post for all...
Thanks a goodluck
dev
|||The number of connection that you can have with MSDE are the same as SQL
Server (32767) . The limits in MSDE are related to the amount of work that
it can do at any given time and the size of the database. If your clients
are not hammering the database server with constant requests then it is
certainly possible to scale to possibly hundreds of connections. For
information about how this workload governor works see this link:
http://msdn.microsoft.com/library/?u...asp?frame=true
Your application should only hit the database when it needs to and you
should strive to get all the data you need for a function of your
application in one request. Use stored procedures to handle the logic of
what data elements you need; rather than getting a piece of data returning
it to the application, deciding you need an additional pieces of data and
then fetching them repeatedly from the database.
We have customers using MSDE with our application and running many client
workstations with no problems. On the other hand, many of our customers
already have one or more installations of SQL Server on site which they
install our database to and are able to scale to dozens of workstations.
Jim
"Ray Lavelle" <bostonpartykid@.yahoo.com> wrote in message
news:d8653140.0404150745.2f9911ec@.posting.google.c om...
> I have a Windows application I have developed in VB using a MSDE
> database. I will be distributing this to clients, most of which will
> have only about 2-4 terminals, but there are many that will have 10-15
> terminals running my application. I am afraid of what the performance
> is going to be like with clients running a high number of terminals
> because of the limitations of MSDE. I looked into the price of the
> full version of SQL server for clients with a larger number of
> terminals but it would cost way to much so that simply is not an
> option for any of them.
> I have spoken to other companies who develop products like mine and
> they say they have clients running close to 30 terminals off of MSDE
> with no slow down. They say they have done this by using connection
> pooling. I have looked around on this issue and most people post
> articles on connection pooling and the MSDE workload governor but I
> have not seen any practical examples on specifically how to setup a
> client application to overcome the MSDE workload limit.
> As far as connection pooling is concerned, how exactly will this help
> in limiting the workload? Does anyone have an specific examples of
> how I would do this? I don't want to keep looking around and having
> to experiment around with things that may or may not work because I
> have no real way of testing this other than giving it to clients to
> test. I'd really like to see an actual example of how someone has
> done specifically what I'm doing here and has gotten it to work. I'm
> surprised there are not more articles on this specific subject, but I
> could not find any.
> Any help would be appreciated, thanks...
|||Jim,
Just curious, how do you change the connection string to specify where the database as some of your clients use MSDE and some use SQL Server.. does it happen during the installation. Also do you prompt them during installation on whether they want to ins
tall MSDE or use SQL Server they already have...
thanks
dev
|||You'd be much better off just disabling the workload governor. It's really
easy to do, all you need is change one byte in a hex editor.
Here's the method:
1. The file you will be changing is SQLBOOT.DLL. This can be found in
Microsoft SQL Server\MSSQL\Binn. The rest of this method only applies to
version 2000.080.0194.00 of the file, as included in Service Pack 3a.
Check the version of the file by looking at the Version tab of its
Properties window, and the size of the file, which should be 33,340 bytes.
You may also want to check the MD5 sum of the file, which is
175b236765fb446f46da5da635681ab8.
2. Obtain a suitable hex editor from somewhere. A fairly decent one that can
do the job is available here:
http://www.catch22.org.uk/software/hexedit.asp
3. Make a backup copy of SQLBOOT.DLL somewhere, just in case.
4. Stop MSDE's MSSQLSERVER service.
5. Open the original SQLBOOT.DLL in the hex editor.
6. Find the byte at location 10145 decimal / 0x27A1 hexadecimal.
7. The current value of the byte should be 08. Change it to 00.
This changes the target benchmark users / concurrent workload limit value
from eight to zero. When the SQL Server reads this as being zero, it
disables the workload governor.
8. Save SQLBOOT.DLL.
9. Start the MSSQLSERVER service.
If it worked, the SQL statement "DBCC CONCURRENCYVIOLATION" should return
without any output (normally it would display a summary of when the workload
governor has been active.)
Hope this helps!