Deleting records from merge replication

I have a merge replication set up.
I need to delete some records at publisher which i don't want to be
replicated.
What should i do?
Thank you.
I suppose you could temporarily disable the merge triggers, but I really
wouldn't recommend this. If changes are made meanwhile, or if changes are
made to the subscriber rows you're keeping, there will be failures later on.
What I'd do is archive off these rows on the subscriber then do the delete.
You can amalgamate the rows (union) for the client application if necessary.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I have the same issue. I want to delete old date but hate to clog up
replication with 500K deletes. A user may have to synch for days to get thru
them.
Untested idea yet but I was considering pausing the merge agent job,
deleting the rows, isolating the deletes in msmerge_tombstone and genhistory
and deleting those specific rows. Restarting the agent would make the big
deletes get ignored , right?
Then run a similar script on the subscriber to trim the size too.
Anyone speculate on any unforseen ill effects? would it throw generation
out of whack?
Mike
If data falls in the woods and nobody is there to see it ...... ?
"ravi lobo" wrote:

> I have a merge replication set up.
> I need to delete some records at publisher which i don't want to be
> replicated.
> What should i do?
> Thank you.
|||I can not disable DELETE trigger because as paul said i may some of the
deletes fired on the subscriber.
Tigermikefl,
I have tried your solution twice some time back. One time it worked. Failed
at the second time!
So i was forced to recreate the replication ! (It was very painful)
gen history table created some problem.
So i will not use your method unless somebody test it full-proff!
Also i need these functionality on a scheduled basis, stopping merge agent
followed by deleting tombstone will be painful.
|||You can change this behavior on the article level. What you need to do is
create an account in the PAL and limit the permissions on the publisher on
an as needed basis. Suppose the PAL account your particular subscriber is
pulling with is called Ravi. Grant select and update permissions to Ravi on
the table in the publisher - we'll call it raviTable.
Now right click on your publication, select publication properties, and
select the articles tab. Click on RaviTable, and click the browse button,
select Merging Changes, ensure that Delete is selected. Now what will
happen is that deletes will occur on the subscriber, and be replicated to
the publisher but be kicked back as conflicts. By default these deletes will
remain in the subscriber (IIRC) even with the compensate_For_errors setting
set to true (the default).
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
"ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
news:DBE8C6C1-82FF-4704-8962-162175CE9F66@.microsoft.com...
>I have a merge replication set up.
> I need to delete some records at publisher which i don't want to be
> replicated.
> What should i do?
> Thank you.
|||Hilary,
Thank you for the reply.
I think the solution given by you prevents the DELETES at the subscriber
to replicate to the publisher.
I have a huge number of records which needs to be deleted. I am afraid
if I use your approach it will clog the n/w.
Infact I need to delete the records at the publisher.
Any suggestions?
"Hilary Cotter" wrote:

> You can change this behavior on the article level. What you need to do is
> create an account in the PAL and limit the permissions on the publisher on
> an as needed basis. Suppose the PAL account your particular subscriber is
> pulling with is called Ravi. Grant select and update permissions to Ravi on
> the table in the publisher - we'll call it raviTable.
> Now right click on your publication, select publication properties, and
> select the articles tab. Click on RaviTable, and click the browse button,
> select Merging Changes, ensure that Delete is selected. Now what will
> happen is that deletes will occur on the subscriber, and be replicated to
> the publisher but be kicked back as conflicts. By default these deletes will
> remain in the subscriber (IIRC) even with the compensate_For_errors setting
> set to true (the default).
> --
> 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
> "ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
> news:DBE8C6C1-82FF-4704-8962-162175CE9F66@.microsoft.com...
>
>
|||The only suggestion I can make is to set ExchangeType to 1 (upload only),
but this will prevent all transactions from moving from the publisher to the
subscriber. You could drop the subscription, do the delete, and then
reinitialize, but I doubt this will work for you.
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
"ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
news:EA14635C-0A17-416F-B800-DD30A9EEA65B@.microsoft.com...[vbcol=seagreen]
> Hilary,
> Thank you for the reply.
> I think the solution given by you prevents the DELETES at the subscriber
> to replicate to the publisher.
> I have a huge number of records which needs to be deleted. I am afraid
> if I use your approach it will clog the n/w.
> Infact I need to delete the records at the publisher.
> Any suggestions?
> --
> "Hilary Cotter" wrote:
|||I can not use ExchangeType to 1,but can use dropping the subscription in the
green zone. Just wanted to avoid that.
Thank you Hilary, paul and Tigermikefl.
Good to know all those options.
"Hilary Cotter" wrote:

> The only suggestion I can make is to set ExchangeType to 1 (upload only),
> but this will prevent all transactions from moving from the publisher to the
> subscriber. You could drop the subscription, do the delete, and then
> reinitialize, but I doubt this will work for you.
> --
> 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
> "ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
> news:EA14635C-0A17-416F-B800-DD30A9EEA65B@.microsoft.com...
>
>
|||Yes, that can be made to work and I have been through that process before.
No, I won't post scripts or the exact steps to follow. You are playing with
internal metadata the merge engine uses. The engine only moves what is in
the metadata tables, if it isn't there, it dosn't know about it. So, if you
remove the wrong row, you will forever be out of synch with the only option
being to reinitialize. They we'd have to hear about why merge isn't
working. If you understand the internal structures behind merge and exactly
how it moves data around the system, you already know how to do this and
don't need it explained. It is absolutely unsupported and very definitely
not recommended.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Tigermikefl" <Tigermikefl@.discussions.microsoft.com> wrote in message
news:A9500349-5C6B-4B61-919A-ADD05A4C3315@.microsoft.com...[vbcol=seagreen]
>I have the same issue. I want to delete old date but hate to clog up
> replication with 500K deletes. A user may have to synch for days to get
> thru
> them.
> Untested idea yet but I was considering pausing the merge agent job,
> deleting the rows, isolating the deletes in msmerge_tombstone and
> genhistory
> and deleting those specific rows. Restarting the agent would make the big
> deletes get ignored , right?
> Then run a similar script on the subscriber to trim the size too.
> Anyone speculate on any unforseen ill effects? would it throw generation
> out of whack?
>
> --
> Mike
> If data falls in the woods and nobody is there to see it ...... ?
>
> "ravi lobo" wrote:
|||And then you will have 100% of the deletes flow down to the subscriber as
well as 1 row for each row deleted flow back up to the publisher to be
logged into the conflict tables. You would then have to go into the
conflict table and clean out potentially thousands of entries. There is no
way I would recommend doing this.
If you need to do something like this, you need to find a maintenance window
when nothing else is being deleted. Then disable the delete trigger on the
publisher, issue your deletes, and reenable the trigger. The delete will
occur on the publisher, but since the trigger was disabled at that time,
there will be no delete logging for the merge engine to move. You will have
to remember to disable all of your validation scripts after this, because
the system will never validate successfully from that point forward.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23fRI%239sFGHA.644@.TK2MSFTNGP09.phx.gbl...
> You can change this behavior on the article level. What you need to do is
> create an account in the PAL and limit the permissions on the publisher on
> an as needed basis. Suppose the PAL account your particular subscriber is
> pulling with is called Ravi. Grant select and update permissions to Ravi
> on the table in the publisher - we'll call it raviTable.
> Now right click on your publication, select publication properties, and
> select the articles tab. Click on RaviTable, and click the browse button,
> select Merging Changes, ensure that Delete is selected. Now what will
> happen is that deletes will occur on the subscriber, and be replicated to
> the publisher but be kicked back as conflicts. By default these deletes
> will remain in the subscriber (IIRC) even with the compensate_For_errors
> setting set to true (the default).
> --
> 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
> "ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
> news:DBE8C6C1-82FF-4704-8962-162175CE9F66@.microsoft.com...
>