0

I'm using SQL server 2012 enterprise edition. I've set up a SQL transaction replication between two servers.
And now I want to drop some articles from the publication.
I'm not sure whether there are some special considerations/actions after the articles being removed from publication.

From MSDN it says:

For snapshot or transactional publications, articles can be dropped with no special considerations prior to subscriptions being created. If an article is dropped after one or more subscriptions is created, the subscriptions must be dropped, recreated, and synchronized.

However, from my test, after the articles being dropped and snapshot re-created, the subscription can successfully sync the data from publication without any special action.

Can anybody give some help?

dingx
  • 1,621
  • 3
  • 20
  • 38

2 Answers2

1

I use the following script and I usually don't need to run a full snapshot, or when I run it, it says that the subscribers are ok and there is no need for a snapshot.

 --(1)
use [MYDATABASE]
exec sp_dropsubscription @publication = N'MYPUBLICATION', @article = N'MYTABLE', @subscriber = N'all', @destination_db = N'all'
GO

-- (2)
use [MYDATABASE]
exec sp_droparticle @publication = N'MYPUBLICATION', @article = N'MYTABLE', @force_invalidate_snapshot = 1
GO
Marcello Miorelli
  • 3,368
  • 4
  • 44
  • 67
0

Once you have dropped the article, you just need to re-initialise the subscription by recreating the snapshot so it can re-sync.

Please see below for the steps to be taken.

http://blog.extreme-advice.com/2012/11/28/delete-article-from-transactional-replication-in-sql-server-2012/

JammoD
  • 419
  • 5
  • 15
  • I think this would be true only if immediate_sync is set to true on the publication. Otherwise, snapshots just sitting around aren't very useful. – Ben Thul Aug 13 '14 at 10:27