Remove Article from Merge replication

Hi NG
Publisher:
Win 2003 Server
SQL 2000 - SP3a
Subscriber:
Win 2000 - SP4
SQL 2000 - SP3a
After many headaches with dog servers and virtually non-existent bandwidth,
I have finally got my Merge Replication up and running OK.
However, I have inadvertently selected 2 tables for replication which should
not be replicated. The reason for this is that these tables are used as
staging tables when consolidating large amounts of data. They get 200,000
inserts/updates/deletes per day. Normally we only expect to be replicating
1,000 inserts/updates/deletes per day.
Is there a way to remove these 2 articles form the replication process.
If not, I will simply have to create 2 new tables, and ensure that all
references to the existing tables are changed to the new non-replicated
tables. However, I dislike workarounds and orphan items.
I have seen a couple of posts indicating sp_MSunmarkreplinfo. However, I
have been unable to find any reference to this SP in Books online.
Regards
Des Norton
Des,
unlike transactional replication this is not possible via normal means. Your
best bet is to drop the publication and recreate without the 2 tables. This
can be made more palatable - especially if you have large tables - by doing a
nosync one, provided you can prevent updates to the data during this period.
The other option is to disable the merge triggers on these tables, but again
this is a bit of a hack.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Kick the users off the subscribers, drop the subscriptions, drop the
articles using sp_dropmergerarticle, or by right clicking on your
publication and selecting properties, then in the articles tab uncheck the
articles you wish to drop.
Then redeploy your subscribers doing a no-sync.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Des Norton" <desREMOVEnortonUPPERCASE@.gmail.com> wrote in message
news:uncu3HIAGHA.140@.TK2MSFTNGP12.phx.gbl...
> Hi NG
> Publisher:
> Win 2003 Server
> SQL 2000 - SP3a
> Subscriber:
> Win 2000 - SP4
> SQL 2000 - SP3a
>
> After many headaches with dog servers and virtually non-existent
> bandwidth, I have finally got my Merge Replication up and running OK.
> However, I have inadvertently selected 2 tables for replication which
> should not be replicated. The reason for this is that these tables are
> used as staging tables when consolidating large amounts of data. They get
> 200,000 inserts/updates/deletes per day. Normally we only expect to be
> replicating 1,000 inserts/updates/deletes per day.
> Is there a way to remove these 2 articles form the replication process.
> If not, I will simply have to create 2 new tables, and ensure that all
> references to the existing tables are changed to the new non-replicated
> tables. However, I dislike workarounds and orphan items.
> I have seen a couple of posts indicating sp_MSunmarkreplinfo. However, I
> have been unable to find any reference to this SP in Books online.
>
> Regards
> Des Norton
>