Re: [ot] MS SQL and Access - connection usage

simonk wrote:

> We have an application which used to be a straight MS access database, but
> which has been migrated to a SQL Server back-end (I imagine using some
> godawful "wizard").
>
> Aside from the general bogosity of the situation, we're finding that each
> client application (i.e. running instance of MS Access on a PC) is using
> up
> to 6 connections to the SQL database. With a user base of around 100, you
> can imagine that things are beginning to creak.
>
> The system is to be removed within the next 2 or 3 months but there's
> still a risk that things might go "bang" in the interim.
>
> Knowing more about Oracle than I do about SQL Server, I'm making the
> assumption that the database is dying because of the sheer number of
> connections - it's running on bang-up-to-date hardware with fast disks and
> shed loads of memory and network bandwidth.
>
> We can't redesign the application because we wouldn't have the time, but
> can something be done with SQL Server or the SQL Server Client/ODBC
> software - I'm thinking along the lines of can a connection pool or load
> balanced/clustered arrangement - such that the current single-node
> database doesn't have to spend all its time juggling the demands of
> 600-800 concurrent connections?
>
> TIA etc
>
Can't remember the specifics, but thus *might* be related to a known DNS
issue. It sounds similar, but ICBA to look it up. Feed it into to google
and see if the issues match your symptoms.
--
Catman MIB#14 SKoGA#6 TEAR#4 BOTAFOF#38 Apostle#21 COSOC#3
Tyger, Tyger Burning Bright (Remove rust to reply)
Alfa 116 Giulietta 3.0l (Really) Sprint 1.7 156 TS S2
Triumph Speed Triple: Black with extra black bits
www.cuore-sportivo.co.uk
.

Relevant Pages

  • Re: Access 2007->SQL Server2005 "connection was forcibly closed",G
    ... an Access database in former times this was a lot of work which took me ... closed connections - but all of these errors are in the version which used ... the SQL Server 2000 and everything worked ... client for programming easy, fast and good applications. ...
    (microsoft.public.sqlserver.connect)
  • Re: TCPv4 Counters "Connetions Active" and "Connections Passive" on W2K3 IIS 6 S
    ... Active is the number of connections that are initiated by the system. ... For the SQL server some indications might be: ... values indicates that active queries are waiting for other queries. ... Also database size, queries and indexes can make huge differences. ...
    (microsoft.public.inetserver.iis)
  • Re: Failure of Linked table in Access...
    ... SQL Server database via an ODBC DSN. ... in the Access database with various forms and reports. ... error and all subsequent queries on the PROJ table fail. ... The table is one of many tables that are linked from SQL Server ...
    (microsoft.public.access.modulesdaovba)
  • Re: Strange SQL 2000 connections ! (ghost connections)
    ... I no longer like to comment on Access/JET database issues--there are simply ... Access/JET is a very challenged interface to SQL Server. ... I think that the 'ghost' connections are unrelated to your problems. ... If you find any open transactions that are more than a few minutes old, ...
    (microsoft.public.sqlserver.connect)
  • Re: MS Access DAO -> ADO.NET Migration
    ... William Vaughn ... Microsoft MVP ... Hitchhiker's Guide to Visual Studio and SQL Server ... My migration app works building a SSCE database file with imported data ...
    (microsoft.public.dotnet.framework.adonet)