SQL Server Compact Edition The What Why and

SQL Server Compact Edition The What, Why, and How. . . 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 Local store Data store unification Store options � What SQL CE Introduction Architecture � How Deployment technologies Replication technologies Tools

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

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

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

SQL what? SQL CE SQL Everywhere SQL Mobile Microsoft SQL Server 2005 Compact Edition 3. 1 SQL CE SQLce SSCE

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

What is SQLce? �Fully relational in-process database �Win 32 + 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

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 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 DOES NOT ru

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

Runs fine. . . ? SELECT IMEI, Product. Code, Quantity FROM (SELECT NULL AS IMEI, product AS Product. Code, (physicalqty - allocatedqty) AS Quantity FROM importstock WHERE (NOT mpstype IN(N'U', N'C', N'M', N'X', N'Y', N'P')) AND product IN (SELECT Product. Code FROM (SELECT importstock. product AS Product. Code FROM Stock. Count. Schedule INNER JOIN Stock. Count. Product. Categories ON (Stock. Count. Schedule. ID = Stock. Count. Product. Categories. ID) INNER JOIN importstock ON (Stock. Count. Product. Categories. Product_Type = importstock. product_type) WHERE (Stock. Count. Schedule. Is. Recount = 0) AND (Stock. Count. Schedule. ID = 121231) UNION SELECT Product. Code FROM Stock. Count. Schedule INNER JOIN Cross. Device_Products. To. Recount ON (Stock. Count. Schedule. ID = Cross. Device_Products. To. Recount. Stock. Count. ID) WHERE (Stock. Count. Schedule. Is. Recount = 1) AND (Stock. Count. Schedule. ID = 121231)) AS Stock. Count. Products) UNION SELECT IMEI. imei AS IMEI, NULL AS Product. Code, 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')) AND importstock. product IN (SELECT Product. Code FROM (SELECT Stock. Count. Schedule. ID AS Stock. Count. ID, importstock. product AS Product. Code FROM Stock. Count. Schedule INNER JOIN Stock. Count. Product. Categories ON (Stock. Count. Schedule. ID = Stock. Count. Product. Categories. ID) INNER JOIN importstock ON (Stock. Count. Product. Categories. Product_Type = importstock. product_type) WHERE (Stock. Count. Schedule. Is. Recount = 0) UNION SELECT Stock. Count. Schedule. ID AS Stock. Count. ID, Product. Code FROM Stock. Count. Schedule INNER JOIN Cross. Device_Products. To. Recount ON (Stock. Count. Schedule. ID = Cross. Device_Products. To. Recount. Stock. Count. ID) WHERE (Stock. Count. Schedule. Is. Recount = 1)) AS Stock. Count. Products)) AS Stock. Count. Items

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)

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

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

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

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

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

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

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

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

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

So… ASP. NET? Since SQLce will run on the ‘desktop’, can I use it to back-end my ASP. NET website ? No. (but. . ) App. Domain. Current. Domain. Set. Data(“SQLServer. Ever ywhere. Under. Web. Hosting”, true)

SSCE 3. 5 Ships with Orcas (VS 2008 /. NET (CF) 3. 5) Can work w/. NET CF 2. 0 & 3. 5 90 RTM 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

Demos

Caveats / Getting Started VS 2005 SP 1 SQL Server 2005 SP 2 SQL SQL SQL Server Server 2005 2005 Compact Compact Edition Edition Tools for Visual Studio 2005 Developer SDK Books Online (May 2007) Runtime Server Tools

Resources http: //www. microsoft. com/sql/editions/compact/default. mspx http: //msdn 2. microsoft. com/en-us/sql/bb 204609. aspx http: //www. microsoft. com/technet/prodtechnol/sql/2005/tech nologies/sqlcompact. mspx http: //blogs. msdn. com/Steve. Lasker http: //blogs. msdn. com/SQLServer. Compact/

Questions?

Thank You!