Transcript SESSION CODE: ARC304 Get Out, and Take Your Data With You! Ulrich Roxburgh Services2 Ltd. Agenda Occasionally Connected Applications Microsoft Sync Framework V2 Data Synchronisation Fundamentals SQL Server Synchronisation The.

SESSION CODE: ARC304
Get Out, and Take Your Data
With You!
Ulrich Roxburgh
Services2 Ltd.
Agenda
Occasionally Connected Applications
Microsoft Sync Framework V2
Data Synchronisation Fundamentals
SQL Server Synchronisation
The Cloud
3
Enterprise Application Model
ERP
4
Database
CRM
Smart Client Applications
Disadvantages
Network Requirements - The remote device requires constant connection to
the corporate network while accessing their data
Data Access Speeds - Remote workers are typically connected over slow,
unreliable wired or wireless networks
Single Point of Failure - All users are reliant on a single server. If that
database becomes unavailable due to planned server downtime or from
server failures, all remote workers will be disconnected
Server Scalability - As more workers work remotely, the performance of the
corporate servers will be affected, leading to a need to add additional
hardware
5
Occasionally Connected Applications (OCAs)
An OCA allows a remote worker to continue to access their
data, but locally.
OCA requires data synchronization capabilities.
Data synchronization takes information stored in the client
database and synchronizes changes with a server database.
Advantage of a synchronization-based solution
Users do not require constant network connection
Users are not limited by network speed
Common application code
Offloads processing requirements from the central database.
6
Occasionally Connected Scenarios
7
Challenges associated with Data Synchronisation
Change Tracking
Maintaining Change Data
Conflict Detection and Resolution
Prioritizing Data Exchange
Background Synchronization
Multiple Synchronization Topologies
Custom Client and Server Databases
Security
8
Introducing Microsoft Sync Framework
Microsoft Sync Framework is a synchronization platform that enables
collaboration and offline scenarios.
Supports sync ecosystems that integrate any type of data, using any protocol
over any network.
Highlights
Add sync support to new and existing applications, services, and devices
Enable collaboration and offline capabilities for any application
Leverage sync capabilities exposed in Microsoft technologies to create sync
ecosystems
Extend the architecture to support custom data types including files
9
Capabilities
Any Store - Add synchronization to a wide range of applications, services, and devices, from
enterprise-level services to simple USB storage devices.
Any Data Type - Easily integrate new data types to sync using Microsoft Sync
Framework’s metadata-based synchronization technology
Any Protocol - Synchronize data using existing architectures and protocols. Integrate
sync into a variety of protocols. Enable web services to expose and synchronize data
with any platform by creating a data feed based on the FeedSync open specification.
Any Network Configuration - Sync-enable your applications, devices, and services in
true peer-to- peer and hub/spoke topologies. Recover easily from network
interruptions without losing data integrity. Reduce network traffic using efficient
change enumeration.
10
Core Components
Database synchronization providers
File synchronization providers
Web feed synchronization components
Custom providers
Metadata storage services
Core API and runtime
11
Developing with Sync Framework
Developer:
The application
The data store
The data transfer protocol
Sync Framework.
Synchronization session, or manager
The synchronization runtime
Sync Framework, or the Developer
The sync provider
The metadata store
12
Synchronisation Flow
Synchronization Session initiated with destination
Destination prepares and sends knowledge
Destination knowledge used to determine changes
to be sent
Change versions and source knowledge sent to
destination
Destination compares items against source version
and knowledge
Conflicts are detected and resolved or deferred
Destination requests item data from source
Source prepares and sends item data
13
Participants
Full Participants - Devices that allow developers to
create applications and new data stores directly on
the device. E.g. Windows Phone, laptop
Partial Participants - Devices that have the ability
to store data either in the existing data store or
another data store on the device but do not have
the ability to launch executables. E.g. thumb drives
or SD Cards.
Simple Participants - Devices that are only capable
of providing information when requested. These
devices cannot store or manipulate new data. E.g.
RSS Feeds and web services.
14
Change Tracking
Change tracking provides a list of changes made from one point in time to another.
Commonly implemented using rowversions and triggers, plus a “deleted” table
The major disadvantages are:
Changes are required to the schema to add columns and tables
Triggers are fired for each change made, which has performance implications.
Logic for maintaining proper rowversions and row deletions can get extremely complicated.
SQL Server 2008 has built-in change tracking, implemented without rowversions and
triggers
The Sync Framework database synch providers take advantage of SQL Server 2008
change tracking and provide the following advantages :
No schema changes are required
Triggers are not required for tracking changes
All of the logic for tracking changes is internal to the SQL Server engine
15
Conflict Resolution
Conflicts occur when two or more databases make a change to the same
piece of data
A variety of ways to resolve these conflicts.
Last change to come in wins
Highest priority user wins
Manual selection
Sync Framework provides conflict detection and resolution capabilities out of
the box
SQL Server 2008 makes it easier to identify conflicts.
16
Sync Providers
Database sync providers - ADO.NET Sync providers and SQL Server Sync Providers.
Can be used to synchronize databases for the following types of scenarios:
Collaborative scenarios.
Offline scenarios
File synchronization provider - Can be used to synchronize files and folders in NTFS,
FAT, or SMB file systems. The directories to synchronize can be local or remote; they
do not have to be of the same file system.
Web feed synchronization components - Can be used in two ways:
To write a provider that represents a FeedSync XML file as its replica.
To synchronize the data of another type of replica (such as a file system) with an RSS or
Atom feed.
Custom providers - Can be used to create synchronization providers for any type of
data store.
17
Database Sync Providers
DbServerSyncProvider and SqlCeClientSyncProvider support
offline scenarios.
Suitable for client-server topologies
Less complex but cannot participate in topologies with other providers
SqlSyncProvider, SqlCeSyncProvider, and DbSyncProvider
support collaboration scenarios
Suitable for peer-to-peer and mixed topologies.
More flexible. E.g. Synchronize one instance of SqlSyncProvider and one
instance SqlCeSyncProvider
Use Sync Framework metadata and can participate in topologies with
other Sync Framework providers.
18
SQL and SQL CE Providers
SQL Server 2008 introduced new change tracking
Sync Framework SQL sync providers take advantage of SQL
Server 2008 change tracking:
No schema changes are required
Triggers are not required for tracking changes
Logic is internal to the SQL Server engine
SqlCeSyncProvider is used for SQL Server Compact databases.
SqlSyncProvider for any other edition of SQL Server, including
SQL Server Express.
SqlSyncProvider can be used as a server or a client
19
SQL Server Data Scalability
SQL Azure
SQL Server 2008 R2 Datacenter
SQL Server 2008 R2 Parallel Data Warehouse
SQL Server 2008 R2 Enterprise
SQL Server 2008 R2 Standard
SQL Server 2008 R2 Express
SQL Server 2008 R2 Compact
20
Other alternatives
Remote Data Access (RDA) - Used to sync SQL Server
Compact with SQL Server
Does not support sync using services, incremental change tracking,
or conflict detection and resolution
Merge replication - Used to synchronize different editions
of SQL Server
Does not support sync using services, or heterogenous databases
21
SQL Sync Architecture: Two-Tier
Data
Sync
Provider
Sync
Adapter
22
Sync
Orchestrator
Sync
Provider
Sync
Adapter
Data
SQL Sync Architecture: n-Tier
Data
Sync
Provider
Sync
Adapter
23
Sync
Orchestrator
Proxy
Sync
Provider
Sync
Adapter
Data
Implementing SQL Sync
Define and Provision Server
// define new scope, get description for Products table, and add to scope
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("ProductsScope");
DbSyncTableDescription tableDesc =
SqlSyncDescriptionBuilder.GetDescriptionForTable("Products", serverConn);
scopeDesc.Tables.Add(tableDesc);
// Create a server scope provisioning object based on the Product Scope and provision
// it, skipping creation of the table because it exists
SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn,
scopeDesc);
serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);
serverProvision.Apply();
24
Implementing SQL Sync
Provision SQL Express Client
// get the description of ProductsScope from the SyncDB server database
DbSyncScopeDescription scopeDesc =
SqlSyncDescriptionBuilder.GetDescriptionForScope("ProductsScope",
serverConn);
// create server provisioning object based on the ProductsScope
SqlSyncScopeProvisioning clientProvision = new
SqlSyncScopeProvisioning(clientConn, scopeDesc);
// starts the provisioning process
clientProvision.Apply();
25
Implementing SQL Sync
Executing Sync
// create the sync orchestrator, and set local and remote providers
SyncOrchestrator syncOrchestrator = new SyncOrchestrator();
syncOrchestrator.LocalProvider = new SqlSyncProvider("ProductsScope",
clientConn);
syncOrchestrator.RemoteProvider = new SqlSyncProvider("ProductsScope",
serverConn);
// set the direction of sync session to Upload and Download
syncOrchestrator.Direction = SyncDirectionOrder.UploadAndDownload;
// execute the synchronization process SyncOperationStatistics syncStats =
syncOrchestrator.Synchronize();
26
Implementing SQL Sync
Get results
// print statistics
Console.WriteLine("Start Time: " + syncStats.SyncStartTime);
Console.WriteLine("Total Changes Uploaded: " +
syncStats.UploadChangesTotal);
Console.WriteLine("Total Changes Downloaded: " +
syncStats.DownloadChangesTotal);
Console.WriteLine("Complete Time: " + syncStats.SyncEndTime);
Console.WriteLine(String.Empty);
27
Implementing SQL Sync
Filtered Scopes
// create server provisioning object
SqlSyncScopeProvisioning serverProvision = new
SqlSyncScopeProvisioning(serverConn, scopeDesc);
serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);
// set the filter column to OriginState and filter value to 'NC'
serverProvision.Tables["Orders"].AddFilterColumn("OriginState");
serverProvision.Tables["Orders"].FilterClause = "[side].[OriginState] =
'NC'";
// start the provisioning process
serverProvision.Apply();
28
The ‘Cloud’
2-tier architecture: Sync Framework runs on the local
computer and uses a SqlSyncProvider object to connect to
the SQL Azure database
N-tier architecture: A Sync Framework database provider
runs in a Windows Azure hosted service and communicates
with a proxy provider that runs on the local computer.
29
2-Tier SQL Server ↔SQL Azure Sync
// Create a scope named "customers" and add tables to it
… Omitted for brevity
// Create a provisioning object for "customers" and apply it to the onpremise database.
SqlSyncScopeProvisioning onPremiseConfig = new
SqlSyncScopeProvisioning(onPremiseConn, customersScopeDesc);
onPremiseConfig.Apply();
// Provision the SQL Azure database from the on-premise SQL Server
database.
SqlSyncScopeProvisioning azureCustomersConfig = new
SqlSyncScopeProvisioning(azureConn, customersScopeDesc);
azureCustomersConfig.Apply();
30
n-Tier SQL Server ↔SQL Azure Sync
Windows Azure hosted service is the middle tier, and
communicates between the local proxy and SQL Azure.
To implement…
Create a component that implements the service contract and
calls a SqlSyncProvider object on the SQL Azure
Deploy this component in a hosted service on Windows Azure
Create a proxy provider that implements KnowledgeSyncProvider,
runs on your local computer, and makes calls to the service
interface implemented by your Windows Azure hosted service.
31
© 2008 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.
The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should
not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS,
IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.