SQL 2005 Transaction Replication

I have an existing publication with 10 articles configured for Transactional replication. The replication is running fine. Now I wish to add another table (article) to this publication. My requirement is to ensure that I dont need to generate a snapshot of all the articles being published, if I add a single article only that particular article should be published.

I added the single article using the below command,

USE mydb
sp_addarticle @publication='mypublication', @article='dbo.test',@source_table='test'

I got the following error for the above command,

Msg 20607, Level 16, State 1, Procedure sp_MSreinit_article, Line 99

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.

The reason behind this error message was that there was already a snapshot that was created recently. Since I added a new article it wouldnt be able to use the existing snapshot so I need to use the option @force_invalidate_snapshot=1 to invalidate the existing snapshot and it would generate a new snapshot to be applied to the subscriber.

USE mydb
sp_addarticle @publication='mypublication', @article='dbo.test',
@source_table='test' , @force_invalidate_snapshot=1

Now I ran Exec sp_helppublication in my publication database and checked the following fields,

1. Immediate_sync

2. Allow_anonymous

Both the fields were set to ON as they showed a value 1 which is enabled. If the Immediate_sync is enabled, everytime you add a new article it will cause the entire snapshot to be applied and not the one for the particular article alone.

Usually, the immediate_sync publication property is set to true if we allowed anonymous subscriptions while creating the publication through the CreatePublication wizard. To prevent the complete snapshot, run the script below,

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

Now I adding the subscription to the existing publisher for the single table alone using the below command,

EXEC sp_addsubscription @publication = 'mypublication', @article = 'dbo.test',
@subscriber = 'Subscriberservername',@destination_db = 'mydestinationdbname'

I got the following error message while running the above command in my publication database.

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

This error occurs when the existing publication was set up with concurrent snapshot option and means that you cant synchronize subscriptions for such publications without a complete resynchronization. There are 2 workarounds: (a) By specifying @reserve = internal when you add the subscription for the new article and the snapshot agent should generate snapshot for the new article after that and b.) Changing the sync_method from concurrent to either database snapshot (enterprise edition only in SQL Server 2005) or native (which locks table during snapshot generation). Change the sync_method will force a reinitialization of all your subscriptions at this point. Alternatively you could create another publication and use this instead.

I ran this command and it worked fine,

EXEC sp_addsubscription
@publication = 'mypublication',@article = 'dbo.test',@subscriber = 'Subscriberservername',
@destination_db = 'mydestinationdbname',@reserved='Internal'

Now I went ahead and started the snapshot agent in publisher, it worked perfectly. I can now see that only the particular table I added was replicated. So from now on to apply the snapshots of the entire articles we need to reinitialize the subscriptions since the immediate_sync is set to off.

3 thoughts on “SQL 2005 Transaction Replication”

  1. Hi Deepak,

    Thanks for such a nice article.

    You missed one thing to add in code:
    @article = ‘all’,

    Hence the script will be like this:
    EXEC sp_addsubscription
    @publication = ‘mypublication’,
    @article = ‘ALL’,
    @subscriber = ‘Subscriberservername’,
    @destination_db = ‘mydestinationdbname’,

  2. Do we need to change these two parameterrs (1. Immediate_sync, 2. Allow_anonymous) to true after completion of the activity. What wiil be their impact in the future.

Leave a Reply

Your email address will not be published. Required fields are marked *