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...
>
Showing posts with label merge. Show all posts
Showing posts with label merge. Show all posts
Thursday, March 29, 2012
Sunday, March 25, 2012
Deleting Large Quantiy of Rows
Hi Guys,
I need a little advice. I need to delete 250000 rows on a monthly basis and
my users are using MSDE 2000 with merge replication, publisher is SQL Server
2000 Standard.
The users will never be uploading or making changes to this table. Is there
any way i can make this a quick delete as currently for each row that is in
the Article published table sql is sending down a delete statement for each
row to the subscriber. Thus taking longer than expected.
Any ideas or advice would be greatly apprecitaed.
Thanks, Tim.
From what you are saying it looks like these rows on the subscriber are read
only and the data flow is only from the publisher to the subscriber for this
table. If so remove this table from the merge publication, put it in a
transactional publication and do your deletes through a stored procedure and
replicate the execution of the stored procedure.
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
"Tim Ford" <tim.ford@.nospamrubbishin2focus.com> wrote in message
news:%23kd7psfrFHA.3604@.tk2msftngp13.phx.gbl...
> Hi Guys,
> I need a little advice. I need to delete 250000 rows on a monthly basis
and
> my users are using MSDE 2000 with merge replication, publisher is SQL
Server
> 2000 Standard.
> The users will never be uploading or making changes to this table. Is
there
> any way i can make this a quick delete as currently for each row that is
in
> the Article published table sql is sending down a delete statement for
each
> row to the subscriber. Thus taking longer than expected.
> Any ideas or advice would be greatly apprecitaed.
> Thanks, Tim.
>
|||Hi Hilary,
Thanks for your response, this is not going to be possible as we use the
ActiveX control and the publications have been hard coded. Which i know is
not good. Is there any other way that we could perhaps specify the order
that things get run in I.E sp_AddScriptexec. I have worked a way out with
the system tables that we could remove generations. However i would need to
make sure that sp_addscriptexec was fired first in the replication process.
Thanks, Tim.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23boP2ShrFHA.1788@.tk2msftngp13.phx.gbl...
> From what you are saying it looks like these rows on the subscriber are
> read
> only and the data flow is only from the publisher to the subscriber for
> this
> table. If so remove this table from the merge publication, put it in a
> transactional publication and do your deletes through a stored procedure
> and
> replicate the execution of the stored procedure.
> --
> 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
> "Tim Ford" <tim.ford@.nospamrubbishin2focus.com> wrote in message
> news:%23kd7psfrFHA.3604@.tk2msftngp13.phx.gbl...
> and
> Server
> there
> in
> each
>
|||I think your best option would be to drop the subscription (possibly drop
the publication as well to drop the triggers which will slow the delete), do
the delete, and then recreate the publication and subscription and do 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
"Tim Ford" <tim.ford@.nospamrubbishin2focus.com> wrote in message
news:eSsLlVhrFHA.3720@.TK2MSFTNGP14.phx.gbl...
> Hi Hilary,
> Thanks for your response, this is not going to be possible as we use the
> ActiveX control and the publications have been hard coded. Which i know is
> not good. Is there any other way that we could perhaps specify the order
> that things get run in I.E sp_AddScriptexec. I have worked a way out with
> the system tables that we could remove generations. However i would need
to
> make sure that sp_addscriptexec was fired first in the replication
process.[vbcol=seagreen]
> Thanks, Tim.
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23boP2ShrFHA.1788@.tk2msftngp13.phx.gbl...
is
>
|||Hi Hilary,
Thanks for that. What would happen to the information that was in the table
on the subscriber? Would that get deleted locally, as this information
would no longer be needed.
Again Thanks for all you help.
Tim.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23HJ2hOjrFHA.2592@.TK2MSFTNGP09.phx.gbl...
>I think your best option would be to drop the subscription (possibly drop
> the publication as well to drop the triggers which will slow the delete),
> do
> the delete, and then recreate the publication and subscription and do 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
> "Tim Ford" <tim.ford@.nospamrubbishin2focus.com> wrote in message
> news:eSsLlVhrFHA.3720@.TK2MSFTNGP14.phx.gbl...
> to
> process.
> is
>
|||It would remain there. This is probably not what you want, so you would have
to delete on both sides.
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
"Tim Ford" <tim.ford@.nospamrubbishin2focus.com> wrote in message
news:OBtaphsrFHA.240@.tk2msftngp13.phx.gbl...
> Hi Hilary,
> Thanks for that. What would happen to the information that was in the
table[vbcol=seagreen]
> on the subscriber? Would that get deleted locally, as this information
> would no longer be needed.
> Again Thanks for all you help.
> Tim.
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23HJ2hOjrFHA.2592@.TK2MSFTNGP09.phx.gbl...
delete),[vbcol=seagreen]
no[vbcol=seagreen]
the[vbcol=seagreen]
order[vbcol=seagreen]
with[vbcol=seagreen]
need[vbcol=seagreen]
are[vbcol=seagreen]
for[vbcol=seagreen]
a[vbcol=seagreen]
SQL[vbcol=seagreen]
Is[vbcol=seagreen]
that[vbcol=seagreen]
for
>
I need a little advice. I need to delete 250000 rows on a monthly basis and
my users are using MSDE 2000 with merge replication, publisher is SQL Server
2000 Standard.
The users will never be uploading or making changes to this table. Is there
any way i can make this a quick delete as currently for each row that is in
the Article published table sql is sending down a delete statement for each
row to the subscriber. Thus taking longer than expected.
Any ideas or advice would be greatly apprecitaed.
Thanks, Tim.
From what you are saying it looks like these rows on the subscriber are read
only and the data flow is only from the publisher to the subscriber for this
table. If so remove this table from the merge publication, put it in a
transactional publication and do your deletes through a stored procedure and
replicate the execution of the stored procedure.
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
"Tim Ford" <tim.ford@.nospamrubbishin2focus.com> wrote in message
news:%23kd7psfrFHA.3604@.tk2msftngp13.phx.gbl...
> Hi Guys,
> I need a little advice. I need to delete 250000 rows on a monthly basis
and
> my users are using MSDE 2000 with merge replication, publisher is SQL
Server
> 2000 Standard.
> The users will never be uploading or making changes to this table. Is
there
> any way i can make this a quick delete as currently for each row that is
in
> the Article published table sql is sending down a delete statement for
each
> row to the subscriber. Thus taking longer than expected.
> Any ideas or advice would be greatly apprecitaed.
> Thanks, Tim.
>
|||Hi Hilary,
Thanks for your response, this is not going to be possible as we use the
ActiveX control and the publications have been hard coded. Which i know is
not good. Is there any other way that we could perhaps specify the order
that things get run in I.E sp_AddScriptexec. I have worked a way out with
the system tables that we could remove generations. However i would need to
make sure that sp_addscriptexec was fired first in the replication process.
Thanks, Tim.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23boP2ShrFHA.1788@.tk2msftngp13.phx.gbl...
> From what you are saying it looks like these rows on the subscriber are
> read
> only and the data flow is only from the publisher to the subscriber for
> this
> table. If so remove this table from the merge publication, put it in a
> transactional publication and do your deletes through a stored procedure
> and
> replicate the execution of the stored procedure.
> --
> 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
> "Tim Ford" <tim.ford@.nospamrubbishin2focus.com> wrote in message
> news:%23kd7psfrFHA.3604@.tk2msftngp13.phx.gbl...
> and
> Server
> there
> in
> each
>
|||I think your best option would be to drop the subscription (possibly drop
the publication as well to drop the triggers which will slow the delete), do
the delete, and then recreate the publication and subscription and do 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
"Tim Ford" <tim.ford@.nospamrubbishin2focus.com> wrote in message
news:eSsLlVhrFHA.3720@.TK2MSFTNGP14.phx.gbl...
> Hi Hilary,
> Thanks for your response, this is not going to be possible as we use the
> ActiveX control and the publications have been hard coded. Which i know is
> not good. Is there any other way that we could perhaps specify the order
> that things get run in I.E sp_AddScriptexec. I have worked a way out with
> the system tables that we could remove generations. However i would need
to
> make sure that sp_addscriptexec was fired first in the replication
process.[vbcol=seagreen]
> Thanks, Tim.
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23boP2ShrFHA.1788@.tk2msftngp13.phx.gbl...
is
>
|||Hi Hilary,
Thanks for that. What would happen to the information that was in the table
on the subscriber? Would that get deleted locally, as this information
would no longer be needed.
Again Thanks for all you help.
Tim.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23HJ2hOjrFHA.2592@.TK2MSFTNGP09.phx.gbl...
>I think your best option would be to drop the subscription (possibly drop
> the publication as well to drop the triggers which will slow the delete),
> do
> the delete, and then recreate the publication and subscription and do 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
> "Tim Ford" <tim.ford@.nospamrubbishin2focus.com> wrote in message
> news:eSsLlVhrFHA.3720@.TK2MSFTNGP14.phx.gbl...
> to
> process.
> is
>
|||It would remain there. This is probably not what you want, so you would have
to delete on both sides.
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
"Tim Ford" <tim.ford@.nospamrubbishin2focus.com> wrote in message
news:OBtaphsrFHA.240@.tk2msftngp13.phx.gbl...
> Hi Hilary,
> Thanks for that. What would happen to the information that was in the
table[vbcol=seagreen]
> on the subscriber? Would that get deleted locally, as this information
> would no longer be needed.
> Again Thanks for all you help.
> Tim.
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23HJ2hOjrFHA.2592@.TK2MSFTNGP09.phx.gbl...
delete),[vbcol=seagreen]
no[vbcol=seagreen]
the[vbcol=seagreen]
order[vbcol=seagreen]
with[vbcol=seagreen]
need[vbcol=seagreen]
are[vbcol=seagreen]
for[vbcol=seagreen]
a[vbcol=seagreen]
SQL[vbcol=seagreen]
Is[vbcol=seagreen]
that[vbcol=seagreen]
for
>
Wednesday, March 21, 2012
Deleting Conflict tables
Hi,
While trying Merge replication for various publications, many conflict
tables were created in the publisher database. Even after deleting the
publications, they haven't gone off and neither can I delete them.
Could someone suggest, how to delete the conflict tables?
Thanks!
PS.
Sometimes the sp_removedbreplication will not remove them. If you ALTER the
tables, then you should be able to just drop them.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||Hi,
Thanks for the response. I tried deleting them through Enterprise
Manager and I got errors, but for some reason, I am able to drop the
conflict tables by dropping them from query analyzer.
PS.
rboyd@.onlinemicrosoft.com (Rand Boyd [MSFT]) wrote in message news:<g3Y0gn6UEHA.1996@.cpmsftngxa10.phx.gbl>...
> Sometimes the sp_removedbreplication will not remove them. If you ALTER the
> tables, then you should be able to just drop them.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
sql
While trying Merge replication for various publications, many conflict
tables were created in the publisher database. Even after deleting the
publications, they haven't gone off and neither can I delete them.
Could someone suggest, how to delete the conflict tables?
Thanks!
PS.
Sometimes the sp_removedbreplication will not remove them. If you ALTER the
tables, then you should be able to just drop them.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||Hi,
Thanks for the response. I tried deleting them through Enterprise
Manager and I got errors, but for some reason, I am able to drop the
conflict tables by dropping them from query analyzer.
PS.
rboyd@.onlinemicrosoft.com (Rand Boyd [MSFT]) wrote in message news:<g3Y0gn6UEHA.1996@.cpmsftngxa10.phx.gbl>...
> Sometimes the sp_removedbreplication will not remove them. If you ALTER the
> tables, then you should be able to just drop them.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
sql
Sunday, March 11, 2012
Deletes/Inserts in Merge replication
Hi,
I am using mergereplication for doing .1 miliion records /day. I find the
replication is getting stuck because of a few deletes/updates.
Is there a better way to handle this?
Ravi,
when you mention that it is getting stuck, what exactly is the error
message - please can you post it up.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
I am using mergereplication for doing .1 miliion records /day. I find the
replication is getting stuck because of a few deletes/updates.
Is there a better way to handle this?
Ravi,
when you mention that it is getting stuck, what exactly is the error
message - please can you post it up.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Deletes on subscriber in merge replication
Using Merge replication between SQL Server 2000 and SQL Server CE,
is there any way that row deletes could occur on the subscriber without a reinitialize
or explicit delete of row on publisher.
More specifically, if there is a row filter that returns a days worth of data with each
days pull, for example, and the filter looked like
select <columns> from Table where UpdateDate < GETDATE() and UpdateDate >= DATEADD(d,1,GETDATE())
would there be some implicit delete at subscriber each day because data sent changed?
My research indicates this does not happen, but I have a colleague who thinks differently.
Merge replication is trigger-based, meaning a change has to happen for the filter to be evaluated. So basically your filter will not guarantee your subscriber to have rows that meet your filter criteria without some process that does some sort of dummy update to the rows for it to be processed.
However please look at this best practice topic "Best Practices for Time-Based Row Filters": http://msdn2.microsoft.com/en-us/library/ms365153.aspx, it shows how you can accomplish what you want.
|||Thanks for the clarification. I have been able to convince myself that deletes occur on the subscriber for records outside the filter if I apply some update. I am trying to help a customer with replication performance issues. The application does a reinitialize on every request from the user for synchronization. This is sending 10000 or more rows. When I asked them why they do a reinit every time, they were not sure (the original programmer is gone), but they said there was a problem with deletes taking so long and it was better to just reinit. That is why I asked the original question. I did not know what deletes they could be talking about.
I am curious about the deleted records. It seems that the server must be sending over deletes for every updated record outside the filter. In my case, the filter is not only time based but uses HOST_NAME(). There are about 1000 handhelds out there. So if I used the method of the best-practices article, it would seem that with each synch I would be sending over deletes for all 1000 users each time.
Maybe that was what the original programmer did and what was taking too much time (even though there would be no actual delete, because those other records were never there).
Is this the behavior that would occur?
Thanks again
Ed Santosusso
|||In SQL 2000, yes deletes are much slower compared to inserts and updates, but this has been improved in SQL 2005 provide you use the new pre-computed partitions.
|||Moving data in and out of a partition can send over deletes, it's the only way to remove the data from the subscriber. And in SQL 2000, yes deletes are much slower compared to inserts and updates, but this has been improved in SQL 2005 provide you use the new pre-computed partitions.
Deletes occurring at publisher
I'm using Merge replication on a table with an autoidentity field. All
users are entering data at the publisher (no records are ever deleted),
and at the subscribers the data is read-only. I'm having a problem
with records being deleted at the publisher. I'm assuming it has to do
with a subscriber having problems, but I can't track it down.
Moving forward (SQL 2005), I want to change the replication type to
transactional. Could someone please tell me the best type of
replication to use in order to prevent records from being deleted at
the publisher?
Any help would be greatly appreciated.
Thanks,
Amy Marshall
Does anything show up in the conflict viewer? The deletes could be logged
here. Are you using join filters? These can cause these types of deletes,
but normally not on the publisher.
Transactional replication is designed for one way replication. It is not
clear to me what your data flow requirements are. With transactional
replication users will be able to delete data in any location, but only
deletes occurring on the publisher will be replicated to the subscriber.
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
<marshallae@.bowater.com> wrote in message
news:1138635297.513324.39100@.g47g2000cwa.googlegro ups.com...
> I'm using Merge replication on a table with an autoidentity field. All
> users are entering data at the publisher (no records are ever deleted),
> and at the subscribers the data is read-only. I'm having a problem
> with records being deleted at the publisher. I'm assuming it has to do
> with a subscriber having problems, but I can't track it down.
> Moving forward (SQL 2005), I want to change the replication type to
> transactional. Could someone please tell me the best type of
> replication to use in order to prevent records from being deleted at
> the publisher?
> Any help would be greatly appreciated.
> Thanks,
> Amy Marshall
>
|||Amy,
the issue might be caused by compensating changes. Please take a look at :
http://support.microsoft.com/default...&Product=sql2k
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||This morning I had 264 conflicts, which I kept to make sure I got the
deleted records back into the table, but this was the first time there
were any conflicts at all for that table. We are not using any join
filters on this table. I was thinking of using transactional
replication, because all entries into the problem table are done at the
publisher, and the inputs into that table are synched to the
subscribers based on a timeframe that is set from an application which
determines sync times. (Most users set synch times anywhere from 60
minutes - 240 minutes).
What's weird, is that there is a table that is in the same merge
package, that links to an ID in the problem table, but those records
are NEVER deleted at the publisher (no foreign key constraint exists
between the 2 tables). This one table seems to have the problem, and
while monitoring replication, I rarely ever see problems with
subscribers and this package.
Thanks for your input.
Amy
users are entering data at the publisher (no records are ever deleted),
and at the subscribers the data is read-only. I'm having a problem
with records being deleted at the publisher. I'm assuming it has to do
with a subscriber having problems, but I can't track it down.
Moving forward (SQL 2005), I want to change the replication type to
transactional. Could someone please tell me the best type of
replication to use in order to prevent records from being deleted at
the publisher?
Any help would be greatly appreciated.
Thanks,
Amy Marshall
Does anything show up in the conflict viewer? The deletes could be logged
here. Are you using join filters? These can cause these types of deletes,
but normally not on the publisher.
Transactional replication is designed for one way replication. It is not
clear to me what your data flow requirements are. With transactional
replication users will be able to delete data in any location, but only
deletes occurring on the publisher will be replicated to the subscriber.
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
<marshallae@.bowater.com> wrote in message
news:1138635297.513324.39100@.g47g2000cwa.googlegro ups.com...
> I'm using Merge replication on a table with an autoidentity field. All
> users are entering data at the publisher (no records are ever deleted),
> and at the subscribers the data is read-only. I'm having a problem
> with records being deleted at the publisher. I'm assuming it has to do
> with a subscriber having problems, but I can't track it down.
> Moving forward (SQL 2005), I want to change the replication type to
> transactional. Could someone please tell me the best type of
> replication to use in order to prevent records from being deleted at
> the publisher?
> Any help would be greatly appreciated.
> Thanks,
> Amy Marshall
>
|||Amy,
the issue might be caused by compensating changes. Please take a look at :
http://support.microsoft.com/default...&Product=sql2k
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||This morning I had 264 conflicts, which I kept to make sure I got the
deleted records back into the table, but this was the first time there
were any conflicts at all for that table. We are not using any join
filters on this table. I was thinking of using transactional
replication, because all entries into the problem table are done at the
publisher, and the inputs into that table are synched to the
subscribers based on a timeframe that is set from an application which
determines sync times. (Most users set synch times anywhere from 60
minutes - 240 minutes).
What's weird, is that there is a table that is in the same merge
package, that links to an ID in the problem table, but those records
are NEVER deleted at the publisher (no foreign key constraint exists
between the 2 tables). This one table seems to have the problem, and
while monitoring replication, I rarely ever see problems with
subscribers and this package.
Thanks for your input.
Amy
Friday, March 9, 2012
Deleted record
Suppose I delete a record in a database at 12pm.
And one staff modified the same record in another database at 1 pm.
Aftere the merge replication, does that record still in both databases or
not.
By default a row deleted at the publisher will be replicated to all
subscribers as a delete. The updated row on the subscriber would be logged
as a delete. A delete originating at all subscribers would be logged as a
conflict and be replaced with an update flowing from the subscriber.
If a row was deleted at a subscriber and then updated at another subscriber
the first one in would remain, and the later one would be rolled back and
logged in the conflict table.
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
"Man Utd" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:%23oN$CLqKGHA.140@.TK2MSFTNGP12.phx.gbl...
> Suppose I delete a record in a database at 12pm.
> And one staff modified the same record in another database at 1 pm.
> Aftere the merge replication, does that record still in both databases or
> not.
>
|||Not really.
If you had the following:
UserA deletes row at noon on Server1 and UserB updates that row on Server2
at 1PM, if the merge engine does not synchronize between the delete and the
update, then you have created a conflict that I refer to "update of a
non-existent row". In this case, if your conflict resolution states that
Server1 should win, then the delete will go through to both servers and the
update will be logged in the conflict table at the publisher. If your
conflict resolution states that Server2 should win, then the update is
applied to both servers and the delete is logged to the conflict table at
the publisher.
However, if UserA deletes row at noon on Server1 and UserB updates that row
on Server2 at 1PM, and the merge engine performed a synchronization cycle
between the delete and the update, then nothing would happen. The update
would change zero rows since the row would not exist in the database. Since
no rows had changed, the merge update trigger would simply return without
logging anything into MSmerge_contents.
The piece of the merge trigger that governs this behavior is as follows:
declare @.article_rows_updated int
select @.article_rows_updated = count(*) from inserted
if @.article_rows_updated=0
return
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:eCpAC8sKGHA.2064@.TK2MSFTNGP11.phx.gbl...
> By default a row deleted at the publisher will be replicated to all
> subscribers as a delete. The updated row on the subscriber would be logged
> as a delete. A delete originating at all subscribers would be logged as a
> conflict and be replaced with an update flowing from the subscriber.
> If a row was deleted at a subscriber and then updated at another
> subscriber the first one in would remain, and the later one would be
> rolled back and logged in the conflict table.
> --
> 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
> "Man Utd" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
> news:%23oN$CLqKGHA.140@.TK2MSFTNGP12.phx.gbl...
>
And one staff modified the same record in another database at 1 pm.
Aftere the merge replication, does that record still in both databases or
not.
By default a row deleted at the publisher will be replicated to all
subscribers as a delete. The updated row on the subscriber would be logged
as a delete. A delete originating at all subscribers would be logged as a
conflict and be replaced with an update flowing from the subscriber.
If a row was deleted at a subscriber and then updated at another subscriber
the first one in would remain, and the later one would be rolled back and
logged in the conflict table.
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
"Man Utd" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:%23oN$CLqKGHA.140@.TK2MSFTNGP12.phx.gbl...
> Suppose I delete a record in a database at 12pm.
> And one staff modified the same record in another database at 1 pm.
> Aftere the merge replication, does that record still in both databases or
> not.
>
|||Not really.
If you had the following:
UserA deletes row at noon on Server1 and UserB updates that row on Server2
at 1PM, if the merge engine does not synchronize between the delete and the
update, then you have created a conflict that I refer to "update of a
non-existent row". In this case, if your conflict resolution states that
Server1 should win, then the delete will go through to both servers and the
update will be logged in the conflict table at the publisher. If your
conflict resolution states that Server2 should win, then the update is
applied to both servers and the delete is logged to the conflict table at
the publisher.
However, if UserA deletes row at noon on Server1 and UserB updates that row
on Server2 at 1PM, and the merge engine performed a synchronization cycle
between the delete and the update, then nothing would happen. The update
would change zero rows since the row would not exist in the database. Since
no rows had changed, the merge update trigger would simply return without
logging anything into MSmerge_contents.
The piece of the merge trigger that governs this behavior is as follows:
declare @.article_rows_updated int
select @.article_rows_updated = count(*) from inserted
if @.article_rows_updated=0
return
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:eCpAC8sKGHA.2064@.TK2MSFTNGP11.phx.gbl...
> By default a row deleted at the publisher will be replicated to all
> subscribers as a delete. The updated row on the subscriber would be logged
> as a delete. A delete originating at all subscribers would be logged as a
> conflict and be replaced with an update flowing from the subscriber.
> If a row was deleted at a subscriber and then updated at another
> subscriber the first one in would remain, and the later one would be
> rolled back and logged in the conflict table.
> --
> 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
> "Man Utd" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
> news:%23oN$CLqKGHA.140@.TK2MSFTNGP12.phx.gbl...
>
Friday, February 24, 2012
Delete Replication per TSQL
Hello,
i use MSDE and try to setup a merge replication. I want to drop the
replicated db, but get some error. Sorry for the german error msg:
1> sp_helpdistributor
2> go
distributor
distribution database
directory
account
min distrib retention
max distrib retention history retention history cleanup agent
distribution cleanup agent
rpc server name
rpc login name
---
---
---
---
---
--- --
-- --
--
---
---
distrib
D:\Program Files\MSSQL\Data\MSSQL$SUDSQLDB\RepData
LocalSystem
0
72 48 Agentverlaufscleanup:
distrib
Verteilungscleanup: distrib
repl_distributor
distributor_admin
1> sp_helpserver
2> go
name
network_name status
id
collation_name
connect_timeout query_timeout
---
-- --
---
-- --
rpc,rpc out,use remote collation
0
NULL
0 0
repl_distributor
rpc,dist,rpc out,system,use remote collation
1
NULL
0 0
1> sp_dropdistributor
2> go
Meldung 14121, Ebene 16, Status 1, Server XXX, Prozedur
sp_dropdistributor, Zeile 150
Der Verteiler 'XXX' konnte nicht gelscht werden. Dieser Verteiler
besitzt zugeordnete Verteilungsdatenbanken.
1> sp_dropdistributiondb distrib
2> go
Meldung 14120, Ebene 16, Status 1, Server BW1H049C\SUDSQLDB, Prozedur
sp_dropdistributiondb, Zeile 49
Die distrib-Verteilungsdatenbank konnte nicht gelscht werden. Diese
Verteilerdatenbank ist einem Verleger zugeordnet.
1> sp_dropdistpublisher
2> go
Meldung 201, Ebene 16, Status 3, Server BW1H049C\SUDSQLDB, Prozedur
sp_dropdistpublisher, Zeile 0
Die sp_dropdistpublisher-Prozedur erwartet den @.publisher-Parameter;
dieser wurde nicht bergeben.
1> sp_dropdistpublisher 'BW1H049C\SUDSQLDB'
2> go
Meldung 21033, Ebene 16, Status 1, Server BW1H049C\SUDSQLDB, Prozedur
sp_dropdistpublisher, Zeile 99
Der Server 'BW1H049C\SUDSQLDB' kann nicht als Verteilungsverleger
gelscht werden, da auf diesem Server Datenbanken fr die Replikation
aktiviert
sind.
1> sp_dropmergepublication @.publication=N'TTPMerge'
2> go
Meldung 21147, Ebene 16, Status 1, Server BW1H049C\SUDSQLDB, Prozedur
sp_MSCheckmergereplication, Zeile 12
Die master-Datenbank wurde nicht fr die Mergereplikation publiziert.
1> sp_helpmergepublication
2> go
How can i delete safely all replication entries so i can drop the db ?
Are there any docs beside BOL to Replication per TSQL ? I see the book
of Hilary and quick check the testchapter , are there more chapter
about replication with TSQL ?
Thx Baumgart
right click on your publication and select delete. Then go to Tools,
Replication, configure distributor, publishers, and subscribers and select
the publication databases tab. Uncheck the sahred databse you wish to drop.
Accept the defaults. Then you should be able to drop the database.
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
"Alexander Baumgart" <al.baumgart@.gmx.de> wrote in message
news:d586cm$g87$1@.mail1.sbs.de...
> Hello,
> i use MSDE and try to setup a merge replication. I want to drop the
> replicated db, but get some error. Sorry for the german error msg:
> 1> sp_helpdistributor
> 2> go
> distributor
> distribution database
> directory
>
> account
>
> min distrib retention
> max distrib retention history retention history cleanup agent
> distribution cleanup agent
> rpc server name
> rpc login name
>
>
>
>
> --- --
> -- --
> --
> --
>
> distrib
> D:\Program Files\MSSQL\Data\MSSQL$SUDSQLDB\RepData
>
> LocalSystem
>
> 0
> 72 48 Agentverlaufscleanup:
> distrib
> Verteilungscleanup: distrib
> repl_distributor
> distributor_admin
> 1> sp_helpserver
> 2> go
> name
> network_name status
> id
> collation_name
> connect_timeout query_timeout
> --
> -- --
> -- --
> rpc,rpc out,use remote collation
> 0
> NULL
> 0 0
> repl_distributor
> rpc,dist,rpc out,system,use remote collation
> 1
> NULL
> 0 0
> 1> sp_dropdistributor
> 2> go
> Meldung 14121, Ebene 16, Status 1, Server XXX, Prozedur
> sp_dropdistributor, Zeile 150
> Der Verteiler 'XXX' konnte nicht gelscht werden. Dieser Verteiler
> besitzt zugeordnete Verteilungsdatenbanken.
> 1> sp_dropdistributiondb distrib
> 2> go
> Meldung 14120, Ebene 16, Status 1, Server BW1H049C\SUDSQLDB, Prozedur
> sp_dropdistributiondb, Zeile 49
> Die distrib-Verteilungsdatenbank konnte nicht gelscht werden. Diese
> Verteilerdatenbank ist einem Verleger zugeordnet.
> 1> sp_dropdistpublisher
> 2> go
> Meldung 201, Ebene 16, Status 3, Server BW1H049C\SUDSQLDB, Prozedur
> sp_dropdistpublisher, Zeile 0
> Die sp_dropdistpublisher-Prozedur erwartet den @.publisher-Parameter;
> dieser wurde nicht bergeben.
> 1> sp_dropdistpublisher 'BW1H049C\SUDSQLDB'
> 2> go
> Meldung 21033, Ebene 16, Status 1, Server BW1H049C\SUDSQLDB, Prozedur
> sp_dropdistpublisher, Zeile 99
> Der Server 'BW1H049C\SUDSQLDB' kann nicht als Verteilungsverleger
> gelscht werden, da auf diesem Server Datenbanken fr die Replikation
> aktiviert
> sind.
> 1> sp_dropmergepublication @.publication=N'TTPMerge'
> 2> go
> Meldung 21147, Ebene 16, Status 1, Server BW1H049C\SUDSQLDB, Prozedur
> sp_MSCheckmergereplication, Zeile 12
> Die master-Datenbank wurde nicht fr die Mergereplikation publiziert.
> 1> sp_helpmergepublication
> 2> go
> How can i delete safely all replication entries so i can drop the db ?
> Are there any docs beside BOL to Replication per TSQL ? I see the book
> of Hilary and quick check the testchapter , are there more chapter
> about replication with TSQL ?
> Thx Baumgart
|||Hilary Cotter wrote:
+AD4- right click on your publication and select delete. Then go to
Tools,
+AD4- Replication, configure distributor, publishers, and subscribers
and
+AD4- select the publication databases tab. Uncheck the sahred databse
you
+AD4- wish to drop.
+AD4-
+AD4- Accept the defaults. Then you should be able to drop the database.
I didnt have any tools , like i told +ADs-(( Only MSDE. I set all per
SP ,
and tried the reverse way to drop the replication but that didnt work.
What i tried, error msg got translate by me from german to english:
a) 1+AD4- sp+AF8-dropmergepublication +AEA-publication+AD0-N'TTPMerge'
Meldung 21147, Ebene 16, Status 1, Server BW1H049C+AFw-SUDSQLDB,
Prozedur
sp+AF8-MSCheckmergereplication, Zeile 12
Die master-Datenbank wurde nicht f+APw-r die Mergereplikation
publiziert.
(the master database didnt get shared per mergereplication)
b) 1+AD4- sp+AF8-dropdistpublisher 'BW1H049C+AFw-SUDSQLDB'
2+AD4- go
Meldung 21033, Ebene 16, Status 1, Server BW1H049C+AFw-SUDSQLDB,
Prozedur
sp+AF8-dropdistpublisher, Zeile 99
Der Server 'xxx+AFw-SUDSQLDB' kann nicht als Verteilungsverleger
gel+APY-scht werden, da auf diesem Server Datenbanken f+APw-r die
Replikation
aktiviert sind.
(the server xxx+AFw-instancename couldnt be delete as distributor,
there are database active for replication)
c) 1+AD4- sp+AF8-dropdistributiondb distrib
2+AD4- go
Meldung 14120, Ebene 16, Status 1, Server BW1H049C+AFw-SUDSQLDB,
Prozedur
sp+AF8-dropdistributiondb, Zeile 49
Die distrib-Verteilungsdatenbank konnte nicht gel+APY-scht werden. Diese
Verteilerdatenbank ist einem Verleger zugeordnet.
(the distrib - distributiondb couldnt be deleted, its assigned to a
distributor)
d) 1+AD4- sp+AF8-dropdistributor
2+AD4- go
Meldung 14121, Ebene 16, Status 1, Server XXX, Prozedur
sp+AF8-dropdistributor, Zeile 150
Der Verteiler 'XXX' konnte nicht gel+APY-scht werden. Dieser Verteiler
besitzt zugeordnete Verteilungsdatenbanken.
(the distributor +ACI-xxx+AFw-instancename+ACI- couldnt be deleted ,
its assigned with distribution-databases)
so how can i delete a merge-replication per osql.exe (TSQL) ?
Thx for your help Hilary , i will see if i can use the GUI way to find
the right SP's to delete the merge replication.
|||Hilary Cotter wrote:
+AD4- right click on your publication and select delete. Then go to
Tools,
+AD4- Replication, configure distributor, publishers, and subscribers
and
+AD4- select the publication databases tab. Uncheck the sahred databse
you
+AD4- wish to drop.
+AD4-
+AD4- Accept the defaults. Then you should be able to drop the database.
Hello,
after 2 day of work i was able to drop the merge replication. The
important cmd was sp+AF8-removedbreplication, as mentioned in
http://support.microsoft.com/default...en-us+ADs-3244
01
I check your book specially the example chapter where u described a way
to drop a replication, that also didnt work for me, because there was a
replication active. How deep u discusss manual setup of replication in
your book (in the example chapter u say more in chapter 6) ?
thx Baumgart
i use MSDE and try to setup a merge replication. I want to drop the
replicated db, but get some error. Sorry for the german error msg:
1> sp_helpdistributor
2> go
distributor
distribution database
directory
account
min distrib retention
max distrib retention history retention history cleanup agent
distribution cleanup agent
rpc server name
rpc login name
---
---
---
---
---
--- --
-- --
--
---
---
distrib
D:\Program Files\MSSQL\Data\MSSQL$SUDSQLDB\RepData
LocalSystem
0
72 48 Agentverlaufscleanup:
distrib
Verteilungscleanup: distrib
repl_distributor
distributor_admin
1> sp_helpserver
2> go
name
network_name status
id
collation_name
connect_timeout query_timeout
---
-- --
---
-- --
rpc,rpc out,use remote collation
0
NULL
0 0
repl_distributor
rpc,dist,rpc out,system,use remote collation
1
NULL
0 0
1> sp_dropdistributor
2> go
Meldung 14121, Ebene 16, Status 1, Server XXX, Prozedur
sp_dropdistributor, Zeile 150
Der Verteiler 'XXX' konnte nicht gelscht werden. Dieser Verteiler
besitzt zugeordnete Verteilungsdatenbanken.
1> sp_dropdistributiondb distrib
2> go
Meldung 14120, Ebene 16, Status 1, Server BW1H049C\SUDSQLDB, Prozedur
sp_dropdistributiondb, Zeile 49
Die distrib-Verteilungsdatenbank konnte nicht gelscht werden. Diese
Verteilerdatenbank ist einem Verleger zugeordnet.
1> sp_dropdistpublisher
2> go
Meldung 201, Ebene 16, Status 3, Server BW1H049C\SUDSQLDB, Prozedur
sp_dropdistpublisher, Zeile 0
Die sp_dropdistpublisher-Prozedur erwartet den @.publisher-Parameter;
dieser wurde nicht bergeben.
1> sp_dropdistpublisher 'BW1H049C\SUDSQLDB'
2> go
Meldung 21033, Ebene 16, Status 1, Server BW1H049C\SUDSQLDB, Prozedur
sp_dropdistpublisher, Zeile 99
Der Server 'BW1H049C\SUDSQLDB' kann nicht als Verteilungsverleger
gelscht werden, da auf diesem Server Datenbanken fr die Replikation
aktiviert
sind.
1> sp_dropmergepublication @.publication=N'TTPMerge'
2> go
Meldung 21147, Ebene 16, Status 1, Server BW1H049C\SUDSQLDB, Prozedur
sp_MSCheckmergereplication, Zeile 12
Die master-Datenbank wurde nicht fr die Mergereplikation publiziert.
1> sp_helpmergepublication
2> go
How can i delete safely all replication entries so i can drop the db ?
Are there any docs beside BOL to Replication per TSQL ? I see the book
of Hilary and quick check the testchapter , are there more chapter
about replication with TSQL ?
Thx Baumgart
right click on your publication and select delete. Then go to Tools,
Replication, configure distributor, publishers, and subscribers and select
the publication databases tab. Uncheck the sahred databse you wish to drop.
Accept the defaults. Then you should be able to drop the database.
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
"Alexander Baumgart" <al.baumgart@.gmx.de> wrote in message
news:d586cm$g87$1@.mail1.sbs.de...
> Hello,
> i use MSDE and try to setup a merge replication. I want to drop the
> replicated db, but get some error. Sorry for the german error msg:
> 1> sp_helpdistributor
> 2> go
> distributor
> distribution database
> directory
>
> account
>
> min distrib retention
> max distrib retention history retention history cleanup agent
> distribution cleanup agent
> rpc server name
> rpc login name
>
>
>
>
> --- --
> -- --
> --
> --
>
> distrib
> D:\Program Files\MSSQL\Data\MSSQL$SUDSQLDB\RepData
>
> LocalSystem
>
> 0
> 72 48 Agentverlaufscleanup:
> distrib
> Verteilungscleanup: distrib
> repl_distributor
> distributor_admin
> 1> sp_helpserver
> 2> go
> name
> network_name status
> id
> collation_name
> connect_timeout query_timeout
> --
> -- --
> -- --
> rpc,rpc out,use remote collation
> 0
> NULL
> 0 0
> repl_distributor
> rpc,dist,rpc out,system,use remote collation
> 1
> NULL
> 0 0
> 1> sp_dropdistributor
> 2> go
> Meldung 14121, Ebene 16, Status 1, Server XXX, Prozedur
> sp_dropdistributor, Zeile 150
> Der Verteiler 'XXX' konnte nicht gelscht werden. Dieser Verteiler
> besitzt zugeordnete Verteilungsdatenbanken.
> 1> sp_dropdistributiondb distrib
> 2> go
> Meldung 14120, Ebene 16, Status 1, Server BW1H049C\SUDSQLDB, Prozedur
> sp_dropdistributiondb, Zeile 49
> Die distrib-Verteilungsdatenbank konnte nicht gelscht werden. Diese
> Verteilerdatenbank ist einem Verleger zugeordnet.
> 1> sp_dropdistpublisher
> 2> go
> Meldung 201, Ebene 16, Status 3, Server BW1H049C\SUDSQLDB, Prozedur
> sp_dropdistpublisher, Zeile 0
> Die sp_dropdistpublisher-Prozedur erwartet den @.publisher-Parameter;
> dieser wurde nicht bergeben.
> 1> sp_dropdistpublisher 'BW1H049C\SUDSQLDB'
> 2> go
> Meldung 21033, Ebene 16, Status 1, Server BW1H049C\SUDSQLDB, Prozedur
> sp_dropdistpublisher, Zeile 99
> Der Server 'BW1H049C\SUDSQLDB' kann nicht als Verteilungsverleger
> gelscht werden, da auf diesem Server Datenbanken fr die Replikation
> aktiviert
> sind.
> 1> sp_dropmergepublication @.publication=N'TTPMerge'
> 2> go
> Meldung 21147, Ebene 16, Status 1, Server BW1H049C\SUDSQLDB, Prozedur
> sp_MSCheckmergereplication, Zeile 12
> Die master-Datenbank wurde nicht fr die Mergereplikation publiziert.
> 1> sp_helpmergepublication
> 2> go
> How can i delete safely all replication entries so i can drop the db ?
> Are there any docs beside BOL to Replication per TSQL ? I see the book
> of Hilary and quick check the testchapter , are there more chapter
> about replication with TSQL ?
> Thx Baumgart
|||Hilary Cotter wrote:
+AD4- right click on your publication and select delete. Then go to
Tools,
+AD4- Replication, configure distributor, publishers, and subscribers
and
+AD4- select the publication databases tab. Uncheck the sahred databse
you
+AD4- wish to drop.
+AD4-
+AD4- Accept the defaults. Then you should be able to drop the database.
I didnt have any tools , like i told +ADs-(( Only MSDE. I set all per
SP ,
and tried the reverse way to drop the replication but that didnt work.
What i tried, error msg got translate by me from german to english:
a) 1+AD4- sp+AF8-dropmergepublication +AEA-publication+AD0-N'TTPMerge'
Meldung 21147, Ebene 16, Status 1, Server BW1H049C+AFw-SUDSQLDB,
Prozedur
sp+AF8-MSCheckmergereplication, Zeile 12
Die master-Datenbank wurde nicht f+APw-r die Mergereplikation
publiziert.
(the master database didnt get shared per mergereplication)
b) 1+AD4- sp+AF8-dropdistpublisher 'BW1H049C+AFw-SUDSQLDB'
2+AD4- go
Meldung 21033, Ebene 16, Status 1, Server BW1H049C+AFw-SUDSQLDB,
Prozedur
sp+AF8-dropdistpublisher, Zeile 99
Der Server 'xxx+AFw-SUDSQLDB' kann nicht als Verteilungsverleger
gel+APY-scht werden, da auf diesem Server Datenbanken f+APw-r die
Replikation
aktiviert sind.
(the server xxx+AFw-instancename couldnt be delete as distributor,
there are database active for replication)
c) 1+AD4- sp+AF8-dropdistributiondb distrib
2+AD4- go
Meldung 14120, Ebene 16, Status 1, Server BW1H049C+AFw-SUDSQLDB,
Prozedur
sp+AF8-dropdistributiondb, Zeile 49
Die distrib-Verteilungsdatenbank konnte nicht gel+APY-scht werden. Diese
Verteilerdatenbank ist einem Verleger zugeordnet.
(the distrib - distributiondb couldnt be deleted, its assigned to a
distributor)
d) 1+AD4- sp+AF8-dropdistributor
2+AD4- go
Meldung 14121, Ebene 16, Status 1, Server XXX, Prozedur
sp+AF8-dropdistributor, Zeile 150
Der Verteiler 'XXX' konnte nicht gel+APY-scht werden. Dieser Verteiler
besitzt zugeordnete Verteilungsdatenbanken.
(the distributor +ACI-xxx+AFw-instancename+ACI- couldnt be deleted ,
its assigned with distribution-databases)
so how can i delete a merge-replication per osql.exe (TSQL) ?
Thx for your help Hilary , i will see if i can use the GUI way to find
the right SP's to delete the merge replication.
|||Hilary Cotter wrote:
+AD4- right click on your publication and select delete. Then go to
Tools,
+AD4- Replication, configure distributor, publishers, and subscribers
and
+AD4- select the publication databases tab. Uncheck the sahred databse
you
+AD4- wish to drop.
+AD4-
+AD4- Accept the defaults. Then you should be able to drop the database.
Hello,
after 2 day of work i was able to drop the merge replication. The
important cmd was sp+AF8-removedbreplication, as mentioned in
http://support.microsoft.com/default...en-us+ADs-3244
01
I check your book specially the example chapter where u described a way
to drop a replication, that also didnt work for me, because there was a
replication active. How deep u discusss manual setup of replication in
your book (in the example chapter u say more in chapter 6) ?
thx Baumgart
Sunday, February 19, 2012
Delete records from subscriber only
I have on distributed enviornment in which data is collected by remote clients and send to the central publisher using merge publication and filters. I need to purge old data from the subsriber and not in the server. How do I do this?
Thanks
Anand,
what if some of the old data is modified on the publisher in future, or
perhaps this is not possible? If it is then you'll have a problem. You could
leave all the data as it is on the subscriber and use views to effectively
remove it for the front-end application. If you need the name to be the same
on the subscriber as the publisher, you could replicate to a different
tablename on the subscriber and use a view name equal to the tablename on
the publisher. Of course this would require reinitializing though. Finally,
you could drop the subscription, add a filter then reinitialize.
HTH,
Paul Ibison
|||The data at the server won't change for those records, if it did and it is replicated to the subscriber occasionaly it won't be a problem. I was trying to find out whether there is a way not to replicate deletes in merge publication (Easily) without dropi
ing the subscription.
Thanks for input
Anand
|||Anand,
there is another solution - you could modify the merge replication triggers
so as to not register your deletes in MSmerge_tombstone, although this would
lead to an unsupported solution.
HTH,
Paul Ibison
|||the cleanest way to do this is using transactional replication with custom
stored procedures. Do you require tranasactions that require on the
subscriber to be replicated to the publisher?
"Anand" <anonymous@.discussions.microsoft.com> wrote in message
news:4FB33D2F-1310-4125-86A0-21EDB56E6388@.microsoft.com...
> I have on distributed enviornment in which data is collected by remote
clients and send to the central publisher using merge publication and
filters. I need to purge old data from the subsriber and not in the server.
How do I do this?
> Thanks
|||Yes. I have already setup a merge replication with 30 odd disconnected clients replicating to a central server. Most of the transactions actually happen at the clients. It is not possible for me change the replication model now. Is there a way to script a
nd tell the clients not to replicate deletes to the publisher.
Anand
|||Can we use alter table to disable all triggers on a table and delete the records and enable triggers. Will this work?
Thanks
Anand
|||Well I tried disabling the triggers, but you can not alter the tables which are replicated. I stoped the SQL server agent and deleted the msmerge_tombstone records after the delete on the subscriber and restarted the SQL server agent. It seems to work the
way I want it. Any caveats?
Thanks
Anand
Thanks
Anand,
what if some of the old data is modified on the publisher in future, or
perhaps this is not possible? If it is then you'll have a problem. You could
leave all the data as it is on the subscriber and use views to effectively
remove it for the front-end application. If you need the name to be the same
on the subscriber as the publisher, you could replicate to a different
tablename on the subscriber and use a view name equal to the tablename on
the publisher. Of course this would require reinitializing though. Finally,
you could drop the subscription, add a filter then reinitialize.
HTH,
Paul Ibison
|||The data at the server won't change for those records, if it did and it is replicated to the subscriber occasionaly it won't be a problem. I was trying to find out whether there is a way not to replicate deletes in merge publication (Easily) without dropi
ing the subscription.
Thanks for input
Anand
|||Anand,
there is another solution - you could modify the merge replication triggers
so as to not register your deletes in MSmerge_tombstone, although this would
lead to an unsupported solution.
HTH,
Paul Ibison
|||the cleanest way to do this is using transactional replication with custom
stored procedures. Do you require tranasactions that require on the
subscriber to be replicated to the publisher?
"Anand" <anonymous@.discussions.microsoft.com> wrote in message
news:4FB33D2F-1310-4125-86A0-21EDB56E6388@.microsoft.com...
> I have on distributed enviornment in which data is collected by remote
clients and send to the central publisher using merge publication and
filters. I need to purge old data from the subsriber and not in the server.
How do I do this?
> Thanks
|||Yes. I have already setup a merge replication with 30 odd disconnected clients replicating to a central server. Most of the transactions actually happen at the clients. It is not possible for me change the replication model now. Is there a way to script a
nd tell the clients not to replicate deletes to the publisher.
Anand
|||Can we use alter table to disable all triggers on a table and delete the records and enable triggers. Will this work?
Thanks
Anand
|||Well I tried disabling the triggers, but you can not alter the tables which are replicated. I stoped the SQL server agent and deleted the msmerge_tombstone records after the delete on the subscriber and restarted the SQL server agent. It seems to work the
way I want it. Any caveats?
Thanks
Anand
Tuesday, February 14, 2012
delete merge filter
I am currently running sql 2000 with 2 way merge replication... I have a problem of I need to delete about 3 million rows from a table that is constantly being accessed buy an e-commerce site. I have thought about setting up a filter to only replicate new or altered rows beyond a a certain indexed number... then delete from both sites the unneeded rows... this should work.. but can I delete the filter after I am done?
If this is not an option, any other suggestions? I cannot take either site offline and I cannot do anything that will take more than a few seconds to replicate... by deleting these rows. my table size will be reduced by almost 8GB...
thanks
Hi
There are several options for filtering data on merge publications here is some more information.
http://msdn2.microsoft.com/en-us/library/ms151775.aspx
These options were developed for scenarios like your where some of the subscribers only need a subset of the data.
As the filter is part of the article you can change the article to remove the filter if you don't need it anymore.
As your e-commerce site cannot afford any down time, I'd suggest you review docs to be sure you select the correct filtering solution.
When merge replication is setup correctly there should be mimimal downtime. There are also additional performance enhanments made in SQL Server 2005 for partition evaluation.
Thanks
Subscribe to:
Posts (Atom)