PermaLink Sync Framework Limitations for Stage/Production Web Usage03/12/2008
In a previous story, I was all excited about this new MS framework, but unfortunately we've found all sorts of limitations that weren't obvious on an initial investigation.  These limitations apply only if you plan to use it for peer to peer SQL server sync instead of the client/server sync (aka PDA) it was meant for.

Initially, it seemed like the holy grail to make Notes-like replication work for DB apps in the Windows world, but this is partially true even if you use the client/server model. The client/server model forces you to use MS SQLCE (the one meant to compete w/ Sybase SQLAnywhere, formerly known as Watcom SQL). The nice part about this is that it supports synchronization of database changes; however, it does *not* include trigger and storedproc changes. This makes sense since MS SQLCE probably doesn't have the storedproc functionality of a full blown MSSQL server.

There is also a peer-to-peer sync mode, but this requires a direct MSSQL connection between the two machines you want to synchronize (won't work too well with customers who are paranoid enough that you're trying to sync your web site through their firewall); only client/server mode uses a windows service API (WCF actually, so you're tied to XP at least) to get through firewalls. Peer-to-peer sync mode doesn't synchronize database schema changes at all.

Lastly, to make it easy to use, they added support for tracking DB table row changes in MSSQL2008 so it's as simple as turning this on for the database. However, in MSSQL2005, you have to add a crapload of triggers, tables, and columns; this should have been automated by MS (we ended up writing a tool to do this). MSSQL2000 is not supported at all because transaction snapshot mode from MSSQL2005 is used.

Granted this is just a CTP version and not the released version, so these limitations may change. Foreign keys are also handled nicely using a concept called "sync groups" where you define a set of related tables (every table has to have a primary key) and a DB transaction will be used to insert the entire set of changes at once to maintain DB integrity. The jury's still out on how tombstone deletions work when foreign keys still exist on another system (the case where data is added on multiple peers).

Comments :v
No comments.

Start Pages
RSS News Feed RSS Comments Feed CoComment Integrated
The BlogRoll
Calendar
June 2024
Su
Mo
Tu
We
Th
Fr
Sa
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
Search
Contact Me
About Ken
Full-stack developer (consultant) working with .Net, Java, Android, Javascript (jQuery, Meteor.js, AngularJS), Lotus Domino