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?

 

Answer 1

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

 

Answer 2

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?

 

 

Answer 3

 

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?

Answer 4

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

 

Answer 5

 

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.

Answer 6

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.

Answer 7

Yes it should.  Post edited.

Answer 8

 

Hi

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

 BR

AZIM 

Answer 9

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.

Answer 10

Is this SQL 2000? It looks like you will need to drop your entire subscription and then recreate it.

 

Answer 11

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

Answer 12

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

Answer 13

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,