We have 3 databases, one publisher and two subscriber.
Merge-Replication, constantly replicated.
Because of unknown circumstances, the replication process failed for
over 14 days, and the publication had a retention time of 14 days.
I wasn=B4t detected earlier, and now is the great question:
HOW CAN I MERGE MY 3 DATABASES TOGETHER?
Is there a possibility to tell the subscribion, that they are NOT
obsolete?
To create a new snapshot won't work, i get a lot of errors afterwards.
To remove the subscriptions and setup the subscriptions anew, will
procede a well-running merge-replication, but with different data.
What about turning back the system time for a few days?
I would be very glad if you have an idea.
Andreas, you could manually synchronize the data using datacompare from
Redgate then do a nosync initialization.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul Ibison schrieb:
> Andreas, you could manually synchronize the data using datacompare from
> Redgate then do a nosync initialization.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
But SQL DataCompare can either copy from database A to B or from B to
A, but cannot merge, or am I wrong?
And I a dataset exists on site A, but not on B, does that mean, that
the dataset was new on A and should copy to B, or was the dataset
delete on B and should also be deleted on A?
|||there is no way in Merge, there is a way in transactional but its dangerous.
If you do reinitialize it will prompt you to upload changes from the
subscriber first which will send the subscribers changes to the publisher.
I think you need to investigate 1) what is causing the problem which makes
your subscriber go offline 2) what is causing these errors you get after
running the snapshot.
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
"Andreas Lauffer" <alweb01@.online.de> wrote in message
news:1128770684.895595.285420@.g44g2000cwa.googlegr oups.com...
We have 3 databases, one publisher and two subscriber.
Merge-Replication, constantly replicated.
Because of unknown circumstances, the replication process failed for
over 14 days, and the publication had a retention time of 14 days.
I wasnt detected earlier, and now is the great question:
HOW CAN I MERGE MY 3 DATABASES TOGETHER?
Is there a possibility to tell the subscribion, that they are NOT
obsolete?
To create a new snapshot won't work, i get a lot of errors afterwards.
To remove the subscriptions and setup the subscriptions anew, will
procede a well-running merge-replication, but with different data.
What about turning back the system time for a few days?
I would be very glad if you have an idea.
|||Andreas - you're quite right. To get this to work, you'll have to select
which one is the master in terms of updates to the same record on both
sites. If the updates are partitioned, then you can use this tool more
easily, but if there are a lot of conflicts then there is a big problem to
sort out, and only you can decide what is appropriate ie who wins these
conflicts, or in other words, which node is the master.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hillary,
thank you for your answer, but I don=B4t understand exacly what this
means for me.
I use merge-replication, not transactual replication.
What happens, if I reinitialize my databases? Will the subscribers
upload their data then?
What is so dangerous?
|||Unfortunately there is no master. Each of the 3 databases have the same
priority.
I cannot set anything aside.
What about my question:[vbcol=seagreen]
Do you think there is a chance to get a system which synchronizes the
data, without marking the subscibers as "obsolete"?
|||What do you think about this scenario:
1) Deleting the subscriptions
2) New pull Merge Subscriptions => Replication will run again, but with
different data
3) Restore the subscibers databases, state before deleting the
subscriptions
Do you think this will work to synchronize the databases?
If this works, maybe the changes from the publisher were not
transmittet to the subscriters yet, and I have to backup the subscribes
database and restore it as the subscribers database.
MAY THIS WORK?
|||Andreas,
this sounds feasible. You'll probably also need to change the value of the
status flag in sysmergesubscriptions. Alternatively (and probably easier)
you might be able to increase the activation/retention period and change
this flag. I haven't tried either approach and don't have any expired
subscriptions to test, so please let us know how you get on.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Yes, with merge replication when you reinitialize you are prompted to upload
the changes which happened on your subscribers first.
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
"Andreas Lauffer" <alweb01@.online.de> wrote in message
news:1128924652.854174.261410@.g47g2000cwa.googlegr oups.com...
Hillary,
thank you for your answer, but I dont understand exacly what this
means for me.
I use merge-replication, not transactual replication.
What happens, if I reinitialize my databases? Will the subscribers
upload their data then?
What is so dangerous?
Showing posts with label replicated. Show all posts
Showing posts with label replicated. Show all posts
Wednesday, March 28, 2012
Wednesday, March 21, 2012
HELP! More deleted records!
I have a replicated database and hundreds of records keep "disappearing" from
one table in particular. The table is a child table storing the answers to a
number of questions. Also;
1. The tables have referential integrity enforced, with cascade delete.
2. The relationships are enforced for replication.
3. There are no stored procedures which delete records from the offending
table.
4. There is no way of manually deleting records directly from the offending
table using the front-end application.
5. The parent tables have exactly the same records in as previously.
6. There are no replication conflicts.
Any ideas?
I am keeping transaction logs for 3 weeks. Is there any way that I can find
out from the transaction logs when the records were deleted? Is there any way
I can find out why the records would have been deleted?
Thanks,
Nigel Taylor
http://www.tinit.co.uk/
Nigel,
you don't say if this is on the publisher or on the subscriber, but I'm
guessing the latter. It could be that some updates are replicated as a
delete-insert pair for transactional replication in ceertain cases (see
http://support.microsoft.com/default...NoWebContent=1).
Perhaps this is your case?
Also I'm not clear why you're using cascading constraints for the subscriber
which is normally treated as RO - or maybe I have misunderstood?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi,
It's an ftp merge replication. I'm not sure where the records are
disappearing from first but the publisher is definately causing the other
subscribers (of which there are about 12) to lose the records too.
The relationships (i.e. cascade delete and enforce for replication) were
automatically created when the subscription was first created from the
publisher.
With the delete-insert pair the records would be recreated wouldn't they?
Thanks,
Nigel Taylor
http://www.tinit.co.uk/
|||In fact, when I use your spBrowseMergeChanges procedure on the offending
table, there are a load more records marked for deletion and without a
replacement!
Nigel Taylor
http://www.tinit.co.uk/
|||Nigel,
as you're using merge, and assuming you have all the related tables being
replicated, I'd modify all relationships to be not enforced for replication.
The delete-insert pairs are a problem for transactional and not merge, but
with merge there are other reasons the FK constraints are not applied (see
http://www.replicationanswers.com/Me...derArticle.asp). This
will simplify the setup somewhat and hopefully allow us to see if this is
the cause of the problems.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Okay, thanks Paul. I've removed the 'enforce relationship for replication'
flag and removed the cascade deletes on the offending tables. Hopefully it'll
work and I can get back to doing something else.
Cheers,
Nigel Taylor
http://www.tinit.co.uk/
one table in particular. The table is a child table storing the answers to a
number of questions. Also;
1. The tables have referential integrity enforced, with cascade delete.
2. The relationships are enforced for replication.
3. There are no stored procedures which delete records from the offending
table.
4. There is no way of manually deleting records directly from the offending
table using the front-end application.
5. The parent tables have exactly the same records in as previously.
6. There are no replication conflicts.
Any ideas?
I am keeping transaction logs for 3 weeks. Is there any way that I can find
out from the transaction logs when the records were deleted? Is there any way
I can find out why the records would have been deleted?
Thanks,
Nigel Taylor
http://www.tinit.co.uk/
Nigel,
you don't say if this is on the publisher or on the subscriber, but I'm
guessing the latter. It could be that some updates are replicated as a
delete-insert pair for transactional replication in ceertain cases (see
http://support.microsoft.com/default...NoWebContent=1).
Perhaps this is your case?
Also I'm not clear why you're using cascading constraints for the subscriber
which is normally treated as RO - or maybe I have misunderstood?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi,
It's an ftp merge replication. I'm not sure where the records are
disappearing from first but the publisher is definately causing the other
subscribers (of which there are about 12) to lose the records too.
The relationships (i.e. cascade delete and enforce for replication) were
automatically created when the subscription was first created from the
publisher.
With the delete-insert pair the records would be recreated wouldn't they?
Thanks,
Nigel Taylor
http://www.tinit.co.uk/
|||In fact, when I use your spBrowseMergeChanges procedure on the offending
table, there are a load more records marked for deletion and without a
replacement!
Nigel Taylor
http://www.tinit.co.uk/
|||Nigel,
as you're using merge, and assuming you have all the related tables being
replicated, I'd modify all relationships to be not enforced for replication.
The delete-insert pairs are a problem for transactional and not merge, but
with merge there are other reasons the FK constraints are not applied (see
http://www.replicationanswers.com/Me...derArticle.asp). This
will simplify the setup somewhat and hopefully allow us to see if this is
the cause of the problems.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Okay, thanks Paul. I've removed the 'enforce relationship for replication'
flag and removed the cascade deletes on the offending tables. Hopefully it'll
work and I can get back to doing something else.
Cheers,
Nigel Taylor
http://www.tinit.co.uk/
Subscribe to:
Posts (Atom)