SQL 2008 Replication corrupt data problem
- by Jonathan K
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.
