handling failover at the application level with Sql2005 Mirroring

We're in the process of writing an application that we want to work with a
synchronous mirrored Sql 2005 database and a witness.
As I currently understand it the best way to handle failover with this setup
is to catch the server not available error and then retry the operation.
Any open transaction will be rolled back (in effect) so we need to retry the
entire transaction.
We have complicated transactional processes at our business logic layer, so
we have quite a few places where we begin a transaction, carry out a number
of operations that may involve multiple database calls, and then commit or
rollback on error.
In order to add this retry functionality it looks like I need to add a
try-catch around every call at the business logic layer as it needs to wrap
the transaction. This is possible, but messy.
Is there a better way of doing this? Ideally the handing of the retry
should be at the data access layer, but as the transaction in progress when
the failover happens will be lost it doesn't look possible to handle this in
individual data commands.
Any ideas?
Keith Henry
This isn't unique to Database Mirroring. You would have to do that in every
case where you are dealing with a failover configuration. There is no logic
that basically says "retry". You have to code this all yourself.
When the mirror fails over, you will get a disconnect and any transactions
in flight will be rolled back. The only thing your applications can take
advantage of if they are using the new MDAC library. In this situation,
there is code carried that will cache both the principal and mirror. Your
application can simply reconnect to the principal and the MDAC layer will
transparently redirect the connection and requests to the mirror.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Keith Henry" <k.henry@.link-hrsystems.com> wrote in message
news:u2mk5jjLGHA.984@.tk2msftngp13.phx.gbl...
> We're in the process of writing an application that we want to work with a
> synchronous mirrored Sql 2005 database and a witness.
> As I currently understand it the best way to handle failover with this
> setup is to catch the server not available error and then retry the
> operation. Any open transaction will be rolled back (in effect) so we need
> to retry the entire transaction.
> We have complicated transactional processes at our business logic layer,
> so we have quite a few places where we begin a transaction, carry out a
> number of operations that may involve multiple database calls, and then
> commit or rollback on error.
>
> In order to add this retry functionality it looks like I need to add a
> try-catch around every call at the business logic layer as it needs to
> wrap the transaction. This is possible, but messy.
>
> Is there a better way of doing this? Ideally the handing of the retry
> should be at the data access layer, but as the transaction in progress
> when the failover happens will be lost it doesn't look possible to handle
> this in individual data commands.
> Any ideas?
> Keith Henry
>
|||Thanks,
I guess we'll have to go with the retry from the top level then.
Keith Henry
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:udYhiKnLGHA.208@.tk2msftngp13.phx.gbl...
> This isn't unique to Database Mirroring. You would have to do that in
> every case where you are dealing with a failover configuration. There is
> no logic that basically says "retry". You have to code this all yourself.
> When the mirror fails over, you will get a disconnect and any transactions
> in flight will be rolled back. The only thing your applications can take
> advantage of if they are using the new MDAC library. In this situation,
> there is code carried that will cache both the principal and mirror. Your
> application can simply reconnect to the principal and the MDAC layer will
> transparently redirect the connection and requests to the mirror.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
>
> "Keith Henry" <k.henry@.link-hrsystems.com> wrote in message
> news:u2mk5jjLGHA.984@.tk2msftngp13.phx.gbl...
>