Hello,
We are setting up a 2-node active/active SQL 2000 cluster on Windows 2003
ent. ed.
Our DB team writes MS Access XP front-ends for client connection to SQL
cluster.

NODE1 and NODE2 are working fine. I can fail INST1 over to NODE2 and INST2
over to NODE1, vice versa, all is good.

However, in testing, if a is client using the MS-Access front-end connecting
to INST1 on NODE1, and INST1 is failed over to NODE2, the client loses
connection and receives an error. If we close MS-Access and re-open the
front-end (while INST1 is still on NODE2 the failover node), it reconnects
and works just fine.

I am reading this article which describes the behavior of a client app
during a failover...
http://support.microsoft.com/default...273673&sd=tech

From this article, I don't understand what has to be done to ensure a client
re-establishes the connection automatically (and transparently) once the
failover occurs? Or is this not possible to get a transparent failover with
SQL cluster?

This is our second sql cluster. The other one is a document management
software and that software doesn't miss a beat when SQL is failed to node 2.

Thanks in advance.
Jeff

If the software doesn't miss abeat during failover then they did their
homework when they wrote the app. The application must be made cluster
aware, it doesn't come that way. By being cluster aware means that they
need to be able to detect a loss of connection and reconnect. And hopefully
save the current state of any transactions so it can resubmit them.

--
Andrew J. Kelly SQL MVP


"JJP" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Thanks Andrews. Let me make sure I understand this then....you are saying
that our database admins must write their MS-Access front-ends to detect a
failover on the SQL cluster and accordingly reconnect and resubmit the
transactions?
Thanks.
Jeff



"Andrew J. Kelly" <sqlmvpnooospam (AT) shadhawk (DOT) com> wrote

I don't know if it is the admins or not but the app must certainly be able
to detect a lost connection and be able to reconnect. Once the active node
fails you loose connection and there is nothing that can be done about that.
Since when the connection is broken SQL Server will rollback any open trans
it is up to the application to resubmit the transactions after it gets
reconnected. The app does not have to know anything about a failover per
say, just the fact they lost connection. This can happen for many reasons,
a fail over just happens to be one of them. The app won't know which node
it's on, they both appear exactly the same to it.

--
Andrew J. Kelly SQL MVP


"JJP" <anonymous (AT) discussions (DOT) microsoft.com> wrote

I think an important thing to keep in mind here is the use of Access. If tables are Linked in Access then this connection checking becomes a little more combersome than if you use ADO Code to connect to SQL Server.

"Andrew J. Kelly" wrote:

Doug, thanks for the reply. Our tables are linked in Access. I am checking
the MSDN website and will forward that to our DB admins. If you have any
other thoughts or suggestions, let me know, otherwise thank you and thanks
everyone.



"Doug Guerena" <Doug Guerena (AT) discussions (DOT) microsoft.com> wrote

more combersome than if you use ADO Code to connect to SQL Server.