Max users for ADP/SQL Server

[Birgit]

Hi,

I am currently running a departmental ADP with SQL Server
2000. I have about 40 users, and my ADP is about 15 Meg.
Our parent organization (which has about 200 users) is
considering using my application, but they are concerned
about whether the solution is robust enough for the large
user base.

I am wondering if anybody is running an ADP with anywhere
close to 200 users. If you are, I would REALLY appreciate
it if you could let me know the total number of users
(concurrent would be nice), and the total application
size. If you would be willing to share your company name,
it would be super (I PROMISE not to call or follow up with
the company directly).

You may email me directly if you do not wish to share that
information on the board.

Thanks so much for your assistance!

Birgit

 

[Vadim Rapp]

Brigit:

B> I am wondering if anybody is running an ADP with
B> anywhere close to 200 users. If you are, I would

I think your main concern should be the fact that Access is using
client-based cursors. If user A is editing a record, and meanwhile user B
changes it, user A will receive the message "this record was changed by
another user" when he tries to save.

This is about the only matter related to the number of the users, the
probability of two users changing the same data at the same time, and
consequences. ADP after all is only a client, and the ADP itself will work
equally for 10 users or for 1,000, nothing will change. Things will indeed
change for SQL Server, but hopefully the proof that it can handle 200 users
is not required.

Vadim

 

[BJ Freeman]

Not sure if this will really help you,
I run nt 4.0 SQL server 7
I find that the robustness is more on the understanding of the SQL server
needs. This includes the size of the DB, relative to disk space. I have
had to move some portions of Dataprocessing to another DB, that was on the
same server, but different hard Disk to get better performance.

I also use a interface DB that routes the appropriate Store procedures to
the correct SQL sever(note I said SQL server, not SQL DB). This is concept
that not many people employ, but is available. This does require all SQL
server interaction with the ADP, to be Stored Procedures. This allows things
like having sales and production on different servers but still have a
uniform calling interface. This allow scalability to meet any large
corporation requirements.

Since the ADP executes it code on the user machine, it is more a function of
the Workstation Ability to handle what the ADP is doing.
BTW is the that 15 megs after it has be compressed? I run 5-10 meg ADP
applications.

Also you have, where you are using Access type coding or Stored procedure
type coding for your forms.
This relates to the network overhead. Also with network overhead is if your
are returning more records in a recodset than necessary from the SQL server.

What is more important that the number of user is the number of processes
running on the server and the efficient use of indexing. I have one query
that ties 42 tables together and looks up 10's of thousand of records.
Before I indexed everything it run about 10 seconds. Now it 105 ms.

as far as concurrent users this is a function of the number of connection
allowed. I have heard, on SQL 2K there is a limit. This is not for sure.
Most ADP use Pooling so the number of connection from one ADP is minimal.

I believe MS once stated that 400-500 users, at once is max per server.
However if you use distributive processing, this should not be a problem.