Data Access with SQL Server 2005 Mobile Edition and the .NET Compact Framework v2.0

1. Data Access with SQL Server 2005 Mobile Edition and the .NET Compact Framework v2.0 Scott Colestock

2. Agenda • Overview – SQL Mobile Architecture • SQL Server 2005 Integration • Visual Studio 2005 Integration • Merge Replication

3. Native Stack ManagedStack VS 2005 C++ VB .NET / C# OLEDB CE ADO.NET SQL Server CE Data Provider OLEDB Provider CLR / .NET CF QP / Cursor Engine / ES Storage Engine / Replication Tracking SQL Server Mobile Edition OverviewDeviceDataAccess

4. OverviewHighlyIntegratedintoDevelopmentTools • 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

5. OverviewArchitecture 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

6. OverviewSynchronization 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

7. OverviewIntroducing 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

8. SQL Server 2005 IntegrationSQL Server 2005 Tools • Business Intelligence Development Studio • Visual Tools for creating Data Transformation • Data Visualization • Enhanced Debugging

9. SQL Server 2005 IntegrationSQL Server 2005 Tools • SQL Server Management Studio • Create SQL Mobile Databases • Show Plan and Hints • Faster Optimization • Enhanced Publication • Subscription Wizard

10. SQL Server 2005 Integration

11. Visual Studio 2005 IntegrationVisual Studio Tools • Flexible Development • Native and managed development environment • Visual Basic . NET, Visual C++, Visual C# all localized in the IDE • Targets multiple devices

12. Visual Studio 2005 IntegrationVisual Studio Tools • Rapid Development • Data designer support via Visual Studio 2005 IDE • Drag and drop controls onto form and automatically bind

13. Visual Studio 2005 IntegrationVisual Studio Tools • Library Evolution • SqlCeEngine, SqlCeConnection, SqlCeCommand, SqlCeDataReader • SqlCeResultSet (new updatable scrollable cursor)

14. Visual Studio 2005 Integration

15. SynchronizationGoals 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

16. Application SQL Server Database Mobile Database SynchronizationOverallArchitecture HTTP SQL Server OLE DB Provider OLE DB SQL Mobile Client Agent SQL Mobile Server Agent Internet Information Services (IIS) OLE DB SQL Mobile Engine

17. SynchronizationOutliningYourOptions • SQL Server Mobile Edition provides two synchronization solutions • Remote Data Access (RDA) • Merge replication • Both require server components that are on IIS.

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

19. Remote Data AccessUsingSqlCeRemoteDataAccess • 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)

20. Remote Data AccessPullOperation • 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

21. Remote Data AccessPullOperation • 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

22. Remote Data AccessUsingRDAPull 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.adventure-works.com/sqlmobile/sqlcesa30.dll” rda.Pull( “Authors”, _ “Select * from authors where state = ‘CA’”, _ ServerOledbStr, _ RdaTrackOption.TrackingOn) End Sub

23. Remote Data AccessPushOperation • 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

24. Remote Data AccessUsingRDAPush 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.adventure-works.com/sqlmobile/sqlcesa30.dll” rda.Push( “Authors”, ServerOledbStr, RdaBatchOption.BatchingOn) End Sub

25. Merge ReplicationOverview • 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

26. Merge ReplicationOverview • 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

27. Merge ReplicationExaminingtheCode 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

28. Merge ReplicationWeighingtheCosts • 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

29. Merge ReplicationMinimizingtheCostsforDeviceApplications • Key to success for device apps is minimizing communication • Take only what you need • Apply filters • Static or Parameterized • Join • Column • Reducing the amount of data sent between devices and server… • Reduces sync time • Improves responsiveness

30. Exploring Merge Replication

31. Making The Replication ChoiceRecommendations • 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

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

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! • http://www.heartlandDC.com

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