locked
Trouble mirroring more than 5 databases - both machines Principal Disconnected state RRS feed

  • Question

  •  

    I understand it is possible to mirror many many databases. If we have more than 5 ( 7 actually) databases mirrored, in the event of an automatic failover, not all will failover.

     

    Some of the databases end up in a state of "Principal Disconnected" on BOTH the original Primary and the now operational Mirror.

     

    On the few occasions this has happened, it's not always the same databases resulting in this state.

    To fix, the mirror then needs to be broken, logins deleted to prevent logon and database locks, restore the database, recreate logins and remirror. This is a major problem.

     

    Config:

    Dell PER200

    4GB RAM

    Windows 2003 Standard SP2 fully patched.

    SQL 2005 Standard SP2 latest cumulatives and patches applied.

    Wednesday, August 20, 2008 7:26 AM

All replies

  • Hittin.

     

    Its not a problem, there is no problem with 5-7 database for automatic failover.I have configured more then that they all failover without any issue.

     

    I guess whats your scenerio is ----

    When failover occurs Principal database is not fully syncronised with mrror right it shows disconnected or syncronising.

    It means there are unsent or redo log which not has been restored to mirror.

    It happens when there is Bulk oprations happens on the databases and network bandwidth is not upto the mark to transfer all transactions to mirrored. So it shows Disconnected.

     

    I would suggest you to check is the monitor logs;

     

    Principal and mirrored both.

     

    KIndly check the status of Unsent and restore log and rest of the information,

     

    BR

    Praveen barath

     

    Wednesday, August 20, 2008 8:11 AM
    Answerer
  • TO check the details -

     

    Right click > Task>Launch Database mirroring monitor>Select registered database (If not register then register it)>Check the Status tab > then next column called "Principal Log"&'Mirrored Log'. there check Unsent log /Unrestored Log and current send rate which will help to understand the Bottleneck.

     

    Praveen Barath

    Wednesday, August 20, 2008 8:12 AM
    Answerer
  • DO you still having issues.

     

    BR
    Praveen Barath

    Friday, August 22, 2008 9:05 AM
    Answerer
  • Waiting on the SQL guy for some feedback.

    Friday, August 22, 2008 11:36 AM
  • Okay,

     

    If possible keep me posted, let me know if any issue occurs.

     

    BR
    Praveen Barath

     

    Friday, August 22, 2008 12:38 PM
    Answerer
  • The sent rate is so small it's almost immeasurable. There's gigabit links between these machines and network utilisation is not an issue at all.

    Monday, September 1, 2008 1:33 AM
  • I had the same issue, i simply removed mirroring and then reconfigured that solved my problem.

     

    I hope it an be helpful to you as well.

     

    BR
    Praveen Barath

    Monday, September 1, 2008 8:17 AM
    Answerer
  •  

    From my experience, I think it is hard to tell what is causing the problems from reading the error messages when dealing with database mirroring.

    In most cases it is related to authentication. From your description, I don’t believe that is the case here. Which authentication mode are you running on your endpoints?  If its possible, you can try to set authentication to windows negotiate if you are running on a windows account. I have had problems with Kerberos authentication and that’s why I suggest that test.

     

    Do you run on a 32 or 64bit platform? Mirroring sessions take there fare share of worker threads and it is possible to run in to thread starvation and/or memory pressure when you are running many sessions.

    Check the dmv sys.dm_os_workers to see how many worker threads are allocated to mirroring.   

     

    Thursday, September 4, 2008 1:50 PM
  • Hi Jonas,

     

    - Authentication mode is Windows Negotiate

    - 32 bit Windows 2003 Standard SP2/ SQL 2005 SP2 + cumulatives and latest SQL Native client

    - 1 thread per database

     

    thanks

     

     

    Friday, December 5, 2008 1:51 AM
  • These type of issues can be hard to diagnose retrospectively. However i would agree with the posters above who say that this is not related to the number of DBs you are mirroring, nor in my experience would it be related to the endpoint configuration (assuming the partners are normally working ok).

     

    First off in these type of situations I would always check the default trace profiler output to see what each partner actually thought in the lead up to this scenario, which they will all log through the mirroring state change event type. This can help to understand the sequence of events which happen in terms of thought process such as

     

    I see the principal lost connection to the mirror and witness and says it is running exposed.
    I wonder why the server lost connection
    Was it a network problem
    Was one of the partners so busy that it was unable to communicate effectively
    and so forth

     

    In addition to this I would then check the SQL Server error logs for errors which are being logged in the same timeframe leading up to the failure event, and i would also be interested to note if you occasionally see any errors at other times, indicating transitory problems with the partners and their communications and quorum. The comments about log send rate and the mirroring monitor are also valid. If you want to you can also access similar counters via perfmon.

     

    In my experience it is important to understand the exact nature of the what caused the failover event, before trying to work out what caused the inappropriate behavior of the mirroring partnership.

     

    This information may help you to diagnose or understand the problem, but the bottom line here is that such scenarios are often hard to prove definitively once they have already occurred. The only way we can often diagnose them for certain is to reproduce them with proactive monitor running on all the partners (we'd normally use PSSDIAG or SQLDIAG (including perfmon, profiler and some extra mirroring DMV scripts added in)

     

    I would also recommend a review of some of the scenarios in these white papers, as they help to conceptualize the failover and role change events.

    http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx
    http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx

     

     

     

    Friday, December 5, 2008 11:11 AM