Solved

Multi-User Configuration with MSDE

Posted on 2004-03-30
7
693 Views
Last Modified: 2008-03-17
I am building an Access application (ADP) that will be used by 70 users in 11 different locations.  Just found out that I do not have access to SQL Server, so I must rely on MSDE.

Can I install MSDE on each client machine and attach to a database (mdf) file on a central file server?  Will I run into any concurrent user limitations since there will be multiple instances of MSDE?

Can anyone suggest alternative configurations?

Thanks
0
Comment
Question by:codeguy
  • 4
  • 3
7 Comments
 
LVL 7

Expert Comment

by:donpricejr
ID: 10717116
There can be up to 16 instances of MSDE on any one given machine.

-Don
0
 

Author Comment

by:codeguy
ID: 10718905
To clarify my question.  Is the concurrent user limit of 5 applicable when multiple clients (with seperate instances of MSDE) are connecting to a data file on a central server?

0
 
LVL 7

Expert Comment

by:donpricejr
ID: 10719356
Some say that the 5 is actually 8, but that is not really your question--that said, however, I must agree with the fact that it is 5 because "5" will be the basis of my answer.

MSDE only allows 5 threads to the engine at any given time; therefore, any additional threads (eg, stored procedures, views, etc.) will sit in queue unti which time only 4 are available. To that end, you may have 20 concurrent users and only 3 requests at the same time going on. I hope that this helps to clear up your question.

-Don
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

Author Comment

by:codeguy
ID: 10719643
Thanks Don.  If you and I (and three of our friends) had MSDE installed on each of our workstations, and the data (mdf/ldf files) sat on a file server that we have access to, is this only one instance on each of the five machines, or is this five instances because each MSDE is attached to a central data source?

Note that I am increasing the points here because of the rephrased question...
0
 
LVL 7

Accepted Solution

by:
donpricejr earned 100 total points
ID: 10720833
You have a great question! That is the good news. :( However, I think you are okay, but it is simply an educated guess. Here is what I think:

MSDE is required on at least one machine, or MSDE may be ran on multiple machines, as you would like in this case. MSDE has a governer on it, to quote a fellow Expert today I spoke with, and that governer governs only that particular instance of MSDE, not the data file (mdf). Therefore, you should be okay to re-route everything through individual instances of MSDE. That said, try a pilot test and see what you come up with.

On another note, must all of your data be real-time all-of-the-time? In other words, are all 70 users reading and writing, and if so, reading and writing to each other's data? If not, perhaps there are other ways to go about this--in fact, I have the same problem for a Fortune 50 firm in which I must create a global system based on cities (OLTP) and then push it out (ETL) and bring it into a warehouse (OLAP) without enterprise systems, including MSDE cannot be used. Oh, the fun we techies have!!! ;-)

I like this question, perhaps getting Nico (nico5038) and Jack (jadedata) involved would be beneficial! I respect their advise a lot--as well as--walt (walterecook) and others. They are much better suited for these questions and I do not want to give you the wrong answer!

-Don
0
 

Author Comment

by:codeguy
ID: 10725125
Thanks Don.  I welcome other responses to this question, because I agree that it could be educational.  I want to award you points for the followup and great answer.  I will do a pilot test with multiple MSDE versions, but its nice to hear that this is not problematic on its face.

I will also consider a nightly ETL job.  I assume we are talking about some type of batch job that might run an Access procedure that fires an update query, etc.  Or, is there another third party tool that you were thinking of?  It would be nice to just develop a DTS package, but again, no SQL Server available.

Thanks again...
0
 
LVL 7

Expert Comment

by:donpricejr
ID: 10725398
Thank you for the points--I appreciate the support.

I will create the ETL tool in my example above because it should not take very long to hand-code, as independent systems have minimal impact. That said, however, I like Informatica for much larger projects--I believe this to be a solid piece of software, but it comes with a handsome price and is usually reserved when you are loading from CRM, Payrolling, HR, etcetera systems. Without knowing the specifics, I would recommend that you write your own pushes or pulls (depending on the architecture) and this way you have total control. DTS is nice, but sometimes building it yourself is more flexible because you know the inner workings of the machine! As for other 3rd party tools, I am sure there are DTS (scaled down) like packages, but I have never looked to be honest. With the ghosting of machines and being constrained by policies, making our own is what I am usually left with.

Best of luck!

-Don
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

948 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

21 Experts available now in Live!

Get 1:1 Help Now