Heads-up on an Important Merge Replication Bug in SQL Server 2005

Discovered by Patrick Molijn and investigated by Guy Barnard

This is a quick article provided by Patrick Molijn who has discovered a worrying bug in SQL Server 2005. DATA is not being sent from subscriber to publisher where a column has been dropped from the table.

From Patrick : "I contacted Microsoft, they acknowledge the bug, repro steps applied on RTM ,SP1 and POST SP1 and ALL expose the bug. Microsoft assigned the bug as critical and will be delivering a hotfix to our company very soon". No doubt this'll be publicly available soon and I'll post up info when this is the case.

So, why is some data not replicated on SQL 2005?

Here are the repro steps.

1) Create a database TestPub, with a table 'Test' having the following columns:
   a) rowguid
   b) Col1 varchar(50)
   c) Col2 varchar(50)
   d) Col3 varchar(50)
   e) Col4 varchar(50)
   f) Col5 varchar(50)

2) Delete Col3

3) Insert a row in the table

4) Backup database

5) Restore the database as TestSub

6) Create a Merge Publication with the table 'Test' as an article. For this article, ensure column_tracking is set to true

7) Create a Subscription - with NO initialise

Now when you make an update to Col4 (at the subscriber), it is NOT replicated.

Please be warned when using merge replication in SQL 2005 (RTM, SP1 and POST SP1 build 2153) for above scenario.!

Current workarounds:

(1) - drop subscriptions
- copy table data to temptable
- recreate table (so we do not have to delete columns!)
- copy data back in
- Re-establish replication.

(2) Do an automatic initialization.