April 29, 2011 at 5:53 pm
I hope this is the correct place to post this, though it's a combined VS and SQL question...
I have a database set up with (high security mode - no witness) mirroring. I have a web-based application (actually, web-services; SOA-based) written in VS2010 using the Enterprise Library database tools.
I used the "server=AServer;Failover Partner=BServer" syntax in my connection string.
What I find, at least from my development machine, is that when I fail the database from the AServer to the BServer, it takes about 2 minutes before the connection actually works. When I fail the database from the BServer to the AServer, it works nearly immediately.
It DOES eventually work, and once BServer get's "activated" it works very well - quickly, and without issue, so general routing doesn't seem to be the problem - ports are open.
Any thoughts as to why the "primary-to-partner" direction would be so slow?
Thanks,
Jim
April 30, 2011 at 9:36 am
First, I now noticed the "High Availability" forum...how can I get this thread moved over there?
Next...doing some more research...
Had a thought that this was perhaps being caused by connection pooling. First attempt was to catch the exception and issue a System.Data.SqlClient.SqlConnection.ClearAllPools() call when I catch it.
No better.
Second thought was to set "Pooling=false" in my connection string.
Still no better.
Using Wireshark in combination with my testing environment, I see the following:
- Get my web app running - observe good traffic to AServer.
- Issue the manual database failover command (Fail from AServer to BServer)
-- Observe TCP socket(s) close IMMEDIATELY (while no web requests are being made)
- Issue a new web request
-- Observe NO TCP TRAFFIC trapped, but exception thrown in code.
- Issue another new web request.
-- Observe TCP TRAFFIC between my machine and the AServer. NO attempts seen to the BServer.
- Issue multiple web requests over the next few minutes.
-- Eventually, a request will result in traffic FIRST to AServer, then BServer - successful call.
-- Subsequently, requests ONLY go to BServer
- Issue the manual database failover command (Fail from BServer to AServer)
-- Observe TCP socket(s) close IMMEDIATELY (while no web requests are being made)
- Issue a new web request
-- Observe NO TCP TRAFFIC trapped, but exception thrown in code.
- Issue another new web request.
-- Observe IMMEDIATE TCP connection to AServer - successful call.
Thoughts?
April 30, 2011 at 10:16 am
Hi Jim,
There are 2 points to notice here
1. The time it takes to failover the Db from A to B
2. The time it takes for the application to get redirected from A to B
At which point are you observing the delay ?
Is the failover happening immediately but the application redirection is taking more time ?
Or the failover itself is taking more time from A to B ?
Thank You,
Best Regards,
SQLBuddy
April 30, 2011 at 11:23 am
Good question.
I am using the manual mirror failover command (button) from the SSMS user interface.
The partner database is available immediately (or, at least, as quickly as I can do something to test it). It is recognized as PRINCIPAL in the interface immediately.
It appears to be my app that is not sensing the failover quickly.
Jim
April 30, 2011 at 11:57 am
Is the "application Redirection to B getting Slowed Down" is happening always or you observed it just once or twice ?
Thank You,
Best Regards,
SQLBuddy
April 30, 2011 at 12:01 pm
It is entirely consistent, at least from my development machine. I have not yet tested it from our "production" web server environment (but the SQLs are the production servers).
Jim
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply