[AccessD] Access and SQL Server

Hi Stuart:

A number of years ago the philosophy was to have "tight-binding" (or you
could call it bound or forced-synchronization or a dedicated connection)
with the BE data server.

Over a fixed LAN system, the older standard was possible but there were
problems. For example: Connect an Access FE to an MDB BE on a server and if
the server crashed or needed a remote reboot the MDB was very likely to
corrupted. (If some user was connected...Had this happen a number of years
ago while working on some government contracts and the client demanded some
thing better.)

When BE SQL servers were first introduced, again the philosophy was to have
dedicated connections to the server. The result was server farms as
thousands of fixed connections were required, depending on the number of
users. 

Now a days, a single server, with only 20 connections can support 50K hits
(different users?) a day. When a request arrives, it is queued and the
connection is immediately terminated. When the server can handle the
request, it does so and then queues up to open up a new connection, to the
remote station. If the remote station responds immediately the response is
given and any data is transferred otherwise the connection is immediately
terminated and the response queue cycles and tries again later. 

In most cases, the user is blissfully unaware of what is going on in the
background as to them it appears that they have dedicated, synchronized,
bound connection. 

When working with a program such as Access, the programmer can force a
dedicated connection by holding the connection open but unfortunately, under
load conditions, the BE server will run out of resources, refuse to respond
and there goes that tight binding. Added to that is the type of connection.
A dedicated LAN connection is very different from an internet connection as
the internet by its nature is flaky and unstable and again there goes that
tight binding.

So there is my interpretation and a lot more detailed than I wanted to get
in to.

Jim

          

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: Tuesday, March 01, 2011 3:55 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Access and SQL Server

See inline.
On 1 Mar 2011 at 15:26, Jim Lawrence wrote:

> I would suggest that most MS SQL applications are web based and
> therefore are using asynchronous type connections... they connect,
> call to a SP and wait for the remote server to respond. When the data
> is ready for receipt your Ajax connections notes data and the
> population of the recordsets begin, then connection is terminated.
> 

That is a perfect description of a "synchronous" connection.  Send a request
and wait for a 
reply before doing anything else.

> I would suspect that MS SQL runs similar to Oracle. Oracle just has
> more of its internal features exposed so I doubt whether there is any
> difference. When accessing data on an Oracle server the data request
> is queued, when the system has time it checks the request and then
> retrieves any data. It then calls the remote site indicating that the
> data ready, when the remote site says 'yes', the data is transferred.
> 
> That does not describe a synchronous connection to me.
> 

No, that is asynchronous.  But that is only one of the ways Oracle works, it
also works 
asynchronously:


>From my reading of this link, synchronous connections are the default in
Oracle to. Note the 
use of the word forced in the last sentence.:

http://www.di.unipi.it/~ghelli/didattica/bdldoc/A97329_03/integrate.902/a952
65/wfapi11.htm

<quote>
A workflow process can be either synchronous or asynchronous. A synchronous
process is a 
process that can be executed without interruption from start to finish. The
Workflow Engine 
executes a process synchronously when the process includes activities that
can be 
completed immediately, such as function activities that are not deferred to
the background 
engine. The Workflow Engine does not return control to the calling
application that initiated 
the workflow until it completes the process. With a synchronous process, you
can 
immediately check for process results that were written to item attributes
or directly to the 
database. However, the user must wait for the process to complete.
...
An asynchronous process is a process that the Workflow Engine cannot
complete 
immediately because it contains activities that interrupt the flow. Examples
of 
activities that force an asynchronous process include deferred activities,
notifications 
with responses, blocking activities, and wait activities. 
</quote>

-- 
Stuart