SQL 2012 Mirroring - Worked once, then not again

Category: sql server mirroring


Greg Witek on Fri, 26 Apr 2013 13:01:58

I setup SQL 2012 Standard on 2 different servers and installed SQL 2012 Express on a Witness server. All 3 servers are running Server Enterprise 2008 R2.

I'm using these instructions: http://blogs.msdn.com/b/suhde/archive/2009/07/13/step-by-step-guide-to-configure-database-mirroring-between-sql-server-instances-in-a-workgroup.aspx

Everything connected perfectly. I disabled the NIC in the principal server, verified the mirror server had become the principal and was able to access the application! Success!

That was the only time it worked. Now, when I disable the Principal server I get a timeout for our .net application.

-In SQL Management studio it shows that the switch was made from Mirror to Principal so that is working correctly.

-As soon as I re-enable the NIC on the Principal server the application comes up fine.

-We have extended the timeout per the knowledge base article - but that doesn't help.http://support.microsoft.com/kb/2605597

-I even rebuilt the Witness Server from scratch. Once again, the fail over worked once, but then stopped working.

-All Windows Firewalls are turned off

-All ports are completely open on the firewall between the Witness, Application Server, and SQL Servers



Janos Berke on Fri, 26 Apr 2013 13:25:31


Do you use Failover Partner property in your connection string?



Greg Witek on Fri, 26 Apr 2013 13:41:45

I'm not sure I understand what that means, can you be a little more specific or include an example?

Janos Berke on Fri, 26 Apr 2013 14:13:08

Let me try to clarify how transparent client redirect works:

scenario 1: server1 is principal, server2 mirror. both are online. When an application connects to the server1 and using the ADO.NET or SNAC driver then the mirror server - server2 - will be cached in the connection. Server 1 fails and mirroring session fails over to server2. Application will be able to reconnect to server2 because failover partner has already cached. 

scenario 2: server1 is principal, server2 mirror. server 1 is offline, connection string does not have the Failover Partner property set. Application tries to connect to server1, it will fail and will not fail over to server2. 

scenario 3: server1 is principal, server2 mirror. Both are online or server1 is offilne and connection string have the Failover Partner property set to Server2. in this case application can connect to server2 even if server1 is offline at the time of the first connection.

You can find database mirroring related connection string samples at here: http://www.connectionstrings.com/sql-server-2008

I hope it helps.


Greg Witek on Fri, 26 Apr 2013 14:16:53

Now I understand. Yes - we are using the failover property.

Another test - if I go into SQL Management Studio I can click on the button "failover" to manually fail over, it works perfectly every time.

Uri Dimant on Sat, 27 Apr 2013 08:42:16

I have not tried the mirroring in SS2012 as they state that is depricated feature. Have you tried AlwaysOn Availability Groups instead...

But your issue looks like a bug , can you open the case with Microsoft?

Greg Witek on Mon, 29 Apr 2013 12:35:44

I was under the impression that AlwaysOn was an Enterprise feature only. Can you confirm?

Where can I "open a case with Microsoft"?

Janos Berke on Mon, 29 Apr 2013 19:36:05


AlwaysOn Availability Group is an enterprise feature hence it is available in Enterprise edition only. You may contact to Microsoft through your premier support or at https://support.microsoft.com/select/default.aspx?target=assistance

I hope it helps.


ps.: I have mirroring and AlwaysOn AG working together on the same instance w/o any problem ;-)

Uri Dimant on Tue, 30 Apr 2013 05:28:30

There are AlwaysOn (STD+EE) --instance level fail over and AlwaysON Availability group (aka Mirroring) features (only EE)

Uri Dimant on Tue, 30 Apr 2013 05:29:17

>>>ps.: I have mirroring and AlwaysOn AG working together on the same instance w/o any problem ;-)

Interesting.. What point to have such configuration?

Janos Berke on Tue, 30 Apr 2013 05:54:10


That instance has been upgraded from SQL 2008 R2 where we were using mirroring. A 3rd party company were not allowing us to upgrade mirroring to AG because of some reasons. New implementations are using AG's instead of mirroring, so this is the reason of the mixed configuration.