Thread: replication errors
04-15-14, 15:44 #1Registered User
- Join Date
- Apr 2014
Unanswered: replication errors
Yes I am a dba for a very large company. We have a website that depends on transaction replication from a source server to one of our own. We lately have been having many errors and failures with said transactional pull replication.. One error that we get with great frequency is that either there are dropped tables and or permissions on the subscription database... Some Consultants said they were not sure but they thought that those dropped items were due to replication failure.. They said that replication drops and recreates all the tables and all the data all the time ie... continuously. How can that be with transactional replication that there is so much data dropping and being recreated that it can not be transactional???
Especially that this happens long after the snapshot was taken and is out of the blue with no correlation to any particular situation.. Please advise if this is a possibility that replication can cause dropped table or schema changes in the subscription database
04-16-14, 07:46 #2Annie's Dog Walker
Provided Answers: 6
- Join Date
- Nov 2004
- on the wrong server
It's been a while since I have dealt with replication or been a DBA, but I do remember before SQL Server 2008 there were a bunch of no-nos when it came to what Data Definition Language statements you could execute against a publisher and not totally jack up your replication. I read that some of this has gotten better in later versions, but I remember doing a lot of deployments on the publisher where we had to make changes to the published articles and re-snapshot and re-apply.
However, if tables are being dropped on your subscriber and that is the cause of your woes, why not just re-snapshot and apply it to the subscriber. That should fix you up.If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry. Earnest Hemingway, A Farewell To Arms.
04-16-14, 08:32 #3Resident Curmudgeon
Provided Answers: 54
- Join Date
- Feb 2004
- In front of the computer
Depending on the choices that the administrators make on the replication publisher, distributor, and subscriber it is possible for replication to drop tables at the subscriber. This is common when replication is configured incorrectly, and it is also a direct consequence of schema changes at the publisher.
You need to keep in mind that "database transactions" and "transactional replication" sound similar, but that they are completely different ideas. There are a number of common threads in the design of both, but they are quite distinct.
-PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.