replication

  • We had setup replication between production and two reporting database servers.

    And PPTR between production and two webservers.

    last night we had a outage and servers got restarted.

    So replication is catching up.

    So now..

    Reporting server 1 appears ok but significantly behind on reporting server 2. I need to understand how I can get server 2 catch up.

    Reporting server 1 – undistributed transactions 6164 – est time 20 min

    Reporting server 2 - undistributed transactions 4442941 – est time 5 hr 34 min

    web server 2 - undistributed transactions 5098501 – est time 3 hr 34 min

    web server 2 - undistributed transactions 5103928 – est time 1 hr 40 min

    web servers: even though undistributed transactions are almost same number, why the est time is different to sync?

    Please let me know.

  • So now..

    Reporting server 1 appears ok but significantly behind on reporting server 2. I need to understand how I can get server 2 catch up.

    Reporting server 1 – undistributed transactions 6164 – est time 20 min

    Reporting server 2 - undistributed transactions 4442941 – est time 5 hr 34 min

    web server 2 - undistributed transactions 5098501 – est time 3 hr 34 min

    web server 2 - undistributed transactions 5103928 – est time 1 hr 40 min

    web servers: even though undistributed transactions are almost same number, why the est time is different to sync?

    Performance. How is your replication architecture set up? Is your publisher and distributor on the same machine? Separate servers? There's quite a bit of overheard involved on the distribution server and it's very normal for things to fall behind. Since you experience a power outage it's more than likely (especially so in an OLTP environment) there are literally millions of rplcommands that need to be pushed to the subscribers.

    If you really want to gauge the performance of which one(s) is causing the issue(s) stop/disable the Log Reader Agent for a couple of the publications and see if that helps speed up the other times. Don't forget to start it up again...

    IMHO I don't believe there's much you can do about it but wait it out...

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • Thank you.

    Publisher and distributor are separate servers.

    If you really want to gauge the performance of which one(s) is causing the issue(s) stop/disable the Log Reader Agent for a couple of the publications and see if that helps speed up the other times. Don't forget to start it up again...

    CAN YOU PLEASE PROVIDE MORE DETAILS..you mean uncheck articles in publisher publications? and see

    how to stop/disable log reader agent for only couple of publications? please advise.

  • Do not drop and re-add the articles or you will have to go through it all over again! In the replication monitor (or in the SQL agent monitor) navigate to your agents, select the log reader one and stop it. What version of MSSQL are you using?

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • going back to my first question

    Reporting server 2 - undistributed transactions 9442941 – est time 10 hr 34 min

    It clreated the phontom backlog so quickly...in 5 hrs..so how did it cleared so fast?

    One of our DBA has validated the subscriptions...is this the reason?

    Please let me know.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply