none
Question on appraoch for temporarily stopping Subscriber

    Question

  • Fellow DBA's

    I have a need to deploy some indexes ONLY on the subscriber and have a simple setup for transactional replication. Server A has the publisher and distributor. Server B is the subscriber.

    I would like to not process anything on the subscriber while I deploy these indexes. I have read the MS documents on ways to do this but I do have a few questions.

    1 - If I turned off the log reader on Server A, and transaction log backups are going on during this time, I would possibly loose some tranctions in the log file that the distributor would not pickup. Correct?

    2 - If the above is true, my plan would be to let Server A continue is capturing for a few minutes in the distributor. So, I am leaving Server A configuration alone !!!!!. But my plan would be to go to Server B by going to Replications | Local Subscriptions and open up each subscription, right click on View Subscription Status and select STOP.  After my release is done, I would turn it back on. Is this the most logical path?

    Thanks

    MG

    Tuesday, June 12, 2018 5:48 PM

Answers

  • 1) That is correct. The logs will not be "truncated" until the log reader agent starts up again and it reads the transactions marked for replication in the log and written them to the distribution database. Once the log reader agent runs it may be possible to backup the transaction log and then free up space in the log for new transactions.

    2) Yes, you are correct. Disable the job, otherwise it may restart on the next schedule.

    • Proposed as answer by Mohsin_A_Khan Saturday, June 16, 2018 11:07 PM
    • Marked as answer by mg101 Sunday, June 17, 2018 1:36 AM
    Friday, June 15, 2018 6:43 PM
    Moderator

All replies

  • If you really mean the subscriber, you can disable the login that replication uses to connect to the Subscriber.

    You can put the subscription database into single_user mode and run the index script.

    You can stop the distribution agent and then disable the job that the distribution agent uses.

    To answer your questions.

    1) No, but your log might grow significantly depending on the number of changes occurring in the publication database. When you restart the log reader agent it will pick up where it left off.

    2) This is an option if you are using pull subscriptions. But your agent might start up again - if it is running on a schedule.

    Tuesday, June 12, 2018 5:53 PM
    Moderator
  • Hi Hillary,

    thanks for the info.

    As for your reply to "1", you are telling me that if I turn off the log reader on the publisher and transaction log backups are running "which truncate the log after backup" that I will NOT loose any data after I turn it back on?

    As for your reply to "2" We are using pull subscriptions. I would think it would be much simplier and direct to just stop the pull subscrition process on the Subscriber. It is running continuously.

    Thanks,

    mg

    Tuesday, June 12, 2018 6:33 PM
  • 1) That is correct. The logs will not be "truncated" until the log reader agent starts up again and it reads the transactions marked for replication in the log and written them to the distribution database. Once the log reader agent runs it may be possible to backup the transaction log and then free up space in the log for new transactions.

    2) Yes, you are correct. Disable the job, otherwise it may restart on the next schedule.

    • Proposed as answer by Mohsin_A_Khan Saturday, June 16, 2018 11:07 PM
    • Marked as answer by mg101 Sunday, June 17, 2018 1:36 AM
    Friday, June 15, 2018 6:43 PM
    Moderator
  • Thanks Hillary
    Tuesday, June 19, 2018 8:00 PM