locked
Switching properties without needing to reinitialize? RRS feed

  • Question

  • Greetings.

    We've got transactional replication setup between a 3 TB database and several reporting servers. For very long winded reasons we want to remove large amounts of data from OLTP, and would be totally fine with it staying on the replication servers. A potentially important fun fact is that the Subscriptions are initiated from a backup, not from a snapshot.

    My thought was to change the Publication properties for "DELETE delivery format" to "Do not replicate DELETE statements". However, when I make that change I get the following warning:

    I hit Mark For Reinitialization as I have no option. Once complete, I attempt to launch a Tracer Token in Replication Monitor, and get the following error:

    Is there any way to make this magic happen without needing to reinitialize?


    Thanks in advance! ChrisRDBA

    Thursday, December 12, 2019 8:50 PM

Answers

  • Getting back to your question Chris, replace the delete statement with the word NONE, use sp_changearticle for this.

    sp_changearticle 'PublicationName',ArticleName,'del_cmd','NONE'

    Making this change via a gui will force a reinitialization whether you need one or not.


    Monday, January 6, 2020 3:30 PM
    Answerer

All replies

  • You should make this change via sp_changearticle, BUT I think you made a change and rolled back and now your publication is not active.

    Do sp_helppublication PublicationName in your publication database.

    What is the value of Status? if it is 0 you will need to drop your subscription and start again.

    You might be lucky and be able to do a no-sync subscription with a minimum of data loss.

    Thursday, December 12, 2019 10:15 PM
    Answerer
  • Thanks Hillary, the status is 1. However, this is just a lab environment, I can blow it all away and it doesn't matter. The question is more how could I do this w success, as opposed to how do I fix this particular problem?

    Thanks again!


    Thanks in advance! ChrisRDBA

    Thursday, December 12, 2019 10:44 PM
  • Here is a 'not suggested' way:

    Use the 'https://docs.microsoft.com/en-us/sql/relational-databases/replication/transactional/publishing-stored-procedure-execution-in-transactional-replication?view=sql-server-ver15' to create the delete procedure

    For the stored procedure in sub, make it do nothing...


    https://sqlserver.code.blog/

    Friday, January 3, 2020 11:31 PM
  • Getting back to your question Chris, replace the delete statement with the word NONE, use sp_changearticle for this.

    sp_changearticle 'PublicationName',ArticleName,'del_cmd','NONE'

    Making this change via a gui will force a reinitialization whether you need one or not.


    Monday, January 6, 2020 3:30 PM
    Answerer
  • Getting back to your question Chris, replace the delete statement with the word NONE, use sp_changearticle for this.

    sp_changearticle 'PublicationName',ArticleName,'del_cmd','NONE'

    Making this change via a gui will force a reinitialization whether you need one or not.


    Are you saying I can accomplish my goal if I ditch the GUI and make my changes through sprocs? I don't have ime to test right now, but am on the edge of my seat.

    Thanks in advance! ChrisRDBA

    Monday, January 6, 2020 8:16 PM
  • Yes, avoid using the gui if at all possible.
    Monday, January 6, 2020 8:26 PM
    Answerer
  • Thanks Hillary -- are there any unintended gotcha's I need to watch out for when doing this?

    Thanks in advance! ChrisRDBA

    Thursday, January 9, 2020 5:28 PM
  • Be careful. I think IIRC depending on the  version of SSMS you might not get the NONE value scripted out.
    Thursday, January 9, 2020 5:51 PM
    Answerer
  • Be careful. I think IIRC depending on the  version of SSMS you might not get the NONE value scripted out.

    I'll write my own scripts for all of it anyways, but I appreciate the warning. 


    Thanks in advance! ChrisRDBA

    Friday, January 10, 2020 2:18 PM