SQL Server Performance

Replication Doesnt work after Manual Failover

Discussion in 'SQL Server 2005 Replication' started by ramkumar.mu, Dec 27, 2010.

  1. ramkumar.mu New Member

    I have the following replication setup in my PROD Servers.
    ServerA -> ServerB -> ServerC
    ServerA is the Publisher
    ServerB is the Distributor. Push Subscription is used.
    ServerC is the subscriber
    Also, there is a Asynchronous Mirroring Setup between Server A & ServerD for the same database without a witness.
    I have also given the -PublisherFailoverPartner parameter to define ServerA and ServerD as publishers for ServerB
    Now when i manually stop the SQL services on ServerA, and force a service on ServerD, and if i update few rows on Published table on ServerD, the rows did not reflect on the Subscriber - ServerC.
    It reflects only when i bring the Principal up and Resume the mirroring session again.
    Why is this so? Please help...
    ramkumar.mu, Dec 27, 2010
    #2

  2. satya Moderator

    Is this related to your previous question on how to setup.
    Are you using HIGH PERFORMANCE mode in the mirroring?
    As per BOL:
    In high-performance mode, as soon as the principal server sends the log for a transaction to the mirror server, the principal server sends a confirmation to the client, without waiting for an acknowledgement from the mirror server. Transactions commit without waiting for the mirror server to write the log to disk. Asynchronous operation permits the principal server to run with minimum transaction latency.
    The mirror server attempts to keep up with the log records sent by the principal server. But the mirror database might lag somewhat behind the principal database, though typically the gap between the databases is small. However, the gap can become substantial if the principal server is under a heavy work load or the system of the mirror server is over loaded.
    satya, Dec 29, 2010
    #3

  3. ramkumar.mu New Member

    Happy New Year All [:)]
    Yes! I am testing Replication and Mirroring on my Test Environment.
    I tried all three mirroring modes - Performance, Availability & Protection. Still, i am facing the same problem
    ramkumar.mu, Jan 3, 2011
    #4

  4. satya Moderator

    Do you have witness involved here?
    Are you using automatic failover?
    satya, Jan 4, 2011
    #5

  5. ramkumar.mu New Member

    First i tried Asynchronous without witness, i got the error (mentioned above)
    Then i tried synch without witness, i got the same error
    AtLast, i tried automatic failover with witness, even then i am getting the same error
    ramkumar.mu, Jan 4, 2011
    #6

  6. satya Moderator

    What error you are referring?
    I don't seem to get any text from above.
    satya, Jan 4, 2011
    #7

  7. ramkumar.mu New Member

    I am sorry about that. There was no error i am getting.
    The records that are updated at the Mirror Server after failover from the principal does not reflect into the subscribers. There are no errors. The record does not show in replication monitor too.
    Only When i bring back the principal and resume mirroring, the record automatically gets inserted into the subscriber. Till then it subscribers are not updated with the changes in the Mirror Server
    ramkumar.mu, Jan 4, 2011
    #8

  8. satya Moderator

    OK, that clears the confusion.
    What is the schedule of subscription snapshot, on demand or immediate?
    Also do you see any warnings/error from SQLAgent lot or replication agents log?
    satya, Jan 5, 2011
    #9

  9. ramkumar.mu New Member

    Sorry for the late response.
    I found the solution for my problem. this is due to the default state that Log Reader Agent will be waiting if Mirroring is broken. This message was available in the Replication-Distributor History
    Log Reader Agent usually waits for log records to harden on the mirror before replicating them to the Distributor. When the server is started with trace flag 1448 enabled, this restriction is removed so the Log Reader Agent can continue replicating changes regardless of the mirroring state. This trace flag is available in 2008. In SQL 2005, below hot fix gives that trace flag.
    http://support.microsoft.com/kb/937041
    Thanks Satya for your support. The thought of checking the Replication-Distributor History came from your question [:)]
    ramkumar.mu, Jan 27, 2011
    #10

  10. satya Moderator

    Ram, much appreciated on your explanation on the solution.
    I always recommend and refer to the logs for any sort of information if any problem arises, best place to start and put piece to piece to solve [:)]. Glad to be a help...
    satya, Feb 7, 2011
    #11

Share This Page