[AccessD] Access and SQL Server

Hi John:

You are definitely going into new territory, to my experience on this one.
Having a bound system, connecting over the internet, I would suspect would
drain resources from your BE server, (20+ dedicated connections per remote
station? One for every bound table?) And then there is that flaky internet
in between. 

Me thinks there is a good reason for not exceeding the default 100
connection...Have you calculated how much resources like bandwidth, CPU and
Memory each connection requires?
 
Fifteen years ago I tried to get a similar system working and failed but the
internal design of Access may have improved dramatically since then. As I
said before, hats off if you get it working.

Jim



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Wednesday, March 02, 2011 7:17 AM
To: Access Developers discussion and problem solving
Subject: Re: [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>
>
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com