Solved

Installation of MSDE and Maximum Number of Concurrent Users Legal

Posted on 2006-06-26
6
293 Views
Last Modified: 2008-01-09
I have read many posts regarding MSDE usage.  Unfortunately many of them are old (2002 and up).  MS now has SQL Expres (2005).

I am 3/4 finished with (1) re-distributable application that will probably have no more than 5 users concurrently where I am implementing MSDE 2000.  However, I will be starting on another re-distributable application in the same market that will take advantage of 80% of the data structure, forms and reports previously written in application #1.

My problem is that the amount of data may increase exponentially and the number of concurrent users may double or triple.  The key word is MAY.  I don't want to initially lock myself into having to raise the price of my #2 application for full-price SQL Server because of database size and/or performance.  I understand the 2gb limitation of the MSDE database.  Unfortunately, economics is a driving force in marketing this app.  Fair amount of competition.

Many of the links found on the Expert postings regarding performance issues, licensing, etc. refer to MSDE 2000 and those links aren't available anymore now that MS is pushing SQL Express/Server 2005.

I feel that I am at a crossroads in my development and need to decide several things before moving forward:

1)  Do I stick with MSDE 2000 or do I look at another database like mySQL (considerably cheaper)?
2)  Do I stick with MSDE and upgrade to 2005?
3)  Where do I find some correct information on performance and licensing issues?
4)  Where do I find information on integrating installation (Server & Client) of my SQL database into my application installation CD?

Thanks in advance!

Phil Tate
0
Comment
Question by:TSFLLC
  • 2
  • 2
  • 2
6 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16986402
if the application and database are tuned, you can have more than 5 concurrent users. even having 20 will not be a big issue.

1) you will have to potentially review ALL your sql, whatever choice you make
2) you can upgrade to SQL Express. you can take advantage of those new features, but what worked in msde will continue to work in sql express if you put the compatiblity mode to 80. with 90 (sql server 2005) you will have to test again all over again
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 75 total points
ID: 16986409
For the record: SQL Server Express does not have a concurrent user limit and the maximum size of the database is increased to 4 GB.
0
 

Author Comment

by:TSFLLC
ID: 16986571
AngelIII,

I respect your opinion of the utmost.  

"even having 20 will not be a big issue."

Why do you say 'even with 20' when everything I have read so far about MSDE makes reference to 'load governor', '5 threads maximum'.  90% of my VB.NET application is based on select..., insert... & update...  command stored procedures instead of SQL statements imbedded in code.  Therefore it is being tuned from it's inception.  However, if there is validity to a 'governor' or 'thread' issue(s) in MSDE...the optimization of my code will only go so far if these other issues are valid?

Thanks.

Phil
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 425 total points
ID: 16987604
>Why do you say 'even with 20' when everything I have read so far about MSDE makes reference to 'load governor', '5 threads maximum'.

practice (and a bit of maths).
20 concurrent users means 20 active connections at the same time (ie doing something actively), which is not the same as 20 users sitting at their desks clicking on the application that runs the queries.

now, 20 users will be no problem at all definitively, because their click rate will be something around 1 click/second in the fastest period, mostly rather 1 click per 5 seconds. so 20 users -> 20 clicks/second, means 0,05 seconds (50 milliseconds) per query, which is fine for single row-lookup queries (don't forget we speak about msde database with 2gb max data + index size). heavier queries will still run in less than 1-2 seconds, but those will be at much longer intervals.

so, although the msde is optimized for having 5 concurrent active connections working, it still can handle up to 20 concurrent active sessions without problem.

CHeers
0
 

Author Comment

by:TSFLLC
ID: 16987797
Thanks Angel,

This was a very appropriate reply for me.  I appreciate it very much.  Like you said, it's not like 20 users are going to be performing data entry or generating reports non-stop.  You have put my mind at ease 100%.  Nothing like educated or logical confirmation.

I'm will go ahead and divy up the points.  I appreciate Anthony's comment about changes to 4gb for SQL Express.  I would like to divide accordingly.

#3 is a mute issue now, but can you help me with my #4 question.  This is my first use of SQL from a re-distribution standpoint.  I feel like I have investigated re-distributed installation somewhat thoroughly, but just as with my original issue you have resolved....I'm not sure the best way to move forward.

For additional reference....I am planning on having the complete installation available via CD and also via a downloadable .exe from my website www.thesoftwarefirm.com.  The app will install initially as a 30 day demo...which can then be registered upon payment with an registration/install key.

Many thanks, Angel & Anthony!

Phil
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16989024
>>but can you help me with my #4 question.<<
Here is one solution:

SQL Packager™ Standard
Packages a database or a database update
http://www.red-gate.com/products/SQL_Packager/index.htm
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now