none
Initialization for just one table schema change RRS feed

  • Question

  • Fellow SQLers,

    On a SQL 2008r2 box and coming up to speed on replication. I saw a youtube where someone explained a process whereby IF you altered the schema of a table, you could get replication to create a script-snapshot of JUST that table. I cannot find that process to do that.

    As an example, I have a pulication with two tables. I have added a field to one of those tables in the DB. Now I want to add that field to the publication. When you do that, you get the popup that indicates you need to reinitialize.  I would rather not reinitialize the whole publication (both tables) to the subscriber.

    I had seen a youtube whereby a person describes dropping the table from the publisher, then adding it back. I am still seeing that the process wants to reiniti the entire publication. Any trick around this to just publish the one table schema change?

    Thanks, MG


    • Edited by mg101 Monday, March 25, 2019 4:42 PM wording
    Monday, March 25, 2019 4:31 PM

Answers

  • Basically if your publication is configured to replicate changes (and by default it is) SQL Server will generate a script to do the change and store it in the snapshot folder. In some cases it will just write the alter statement to the distribution database. 

    Note however that you need to do your table changes via alter table statements. You cannot do it via SSMS.

    To get around the initialization requirement you need to do this to your publication.

    use YourPublicationDatabase
    GO
    sp_changepublication 'YourPublicationName',independent_agent, true
    GO
    sp_changepublication 'YourPublicationName',allow_anonymous, false
    GO
    sp_changepublication 'YourPublicationName',immediate_sync, false 
    GO
     

    • Proposed as answer by Tom Phillips Monday, March 25, 2019 6:09 PM
    • Marked as answer by mg101 Monday, March 25, 2019 6:39 PM
    Monday, March 25, 2019 5:21 PM
    Moderator

All replies

  • Basically if your publication is configured to replicate changes (and by default it is) SQL Server will generate a script to do the change and store it in the snapshot folder. In some cases it will just write the alter statement to the distribution database. 

    Note however that you need to do your table changes via alter table statements. You cannot do it via SSMS.

    To get around the initialization requirement you need to do this to your publication.

    use YourPublicationDatabase
    GO
    sp_changepublication 'YourPublicationName',independent_agent, true
    GO
    sp_changepublication 'YourPublicationName',allow_anonymous, false
    GO
    sp_changepublication 'YourPublicationName',immediate_sync, false 
    GO
     

    • Proposed as answer by Tom Phillips Monday, March 25, 2019 6:09 PM
    • Marked as answer by mg101 Monday, March 25, 2019 6:39 PM
    Monday, March 25, 2019 5:21 PM
    Moderator
  • HI Hillary,

    Thanks for all of that.  One more question, I am assuming it is not recommended to open the snapshot folder and REMOVE the files that are NOT tied to that table. For example, I have three tables being replicated. I only want the change to one of them. In the snapshot I can see 3 sets of 4 different scripts (sch, idx, pre,bcp) - one set for each table. I was wondering if I could also alter this snapshot and just keep the one I want. Something tells me that this might NOT be a good idea.

    MG

    Monday, March 25, 2019 5:36 PM
  • Do not remove the files, but you can zero the contents out.
    Monday, March 25, 2019 5:56 PM
    Moderator
  • Thanks Hilary

    MG

    Monday, March 25, 2019 6:43 PM