Wednesday, November 15, 2006
Replication and Relationships
This was one I struggled with for a long time. I was attempting to publish a SQL Server 2000 database, using merge replication. Everything seemed to work fine. I set up the publisher and distributor as per the books. I Published the database. I had to add GUID's and spent some time removing identity columns (demoting them to just INTs) wherever I could in the database. I learned a lot about identity range management. When all was said and done, I then subscribed to the database. The subscription went smoothly. I tested, and replication seemed to be working. Wow, I thought. Just like in the books. Soon thereafter, I discovered how horribly wrong I was.
Since the testing phase is that part of the development cycle where errors are guaranteed to fail to appear, my database replicated flawlessly. So, after a few weeks of testing, it went into production. Production is the phase after development where errors occur. I suddenly started receiving replication errors. The typical error was:
- Msg 548, Level 16, State 2, Server %s, Line 1 The identity range managed by replication is full and must be updated by a replication agent. The INSERT conflict occurred in database '%s', table '%s', column '%s'. Sp_adjustpublisheridentityrange can be called to get a new identity range. The statement has been terminated.
This is where I learned about Identity range management. Basically, when replicating identity values, each subscriber (and the publisher) is given their own range of identity values to use. When a subscriber gets about 80% of the way through its' range, it will communicate with the publisher and get a new range. So I republished with outrageous ranges for the identity values. I continued to get the same error. I ran DBCC CHECKIDENT('table', NORESEED) to see what the current values were. They were well within the 80%. I went ahead and reseeded anyway (DBCC CHECKIDENT('table', RESEED)) and promptly continued to receive error messages. I wailed. I cried. I gnashed my teeth. Despite all evidence to the contrary, SQL Server would continue to insist that my identity range was full.
Since it was completely clear that the identity range was not, in fact, full, it seemed to me that I must somehow be misunderstanding the message. Unfortunately, no matter how I examined the message it seemed to keep saying that the identity range was full. It mocked me. I noticed that the errors always seemed to occur in tables with foreign key dependencies. It began to dawn on me with glacial slowness that the error had nothing at all to do with identity ranges. In fact, it had nothing to do with identity values at all. Of Course! The error message seems so obvious now! Thank you Microsoft, for your clear and informative error messages! The error message had even gone so far as to include a solution that would not fix the problem.
What was happening was that, during replication, data would start getting pumped into ancillary tables (you know, the kind that hold phone numbers), but sometimes (not always) the agent would try to pump the data into the ancillary table before a matching entry in the primary table was present. This generated a relational error, and prevented the data from getting propagated across. By the time I went to the conflict viewer to resolve the conflicts, the primary table was populated, so I could accept the data and it would then propagate fine.
Once I realized that the true error was due to referential integrity, I began examining the relationships themselves. And I looked once more at the property called "Not for replication". The palm of my hand exploded upon my forehead! All the devices of Bill Gate's minions were now laid bare before me, and I realized my folly. I had looked at that option before, when I was first preparing the database for replication. And my first interpretation of that option was that by checking it, the relationship would not be enforced at the subscriber. Well, of course I wanted it enforced at the subscriber, so I left in unchecked. What the option really means, though, is that the relationship is not to be enforced during the replication process. The same meaning as the "not for replication" option for Identity values.
By declaring relationships as "not for replication", the problem was solved. I feel like an idiot. I mean, in retrospect, the error message is perfectly clear. At the risk of appearing exceedingly stupid, I have to ask: Why would you ever want a relationship enforced during replication? As the rules were enforced already during the initial data entry, you know that parent records have to exist. So why isn't "not for replication" the default setting? Why is it an option at all? Anyone?
What was happening was that, during replication, data would start getting pumped into ancillary tables (you know, the kind that hold phone numbers), but sometimes (not always) the agent would try to pump the data into the ancillary table before a matching entry in the primary table was present. This generated a relational error, and prevented the data from getting propagated across. By the time I went to the conflict viewer to resolve the conflicts, the primary table was populated, so I could accept the data and it would then propagate fine.
Once I realized that the true error was due to referential integrity, I began examining the relationships themselves. And I looked once more at the property called "Not for replication". The palm of my hand exploded upon my forehead! All the devices of Bill Gate's minions were now laid bare before me, and I realized my folly. I had looked at that option before, when I was first preparing the database for replication. And my first interpretation of that option was that by checking it, the relationship would not be enforced at the subscriber. Well, of course I wanted it enforced at the subscriber, so I left in unchecked. What the option really means, though, is that the relationship is not to be enforced during the replication process. The same meaning as the "not for replication" option for Identity values.
By declaring relationships as "not for replication", the problem was solved. I feel like an idiot. I mean, in retrospect, the error message is perfectly clear. At the risk of appearing exceedingly stupid, I have to ask: Why would you ever want a relationship enforced during replication? As the rules were enforced already during the initial data entry, you know that parent records have to exist. So why isn't "not for replication" the default setting? Why is it an option at all? Anyone?
Mission Statement
I cannot tell you the number of times I have struggled with some obscure (or obvious) problem, that no web site or book addressed. It often times seems as if no one else in the world ever has problems. The books often read like a reprint of the online help -- "click here, set this option to 'foo' and you're done!" Yet no matter how many times I compare what the book says to what I have done, it simply refuses to work. SQL Server will typically cough up verbose error messages that may or may not have any relation to reality. My biggest complaint about books is they never seem to be talking about my specific case. I think this means that I am doing everything wrong, and so thus the title of the blog.
So, when I struggle with such an issue, and I cannot find any obvious solutions in the books or online, I will post it here, along with the resulting solution (if any). The fact that the problem may be caused, in large part, by my bone headed approach is irrelevant. I work in a school district. I know I am not the only bonehead out there. Please feel free to submit any similar stories you have. Accepting your stupidity is only the first step. Letting everyone know just how stupid you are in a public forum is the next. This is why God made blogs.
So, when I struggle with such an issue, and I cannot find any obvious solutions in the books or online, I will post it here, along with the resulting solution (if any). The fact that the problem may be caused, in large part, by my bone headed approach is irrelevant. I work in a school district. I know I am not the only bonehead out there. Please feel free to submit any similar stories you have. Accepting your stupidity is only the first step. Letting everyone know just how stupid you are in a public forum is the next. This is why God made blogs.
