Reconnecting to a Database Mirroring Session via ADO.NET

Question

Hello,

I have two (SRV_PRINCIPAL AND SRV_PARTNER) SQL Server 2008 Enterprise Edition (64-bit) Version 10.0.2531.0.  I configured database mirroring between SRV_PRINCIPAL and SRV_PARTNER. My test windows application uses .Net 4 executes 2 sql queries and I was trying to do the following steps and use this connection string:

<add connectionString="Data Source=SRV_PRINCIPAL;Failover Partner=SRV_PARTNER;Initial Catalog=TEST; User Id=user;Password=password;" providerName="System.Data.SqlClient" name="defaultDB"/>

1. Execute the fist query. - Success - (Principal server - SRV_PRINCIPAL).
2. Failover database. - Success - (Principal server - SRV_PARTNER )
3. Try to execute second query  - Failure. (Principal server - SRV_PARTNER). 

I got the following error:
{System.Data.SqlClient.SqlException (0x80131904): 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.)

 I aslo tried to call SqlConnection.ClearPool after step 2 but after that I got this error:
{"A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"}
  
If I stop my application and start over I can successfully execute my queries against SRV_PARTNER.

Any ideas how to make reconnection without restarting my application.

Thank you.

Answer 1

HI,

  It might be the mirrored Db is in Redo state, while its trying to take prinicpal role. Pls wait for few more sec and trying connecting. Pls tel us whether ur using High performance or high avabilaity mode.

Answer 2

Thanks for your reply. Unfortunately waiting time is not the issue. The database is tiny and failing over happens almost instantaneously. I tried to wait for more than a minute and it didn't help. I'm using High-Safety mode.

Answer 3

I would add on error handler within application for Connection string and on resume next (for VB6 :-)) try re-run connection to the secondary server. Meaning some logic of timer, you know

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 4

I can impement my own logic to connect to the mirrored servers but I thought it was already implemented in .NET Data Provider for SQL Server. Correct me if I'm wrong. Was anybody able to implement the scenario I mentioned above?

Answer 5

what was the initial catalog provided in connection string?

---

Balmukund Lakhani | Please mark solved if I've answered your question
--------------------------------------------------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------------------------------------------------------------------
My Blog: http://blogs.msdn.com/blakhani
Team Blog: http://blogs.msdn.com/sqlserverfaq

Answer 6

The name of the database is TEST. I hope it's not a problem.

Answer 7

Kazimir

Yes, if failover happened you must loose  your current connection ag get an error , but I think  you need to re-direct failed query or whatever to your initial connection string (within Business Logic model) to reconnect to the new principle

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 8

http://support.microsoft.com/kb/912151

FIX: The connection may time out before the first try to connect to the failover partner server when you try to connect to a SQL Server 2005 mirrored database by using an ADO.NET 2.0-based application

---

Balmukund Lakhani | Please mark solved if I've answered your question
--------------------------------------------------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------------------------------------------------------------------
My Blog: http://blogs.msdn.com/blakhani
Team Blog: http://blogs.msdn.com/sqlserverfaq

Answer 9

Balmukund,

Thank you for trying to help but the link to the hot fix applies to .NET Framework 2.0 and Microsoft SQL Server 2005. I use .Net Framework 4 and SQL Server 2008.

Uri,

I understand that after failing over I'll get an error. I don't understand why it doesn't work after clearing connection pools and why it works automatically after restarting my application without changing connection string.

Answer 10

Hi,

This issue may happen if you are using SQL Server Authentication, and the cause is that the SIDs for each SQL Server login in each instance do not match. To resolve this issue, we have two options:

1.        Use Windows domain account (Windows Authentication Mode) since the SID of SQL login for a domain account is based on domain SID.

2.        Create SQL Server login in the principal server, and retrieve the SID of this login, and then create the other login on the partner server with the same login name and specify the SID (the same SID of the login in the principal server. You could use CREATE LOGIN <LOGIN_NAME> WITH PASSWORD = <PASSWORD>, SID = <SID>. To find the SID of a specify login, please refer to http://msdn.microsoft.com/en-us/library/ms179889.aspx.

If you have any more questions, please feel free to let me know.

Thanks,
Chunsong

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 11

Chunsong,

SQL Server logins SIDs are the same. I would get Login failed and had an orphaned user on SRV_PARTNER if they were different. I was able to connect to SRV_PARTNER but I had to restart my application. I could not connect to SRV_PARTNER immediately after failover happened.

Answer 12

Chunsong,

SQL Server logins SIDs are the same. I would get Login failed and had an orphaned user on SRV_PARTNER if they were different. I was able to connect to SRV_PARTNER but I had to restart my application. I could not connect to SRV_PARTNER immediately after failover happened.

Hi Kazimir,

Could you please be more specify on “I would get Login failed and had an orphaned user on SRV_PARTNER if they were different”? What do you mean by "they"? You may need to use sp_change_users_login to resolve orphaned user issues.

More info about sp_change_users_login: http://msdn.microsoft.com/en-us/library/ms174378.aspx.

Thanks,
Chunsong

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 13

If they (SIDs) were different ... In my case SIDs are the same on both servers.

Answer 14

Hi I think I have the same problem. Did you ever find a solution?

 

Answer 15

Unfortunately not.

Answer 16

Hi!

I have seen this problem before, am not sure though how to really solve it... (After all I am not a developer...) The problem is that the connection gets closed but the SQLConnection object doesn't react on that. What you would have to do is either create a new connection, which obviously works as you pointed out, after all restarting the app does the trick... The alternative is reacting on the StateChanged event in the connection object, which catches the disconnect and gives you the chance to immediately rebuild it...

Hope this helps.

Rick

Answer 17

Although this thread is old, I came across the same issue and had trouble finding a solution as well.

In summary, the connection must be closed first, then the pool cleared.  So the initial test case would look like:

1.  Execute the fist query
1.1 Close SqlConnection via .Close() - Success - (Principal server - SRV_PRINCIPAL).
1.2 Clear the connection pool via the static SqlConection.ClearPool(sqlconnectionObject)
2. Failover database. - Success - (Principal server - SRV_PARTNER )
3. call Open() on the SqlConnection
3.1 Try to execute second query  - Should now be success. (Principal server - SRV_PARTNER).

For further info, have a look at:

http://blogs.msdn.com/b/spike/archive/2010/12/08/clarification-on-the-failover-partner-in-the-connectionstring-in-database-mirror-setup.aspx

Answer 18

This error is because of the connections in the pool which were created prior to the failover.

So you have the following options to avoid this error:

1. Disable pooling in your application by setting pooling=false in the connection string.

2. Restart your application after the failover has occured, which will hlep to clear all the "bad connections" in the pool.

or you can simple make a minor change to the web.config file then save it, which will force the application to be restarted.

3. At step 2, call SqlConnection.ClearAllPool() instead of ClearPool() method.

Hope this help.

If it works for you then please vote for it, :-).