SQL Replication Consternation
We've been experimenting with a proof-of-concept application using the .Net Compact Framework; right now, we're just exploring replicating data between a mobile PocketPC (with SQLServerCE) and a SqlServer. Plain vanilla sort of stuff. It was working well despite the errors (typos and misplaced parenthesis) in the MSDN “QuickStart” tutorials, but I got into true trouble when I exported the database and moved it to another server for some other development work we were doing. The web applications hitting this other database weren't functioning properly; all the database activity was causing odd errors referencing things like “sysmergearticles“ and “maxversion” when the ASPX pages would load. This new copy of the database was failing all over the place!
Before long I was searching google and MSDN for any insight, and although there was no direct response to my problem, bit-by-bit (pun fully intended!) the problem started to come into focus. Sysmergearticles and maxversion pointed me towards our replication experimentation, and I was on the right track.
We were using Merge Replication between our SqlServerCE and SqlServer, and part of that replication instrumentation (SQL Server db objects) were messing up the exported database. Our subscriptions and publications (two main pieces of Replication) were apparently fouled up; I removed all the publishing information I could find from the new database, but our problems still persisted. Somewhere there were still replication components that needed cleaning up! Finally, I fired up Red-Gate softeware's SQL Compare to see if I could find anything between the two versions of the databases (note, Brendan Tompkins turned me on to this product several months ago), and after a few minutes of comparing db schemas, I learned that the triggers on the replicated tables were not automatically removed when I used Enterpise Manager to remove the Replication from my database. I deleted the unnecessary triggers from the replicated tables and all was well. Deep breath . . .
I don't know if this is common and I'm just new to SQLServerCE replication, or if it's due to the method I exported the database with, but I thought I'd share this experience. I learned a lot from the process, so it wasn't a futile exercise! Along the way I discovered two new (at least to me) .Net Compact Framework related sites:
Happy .Netting!