locked
SQL 2012 Mirroring - Worked once, then not again RRS feed

  • Question

  • 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

    Ideas/Thoughts?

    Friday, April 26, 2013 1:01 PM

Answers

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

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

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    • Marked as answer by Maggie Luo Sunday, May 5, 2013 2:16 PM
    Tuesday, April 30, 2013 5:28 AM
  • Uri,

    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.

    Janos


    There are 10 type of people. Those who understand binary and those who do not.

    My Blog | Hire Me

    • Marked as answer by Maggie Luo Sunday, May 5, 2013 2:15 PM
    Tuesday, April 30, 2013 5:54 AM

All replies

  • Hi,

    Do you use Failover Partner property in your connection string?

    Thanks,

    Janos


    There are 10 type of people. Those who understand binary and those who do not.

    My Blog | Hire Me

    Friday, April 26, 2013 1:25 PM
  • I'm not sure I understand what that means, can you be a little more specific or include an example?
    Friday, April 26, 2013 1:41 PM
  • 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.

    janos


    There are 10 type of people. Those who understand binary and those who do not.

    My Blog | Hire Me

    Friday, April 26, 2013 2:13 PM
  • 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.

    Friday, April 26, 2013 2:16 PM
  • 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?


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

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Saturday, April 27, 2013 8:42 AM
  • I was under the impression that AlwaysOn was an Enterprise feature only. Can you confirm?

    Where can I "open a case with Microsoft"?

    Monday, April 29, 2013 12:35 PM
  • Hi,

    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.

    Janos

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


    There are 10 type of people. Those who understand binary and those who do not.

    My Blog | Hire Me

    Monday, April 29, 2013 7:36 PM
  • There are AlwaysOn (STD+EE) --instance level fail over and AlwaysON Availability group (aka Mirroring) features (only EE)

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

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    • Marked as answer by Maggie Luo Sunday, May 5, 2013 2:16 PM
    Tuesday, April 30, 2013 5:28 AM
  • >>>ps.: I have mirroring and AlwaysOn AG working together on the same instance w/o any problem ;-)

    Interesting.. What point to have such configuration?


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

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Tuesday, April 30, 2013 5:29 AM
  • Uri,

    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.

    Janos


    There are 10 type of people. Those who understand binary and those who do not.

    My Blog | Hire Me

    • Marked as answer by Maggie Luo Sunday, May 5, 2013 2:15 PM
    Tuesday, April 30, 2013 5:54 AM