DESIGNING APPLICATIONS FOR THE OCCASIONALLY CONNECTED SCENARIO

Local Database Caching with the Microsoft Sync Frameworkpresented to NUFWon 11/10/2009

About Me

Work for Systemental, Inc as a Consultant and Software Developer

Software development to support Corporate business process improvement since 2000 (Lean or Continuous Improvement Initiatives)

.Net since 2004

President, fwPASS.org

Mfg. Eng. Technology degrees from Ball State University

A case study in failure

What could go wrong preparing this presentation did

Initial demo worked in minutes (after about three minor problems)

SQL CE debugging challenges

1. Refactor - created numerous problems

2. Test - find problems requiring schema changes

3. Repeat 1-2 ad nauseum

What we will cover

What is the Microsoft Sync Framework?

How you can use it?

Why?

How does it work?

How do you implement it?

Demo’s

Gotcha’s, gotcha’s, and debugging

What is the Sync Framework

Provider-based API for synchronizing data or files across multiple applications, services, and physical devices

Included providers

Database *

File system

Web – RSS/Atom

How can it be used?

Server to Local

Local to Server

Bi-directional

Why?

Why not pub/sub, log shipping, replication? Reliability

Purpose

schedule granularity

Disconnected, hello….

Argument: Internet is everywhere and growing

CE vs. Express Encrypt or password protect the file

Document-safe, no code stored in file

Light 1.8Mb vs. 58Mb

How does it work?

Visual Studio local database cache template

Schema changes (couple fields and tombstone)

Triggers or 2008 Change data capture

Sync Process

Steps to Implement

Add new item - local database cache

Create database/tables to sync or modify DDL

Sync Configuration wizard Configure source/target databases

add/edit .sdf database

add sync tables

Generate/run scripts to adjust ddl/sync

Generate typed dataset/table adapters (optional)

Generate Linq2SQL or EF classes (optional)

Set sync direction and .Synchronize

Demo project

Keyboard Encoder Module

Demo project details

WPF application with a single keystroke input F5 key

Changing background color (green, yellow, red) based on actual count vs. target count

Two dispatcher timers:1. Screen display color update (every second)

2. Db synchronization (every minute)

WinForm SQL CE cache table viewer

Using Repository pattern and Linq to SQL

Demos

Existing SQL db, switch to CE 3.5 using provider and connection string change

Wizardry

SQLMetal

Profiler

Server to local sync (default)

local to server sync/bi-directional (code-behind)

SQLMetal to create Linq2SQL classes

Gotcha’s Visual Studio Linq2SQL template doesn’t support SQL CE

provider – use SQLMetal to generate, then include in project

LINQ dbml created from MSSQL, not compatible CE

SSMS 2005 would not open a CE 3.5 .sdf file. Need SSMS 2008

Set sync direction if anything other than server to local

Changing schema’s after generation – remove everything and start over (use sql scripts to roll back)

Insert/Update triggers not auto created?

.sdf is re-initialized to empty and copied to bin/debug if newer

Existing triggers on the table

Gotcha’s – primary key

Primary key defined on table to cache

auto-incrementing integer primary key, unique identity with SQL CE db

Some table defaults like newid() for MSSQL Uniqueidentifer field do not transfer to the .sdf schema definition. Set in code <table>.id=Guid.NewGuid

Linq can’t autogenerate guid on server. Use id=Guid.NewGuid in code

Debugging SQL CE 3.5• SSMS 2005 gives the following error when you try to connect to a SQL CE 3.5 .sdf file.• Created a WinForm project PeekLocalCache to display data from .sdf file. Caution: make sure

you know which .sdf file the app is using (bin/debug, bin/release, project)• SSMS 2008 – hopefully works• Be careful re-creating the .sdf file using the local database cache configuration wizard

without deleting the records from the Server table.

I cleared out the Server table and ran for a while on 11/2 without ex, on 11/3 cleared out the table and ran for 13 records. Changing to update server table, works. Violation is in local cache db

Switched PK to GUID from Int64

Set autogen=true in dbml: fail!

added id = Guid.NewGuid

Conflict resolution

Sorry, not covering in Demos

Enums – conflict types (between sync sessions) ClientInsertServerInsert - A new row is created with the same

primary key. ClientUpdateServerUpdate - The same row is updated. Most

common ClientUpdateServerDelete - A row is updated on the client but

was deleted on the server. ClientDeleteServerUpdate - A row is deleted on the client but

was updated on the server. ErrorsOccurred – all others

Methods for handling Continue RetryApplyingRow RetryWithForceWrite

Resolution Policies

Source wins

Destination wins

Specified replica wins

Last writer wins

Merge

Log it or defer

References

http://dean-o.blogspot.com/

http://channel9.msdn.com/posts/funkyonex/Offline-Data-Synchronization-Services-in-Visual-Studio-2008/

http://videoworld-rong.blogspot.com/2009/10/adonet-sync-services.html

http://social.microsoft.com/Forums/en-US/uklaunch2007ado.net/thread/0fd34e49-ae3d-4b50-91f5-5e24d4a0b145

http://skysigal.xact-solutions.com/Blog/tabid/427/EntryId/898/Using-LinqToSQL-with-SqlServer-Compact.aspx

http://download.microsoft.com/download/A/4/7/A47B7B0E-976D-4F49-B15D-F02ADE638EBE/Compact_Express_Comparison.doc

http://www.developer.com/net/article.php/3815506/Handling-Data-Conflicts-in-the-Microsoft-Sync-Framework.htm

http://www.geekzone.co.nz/JamesHip/6568

http://code.msdn.microsoft.com/sync

http://mattgoebel.spaces.live.com/blog/cns!DB12DAA600AD8227!182.entry?wa=wsignin1.0&sa=84900002

http://devlicio.us/blogs/billy_mccafferty/archive/2009/11/09/dependency-injection-101.aspx

http://stackoverflow.com/questions/385796/sync-framework-can-i-sync-only-a-subset-of-my-tables

ASP.NET MVC Framework Unleashed by Stephen Walther, SAMS, ISBN 978-0-672-32998-2

Thank you!

Website http://www.systemental.com

Blogs http://dean-o.blogspot.com/ http://practicalhoshin.blogspot.com

Twitter http://www.twitter.com/deanwillson

Email dean@systemental.com

LinkedIn http://www.linkedin.com/in/deanwillson