1 / 30

SQL Mobile

SQL Mobile. Prashant Dhingra Lead Program Manager Microsoft Corporation (India). Agenda. Introduction Supported Platform Release Schedule & License Architecture Features Data Sync Performance. Introduction. Mobility Freedom to work anywhere with up to date data

kanan
Download Presentation

SQL Mobile

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Mobile Prashant Dhingra Lead Program Manager Microsoft Corporation (India)

  2. Agenda • Introduction • Supported Platform • Release Schedule & License • Architecture • Features • Data Sync • Performance

  3. Introduction • Mobility • Freedom to work anywhere with up to date data • Connected and Disconnected • Offline/Disconnected solution • App can execute without a connection • Client side business logic executes on device • Data store on device • Data is Synchronized with Backend server • Integrated with enterprise apps • Customer Scenarios • Field Force Automation • DSD • Healthcare • CRM • Inventory • Military

  4. Introduction – SQL Mobile • SQL CE 3.0 or SQL Server 2005 Mobile Edition • Relational database with small foot print • New code base - developed from scratch • ADO and OLEDB programming interface • Desktop access/management for development • Multi user access • Sync mechanism with SQL Server (2000, 2005) • Security • Dependant upon .NET CF 2

  5. SQL Mobile –Platform, Schedule, License • Pocket PC 2003, Phone edition(03,05) • Smart Phone 5.0 • Windows CE.NET 5.0 • Tablet PC • Release • With VS 2005 and SQL 2005 • Beta1 & Beta2 already released • License • Same as SQL CE 2.0 • Desktop development for DB if VS 2005 or SQL Server 2005 installed

  6. Platform – Project Types • Device • Pocket PC 2003 • Windows Mobile 5.0 • Smartphone Mobile 5.0 • Win CE 5.0

  7. SQL Mobile - Architecture Enterprise Backend Oracle / db2 / etc PIM Apps Native Apps Managed SQL CE + eDB Apps XML/BizTalk/CS 2002/DTS VS 2005 C++ VS 2005 (VB.NET, C#) SQL Mobile Data Provider SQL Client Provider CLR / .NET CF OLEDB / Replication API OLE DB Replication and RDA IIS Server Agent: Replication and Remote Data Access Internal QP API QP HTTP eDB API Internal SE API Occasionally Connected Storage Engine CLIENT SERVER

  8. Agenda • Introduction • Supported Platform • Release Schedule & License • Architecture • Features • Data Sync • Performance

  9. SQL Mobile - Feature Set • Desktop Development • Tablet PC, Scaling and Deployment testing • Integration with VS 2005 • Integration with SQL Server 2005 • SQL (DDL/DML) Support • Powerful Sync mechanism • Column Level Tracking • Multiple subscription • Download only articles • Conflict Resolution • Programming Interface • ADO.NET • OLEDB • Improvement for GPRS connectivity • Features for better performance • New SQLCEResultSet & many more • DTS Support • Easy migration from SQL CE 2.0

  10. SQL Mobile New Features – Storage Engine • Storage engine re-written to further optimize for mobile applications • Multi-User support • Row level locking data • Lock Escalation • Improved stability • Auto re-use of empty pages

  11. SQL Mobile New Features – Query Processor • ShowPlan • Query Optimization Hints • View Plan and do performance tuning • Named parameters/Parameterized Queries

  12. SQL Server 2005 Management Studio Integration • Same user experience, SQL Server or SQL Mobile • Functionally equivalent • Object Explorer • Query Editor • Management Dialogs • Capabilities • Create SQL Mobile Databases • Show Plan and Hints • Faster Optimization • Enhanced Publication • Subscription Wizard • DTS Support

  13. Visual Studio 2005 Integration • Integrated with Server Explorer • Connect to SDF on device, emulator, or desktop • Manage database schema, view / edit data • Drag and Drop Generation • Drag tables and columns onto form to create databound controls • SqlCeResultSet class, enables performant, type safe, cursor style development • Enabled in the DataSource window for drag and drop development • End to End development. • Add SDF to your project • If the database hasn’t changed, it won’t get deployed • Ensures the database is R/W for databases under source control • Database in Server Explorer shows the database management tools

  14. Agenda • Introduction • Supported Platform • Release Schedule & License • Architecture • Features • Data Sync • Performance

  15. Challenges in Mobile Sync • Concurrency • Division of workflow across mobile user base • Data changing on device, server, or both • Conflict resolution (Single Master vs. Multi-Master) • Connectivity • Reliability • Bandwidth • Security/Firewalls • Administration

  16. Choosing Sync ArchitectureConsider Your Options…. • XML Web Services • You design and control all behavioral aspects • Only option for non-SQL Server synchronization • Remote Data Access (RDA) • SQL-driven PULL and PUSH model • Change tracking with optimistic concurrency • Merge Replication • Publisher - Subscriber Model • Advanced tracking and conflict resolution • Centralized Control over Pubs and Subscribers

  17. Choosing Sync ArchitectureStart at the beginning… • Questions for Architecture Planning: • Read-only data on device with occasional updates? • Data changing at one end or the other? • Data changing at both ends concurrently? • How large is the data to be synchronized?

  18. Remote data access (RDA) • RDA provides loosely-coupled connectivity between SQL CE & SQL Server • talks to SQL Server w/o being “connected” • store SQL Server query results directly in SQL CE • automatically tracks changes locally • send locally changed records back to SQL Server

  19. Application SQL Server OLE DB Provider SQL CE Server Agent HTTP OLE DB SQL CE Client Agent OLE DB Internet Information Server (IIS) SQL CE Engine SQL Server Database Database RDA Architecture

  20. Merge ReplicationOverview • Provides data synchronization between SQL Mobile and SQL Server • SQL Server is the publisher • SQL Mobile is the subscriber • SQL Mobile receives initial snapshot from SQL Server • Both SQL Mobile and SQL Server can modify the data • Conflict resolution can be customized

  21. Choosing A Data Sync Strategy SQLCE

  22. Choosing Sync ArchitectureGeneric Decision Flowchart

  23. Agenda • Introduction • Supported Platform • Release Schedule & License • Architecture • Features • Data Sync • Performance

  24. Performance • Performance by Database design • Query Processor Performance Tips • ADO.NET Performance Tips • ResultSet • Performance for Data Sync

  25. SQL Mobile – Technical resources, Community & MVP SQL Mobile – Technical Resources Product Page :http://www.microsoft.com/sql/ce/mobility/beta1.asp BOL and samples: http://msdn.microsoft.com/SQL/SQLCE/default.aspx Webcast : http://www.sqlservermobile.com/SQLMobileWebCasts White Paper : Replication and RDA features : Multi user access Newsgroup : microsoft.public.sqlserver.ce Newsgroup : microsoft.private.whidbey.sqlmobile Hands on Lab : Developing a SQL Mobile application with VS 2005 and SQL Server 2005 Developers conference :MEDC, TECHED etc MSDN chat : July 2005 Other Resources : .NET CF, VS, Win CE, SQL Server

  26. Questions ?

  27. SQL Mobile Data Access Native Stack Managed Stack 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

  28. SQL Mobile Synch Enhancement • Supportability • Re-sync to restored publisher • Server Agent Diagnostics • Server Agent Statistics • 15 min summary • Per client sync summary • Dr Watson • Others • Managed custom resolver • Article Ordering • SQL 2K support • Connection retry timeout • Compression level • Improved Sync Control • Multi-user sync • Progress notifications • Multiple Subscriptions in the same db • SQL Workbench integration • Improved Web Configuration Wizard • New Subscription Wizard • Subsequent Sync Support • Sync Performance • Partitioned Groups • Dynamic Snapshots (BCP) • Article Partition Options • Article Upload Options • Column level tracking

  29. Performance – Database Design • Smaller row length • Fixed and Variable length columns • Key Length – Small • Normalization/Denormalization • Indexes

  30. Performance – Queries • Sub queries vs Join • Parameterized Queries • Outer join – slow response • Indexes • What to index • Selective Index • Multiple Column Index • No index on small tables

More Related