SQL Server\.Net – Application getting hanged or not able to connect to database after setting database mirroring
Hope this New Year had brought good
time to all my blog readers. After a small break, I’m back with a very
interesting blog post, which I’ve prepared on an interesting problem I faced
recently.
If you’re planning to use
database mirroring as a solution to create secondary backup\failover site for
your database, which is serving as backend database to your .Net Application
hosted on the IIS Server then this is one of the things you need to consider
apart from other pre-requisites.
Scenario
We had two databases running on
different versions of SQL Server. One hosted on SQL Server 2008 R2 SP3 instance
and another database was on SQL Server 2012 SP1 instance (both were stand-alone). We had
two different applications connecting to these databases respectively, one application
hosted locally and other one on remote IIS server.
Problem
The applications were running
fine and without any problems in production and were able to access and work
with test databases created on the remote site as well. But as soon as we
implemented database mirroring the application looked like it got hanged. The
application was getting stuck on the primary page itself (which also use to
create connection with database) and was not able to render at all and keep on
showing the loading icon. Even after attaching the code with debugger, the code
was getting hanged and was not returning any error.
Solution
The one thing which was common
between both the codes and was the root cause of the problem as well here was a
parameter in connection string i.e. “Connection Timeout=0”. Just changing the
connection timeout to "60" made the code to work as usual without any hiccup.
As I said at the start of the
post, this indeed was a very interesting finding for us. Though I was not able
to find a precise reason for this occurrence but what I suspect is that somehow
the application might be also going to Partner Database Server as well to
validate Mirroring setting and because timeout was set to “0”, the application
was getting into an endless retry loop. Now this is just an assumption of mine
as I said, I was not able to find anything concrete but it was able to resolve
the issue and I thought it was interesting enough to share with everyone.
Hope you may also find the post
and solution as interesting as I do. If yes, then please do not forget to share
the post and +1’d it as well. I welcome if someone also able to add clarity to
the reason behind this in the comments.
References (for assumptions)
Comments
Post a Comment