Hi All,
I have tested database mirroring for a few days. The application server
is using a connection string in the registry to point to SQL server.
When failover happens, how can I tell the app server to failover and
run against the mirrored server and mirrored database?
Please advice. Thanks.
-GaryYou must use SQL Native Client driver. This gives you the option to specify
the fail-over mirror server name. Then you must also use a witness server
for the fail-over to be automatic.
See books online;
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/a62d9dd7-3667-4751-a294-a61fc9caae7c.htm
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Gary Lou" wrote:
> Hi All,
> I have tested database mirroring for a few days. The application server
> is using a connection string in the registry to point to SQL server.
> When failover happens, how can I tell the app server to failover and
> run against the mirrored server and mirrored database?
> Please advice. Thanks.
> -Gary
>|||The other thing to keep in mind is that when the primary fails over the
connection closes with an error and the application server will have to
reopen the connection to get to the new primary. This may require some code
changes unless your application already handles this.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Dave Patrick" <DSPatrick@.nospam.gmail.com> wrote in message
news:382B1DEB-E7EB-4A6F-8AA8-C865A9215D05@.microsoft.com...
> You must use SQL Native Client driver. This gives you the option to
> specify the fail-over mirror server name. Then you must also use a witness
> server for the fail-over to be automatic.
> See books online;
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/a62d9dd7-3667-4751-a294-a61fc9caae7c.htm
>
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
> "Gary Lou" wrote:
>> Hi All,
>> I have tested database mirroring for a few days. The application server
>> is using a connection string in the registry to point to SQL server.
>> When failover happens, how can I tell the app server to failover and
>> run against the mirrored server and mirrored database?
>> Please advice. Thanks.
>> -Gary
>|||Thank you for both of your response.
My application server is running on Windows 2000 sp4. I have SQL Native
Client enable in the SQL 2005 server.
Question:
1) I got a "Login timeout expired" error when tried to set up the
connection under ODBC. Do I need to install sqlncli.msi in the app
server to make it work?
2) The connection string in the registry is as below. What needs to be
changed to make the failover to work?
Provider=SQLOLEDB.1;Password=p8ssw0rd;Persist Security Info=True;User
ID=DB_USER1;Initial Catalog=MagicDB;Data Source=MagicSQL2000;Network
Library=DBMSSOCN
Many thanks.
Roger Wolter[MSFT] wrote:
> The other thing to keep in mind is that when the primary fails over the
> connection closes with an error and the application server will have to
> reopen the connection to get to the new primary. This may require some code
> changes unless your application already handles this.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Dave Patrick" <DSPatrick@.nospam.gmail.com> wrote in message
> news:382B1DEB-E7EB-4A6F-8AA8-C865A9215D05@.microsoft.com...
> > You must use SQL Native Client driver. This gives you the option to
> > specify the fail-over mirror server name. Then you must also use a witness
> > server for the fail-over to be automatic.
> >
> > See books online;
> > ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/a62d9dd7-3667-4751-a294-a61fc9caae7c.htm
> >
> >
> > --
> >
> > Regards,
> >
> > Dave Patrick ...Please no email replies - reply in newsgroup.
> > Microsoft Certified Professional
> > Microsoft MVP [Windows]
> > http://www.microsoft.com/protect
> >
> > "Gary Lou" wrote:
> >> Hi All,
> >>
> >> I have tested database mirroring for a few days. The application server
> >> is using a connection string in the registry to point to SQL server.
> >> When failover happens, how can I tell the app server to failover and
> >> run against the mirrored server and mirrored database?
> >>
> >> Please advice. Thanks.
> >>
> >> -Gary
> >>
> >|||You need either SQLNCLI or the .Net 2.0 SQL Client to use the failover
feature so you will have to install the appropriate software on your clients
I'm not a connection string expert. Obviously you will need to change the
provider to SQLNCLI and specify the secondary server. There's a good
explanation here:
http://msdn2.microsoft.com/de-de/library/ms366348.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Gary Lou" <garylou@.gmail.com> wrote in message
news:1165301726.681691.33270@.l12g2000cwl.googlegroups.com...
> Thank you for both of your response.
> My application server is running on Windows 2000 sp4. I have SQL Native
> Client enable in the SQL 2005 server.
> Question:
> 1) I got a "Login timeout expired" error when tried to set up the
> connection under ODBC. Do I need to install sqlncli.msi in the app
> server to make it work?
> 2) The connection string in the registry is as below. What needs to be
> changed to make the failover to work?
> Provider=SQLOLEDB.1;Password=p8ssw0rd;Persist Security Info=True;User
> ID=DB_USER1;Initial Catalog=MagicDB;Data Source=MagicSQL2000;Network
> Library=DBMSSOCN
>
> Many thanks.
>
> Roger Wolter[MSFT] wrote:
>> The other thing to keep in mind is that when the primary fails over the
>> connection closes with an error and the application server will have to
>> reopen the connection to get to the new primary. This may require some
>> code
>> changes unless your application already handles this.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> Use of included script samples are subject to the terms specified at
>> http://www.microsoft.com/info/cpyright.htm
>> "Dave Patrick" <DSPatrick@.nospam.gmail.com> wrote in message
>> news:382B1DEB-E7EB-4A6F-8AA8-C865A9215D05@.microsoft.com...
>> > You must use SQL Native Client driver. This gives you the option to
>> > specify the fail-over mirror server name. Then you must also use a
>> > witness
>> > server for the fail-over to be automatic.
>> >
>> > See books online;
>> > ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/a62d9dd7-3667-4751-a294-a61fc9caae7c.htm
>> >
>> >
>> > --
>> >
>> > Regards,
>> >
>> > Dave Patrick ...Please no email replies - reply in newsgroup.
>> > Microsoft Certified Professional
>> > Microsoft MVP [Windows]
>> > http://www.microsoft.com/protect
>> >
>> > "Gary Lou" wrote:
>> >> Hi All,
>> >>
>> >> I have tested database mirroring for a few days. The application
>> >> server
>> >> is using a connection string in the registry to point to SQL server.
>> >> When failover happens, how can I tell the app server to failover and
>> >> run against the mirrored server and mirrored database?
>> >>
>> >> Please advice. Thanks.
>> >>
>> >> -Gary
>> >>
>> >
>|||Thank you Roger and Dave. It's working all together.
Roger Wolter[MSFT] wrote:
> You need either SQLNCLI or the .Net 2.0 SQL Client to use the failover
> feature so you will have to install the appropriate software on your clients
> I'm not a connection string expert. Obviously you will need to change the
> provider to SQLNCLI and specify the secondary server. There's a good
> explanation here:
> http://msdn2.microsoft.com/de-de/library/ms366348.aspx
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Gary Lou" <garylou@.gmail.com> wrote in message
> news:1165301726.681691.33270@.l12g2000cwl.googlegroups.com...
> > Thank you for both of your response.
> >
> > My application server is running on Windows 2000 sp4. I have SQL Native
> > Client enable in the SQL 2005 server.
> >
> > Question:
> > 1) I got a "Login timeout expired" error when tried to set up the
> > connection under ODBC. Do I need to install sqlncli.msi in the app
> > server to make it work?
> >
> > 2) The connection string in the registry is as below. What needs to be
> > changed to make the failover to work?
> > Provider=SQLOLEDB.1;Password=p8ssw0rd;Persist Security Info=True;User
> > ID=DB_USER1;Initial Catalog=MagicDB;Data Source=MagicSQL2000;Network
> > Library=DBMSSOCN
> >
> >
> > Many thanks.
> >
> >
> >
> > Roger Wolter[MSFT] wrote:
> >> The other thing to keep in mind is that when the primary fails over the
> >> connection closes with an error and the application server will have to
> >> reopen the connection to get to the new primary. This may require some
> >> code
> >> changes unless your application already handles this.
> >>
> >> --
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >> Use of included script samples are subject to the terms specified at
> >> http://www.microsoft.com/info/cpyright.htm
> >>
> >> "Dave Patrick" <DSPatrick@.nospam.gmail.com> wrote in message
> >> news:382B1DEB-E7EB-4A6F-8AA8-C865A9215D05@.microsoft.com...
> >> > You must use SQL Native Client driver. This gives you the option to
> >> > specify the fail-over mirror server name. Then you must also use a
> >> > witness
> >> > server for the fail-over to be automatic.
> >> >
> >> > See books online;
> >> > ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/a62d9dd7-3667-4751-a294-a61fc9caae7c.htm
> >> >
> >> >
> >> > --
> >> >
> >> > Regards,
> >> >
> >> > Dave Patrick ...Please no email replies - reply in newsgroup.
> >> > Microsoft Certified Professional
> >> > Microsoft MVP [Windows]
> >> > http://www.microsoft.com/protect
> >> >
> >> > "Gary Lou" wrote:
> >> >> Hi All,
> >> >>
> >> >> I have tested database mirroring for a few days. The application
> >> >> server
> >> >> is using a connection string in the registry to point to SQL server.
> >> >> When failover happens, how can I tell the app server to failover and
> >> >> run against the mirrored server and mirrored database?
> >> >>
> >> >> Please advice. Thanks.
> >> >>
> >> >> -Gary
> >> >>
> >> >
> >
Showing posts with label mirroring. Show all posts
Showing posts with label mirroring. Show all posts
Thursday, March 8, 2012
Application Server to Point to Mirror Server
Hi All,
I have tested database mirroring for a few days. The application server
is using a connection string in the registry to point to SQL server.
When failover happens, how can I tell the app server to failover and
run against the mirrored server and mirrored database?
Please advice. Thanks.
-GaryYou must use SQL Native Client driver. This gives you the option to specify
the fail-over mirror server name. Then you must also use a witness server
for the fail-over to be automatic.
See books online;
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/a62d9dd7-3667-4751-a294-a61f
c9caae7c.htm
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Gary Lou" wrote:
> Hi All,
> I have tested database mirroring for a few days. The application server
> is using a connection string in the registry to point to SQL server.
> When failover happens, how can I tell the app server to failover and
> run against the mirrored server and mirrored database?
> Please advice. Thanks.
> -Gary
>|||The other thing to keep in mind is that when the primary fails over the
connection closes with an error and the application server will have to
reopen the connection to get to the new primary. This may require some code
changes unless your application already handles this.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Dave Patrick" <DSPatrick@.nospam.gmail.com> wrote in message
news:382B1DEB-E7EB-4A6F-8AA8-C865A9215D05@.microsoft.com...
> You must use SQL Native Client driver. This gives you the option to
> specify the fail-over mirror server name. Then you must also use a witness
> server for the fail-over to be automatic.
> See books online;
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/a62d9dd7-3667-4751-a294-a6
1fc9caae7c.htm
>
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
> "Gary Lou" wrote:
>|||Thank you for both of your response.
My application server is running on Windows 2000 sp4. I have SQL Native
Client enable in the SQL 2005 server.
Question:
1) I got a "Login timeout expired" error when tried to set up the
connection under ODBC. Do I need to install sqlncli.msi in the app
server to make it work?
2) The connection string in the registry is as below. What needs to be
changed to make the failover to work?
Provider=SQLOLEDB.1;Password=p8ssw0rd;Persist Security Info=True;User
ID=DB_USER1;Initial Catalog=MagicDB;Data Source=MagicSQL2000;Network
Library=DBMSSOCN
Many thanks.
Roger Wolter[MSFT] wrote:[vbcol=seagreen]
> The other thing to keep in mind is that when the primary fails over the
> connection closes with an error and the application server will have to
> reopen the connection to get to the new primary. This may require some co
de
> changes unless your application already handles this.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Dave Patrick" <DSPatrick@.nospam.gmail.com> wrote in message
> news:382B1DEB-E7EB-4A6F-8AA8-C865A9215D05@.microsoft.com...|||You need either SQLNCLI or the .Net 2.0 SQL Client to use the failover
feature so you will have to install the appropriate software on your clients
I'm not a connection string expert. Obviously you will need to change the
provider to SQLNCLI and specify the secondary server. There's a good
explanation here:
http://msdn2.microsoft.com/de-de/library/ms366348.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Gary Lou" <garylou@.gmail.com> wrote in message
news:1165301726.681691.33270@.l12g2000cwl.googlegroups.com...
> Thank you for both of your response.
> My application server is running on Windows 2000 sp4. I have SQL Native
> Client enable in the SQL 2005 server.
> Question:
> 1) I got a "Login timeout expired" error when tried to set up the
> connection under ODBC. Do I need to install sqlncli.msi in the app
> server to make it work?
> 2) The connection string in the registry is as below. What needs to be
> changed to make the failover to work?
> Provider=SQLOLEDB.1;Password=p8ssw0rd;Persist Security Info=True;User
> ID=DB_USER1;Initial Catalog=MagicDB;Data Source=MagicSQL2000;Network
> Library=DBMSSOCN
>
> Many thanks.
>
> Roger Wolter[MSFT] wrote:
>|||Thank you Roger and Dave. It's working all together.
Roger Wolter[MSFT] wrote:[vbcol=seagreen]
> You need either SQLNCLI or the .Net 2.0 SQL Client to use the failover
> feature so you will have to install the appropriate software on your clien
ts
> I'm not a connection string expert. Obviously you will need to change the
> provider to SQLNCLI and specify the secondary server. There's a good
> explanation here:
> http://msdn2.microsoft.com/de-de/library/ms366348.aspx
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Gary Lou" <garylou@.gmail.com> wrote in message
> news:1165301726.681691.33270@.l12g2000cwl.googlegroups.com...
I have tested database mirroring for a few days. The application server
is using a connection string in the registry to point to SQL server.
When failover happens, how can I tell the app server to failover and
run against the mirrored server and mirrored database?
Please advice. Thanks.
-GaryYou must use SQL Native Client driver. This gives you the option to specify
the fail-over mirror server name. Then you must also use a witness server
for the fail-over to be automatic.
See books online;
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/a62d9dd7-3667-4751-a294-a61f
c9caae7c.htm
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Gary Lou" wrote:
> Hi All,
> I have tested database mirroring for a few days. The application server
> is using a connection string in the registry to point to SQL server.
> When failover happens, how can I tell the app server to failover and
> run against the mirrored server and mirrored database?
> Please advice. Thanks.
> -Gary
>|||The other thing to keep in mind is that when the primary fails over the
connection closes with an error and the application server will have to
reopen the connection to get to the new primary. This may require some code
changes unless your application already handles this.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Dave Patrick" <DSPatrick@.nospam.gmail.com> wrote in message
news:382B1DEB-E7EB-4A6F-8AA8-C865A9215D05@.microsoft.com...
> You must use SQL Native Client driver. This gives you the option to
> specify the fail-over mirror server name. Then you must also use a witness
> server for the fail-over to be automatic.
> See books online;
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/a62d9dd7-3667-4751-a294-a6
1fc9caae7c.htm
>
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
> "Gary Lou" wrote:
>|||Thank you for both of your response.
My application server is running on Windows 2000 sp4. I have SQL Native
Client enable in the SQL 2005 server.
Question:
1) I got a "Login timeout expired" error when tried to set up the
connection under ODBC. Do I need to install sqlncli.msi in the app
server to make it work?
2) The connection string in the registry is as below. What needs to be
changed to make the failover to work?
Provider=SQLOLEDB.1;Password=p8ssw0rd;Persist Security Info=True;User
ID=DB_USER1;Initial Catalog=MagicDB;Data Source=MagicSQL2000;Network
Library=DBMSSOCN
Many thanks.
Roger Wolter[MSFT] wrote:[vbcol=seagreen]
> The other thing to keep in mind is that when the primary fails over the
> connection closes with an error and the application server will have to
> reopen the connection to get to the new primary. This may require some co
de
> changes unless your application already handles this.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Dave Patrick" <DSPatrick@.nospam.gmail.com> wrote in message
> news:382B1DEB-E7EB-4A6F-8AA8-C865A9215D05@.microsoft.com...|||You need either SQLNCLI or the .Net 2.0 SQL Client to use the failover
feature so you will have to install the appropriate software on your clients
I'm not a connection string expert. Obviously you will need to change the
provider to SQLNCLI and specify the secondary server. There's a good
explanation here:
http://msdn2.microsoft.com/de-de/library/ms366348.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Gary Lou" <garylou@.gmail.com> wrote in message
news:1165301726.681691.33270@.l12g2000cwl.googlegroups.com...
> Thank you for both of your response.
> My application server is running on Windows 2000 sp4. I have SQL Native
> Client enable in the SQL 2005 server.
> Question:
> 1) I got a "Login timeout expired" error when tried to set up the
> connection under ODBC. Do I need to install sqlncli.msi in the app
> server to make it work?
> 2) The connection string in the registry is as below. What needs to be
> changed to make the failover to work?
> Provider=SQLOLEDB.1;Password=p8ssw0rd;Persist Security Info=True;User
> ID=DB_USER1;Initial Catalog=MagicDB;Data Source=MagicSQL2000;Network
> Library=DBMSSOCN
>
> Many thanks.
>
> Roger Wolter[MSFT] wrote:
>|||Thank you Roger and Dave. It's working all together.
Roger Wolter[MSFT] wrote:[vbcol=seagreen]
> You need either SQLNCLI or the .Net 2.0 SQL Client to use the failover
> feature so you will have to install the appropriate software on your clien
ts
> I'm not a connection string expert. Obviously you will need to change the
> provider to SQLNCLI and specify the secondary server. There's a good
> explanation here:
> http://msdn2.microsoft.com/de-de/library/ms366348.aspx
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Gary Lou" <garylou@.gmail.com> wrote in message
> news:1165301726.681691.33270@.l12g2000cwl.googlegroups.com...
Friday, February 24, 2012
application failover error
We currently have a system that uses sql server 2005 mirroring. In testing the application, we fail over the database in the middle of a 2.0 .NET web application. The failover partner is specified in the connection string.
The next request to the web server results in an error page popping up with the message "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.)"
This message only occurs for the first request to the web server, all subsequent requests sucessfully access the new failed over database.
Thanks for any suggestions.
I'm having the same problem and haven't yet found anything. I know that if you leave the app alone for at 1.5 minutes and then try to execute it again it will connect just fine. Some timeout is occurring that is then allowing the app to connect and I'm trying to find a way to force that timeout after a failover condition.
Let me know if you have any luck. :-)
|||This confused me also. From what I can tell, this is bydesign. If you want the user to see no errors, the application
needs to trap the error and re-open the connection. The
documentation says
http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
Assume a client is successfully connected to the principal, and a database
mirroring failover (automatic, manual, or forced) occurs. The next time
the application attempts to use the connection, the ADO.NET or SQL Native
Client driver will detect that the connection to the old principal has failed,
and will automatically retry connecting to the new principal as specified in
the failover partner name.
This message talks more about it:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=752968&SiteId=1|||
Could I get some feedback from a Microsoft person if this is supposed to be a "feature" or a "bug" so I could go back to the customer with an answer?
Thanks.
|||Yes, this is expected behavior. Your application needs to handle these errors and retry the connection.
Regards,
Matt Hollingsworth
Sr. Program Manager
SQL Server High Availability
|||I'm not sure if that's true.
I'm catching the error using a TRY CATCH statement and once I try to connect again after catching the error I still am unable to connect. The only way I'll be able to connect is if I leave my app alone for about 60 seconds and then try to connect. At that point it will work just fine.
This is a sample of my code:
Dim constring As String = "Server=SQL01.eqa\Fantabet;Failover Partner=SQL02.eqa\Fantabet;" _
& "Database=test;" _
& "UID=FailTest2;PWD=test;Network=dbmssocn"
Using con As New SqlConnection(constring)
Using cmd As New SqlCommand
Try 'First TRY statement to catch errors when opening the connection
con.Open()
cmd.Connection = con
cmd.CommandText = "INSERT INTO SystemsTest (FirstName, LastName)VALUES ('SQLWriter','" & con.DataSource & "')"
Try 'Second Try statement only for executing the SQL Command
cmd.ExecuteNonQuery()
Catch ex As SqlException
lblresult.Text = "Error"
Catch ex As SqlException
con.Close()
lblresult.Text = lblresult.Text & "<BR>Con.open error<BR>Error code = " & ex.ErrorCode & "<BR>Error Description = " & ex.Message
End Try
lblprimary.Text = "Current Primary Database = " & con.DataSource
con.Close()
con.Dispose()
End Using
End Using
So what my code does is that it tries to open the connection and if everything goes smooth a SQL command is executed but if an error is encountered while opening the connection object then the sub procedure finishes and changes the text of a label to "Error". The SQLConnection object is closed manually using a con.close but also by design since I'm using a "Using" statement.
Yet when I try to execute the procedure afterwards it still cannot connect. I must wait about 60 seconds to the procedure will work again.
|||Matt,
Thanks for your response. My understanding based on Microsoft’s documentation, was that there were no modification required in the application code.
In the event of a fail-over, the sqlclient would try the partner database. In Explicit mode you can even provide the partner in the connection string.
Can you point me to MS documentation that supports your response?
Thank you
application failover error
We currently have a system that uses sql server 2005 mirroring. In testing the application, we fail over the database in the middle of a 2.0 .NET web application. The failover partner is specified in the connection string.
The next request to the web server results in an error page popping up with the message "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.)"
This message only occurs for the first request to the web server, all subsequent requests sucessfully access the new failed over database.
Thanks for any suggestions.
I'm having the same problem and haven't yet found anything. I know that if you leave the app alone for at 1.5 minutes and then try to execute it again it will connect just fine. Some timeout is occurring that is then allowing the app to connect and I'm trying to find a way to force that timeout after a failover condition.
Let me know if you have any luck. :-)
|||This confused me also. From what I can tell, this is bydesign. If you want the user to see no errors, the application
needs to trap the error and re-open the connection. The
documentation says
http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
Assume a client is successfully connected to the principal, and a database
mirroring failover (automatic, manual, or forced) occurs. The next time
the application attempts to use the connection, the ADO.NET or SQL Native
Client driver will detect that the connection to the old principal has failed,
and will automatically retry connecting to the new principal as specified in
the failover partner name.
This message talks more about it:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=752968&SiteId=1|||
Could I get some feedback from a Microsoft person if this is supposed to be a "feature" or a "bug" so I could go back to the customer with an answer?
Thanks.
|||Yes, this is expected behavior. Your application needs to handle these errors and retry the connection.
Regards,
Matt Hollingsworth
Sr. Program Manager
SQL Server High Availability
|||I'm not sure if that's true.
I'm catching the error using a TRY CATCH statement and once I try to connect again after catching the error I still am unable to connect. The only way I'll be able to connect is if I leave my app alone for about 60 seconds and then try to connect. At that point it will work just fine.
This is a sample of my code:
Dim constring As String = "Server=SQL01.eqa\Fantabet;Failover Partner=SQL02.eqa\Fantabet;" _
& "Database=test;" _
& "UID=FailTest2;PWD=test;Network=dbmssocn"
Using con As New SqlConnection(constring)
Using cmd As New SqlCommand
Try 'First TRY statement to catch errors when opening the connection
con.Open()
cmd.Connection = con
cmd.CommandText = "INSERT INTO SystemsTest (FirstName, LastName)VALUES ('SQLWriter','" & con.DataSource & "')"
Try 'Second Try statement only for executing the SQL Command
cmd.ExecuteNonQuery()
Catch ex As SqlException
lblresult.Text = "Error"
Catch ex As SqlException
con.Close()
lblresult.Text = lblresult.Text & "<BR>Con.open error<BR>Error code = " & ex.ErrorCode & "<BR>Error Description = " & ex.Message
End Try
lblprimary.Text = "Current Primary Database = " & con.DataSource
con.Close()
con.Dispose()
End Using
End Using
So what my code does is that it tries to open the connection and if everything goes smooth a SQL command is executed but if an error is encountered while opening the connection object then the sub procedure finishes and changes the text of a label to "Error". The SQLConnection object is closed manually using a con.close but also by design since I'm using a "Using" statement.
Yet when I try to execute the procedure afterwards it still cannot connect. I must wait about 60 seconds to the procedure will work again.
|||Matt,
Thanks for your response. My understanding based on Microsoft’s documentation, was that there were no modification required in the application code.
In the event of a fail-over, the sqlclient would try the partner database. In Explicit mode you can even provide the partner in the connection string.
Can you point me to MS documentation that supports your response?
Thank you
Subscribe to:
Posts (Atom)