[AccessD] Access and SQL Server

Because I was leaking connections, I looked it up.  SQL Server actually has a pool of 100 
connections, and that number can be changed (though I haven't figured out how).  Someone on the 
internet had set his to 1000 for some reason.

John W. Colby
www.ColbyConsulting.com

On 3/2/2011 8:07 AM, Jim Lawrence wrote:
> 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>
>