Server with many clients

[Nathan]

Hi,

We're planning on creating a multi-user application with data stored in a
SQL database. We're unable to decided on the best setup.

Would many users be able to do the following;

CLIENT > SQL SERVER

Would many users be able to connect to the SQL sever at the same time, what
will happen if users try to alter the same data.

Or would i need to set it up like the following;

CLIENT > SERVER > SQL SERVER

So the server can process all of the requests made by clients in order.

Any advice would be grateful.

Thanks

 

[vanderghast]

Your clients (front end) can connect to the server (back end), preferably
with their network identification if possible (as in cases where all the
workstations are all under a Window OS, as simple example) so they won't
have to give another password to be able to connect themselves (although
there are many alternatives, indeed).

They will be all able to connect to SQL server at the same time.

Against two users trying to alter the same data, you have many strategies.


-- In a connected world, you can relay on transaction to lock out other
users (and MS SQL Server has more possibilities than Jet as what kind of
lock you want for transactions). As example, is another user able to
delete/append records to a table where another user is reading the same
table? Maybe that second user is issuing a COUNT(*) on the same table? So,
sometimes, it is ok, sometimes, it is not, and that is why transactions, on
MS SQL Server, allow you different level of 'isolation', to fit your case
without locking out, un-necesssary, other users.


-- In a disconnected world, it is usual to remember the value of each field
WHEN WE READ the record, initially, and, when an update is made, to read the
actual values, in the record, and compare them with the original values. If
they are equal, it is asumed that the record has not been modified, so the
UPDATED proposed values are written in the db. Otherwise, a 'conflict' (a
little bit like for replication) occur and you have to 'solve' it,since,
which of the two users has the legit update? or must we use another kind of
update for a particular scenario?


Note that Access is more likely than otherwise in a 'connected' model.


Vanderghast, Access MVP

 

[Ignacio Machin ( .NET/ C# MVP )]

Hi,

We're planning on creating a multi-user application with data stored in a
SQL database. We're unable to decided on the best setup.

Would many users be able to do the following;

CLIENT > SQL SERVER

SQL Server support many concurrent users. It has different strategies
to cope with concurrency issues (two users modifying the same data)
you can use a pessimistic or optimistic approach (look for more
details about these terms for further explanations)

Would many users be able to connect to the SQL sever at the same time, what
will happen if users try to alter the same data.

Or would i need to set it up like the following;

CLIENT > SERVER > SQL SERVER

This solution has even more problems, now it's your duty to detect
conflicts and solve them in the SERVER component, it also involve more
code to maintain. It has the benefit that it might reduce the size and
complexity of the client app.

 

[Nathan]

Many thanks for your replies. I'll look into the things you mentioned.