Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

We took a SQL 2000 database. Took a lightspeed backup. Restored on SQL 2008 active/passive cluster. Then setup replication to replicate the data back to SQL 2000. So 2008 is the publisher/distributor, and 2000 is doing a pull subscription. Everything works well, execpt we occassionally get corrupt data in varchar/text fields on the subscriber.

So for example we have a table with 4500 records. When we run this statement:

update MedstaffProvider set Notes = 'Cell Phone: 360.123.4567   Answering Service: 360.123.9876'
where LastName = 'smith'

The record in the 2008 database is updated as expected. But in the subsriber datbase we'll get gibberish in the notes field:

óPÌ[1]    T       $Oé[1]   ð²ñ.  K

Here's what we know:

  • This is repeatable, meaning we can run that same query all day long and get the same gibberish. If you alter update statement slightly the data gets replicated just fine.
  • The collation on both databases is the same.
  • So far we've only detected the problem with text/varchar fields. (The notes field above is text).
  • Only one or two records in a table are impacted.
  • The table structure looks identical in both 2000/2008. We haven't made any changes.

We have found one solution that fixes the problem. Basically if we recreate the table in 2008 (say as MedStaffProvider2) and then insert all the data. Drop the original table. Rename the table to it's original name. Setup replication again. And run the exact same update statement it works as expected.

Does anyone have any idea what might be happening here? Or are there any other techniques we can use to troubleshoot this? I've found a solution for this, but would really like to undertsand why this is happening.

share|improve this question
add comment

2 Answers

If you run SQL Profiler on the Distribution database and the subscriber database do you see the corrupted data on both the distribution database and the subscriber or just the subscriber?

Have you tried running the distribution agent on the Publisher/Distributor?

share|improve this answer
    
I'm not sure how I'd configure Profiler to monitor the subscriber. What type of event would be categorized under? On the publisher I do see that it's adding a CONVERT to my update statement. Perhaps this is somehow related: "Notes" = CONVERT(text,''The notes data'',0) We haven't tried setting up a push subscription. Do you think this is a better approach for a 2000/2008 setup? Also, we are replicating the raw insert/update/delete statements as it looked more efficient for our workload. –  Jonathan K Aug 31 '09 at 18:46
    
You'll have to watch the RPC and SQL events based on the computer and application that is calling them. It'll probably take a little trial and error to get the filter just right. I was thinking that a PUSH might be a better option so that the updated version of the distribution agent was doing the work, instead of the old version. –  mrdenny Sep 1 '09 at 2:50
add comment

Didn't SQL Server 2008 change the way it handles certain fields due to unicode or something like that?

This guide might give some guidance. Maybe you overlooked something in the migration?

Edit: Does this article point to anything that could be causing it?

share|improve this answer
    
Neither of those seems relevant. I ran the upgrade advisor again and it didn't seem to point out anything obvious. I've wondered if the Lightspeed backup could be a culprit. Also the second link is talking about bi-directional replication. We're only doing one-way replication. –  Jonathan K Aug 31 '09 at 18:48
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.