Notes (hidden) PC40 Embedding SQL Server Compact In Desktop And Device Applications Steve Lasker Program Manager Microsoft Corporation blogs.msdn.com/SteveLasker What we'll cover today Why data on the client is important How SQL Server Compact changes the game
Performance Best Practices Differences between Compact & Express Getting the most by understand the architecture Interesting ways to leverage local data Custom Document Format Run directly from a DVD Visibility into Client Application Health Types Of Local Data Read Only, Reference Data
Is it 100% static for the life of a version? Are there changes? (list of States, Product Catalog?) Is it 100% the same for all users? User Data Each user has their data and they make changes Orders, My Customers, Settings Aggregation
Ability to slice and dice on data across various sources Caching Simplifies Data Access Offloads detailed questions to the client Enables local processing, closer to the user Products w/a Products > 5, Products in Category 2, Products Discontinued, Most sold products for last 3 months Top Products for My Customers Enables offline scenarios
Whats changed for my data Added, Updated, Deleted Enabling data movement from the smallest device, capturing data at the point of activity through the largest data center, to the offsite cloud Clients From embedded devices to desktops Servers From desktop to data center Synchronizing data from the point of activity to the data center Cloud To hosted services
One Size Fits All? Best practices, not all that different Architectural differences, but suited to the task Choose your weapon Choosing The Right Tool demo How Compact Changes The Game What good is a database if you cant deploy it?
Deploying Target = Any Change Managed Provider reference to Copy Local = true Include both 32 and 64 bit Compact engine dlls Place 32bit version under an X86 Place 64bit version under AMD64 AMD64 includes Intel X64 Folder Name based off: Environment.GetEnvironmentVariable("PROCESSOR_ARCHITEC TURE")
SQL Server Compact DLLs DLL Name Functionality sqlcese35.dll Native* Storage Engine sqlceqp35.dll Native* Query Processor sqlceme35.dll Native / Managed Translation Layer System.Data.SqlServerCe.dll
ADO.NET Managed Provider sqlcecompact35.dll Compression & Upgrade APIs sqlceca35.dll Merge Replication, RDA Client APIs Only needed for Merge & RDA, Not needed for Sync Services for ADO.NET sqlceoledb35.dll OleDB API Needed for C++, VB Classic, & Merge/RDA OleDB APIs Designed for Mobile Devices Not a full desktop OleDB Implementation sqlceer35EN.dll
Localized Error Strings Only needed when errors returned directly to the user Architectural Comparison One Size Fits All? Architectural Comparisons SQL Server (Express*) SQL Server Compact Shared Data Services Local Data Documents
Multiple Connections from multiple clients Multiple Connections from multiple processes & threads Runs as a service Runs In-Proc w/Application Connection Pooling through SqlClient Connections through SqlCeConnection Query Plans cached in SQL Server Engine
Query Plans cached in SqlCeCommand object Multi-proc access through shared memory Demo mand cmd = conn.CreateCommand(); eters.Add("@name", System.Data.SqlDbType.NVarChar, 50); er.Read()) { d.CommandText = "INSERT INTO Customer (CustomerId, Name) VALUES(@id, Performance Best Practices .Parameters["@customerId"].Value = id; Eeeking out that extra bit of performance
Best Practices Performance Parameterized Commands, with type and precision SqlCeCommand cmd = conn.CreateCommand(); cmd.Parameters.Add("@customerId", System.Data.SqlDbType.Int); cmd.Parameters.Add("@name", System.Data.SqlDbType.NVarChar, 50); cmd.CommandText = "INSERT INTO Customer (CustomerId, Name) VALUES(@id, @name"); while (reader.Read()) { cmd.Parameters["@customerId"].Value = id; cmd.Parameters["@name"].Value = name; Bulk Insert w/SqlCeResultSet
cmd.CommandText = "Customer"; cmd.CommandType = System.Data.CommandType.TableDirect; SqlCeResultSet rst = cmd.ExecuteResultSet(ResultSetOptions.Updatable); SqlCeUpdatableRecord newRow; while (reader.Read()) { newRow = rst.CreateRecord(); newRow["customerId"] = customerId; newRow[name"] = name; rst.Insert(newRow); "Bulk Load" Performance Device 400 350 Desktop 12000 10000
300 250 150 6000 4000 100 50 Co Ca m ch Pa m an ed ra m
ds Co et m er m ize an d ds Co m m an Pa ds ra m sw
Sq /S lC ize eR es ul tS et 0 Ne w Inserts/Second 200
8000 2000 0 Deferred Index Creation 14000 12000 10000 8000 6000 4000 2000 0 Active Index Post Index
Using Trace Listener To Gather Information In The Field Monitoring Client Health Data Center ? Acme Widgets How to see whats happening at the client? Clients arent reachable Either arent Online when you need them Or theyre not addressable with a static IP What to do? Phone home? Web Service TraceListner
Acme Widgets Data Center try { try { productInfo barcode = productSvc.GetProductInf(barcode); scanner.Scan(); } catch (NetworkException } catch (Exception ex) {ex) { Trace.Listeners.Add(new Trace.WriteLine(UnableToConnect + ex.Message, Product Trace.WriteLine("Scan Failed - " -+" ex.Message, "Barcode
WebServiceTraceListner("http://MyTraceService.svc")) Lookup") Scanner"); } } Web Service TraceListener SQL Server Compact TraceListner Acme Widgets Data Center try { productInfo = productSvc.GetProductInf(barcode); } catch (NetworkException ex) { Trace.Listeners.Add( Trace.WriteLine(UnableToConnect - " + ex.Message, Product new SqlCeTraceListner("Log.sdf",
Lookup") "http://LogSyncService.svc")); } Compact TraceListener Sync Services to move Log info when a connection is available demo Using Trace Listener to Gather Information in the Field Getting visibility to whats actually
going on SQL Server Compact TraceListener Data Center Acme Widgets Custom Doc Formats Structured document Single file, code free, email attachment safe Post Documents to SharePoint Create custom extensions, map to your app Password protect, requiring users to open documents w/your app File Associations Supported by Visual Studio 2008 & ClickOnce demo
Using a SQL Server Compact Database as a Document ClickOnce, w/.NET FX 3.5 enables file associations Recap Why data on the client is important Power of the client, freedom to add value How SQL Server Compact changes the game No need to worry about deployment
Performance Best Practices Take advantage of the in-proc nature Interesting ways to leverage local data Custom Document Format Run directly from a DVD Visibility into Client Application Health Summary SQL Server Compact
~1mb embedded database, no brainer deployment Its free, fast, safe/encryptable and compact Its easy to program and manage Single file, code free, doc centric file format Its an alternative to JET, XML, Persisted DataSets, SQL Server Express Its integrated into:
Visual Studio 2005 SP1 Visual Studio 2008 (Orcas) Adds Sync Services for ADO.NET SQL Server Management Studio 2008 (Katmai) 3.1 In ROM for Windows Mobile 6.x Related Content TL40 Microsoft Sync Framework Advances Lev Novik Day 1 BB15 SQL Server: Database to Data Platform - Road from Server to Devices to the Cloud David Campbell Day 1 BB40 Sync Framework: Enterprise Data in the Cloud and on Devices Liam Cavanaugh Day 2 TL08 Offline-Enabled Data Services and Desktop Applications Pablo Castro Day 3 PC45 WPF: Data-centric Applications Using the DataGrid and Ribbon Controls
- Samantha Durante, Mark Wilson-Thomas Day 4-Noon SQL Server Compact Resources SQL Server Compact www.Microsoft.com/SQL/Compact SQL Server Compact Q&A Blogs.msdn.com/SqlServerCompact Blogs.msdn.com/SteveLaskerBlogs.msdn.com/Laxmi Books Microsoft SQL Server 2005 Compact - Prashant Dhingra HitchhikerGuides.net - Bill Vaughn
SQL Server Compact - Jose M. Torres Windows Mobile Data Synchronization Rob Tiffany Tools Primeworks GUI Innovations Microsoft Synchronization Services for ADO.NET MSDN.Microsoft.com/Sync blogs.msdn.com/Synchronizer Evals & Recordings ll fi e s a e
l P r out you for n o ti a u l eva t: a n o i s s
this se This sess ion will be availa ble as a record ing at: www.microsoftpdc.com SQL Server Compact Resources SQL Server Compact www.Microsoft.com/SQL/Compact
SQL Server Compact Q&A Blogs.msdn.com/SqlServerCompact Blogs.msdn.com/SteveLaskerBlogs.msdn.com/Laxmi Books Microsoft SQL Server 2005 Compact - Prashant Dhingra HitchhikerGuides.net - Bill Vaughn SQL Server Compact - Jose M. Torres Windows Mobile Data Synchronization Rob Tiffany Tools Primeworks GUI Innovations Microsoft Synchronization Services for ADO.NET MSDN.Microsoft.com/Sync blogs.msdn.com/Synchronizer
2008 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.
