none
Schema changes to Transaction replcation setup 2018 R3 to 2014 RRS feed

  • Question

  • Hi Currently my have transaction replication 2018 R3 to  2014. Now I business needs schema changes.

    add new Three tables
    add new three produces
    add new  two functions

    Can I add those with out breaking existing replication setup to primary and propergate to replication site.

    or I need to break the existing replication and rebuid it after add new tables.procedures, function to primary?

    your advise is highly appreciated .

    regards

    Friday, February 15, 2019 4:45 AM

All replies

  • NO, you Can not add those with out breaking existing replication setup to primary and propergate to replication site.

    you need to reinitialize your existing replication and it create new snapshot and import data and table,sp,index from beginning. so you need downtime.

    Friday, February 15, 2019 5:22 AM
  • You got the wrong SQL version. Maybe you wanted to say 2008 R2. Anyway, the answer to your question is, No, you don't need to break the existing replication to be able to add new articles (tables, sprocs, functions). Just add them to the desired publication and run a new snapshot followed by the distribution agent run. Doing this will initialize the newly added articles and starts synchronizing them to the subscriber. 

    I see you mentioned "Schema changes" but that's different from adding new objects to a publication. Schema changes property for tables/objects is set to yes by default in the transactional replication. You can change it to No to replicate only data and avoid any schema level changes to the replication objects. 


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    • Proposed as answer by Puzzle_Chen Monday, February 18, 2019 9:05 AM
    Friday, February 15, 2019 5:25 AM
  • Hi Tyagisql

    Thanks for the reply. What step should I follow to do in minimum effort.

    ex: remove subscribers

    remove publishers

    do not need to delete distribution DB.

    This DB is pretty big and hoping to do backup and restore  instated of using Agent snapshot.

    Are you able to advise

    regards

    Friday, February 15, 2019 5:27 AM
  • Hi Mohsin

    Previously configured using backup and restore option. do you think if start Snapshot backup will that works?

    any possibilities of doing a backup and restore  and enable replication instead of breaking the setup  

    thanks

    Friday, February 15, 2019 8:03 AM
  • no need to remove anything only right click on your publication select properties select article in window add new artilce (table,view etc) click ok. 
    after that right click on your publication select reinitialize and new sanpshot. 
    then it create new snapshot and import data and table,sp,index from beginning. time depending on your data.
    Friday, February 15, 2019 1:41 PM

  • any possibilities of doing a backup and restore  and enable replication instead of breaking the setup  

    Adding new articles to an existing publication does NOT incur reinitialization as the other responses are suggesting. Simply right-click the publication-->properties--->check the articles you want to add to the publication-->Hit OK--->run a new snapshot--->run the distribution agent to copy the schema and data of the newly added articles over to the subscriber. 

    Note: if after checking the new articles on the add articles screen it gives you a warning that the action causes reinitialization then you have accidentally changed something, in which case hit cancel and exit out. Then start over again.

    If you have pull subscription (where the distribution agent runs on the subscriber), you have to run "sp_refreshsubscriptions" too (when using T-SQL). 

    The following links have clear steps on how you can add new objects to an existing publication. 

    https://docs.microsoft.com/en-us/sql/relational-databases/replication/publish/add-articles-to-and-drop-articles-from-existing-publications?view=sql-server-2017

    https://dba.stackexchange.com/questions/57600/how-to-add-an-article-to-existing-replication

    http://www.sqlservercentral.com/blogs/sql-geek/2017/07/10/add-new-articles-to-existing-transactional-replication-without-initializing-old-articles/


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Friday, February 15, 2019 2:55 PM
  • If you use script and the immediate_sync option is off you will not need to reinitialize.

    If you use script and the immediate_sync option is on you will need to reinitialize.

    If you use SSMS and right click on the publication and add objects to replicate you will need to reinitialize all objects if and the immediate_sync option is on.

    If you use SSMS and right click on the publication and add objects to replicate you will need to reinitialize only the new objects if and the immediate_sync option is off.

    If you create a separate publication for these new objects you will only need to generate a snapshot for these new objects.

    Friday, February 15, 2019 4:05 PM
    Moderator
  • Hi Mohsin Thank you very much. Yes this is pull replication.

    I followed the link

    http://www.sqlservercentral.com/blogs/sql-geek/2017/07/10/add-new-articles-to-existing-transactional-replication-without-initializing-old-articles/

    but I am end up with following messages after run "Snapshot agent". But no new table was added even I added through "Article" through publisher properties .

    Secondly I set it immediate_sync =false

    Then run exec sp_refreshsubscriptions'DB_PUB'  from Publication site.

    But no luck.

    any idea


    • Edited by ashwan Sunday, February 17, 2019 10:46 PM
    Sunday, February 17, 2019 6:55 PM
  • Hi  Hilary

    I am having following error while doing reinitialize option   from "subscriber site"

    On publication site, I did following changed the immediate sync. But no luck. Aany more action I missed? 

     

    EXECsp_changepublication@publication ='DB_PUB',@property =

    N'immediate_sync',@value='TRUE'

    Go

    regards


    • Edited by ashwan Sunday, February 17, 2019 10:26 PM
    Sunday, February 17, 2019 10:14 PM