A smart guy here at work was working on being able to remove and add an article to our SQL server 2008 R2 server. (We used to do it all the time in 2000)
After much googling he finally hit on this blog that ‘actually works’ (compared to many that didn’t)
http://ansqldba.blogspot.com/2012/02/adding-new-article-to-existing.html
Adding new article to Existing Publication (Transactional Replication)
First of all 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.
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.
Step :- 1
EXEC sp_changepublication
@publication = ‘Pub_dbAmericasCitrixFarm’,
@property = N’allow_anonymous’,
@value = ‘false’
GO
EXEC sp_changepublication
@publication = ‘Pub_dbAmericasCitrixFarm’,
@property = N’immediate_sync’,
@value = ‘false’
GO
EXEC sp_changepublication
@publication = ‘Pub_dbAmericasCitrixFarm’,
@property = N’allow_anonymous’,
@value = ‘false’
GO
EXEC sp_changepublication
@publication = ‘Pub_dbAmericasCitrixFarm’,
@property = N’immediate_sync’,
@value = ‘false’
GO
Step :- 2
I added the single article using the below command,
EXEC sp_addarticle
EXEC sp_addarticle
@publication = Pub_dbAmericasCitrixFarm,
@article = Table_2,
@source_object = Table_2,
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 wouldn’t 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.
EXEC sp_addarticle
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 wouldn’t 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.
EXEC sp_addarticle
@publication = Pub_dbAmericasCitrixFarm,
@article = Table_2,
@source_object = Table_2,
@force_invalidate_snapshot=1
Step:-3
Now I adding the subscription to the existing publisher for the single table alone using the below command,
EXEC sp_addsubscription
EXEC sp_addsubscription
@publication = ‘Pub_dbAmericasCitrixFarm’,
@subscriber = ‘FTDCCWPCTRXSQL’,
@destination_db = ‘dbAmericasCitrixFarm’,
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 can’t 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
“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 can’t 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 = ‘Pub_dbAmericasCitrixFarm’,
@subscriber = ‘FTDCCWPCTRXSQL’,
@destination_db = ‘dbAmericasCitrixFarm’,
@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.
Drop new article to Existing Publication (Transactional Replication)
EXEC sp_dropsubscription
@publication = Pub_dbAmericasCitrixFarm,
@article = N’Table_2′,
@subscriber = ‘FTDCCWPCTRXSQL’;
GO
EXEC sp_droparticle
@publication = Pub_dbAmericasCitrixFarm,
@article = Table_2,
@force_invalidate_snapshot = 1;
For Pull Subscription (Existing Publication and subscription)
First Make sure that the publisher properties “allow_anonymous” and “immediate_sync” are set to “False”, if these 2 options are set to “True” then this SP will mark all the articles for generating snapshot instead of marking only the newly added articles.To Check the publication properties, use this query.
exec sp_helppublication ‘PublicationName’
GO
GO
If the values of the output columns “allow_anonymous” and “immediate_sync” are 0 then they are set to “False” if their values are 1 then they are set to “True”
Add Article using below command
EXEC sp_addarticle
@publication = Pub_dbAmericasCitrixFarm,
@article = Table_2,
@source_object = Table_2,
@force_invalidate_snapshot=1
–Refresh Subscriptions
exec sp_refreshsubscriptions ‘ Pub_dbAmericasCitrixFarm ‘
GO
After running the above commands, run the snapshot agent.
>> Adding Article in existing transactional replication using entire snapshot regenerate use below method.
Step 1:-
Set properties “allow_anonymous” and “immediate_sync” to true
Step 2:-
EXEC sp_addarticle
@publication = Pub_dbAmericasCitrixFarm,
@article = Table_2,
@source_object = Table_2,
Step 3:-
EXEC sp_addsubscription
@publication = ‘Pub_dbAmericasCitrixFarm’,
–@article = ‘Table_2’,
@subscriber = ‘FTDCCWPCTRXSQL’,
@destination_db = ‘dbAmericasCitrixFarm’,
–@subscription_type = N’push’
@reserved=’Internal’
Step 3:-
Run the Snapshot agent, it will genrate the entire snashot again for all the article
Filed under: Applied IT, Uncategorized | Tagged: sql | Leave a comment »
