Notes (hidden)

Embedding SQL Server Compact In Desktop And Device Applications

Steve LaskerProgram ManagerMicrosoft Corporationblogs.msdn.com/SteveLasker

PC40

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

What we'll cover today…

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

Types Of Local Data

Caching Simplifies Data Access

Offloads detailed questions to the client Enables local processing, closer to the user

Enables offline scenarios

Products w/’a’Products > 5,Products in Category 2,Products Discontinued,Most sold products for last 3 months

What’s changed for my data Added, Updated, Deleted

Top Products for My Customers

Clients ServersFrom desktop to data centerFrom embedded devices to desktops

Synchronizing data from the point of activity to the data center

Enabling data movement from the smallest device, capturing data at the point of activity through the largest data center, to the offsite cloud

CloudTo hosted services

Best practices, not all that different Architectural differences, but suited to the task Choose your weapon

One Size Fits All?

Choosing The Right Tool

How Compact “Changes The Game” What good is a

database if you can’t deploy it?

demo

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 X64Folder Name based off:

Environment.GetEnvironmentVariable("PROCESSOR_ARCHITECTURE")

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 APIsOnly needed for Merge & RDA, Not needed for Sync Services for ADO.NET

sqlceoledb35.dll OleDB API –Needed for C++, VB Classic, & Merge/RDAOleDB APIs Designed for Mobile DevicesNot a full desktop OleDB Implementation

sqlceer35EN.dll Localized Error Strings Only needed when errors returned directly to the user

One Size Fits All?

Architectural Comparison

Architectural Comparisons

SQL Server (Express*)

Shared Data ServicesSQL Server CompactLocal Data Documents

Multiple Connections from multiple clients

Multiple Connections from multiple processes & threads

Runs as a service Runs In-Proc w/ApplicationMulti-proc access through shared memory

Connection Poolingthrough SqlClient

Connections through SqlCeConnection

Query Plans cached in SQL Server Engine

Query Plans cached in SqlCeCommand object

Performance Best Practices

Eeeking out that extra bit of performance

DemoSqlCeCommand cmd = conn.CreateCommand();cmd.Parameters.Add("@name", System.Data.SqlDbType.NVarChar, 50);while (reader.Read()) {

cmd.CommandText = "INSERT INTO Customer (CustomerId, Name) VALUES(@id, @name";

cmd.Parameters["@customerId"].Value = id;

Best Practices – Performance

Parameterized Commands, with type and precision

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);

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

"Bulk Load" Performance

New Commands

Cached Comman

ds

Parameteriz

ed Commands

Params w

/Size

SqlCeResu

ltSet

0

50

100

150

200

250

300

350

400

Device

Inse

rts/

Seco

nd

New Commands

Cached Comman

ds

Parameteriz

ed Commands

Params w

/Size

SqlCeResu

ltSet

0

2000

4000

6000

8000

10000

12000

Desktop

Deferred Index Creation

New Commands

Cached Comman

ds, Single

TX

Parameteriz

ed Commands -

Singl.

..

Params w

/Size

Params w

/Size

, w/E

ncryption

SqlCeResu

ltSet

0

2000

4000

6000

8000

10000

12000

14000 Active Index Post Index

Using Trace Listener To Gather Information In The Field

Monitoring Client HealthAcme WidgetsData Center

?• How to see what’s happening at the client?• Clients aren’t reachable

• Either aren’t Online when you need them• Or they’re not addressable with a static IP

• What to do?• Phone home?

Web Service TraceListnerAcme WidgetsData Center

Web Service TraceListener

try { barcode = scanner.Scan();} catch (Exception ex) { Trace.WriteLine("Scan Failed - " + ex.Message, "Barcode Scanner");}

Trace.Listeners.Add(new WebServiceTraceListner("http://MyTraceService.svc"))

try { productInfo = productSvc.GetProductInf(barcode);} catch (NetworkException ex) { Trace.WriteLine(“UnableToConnect - " + ex.Message, “Product Lookup")}

SQL Server Compact TraceListnerAcme WidgetsData Center

Sync Servicesto move Log info when

a connection is available

Compact TraceListener

try { productInfo = productSvc.GetProductInf(barcode);} catch (NetworkException ex) { Trace.WriteLine(“UnableToConnect - " + ex.Message, “Product Lookup")}

Trace.Listeners.Add( new SqlCeTraceListner("Log.sdf", "http://LogSyncService.svc"));

Using Trace Listener to Gather Information in the Field

Getting visibility to what’s actually going on

demo

SQL Server Compact TraceListenerAcme WidgetsData Center

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

Custom Doc Formats

Using a SQL Server Compact Database as a Document

ClickOnce, w/.NET FX 3.5 enables file associations

demo

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

Recap

SQL Server Compact ~1mb embedded database, no

brainer deployment It’s free, fast, safe/encryptable and compact It’s easy to program and manage Single file, code free, doc centric file format It’s an alternative to JET, XML, Persisted DataSets,

SQL Server Express It’s 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

Summary

Related Content

TL40 Microsoft Sync Framework Advances– Lev Novik Day 1

PC45 WPF: Data-centric Applications Using the DataGrid and Ribbon Controls - Samantha Durante, Mark Wilson-Thomas Day 4-Noon

BB15 SQL Server: Database to Data Platform - Road from Server to Devices to the Cloud– David Campbell Day 1

TL08 Offline-Enabled Data Services and Desktop Applications– Pablo Castro Day 3

BB40 Sync Framework: Enterprise Data in the Cloud and on Devices– Liam Cavanaugh Day 2

SQL Server Compactwww.Microsoft.com/SQL/Compact

SQL Server Compact Q&ABlogs.msdn.com/SqlServerCompactBlogs.msdn.com/SteveLaskerBlogs.msdn.com/Laxmi

BooksMicrosoft SQL Server 2005 Compact - Prashant DhingraHitchhikerGuides.net - Bill VaughnSQL Server Compact - Jose M. Torres Windows Mobile Data Synchronization – Rob Tiffany

ToolsPrimeworksGUI Innovations

Microsoft Synchronization Services for ADO.NETMSDN.Microsoft.com/Sync blogs.msdn.com/Synchronizer

SQL Server Compact Resources

Evals & Recordings

Please fill

out your

evaluation for

this session at:

This session will be available as a recording at:

www.microsoftpdc.com

SQL Server Compactwww.Microsoft.com/SQL/Compact

SQL Server Compact Q&ABlogs.msdn.com/SqlServerCompactBlogs.msdn.com/SteveLaskerBlogs.msdn.com/Laxmi

BooksMicrosoft SQL Server 2005 Compact - Prashant DhingraHitchhikerGuides.net - Bill VaughnSQL Server Compact - Jose M. Torres Windows Mobile Data Synchronization – Rob Tiffany

ToolsPrimeworksGUI Innovations

Microsoft Synchronization Services for ADO.NETMSDN.Microsoft.com/Sync blogs.msdn.com/Synchronizer

SQL Server Compact Resources

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