none
Transactional Replication Help

    Question

  • If I add a new article to my publication, using Management Studio, sometimes it asks me to re-initialise the snapshot and sometimes it doesn't. What determines this?

    If I then run the Snapshot Agent it creates a full new snapshot and doesn’t delete the old one.  Shouldn’t it just add the new article to the existing snapshot?

    This new article doesn’t then replicate to the subscription?

    When initially configured the subscription, I didn’t initialise it from the snapshot, does this effect the above in any way?

     

    Wednesday, September 12, 2007 3:50 PM

Answers

  • Hi,

     

    If you would like snapshot agent not to create the full snapshot each time it runs, publicatin property @immediate_sync has to be set to false. But it means that every time you add an new article, you also need to call sp_addsubscription for every existing subscription DB so they can subscribe this article. If your subscription is pull, you may also need to call sp_refreshsubscription. The detailed instruction is outlined at the following document: http://msdn2.microsoft.com/en-us/library/ms146887.aspx

     

    Hope it helps.

     

    Peng

     

    Thursday, September 13, 2007 5:48 PM

All replies

  • If you didn't ititialize from snapshot, it mean you either manually create schema/data at subscriber or use backup/restore. In that case, the snapshot is not necessary. Simply use logreader/distribution agent to sync.

     

    For other questions, you can take a look at publication @immediate_sync option in this BOL page (http://msdn2.microsoft.com/en-us/library/ms188738.aspx).It controls the behavior if subscription automatically get the new article.

     

    Peng

     

    Wednesday, September 12, 2007 11:34 PM
  • Thanks Peng.

     

    What I'm after doing is the following:

     

    1. Create an transactional publication, where the subscriber is initialised from a backup.

    2. At a later date add a new article to the publication, I'd then like this new artlicle to snapshot and by automatically created on the subsciber. 

     

    What is the best way to achieve (2), if indeed it's possible?

     

     

    Thursday, September 13, 2007 8:40 AM
  •  

    Someone else also having this problem:

     

    http://sql-server-performance.com/Community/forums/t/22942.aspx

     

    Surely in new article in 2005 doesn't require a full snapshot of the whole publication?

    Thursday, September 13, 2007 11:13 AM
  • Hi,

     

    If you would like snapshot agent not to create the full snapshot each time it runs, publicatin property @immediate_sync has to be set to false. But it means that every time you add an new article, you also need to call sp_addsubscription for every existing subscription DB so they can subscribe this article. If your subscription is pull, you may also need to call sp_refreshsubscription. The detailed instruction is outlined at the following document: http://msdn2.microsoft.com/en-us/library/ms146887.aspx

     

    Hope it helps.

     

    Peng

     

    Thursday, September 13, 2007 5:48 PM
  •  

    Thanks.  I seem to have got me head round this now.  Here is a summary of whats happening, although why this has been done is beyond me:

     

    If you create a publication and create an initial snapshot (SS), the the following 2 options are set to true:

    allow_anonymous

    immediate_sync

     

    The above options can then be changed using sp_changepublication, but not through the GUI !!!!

     

    Therefore, everytime you add an article a full SS is created.  And if you attempt to run:

     

    EXEC sp_addarticle @publication = 'PublicationName'

    , @article = 'NewTableName'

    , @source_table = 'NewTableName'

     

    You'll get:

     

    Cannot make the change because a snapshot is already generated. Set @force_invalidate_snapshot to 1 to force the change and invalidate the existing snapshot.

     

    If you don't create an initial SS the above options are set to false.  However if you attempt to add a new article using the GUI and then run a SS, it reports "[0%] A snapshot was not generated because no subscriptions needed initialization."  No good !!!

     

    You have to use the code:

     

    EXEC sp_addsubscription @publication = 'PublicationName'

    , @article = 'NewTableName'

    , @subscriber = 'SubscriberComputerName'

    , @destination_db = 'SubscriberDBName'

     

    EXEC sp_addsubscription @publication = 'PublicationName'

    , @article = 'All' --Must specify all !!!

    , @subscriber = 'SubscriberComputerName'

    , @destination_db = 'SubscriberDBName'

     

    Then run the SS agent and you'll get only a SS for the new table!!!  At last after 2 days work.

     

    You can delete the SS files at your leisure.

    Friday, September 14, 2007 10:07 AM
  • Wow.  Thanks for finding the solution!  One question - in that last bit of code, should the second sp_addsubscription have the same publication name?  I believe so, but want to be sure.
    Friday, September 21, 2007 3:27 PM
  • Yes it should.  Post edited.

    Monday, September 24, 2007 8:34 AM
  •  

    Hi

    Great work. I have succesfully tested this approch for Oracle subscriber.

     BR

    AZIM 

    Wednesday, December 05, 2007 1:08 PM
  • Can someone help me?  I am getting error message when i run the sp_addsubscription.

     

    EXEC sp_changepublication

    @publication = 'PROD_TRANS_REPL',

    @property = N'allow_anonymous',

    @value = false

    GO

    EXEC sp_changepublication

    @publication = 'PROD_TRANS_REPL',

    @property = N'immediate_sync',

    @value = false

    GO

    EXEC sp_addarticle @publication = 'PROD_TRANS_REPL',

    @article = 'lkup_yesno' ,

    @source_table = 'lkup_yesno'

     

    I am getting the following error when I run the last statement.

     

     

    .Net SqlClient Data Provider: Msg 14100, Level 16, State 1, Procedure sp_MSrepl_addsubscription, Line 533

    Specify all articles when subscribing to a publication using concurrent snapshot processing.

     

     

     

    Thanks.
    Tuesday, May 20, 2008 6:36 PM
  • Is this SQL 2000? It looks like you will need to drop your entire subscription and then recreate it.

     

    Wednesday, May 21, 2008 10:53 AM
  • Hello all,

    Here is what worked for me.  I hope this works for you...

    --set to publication options to false
    EXEC sp_changepublication
      @publication = 'reporting_publication',
      @property = N'allow_anonymous',
      @value = 'false'
    GO

    EXEC sp_changepublication
      @publication = 'reporting_publication',
      @property = N'immediate_sync',
      @value = 'false'
    GO

    --add article
    EXEC sp_addarticle @publication = 'reporting_publication'
    , @article = 'patients'
    , @source_table = 'patients'


    EXEC sp_addsubscription @publication = 'reporting_publication'
    , @article = 'All'
    , @subscriber = 'it-pros-laptop'
    , @destination_db = '5nqtest_REPL'


    --run snapshot agent from the GUI
    --you should see just the one article being copied over

    --set to publication options to true
    EXEC sp_changepublication
      @publication = 'reporting_publication',
      @property = N'immediate_sync',
      @value = 'true'
    GO

    EXEC sp_changepublication
      @publication = 'reporting_publication',
      @property = N'allow_anonymous',
      @value = 'true'
    GO
    Wednesday, September 10, 2008 7:49 PM
  • jdneilso said:

    Hello all,

    Here is what worked for me.  I hope this works for you...

    --set to publication options to false
    EXEC sp_changepublication
      @publication = 'reporting_publication',
      @property = N'allow_anonymous',
      @value = 'false'
    GO

    EXEC sp_changepublication
      @publication = 'reporting_publication',
      @property = N'immediate_sync',
      @value = 'false'
    GO

    --add article
    EXEC sp_addarticle @publication = 'reporting_publication'
    , @article = 'patients'
    , @source_table = 'patients'


    EXEC sp_addsubscription @publication = 'reporting_publication'
    , @article = 'All'
    , @subscriber = 'it-pros-laptop'
    , @destination_db = '5nqtest_REPL'


    --run snapshot agent from the GUI
    --you should see just the one article being copied over

    --set to publication options to true
    EXEC sp_changepublication
      @publication = 'reporting_publication',
      @property = N'immediate_sync',
      @value = 'true'
    GO

    EXEC sp_changepublication
      @publication = 'reporting_publication',
      @property = N'allow_anonymous',
      @value = 'true'
    GO

    It seems work fine. However, any changes made on subscriber are not applied to the publisher with updatable transactional replication setup. Anyone knows why? How to fix the problem?

     
    Cici
    Friday, February 27, 2009 11:29 PM
  • Hello,

    This did not work in my case. I had to add an article with the filter to the existing publication but the data did not get copied. I am doing anything wrong?

    One more question, how do I re-push the data just for one article if the count is not in sync between source and subscription?

    Please let me know.

    -Thanks,

     

     

     

    Tuesday, April 13, 2010 12:51 PM