"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
>

"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...
>> 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
>>
>
>