1 / 29

SQL Server Compact Edition

The What, Why, and How. SQL Server Compact Edition. Nino Benvenuti MVP – Device Application Development http://nino.net/blog. http://nino.net/blog. Device Application Development. http://www.avanade.com. http://www.cinnug.org. Agenda. Why

kimi
Download Presentation

SQL Server Compact Edition

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. The What, Why, and How . . . SQL Server Compact Edition Nino Benvenuti MVP – Device Application Development http://nino.net/blog

  2. http://nino.net/blog Device Application Development http://www.avanade.com http://www.cinnug.org

  3. Agenda • Why • Local store • Data store unification • Store options • What • SQL CE Introduction • Architecture • How • Deployment technologies • Replication technologies • Tools

  4. Loosely/Occasionally Connected Systems • Local store • Server store • Connectivity-aware • Change reconciliation • Connected • Single data-source • Database-driven • Occasionally Connected • Multiple data sources • Data driven

  5. Local Storage and Store Unification • Local Store • Desktop apps have a local store? • Store unification • Benefits • Architectural • Data sharing • Co$t • Challenges • Architectural • Functional

  6. Common Local Storage Options • Text file (.txt, .csv, .ini, .xml) • Registry (What?!?!) • Access • FoxPro (R.I.P.) • SQL Express • Open-source DB • Oracle, Sybase

  7. SQL what? • SQL CE • SQL Everywhere • SQL Mobile • Microsoft SQL Server 2005 Compact Edition • 3.1 • SQL CE • SQLce • SSCE

  8. SQLServer MultiUser SQL Express Single User Scenarios SQL Server Local Storage Options Server (1000’s of users) Workgroup (Dozens of users) Desktop (Single User) Laptop Tablet PC SQL Server Compact Win 32 Windows CE Device SQL Mobile Pocket PC Smart Phone Graphic courtesy Microsoft

  9. What is SQLce? • Fully relational in-process database • Win32 + Windows CE • Secure • OOB Sync • Tools support both Developer and DBA • Visual Studio • Designer Experience, drag ‘n drop • Server Explorer, Data Sources • SQL Server Management Studio • DB design/creation • Interactive query

  10. SQL CE Overview • 1.4 MB runtime • Admin and Non-Admin deployment options • Single-file, code-free format (.sdf) • In-Proc with the hosting app DOES NOTrun as a service • Up to 4 gigabytes per database • Multi connections for background data operations • Simplified security w/password and encryption • Transaction (yes, ACID) for batch operations • Runs on mainline windows platforms (Windows Mobile/XP/2003) • Common programming model through ADO.NET • Auto re-use of empty pages • Multiple sync options • Proven, lightweight, 2-tier, scalable sync API (RDA) • Full-featured merge replication • Future investments for building occasionally-connected apps

  11. SQL Server Client Data Provider SQL Server CE Data Provider SQL Server Compact Edition Architecture Native Stack Managed Stack Visual Studio 2005 (C++) Visual Studio 2005 (Visual Basic 2005 & C#) ADO.NET Ethernet SQL Server 2000 TDS OLEDB SQL Server 2005 Well Connected OLEDB Provider CLR (.NET / .NET CF) OLEDB / Replication API SERVER Data Provider SQL Server CE IIS Server Agent: Replication and RDA 802.11b/a/g, CDPD, GSM, GPRS, CDMA, TDMA, etc. Client Agent: Replication And RDA QP/Cursor Engine OLEDB HTTP Storage Engine / Replication Tracking Occasionally Connected CLIENT Graphic courtesy Microsoft

  12. Runs fine . . . ? SELECT IMEI, ProductCode, Quantity FROM (SELECT NULL AS IMEI, product ASProductCode, (physicalqty - allocatedqty) AS Quantity FROM importstockWHERE (NOT mpstype IN(N'U', N'C', N'M', N'X', N'Y', N'P')) AND product IN(SELECT ProductCode FROM (SELECT importstock.product AS ProductCode FROMStockCountSchedule INNER JOIN StockCountProductCategories ON(StockCountSchedule.ID = StockCountProductCategories.ID) INNER JOINimportstock ON (StockCountProductCategories.Product_Type =importstock.product_type) WHERE (StockCountSchedule.IsRecount = 0) AND(StockCountSchedule.ID = 121231) UNION SELECT ProductCode FROMStockCountSchedule INNER JOIN CrossDevice_ProductsToRecount ON(StockCountSchedule.ID = CrossDevice_ProductsToRecount.StockCountID) WHERE(StockCountSchedule.IsRecount = 1) AND (StockCountSchedule.ID = 121231)) ASStockCountProducts) UNION SELECT IMEI.imei AS IMEI, NULL AS ProductCode,NULL AS Quantity FROM importstock INNER JOIN IMEI ON importstock.product =IMEI.product WHERE (mpstype IN(N'U', N'C', N'M', N'X', N'Y', N'P')) ANDimportstock.product IN (SELECT ProductCode FROM (SELECTStockCountSchedule.ID AS StockCountID, importstock. product AS ProductCodeFROM StockCountSchedule INNER JOIN StockCountProductCategories ON(StockCountSchedule.ID = StockCountProductCategories.ID) INNER JOINimportstock ON (StockCountProductCategories.Product_Type =importstock.product_type) WHERE (StockCountSchedule.IsRecount = 0) UNIONSELECT StockCountSchedule.ID AS StockCountID, ProductCode FROMStockCountSchedule INNER JOIN CrossDevice_ProductsToRecount ON(StockCountSchedule.ID = CrossDevice_ProductsToRecount.StockCountID) WHERE(StockCountSchedule.IsRecount = 1)) AS StockCountProducts)) ASStockCountItems

  13. Data Reconciliation • Merge Replication • True bidirectional data reconciliation • Fullest-featured synchronization option • Remote Data Access • Unidirectional change tracking (device) • Lightweight option • Can be complimentary (like chocolate & peanut butter)

  14. SSCE Synchronization Architecture • Client/server configuration • Leveraged by both RDA & MR • Client -> ActiveX • SSCE via OLEDB • Communication via HTTP(S) • Server -> IIS / ISAPI extension • Handles interaction with SQL Server

  15. SSCE Synchronization Architecture SQL CE Client Agent SQL CE Server Agent OLE DB OLE DB SQL Server Provider HTTP (S) SSCE Engine IIS SQL CE Database Application SQL Server

  16. Considerations • Server • Mobile-aware data • Specific • Easily partitioned (time/date, geo) • Filter column indexes • Segregate data • R-O vs R-W

  17. RDA • Device-only change tracking • Pull data from server • No calculated columns • Push changes • Not much change from 3.0 • Can now push/pull while db is in use • Identity columns still problematic • ALTER TABLE / manage range

  18. Merge Replication • True synchronization • SSCE is the sub • Gets initial snapshot from Server • SQL Server is the pub • Data changes on both ends reconciled during synchronization • Server-managed, customizable conflict resolution

  19. Merge Replication – SQL Server • SQL Server 2005 now ‘mobile-aware’ • Download-only tables • Progress notification • Partitioned groups

  20. Merge Replication – SQL CE • Multi-user access • Multiple subscriptions / same db • Background sync support • Sends only changed columns • Synchronization cancellation support • Progress updates

  21. Merge Replication • SSMS • Create pub / Define subs • VS • Define sub • Initialize sub / Init sync

  22. Looking ahead - MSF • Sync Framework • “Microsoft Sync Framework (MSF) is a comprehensive synchronization platform enabling collaboration and offline for applications, services and devices. Developers can build sync ecosystems that integrate any application, any data from any store using any protocol over any network. MSF features technologies and tools that enable roaming, sharing, and taking data offline.“ • http://msdn.microsoft.com/sync

  23. So… ASP.NET? Since SQLce will run on the ‘desktop’, can I use it to back-end my ASP.NET website ? No. (but..) AppDomain.CurrentDomain.SetData(“SQLServerEverywhereUnderWebHosting”,true)

  24. SSCE 3.5 • Ships with Orcas (VS2008 / .NET (CF) 3.5) • Can work w/ .NET CF 2.0 & 3.5 • 90RTM publication compatible subscriber • Will work (sync) with Yukon (2005) and Katmai (2008), but not Shiloh (2000) • Support Synchronization Services for ADO.NET (desktop) • LINQ support

  25. Demos

  26. Caveats / Getting Started • VS2005 SP1 • SQL Server 2005 SP2 • SQL Server 2005 Compact Edition Tools for Visual Studio 2005 • SQL Server 2005 Compact Edition Developer SDK • SQL Server 2005 Compact Edition Books Online (May 2007) • SQL Server 2005 Compact Edition Runtime • SQL Server 2005 Compact Edition Server Tools

  27. Resources • http://www.microsoft.com/sql/editions/compact/default.mspx • http://msdn2.microsoft.com/en-us/sql/bb204609.aspx • http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/sqlcompact.mspx • http://blogs.msdn.com/SteveLasker • http://blogs.msdn.com/SQLServerCompact/

  28. Questions?

  29. Thank You!

More Related