locked
Principal slow if mirror unavailable? RRS feed

  • Question

  • I've set up a pair of MSSQL 2008/Windows 2008R2 servers running database mirroring in synchronous mode for experimentation with database mirroring. There is currently no witness. I've configured a ColdFusion application to use the AlternateServer parameter in its connection string so that app is aware there are two database servers it can attempt to connect to.

    I can successfully execute a manual failover of the principal role to the mirror database, and the application appears to work great through that failover event and beyond.

    The problems start when I shut down the mirror database instance. This event introduces what appears to be ~90 second delays in the responsiveness of my ColdFusion application. The data submitted eventually goes through to the principal after that delay. Once I start the mirror database instance back up, the user's experience returns to normal...no delays in the application feeding off this mirrored database pair.

    I want to eliminate this delay if the mirror becomes unavailable. My suspicion of what may be happening here is, because the mirror database instance is down and the mirrored pair is running in synchronous mode, this delay is being caused because the principal is trying to send/commit its database transaction/changes to the mirror. When it fails to send those changes after a period of time (perhaps ~90 seconds), the principal goes ahead and commits the changes.

    Has anyone encountered this behavior or are there any thoughts about what is going on here? Thanks in advance.


    • Edited by gatsby23 Friday, November 30, 2012 12:41 AM
    Friday, November 30, 2012 12:37 AM

Answers

  • Hello,

    After step #4, would it be possible to restart the IIS app pool for that application and see if that clears things up? I'm wondering if it is hanging on to the failover partner address, attempting that first which is being redirected back to the principal. This is cached on the network stack so I'm thinking if this is it (which it may or may not be at all) a simple test should flush it out.

    I'm with you on the library question. I don't know what library coldfusion uses to connect so I'm unsure of the parameters it runs in when using it.

    A witness should have no effect (though you could try it and check for certain) on this. The witness provides quorum for automatic failover, that's it. In this case the failover is manual, so if anything this should make it even more clear what is happening than with a witness (if a witness was used a manual failover would still be needed as the event is forced).

    -Sean


    Sean Gallardy | Blog | Twitter

    • Marked as answer by Maggie Luo Thursday, December 6, 2012 10:28 AM
    Friday, November 30, 2012 1:18 PM
    Answerer

All replies

  • Hello,

    If I am reading this correctly, you've done the following steps in order after mirroring was setup?

    1. Fail the principal server manually. The databases now serves from the mirror. The application is still running when this happens (running in IIS?).

    2. The application successfully connects to the mirror instance.

    3. The mirror instance is failed back to the principal? The application is still running.

    4. A long delay in connection? data retrieval?

    My first thought is that implicit client redirection is possibly to blame for this. If this is not the scenario you're describing please let us know (I'm not familiar with coldfusion) what is running your coldfusion application and what the exact steps taken were.

    -Sean


    Sean Gallardy | Blog | Twitter

    Friday, November 30, 2012 12:54 AM
    Answerer
  • Sean -

    Thanks for replying. Your description of steps 1 through 3 are correct. What it looks like is this:

    1. Fail the principal server manually successfully to the partner. The databases now serves from the mirror. The application is still running when this happens (IIS 7.0 is the webserver...the application is coded in ColdFusion).

    2. The application successfully connects to the mirror instance.

    3. The mirror instance is failed back to the principal. This succeeds; the application is still running.

    4. With this functionality established and working as desired, I attempt to manually shut down the MSSQL instance hosting the mirrored database. This is done to simulate some scenarios for testing purposes. When that instance hosting the mirrored database is shut down, the application begins exhibiting delays.

    FWIW, I have wondered whether this delay is rooted in ColdFusion or in MSSQL. I'm trying to stand up a .NET application to see if I can recreate this delay behavior with separate application/language and remove ColdFusion from the equation. I'm wondering if the presence of a witness would alleviate this at all...

    Friday, November 30, 2012 1:37 AM
  • Hello,

    After step #4, would it be possible to restart the IIS app pool for that application and see if that clears things up? I'm wondering if it is hanging on to the failover partner address, attempting that first which is being redirected back to the principal. This is cached on the network stack so I'm thinking if this is it (which it may or may not be at all) a simple test should flush it out.

    I'm with you on the library question. I don't know what library coldfusion uses to connect so I'm unsure of the parameters it runs in when using it.

    A witness should have no effect (though you could try it and check for certain) on this. The witness provides quorum for automatic failover, that's it. In this case the failover is manual, so if anything this should make it even more clear what is happening than with a witness (if a witness was used a manual failover would still be needed as the event is forced).

    -Sean


    Sean Gallardy | Blog | Twitter

    • Marked as answer by Maggie Luo Thursday, December 6, 2012 10:28 AM
    Friday, November 30, 2012 1:18 PM
    Answerer