Hide Notes 

Slide 1: Scott Colestock Data Access with SQL Server 2005 Mobile Edition and the .NET Compact Framework v2.0
Slide 2: Agenda Overview – SQL Mobile Architecture SQL Server 2005 Integration Visual Studio 2005 Integration Merge Replication
Slide 3: Overview Device Data Access Native Stack VS 2005 C++ OLEDB CE Managed Stack VB .NET / C# ADO.NET SQL Server CE Data Provider OLEDB Provider CLR / .NET CF QP / Cursor Engine / ES Storage Engine / Replication Tracking SQL Server Mobile Edition
Slide 4: Overview Highly Integrated into Development Tools Integration with SQL Server 2005 DTS Support Show Plan and Hints SQL Server Management Studio Integration with Visual Studio 2005 Ships with Visual Studio 2005 Database management functionality Easy code upgrades from SQL Server CE 2.0
Slide 5: Overview Architecture Improvements Storage engine Multiconnection support Row level locking of data Shares common memory pool Auto-shrink support Improved query processor Cost-based optimization Show plan and query hints Scrollable, updatable cursor – SqlCeResultSet
Slide 6: Overview Synchronization Improvements Improved synchronization control Multiuser synchronization Progress notifications Multiple subscriptions in the same database Synchronization performance (w/SQL 2005) Partitioned articles Download-only tables Column-level tracking
Slide 7: Overview Introducing the SqlCeResultSet Development Features Included in SQL Mobile, ships with: Visual Studio 2005 SQL Server 2005 Takes full advantage of SQL Server 2005 Mobile Edition local capabilities Extended Features Scrolling access Update in place Random access
Slide 8: SQL Server 2005 Integration SQL Server 2005 Tools Business Intelligence Development Studio Visual Tools for creating Data Transformation Data Visualization Enhanced Debugging
Slide 9: SQL Server 2005 Integration SQL Server 2005 Tools SQL Server Management Studio Create SQL Mobile Databases Show Plan and Hints Faster Optimization Enhanced Publication Subscription Wizard
Slide 10: SQL Server 2005 Integration
Slide 11: Visual Studio 2005 Integration Visual Studio Tools Flexible Development Native and managed development environment Visual Basic . NET, Visual C++, Visual C# all localized in the IDE Targets multiple devices
Slide 12: Visual Studio 2005 Integration Visual Studio Tools Rapid Development Data designer support via Visual Studio 2005 IDE Drag and drop controls onto form and automatically bind
Slide 13: Visual Studio 2005 Integration Visual Studio Tools Library Evolution SqlCeEngine, SqlCeConnection, SqlCeCommand, SqlCeDataReader SqlCeResultSet (new updatable scrollable cursor)
Slide 14: Visual Studio 2005 Integration
Slide 15: Synchronization Goals of Synchronization Simplify development of synchronization logic Sync becomes a single line of code Conflict resolution happens on the server through SQL Server model Share a common (mobile) data source Allow multiuser access to the same data source (that lives on the device) Improve performance and data concurrency More end-user versatility Allows end users to change devices and still access the data source
Slide 16: Synchronization Application Overall Architecture SQL Mobile Client Agent OLE DB SQL Mobile Engine HTTP SQL Mobile Server Agent Internet Information Services (IIS) OLE DB SQL Server OLE DB Provider Mobile Database SQL Server Database
Slide 17: Synchronization Outlining Your Options SQL Server Mobile Edition provides two synchronization solutions Remote Data Access (RDA) Merge replication Both require server components that are on IIS.
Slide 18: Remote Data Access Overview Loosely coupled connectivity between SQL Server Mobile Edition and SQL Server No configuration of the back-end Sql Server required Client application specifies query to return data for a local table (table-at-a-time metaphor) Optionally tracks changes locally Sends locally changed records back to SQL Server The .NET Compact Framework provides managed wrapper SqlCeRemoteDataAccess
Slide 19: Remote Data Access Using SqlCeRemoteDataAccess An application must specify RDA connectivity parameters Web server information URL – must include sqlcesa30.dll Login and password Local database information (SQL Mobile Edition OLE DB connection string) Proxy server login and password information Remote DB info sent with each command (SQL Connection String)
Slide 20: Remote Data Access Pull Operation Local table created including data and schema Optionally includes primary key Optionally includes indexes Values may come from a table or stored procedure Can retrieve all data for complete data set or… Can filter data to reduce data size View/stored procedure can reference only one table with a primary key
Slide 21: Remote Data Access Pull Operation Optionally track local changes Changes can be later applied Uses optimistic concurrency (no server data locked) Local table must not exist before pull Created during pull operation Error thrown if table exists
Slide 22: Remote Data Access Using RDA Pull Public Sub RDAPull() Dim rda as New SqlCeRemoteDataAccess Dim ServerOledbStr as String = “Provider=sqloledb; Data Source=dataserver;” _ “Initial Catalog=Pubs;User Id=sa;Password=;” rda.LocalConnectionString = ”Provider=Data Source=\My Documents\test.sdf” ‘ Set URL and IIS login/password. rda.InternetUrl = “http://www.adventureworks.com/sqlmobile/sqlcesa30.dll” rda.Pull( “Authors”, _ “Select * from authors where state = ‘CA’”, _ ServerOledbStr, _ RdaTrackOption.TrackingOn) End Sub
Slide 23: Remote Data Access Push Operation Sends changes back to server All changes since last pull or push are applied Changes applied to server indiscriminately Table tracking must have been specified during pull operation Can choose if updates batched as one transaction, or distinct Can have conflicts logged in Errors table
Slide 24: Remote Data Access Using RDA Push Public Sub RDAPush() Dim rda = New SqlCeRemoteDataAccess() Dim ServerOledbStr as String = “Provider=sqloledb; Data Source=dataserver;” _ “Initial Catalog=Pubs;User Id=sa;Password=;” rda.LocalConnectionString = _ ”Data Source=\My Documents\test.sdf” ‘ Set URL and IIS login/password. rda.InternetUrl = “http://www.adventureworks.com/sqlmobile/sqlcesa30.dll” rda.Push( “Authors”, ServerOledbStr, RdaBatchOption.BatchingOn) End Sub
Slide 25: Merge Replication Overview Provides data synchronization between SQL Server 2005 Mobile Edition and SQL Server SQL Server is the publisher SQL Server 2005 Mobile Edition is the subscriber SQL Server 2005 Mobile Edition receives initial snapshot from SQL Server Both SQL Server 2005 Mobile Edition and SQL Server can modify the data Conflict resolution can be customized
Slide 26: Merge Replication Overview Changes reconciled on next synchronization Local SQL Server 2005 Mobile Edition changes sent to SQL Server SQL Server changes sent to SQL Server 2005 Mobile Edition SQL Server Management Studio wizards Publishers created in studio Subscribers create in studio .NET Compact Framework managed wrapper - SqlCeReplication
Slide 27: Merge Replication Examining the Code Public Sub SyncSubscription() Dim repl as New SqlCeReplication() repl.InternetUrl = "http://Server1/SQLServerCE/sqlcesa30.dll" repl.Publisher = “SERVER1" repl.PublisherDatabase = “CustmerInfo" repl.PublisherLogin = "sa" repl.PublisherPassword = "" repl.Publication = "CustomerInfoPub" repl.SubscriberConnectionString = _ ”Data Source=\My Documents\MyLocalDB.sdf“ repl.Subscriber = “CustomerInfoSub" repl.AddSubscription(AddOption.CreateDatabase) repl.Synchronize() End Sub
Slide 28: Merge Replication Weighing the Costs Several triggers and stored procedures added Added to system tables Adds more overhead Replicated tables must have a ROWGUIDCOL Ensures that rows are unique Can avoid if rowguid column is added prior Database must track all modifications Additional tables added to system database Tables used to track modifications Can grow significantly
Slide 29: Merge Replication Minimizing the Costs for Device Applications Take only what you need Apply filters Static or Parameterized Join Column Key to success for device apps is minimizing communication Reducing the amount of data sent between devices and server… Reduces sync time Improves responsiveness
Slide 30: Exploring Merge Replication
Slide 31: Making The Replication Choice Recommendations Remote Data Access If you can’t modify the backend database… If you have small tables to bring to the device… If the tables you modify are distinct from those you reference… Choose merge replication Bi-directional synchronization on all tables – only the deltas are transferred Much more flexible conflict resolution For more on making the choice http://msdn.microsoft.com/library/en-us/dnppcgen/ html/eff_arch_sql_servr_ce_rep.asp “Comparing Remote Data Access (RDA) and Merge Replication” in SQL 2005 BOL
Slide 32: Summary SQL Server 2005 Mobile Edition functionality is greatly enhanced for developers Integration with SQL Server 2005 makes generation and maintenance of SQL Mobile databases better Integration with Visual Studio 2005 makes developing of SQL Mobile applications easier and faster Remote data access vs. merge replication
Slide 33: Heartland Developers Conference ’05 October 13 – 14, Cedar Rapids, IA TWICE THE SIZE (days, sessions, attendees, parties) Developer Pre-Party on the 12th and Developer Jam on the 13th 2 Keynotes, 18 Sessions, Developer Lounges 2 Tracks: Center Stage / Behind the Curtain Many door prizes including a top of the line Alienware Laptop!
Slide 34: © 2004 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY. Content created by 3 Leaf Solutions