Problem with database mirroring and ADO.net

I have set up mirroring and trying to get it to work from my .NET
application. Mirroring seems to work ok.
When I do a manual failover the witness is updated properly in the
sys.database_mirroring_witnesses table so it seems to be configured
correctly. I also can shutdown the master database and the mirror takes over.

My application is configured with Server set to my master database and
FailOverPartner to my mirror database.

If I am connected to the master database and do a manual fail-over all my
attempts to communicate with the database fail with an exception, even after
repeated retries.
(System.Data.SqlClient.SqlException: A transport-level error has occurred
when sending the request to the server. (provider: TCP Provider, error: 0 -
An existing connection was forcibly closed by the remote host.).)

If I do a manual fail-over and restart my application it connects to the
mirror database and everything is working. If I now do a manual fail-over I
get the same exception, but after a few retries it connects to the master
database.

The field Connection.Datasource always shows the master database regardless
if it's connected to the mirror.

What am I doing wrong? Is this how it's supposed to work?

I have been pulling my hair over this problem and I just can't get it to work.

I'm using SQL Server Authentication to access the database and I can connect
to the master when it's acting as principal and I can connect to the mirror
when it's acting as principal, but when doing a fail-over, manual or shutting
down the master server it never tries to connect automatically to the mirror
even if it's changed role to principal.

Do the client need access to the witness in any way?

I'm using version 2.0.50727 of System.Data.dll.

"Mattrix" wrote:

> I have set up mirroring and trying to get it to work from my .NET
> application. Mirroring seems to work ok.
> When I do a manual failover the witness is updated properly in the
> sys.database_mirroring_witnesses table so it seems to be configured
> correctly. I also can shutdown the master database and the mirror takes over.
>
> My application is configured with Server set to my master database and
> FailOverPartner to my mirror database.
>
> If I am connected to the master database and do a manual fail-over all my
> attempts to communicate with the database fail with an exception, even after
> repeated retries.
> (System.Data.SqlClient.SqlException: A transport-level error has occurred
> when sending the request to the server. (provider: TCP Provider, error: 0 -
> An existing connection was forcibly closed by the remote host.).)
>
> If I do a manual fail-over and restart my application it connects to the
> mirror database and everything is working. If I now do a manual fail-over I
> get the same exception, but after a few retries it connects to the master
> database.
>
> The field Connection.Datasource always shows the master database regardless
> if it's connected to the mirror.
>
> What am I doing wrong? Is this how it's supposed to work?
>

Mattrix,
Did you solve this problem. I am experiencing the same problem.

Thanks,
-Ian

"Mattrix" wrote:

> I have been pulling my hair over this problem and I just can't get it to work.
>
> I'm using SQL Server Authentication to access the database and I can connect
> to the master when it's acting as principal and I can connect to the mirror
> when it's acting as principal, but when doing a fail-over, manual or shutting
> down the master server it never tries to connect automatically to the mirror
> even if it's changed role to principal.
>
> Do the client need access to the witness in any way?
>
> I'm using version 2.0.50727 of System.Data.dll.
>
> "Mattrix" wrote:
>
> > I have set up mirroring and trying to get it to work from my .NET
> > application. Mirroring seems to work ok.
> > When I do a manual failover the witness is updated properly in the
> > sys.database_mirroring_witnesses table so it seems to be configured
> > correctly. I also can shutdown the master database and the mirror takes over.
> >
> > My application is configured with Server set to my master database and
> > FailOverPartner to my mirror database.
> >
> > If I am connected to the master database and do a manual fail-over all my
> > attempts to communicate with the database fail with an exception, even after
> > repeated retries.
> > (System.Data.SqlClient.SqlException: A transport-level error has occurred
> > when sending the request to the server. (provider: TCP Provider, error: 0 -
> > An existing connection was forcibly closed by the remote host.).)
> >
> > If I do a manual fail-over and restart my application it connects to the
> > mirror database and everything is working. If I now do a manual fail-over I
> > get the same exception, but after a few retries it connects to the master
> > database.
> >
> > The field Connection.Datasource always shows the master database regardless
> > if it's connected to the mirror.
> >
> > What am I doing wrong? Is this how it's supposed to work?
> >

Yes, I got it to work on the final release of Sql Server 2005 SP1 and setting
connectionString.NetworkLibrary = "dbmssocn";
I also set connectionString.Pooling = false;

"Ian" wrote:

> Mattrix,
> Did you solve this problem. I am experiencing the same problem.
>
> Thanks,
> -Ian
>
> "Mattrix" wrote:
>
> > I have been pulling my hair over this problem and I just can't get it to work.
> >
> > I'm using SQL Server Authentication to access the database and I can connect
> > to the master when it's acting as principal and I can connect to the mirror
> > when it's acting as principal, but when doing a fail-over, manual or shutting
> > down the master server it never tries to connect automatically to the mirror
> > even if it's changed role to principal.
> >
> > Do the client need access to the witness in any way?
> >
> > I'm using version 2.0.50727 of System.Data.dll.
> >
> > "Mattrix" wrote:
> >
> > > I have set up mirroring and trying to get it to work from my .NET
> > > application. Mirroring seems to work ok.
> > > When I do a manual failover the witness is updated properly in the
> > > sys.database_mirroring_witnesses table so it seems to be configured
> > > correctly. I also can shutdown the master database and the mirror takes over.
> > >
> > > My application is configured with Server set to my master database and
> > > FailOverPartner to my mirror database.
> > >
> > > If I am connected to the master database and do a manual fail-over all my
> > > attempts to communicate with the database fail with an exception, even after
> > > repeated retries.
> > > (System.Data.SqlClient.SqlException: A transport-level error has occurred
> > > when sending the request to the server. (provider: TCP Provider, error: 0 -
> > > An existing connection was forcibly closed by the remote host.).)
> > >
> > > If I do a manual fail-over and restart my application it connects to the
> > > mirror database and everything is working. If I now do a manual fail-over I
> > > get the same exception, but after a few retries it connects to the master
> > > database.
> > >
> > > The field Connection.Datasource always shows the master database regardless
> > > if it's connected to the mirror.
> > >
> > > What am I doing wrong? Is this how it's supposed to work?
> > >