MS Access support autom. failover when using SQL database mirrorin

[Erik van der Veen]

Hello there,

Currently we have a Microsoft Access 2003 client connecting to a Microsoft
SQL Server 2000 machine. We use Logshipping for a disaster recovery solution.
Failover is manual and takes some time to complete. We would like to decrease
the time necessary to perform a failover of the database server.

Upgrading to SQL 2005 (or 2008) and database mirroring would take care of
the server part of the failover, but I think the Microsoft Access client
(version 2003 and 2007) does not support this type of automatic failover. Am
I correct and, if so, are there ways to work around this?

I also consider clustering as an option, but that doesn't work for all types
of disaster, so database mirroring would be the prefered solution.

Thank you for your attention.

Erik.

 

[Stefan Hoffmann]

hi Erik,

Currently we have a Microsoft Access 2003 client connecting to a Microsoft
SQL Server 2000 machine. We use Logshipping for a disaster recovery solution.
Failover is manual and takes some time to complete. We would like to decrease
the time necessary to perform a failover of the database server.

http://msdn.microsoft.com/en-us/library/ms175484.aspx
http://msdn.microsoft.com/en-us/library/ms130822.aspx

Try a failover with your Access databases using one of the connection
strings explained in the documents above.


mfG
--> stefan <--

 

[david]

Doesn't look like it will work.

Access caches both connections and connection information,
and in the past broken connections crash the cache.

When you have automatic failover, the client has to drop the
broken connection and create a new connection. I don't think
Access is able to do that, although you could try creating a
separate workspace and not using linked tables.

On the other hand, I don't know anyone who has tested it recently.

(david)