June 25, 2004 at 9:21 pm
Hello,
I've been working with SQL-Server for many years now with an application we developed. We had ported it from MS Access for performance and reliability issues. I recently called Microsoft in reference to the MSDE. We are considering it for small deployments of our VB 6.0 based application. The Microsoft rep was extremelly helpful and nice, but he truly confused me.
1. He said MSDE does NOT come with GUI management tools. I could understand that.
2. He said you COULD NOT MANAGE IT using Enterprise Manager or Query Analyzer. I found this was NOT true and I was able to access it from another machine with these tools.
3. He mentioned there was a "Web Based" management tool. I downloaded it and found it cool but very lacking in basic functionality.
4. He said it would easily support up to 25 users before the "Throtle" became noticeable.
Now for the real question. He misled me on the first two counts. I have a hard time believing the fourth. What should I watch for when using the MSDE instead of SQL Server? Are there any special Gotchas for programmers using ADO/OLE-DB?
Thanks a million in advance,
Jacob
June 26, 2004 at 5:19 am
I think the point at which the "throttle" become noticeable depends on what you are doing with the database.
I've had over 25 users on bog standard Access 2.0 but all they were doing was reading the database.
For complicated updates, even 2 users could cause deadlocks.
I seem to remember that MSDE has an "autoclose" property on its databases. This is useful if you are running on a local PC as it closes the database files when not in use and opens them on demand.
In a server environment there is a noticeable performance penalty in checking the autoclose property.
June 26, 2004 at 2:49 pm
Thanks David... I do appreciate your comments. Our application is designed to connect and disconnect in very sub-atomic routines. I was wondering if the Begin/Commit Transaction mechanism works the same. Also if anyone has experienced a particular level or rate of transactions that may cause this throttle. I couldn't find any specifics so far.
June 27, 2004 at 8:23 am
Actually on count 4 I suggest you read this
http://www.microsoft.com/sql/evaluation/overview/default.asp
It states that it can handle 25 concurrnt users. Basically this means only 25 connections can be open at any given point. This includes connections made by Agent jobs running.
2 thou was a complete error on his part as you noted. In fact I have used MSDE and scavenged EM and QA from the trial version for use with MSDE on a previous project.
The link thou goes into a bit of things and is probably where he got his info (Reps are mostly readers of specs) and he just guessed at the rest from what he read.
The next big gotcha is databases can only be 2 GB max (then they act wierd). And like Standard Edition only supports use with up to 2 GB of ram.
June 27, 2004 at 3:34 pm
Antares, I really appreciate your input. Thanks!
June 28, 2004 at 1:40 am
A common mistake is to confuse connections with users.
One connection is automatically taken up with by SQL Server itself.
If you have EM and QA open then you've just used another 2 at the minimum.
If you have reached the stage when you have 25 users and are doing transactional stuff then you should probably be budgetting for an upgrade next year.
June 29, 2004 at 2:12 pm
A dot net web app is a great front end to an MSDE database because of the disconnected state of the dataset.
If you have a VB6 forms app,then I'd be careful, because you users KEEP their connections open alot.
If your web app uses the SAME connection string, then you get the benefit of connection pooling.
Here's a good article:
June 29, 2004 at 5:41 pm
Sholliday, that article was an excellent resource. I did write the code in VB 6.0 but I operate in mostly disconnected mode. I create connections on the fly, as needed, for specific groups of transactions. This is why I was a bit worried about the performance hit from the MSDE throttling.
Thanks!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply