The what why and how
This presentation is the property of its rightful owner.
Sponsored Links
1 / 29

SQL Server Compact Edition PowerPoint PPT Presentation


  • 35 Views
  • Uploaded on
  • Presentation posted in: General

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

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


The what why and how

The What, Why, and How . . .

SQL Server Compact Edition

Nino Benvenuti

MVP – Device Application Development

http://nino.net/blog


Sql server compact edition

http://nino.net/blog

Device Application Development

http://www.avanade.com

http://www.cinnug.org


Agenda

Agenda

  • Why

    • Local store

    • Data store unification

    • Store options

  • What

    • SQL CE Introduction

    • Architecture

  • How

    • Deployment technologies

    • Replication technologies

    • Tools


Loosely occasionally connected systems

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


Local storage and store unification

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

Common Local Storage Options

  • Text file (.txt, .csv, .ini, .xml)

  • Registry (What?!?!)

  • Access

  • FoxPro (R.I.P.)

  • SQL Express

  • Open-source DB

  • Oracle, Sybase


Sql what

SQL what?

  • SQL CE

  • SQL Everywhere

  • SQL Mobile

  • Microsoft SQL Server 2005 Compact Edition

    • 3.1

    • SQL CE

    • SQLce

    • SSCE


Sql server local storage options

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


What is sqlce

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


Sql ce overview

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


Sql server compact edition architecture

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


Runs fine

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


Data reconciliation

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

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


Ssce synchronization architecture1

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


Considerations

Considerations

  • Server

    • Mobile-aware data

      • Specific

      • Easily partitioned (time/date, geo)

    • Filter column indexes

    • Segregate data

      • R-O vs R-W


Sql server compact edition

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

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

Merge Replication – SQL Server

  • SQL Server 2005 now ‘mobile-aware’

    • Download-only tables

    • Progress notification

    • Partitioned groups


Merge replication sql ce

Merge Replication – SQL CE

  • Multi-user access

  • Multiple subscriptions / same db

  • Background sync support

  • Sends only changed columns

  • Synchronization cancellation support

  • Progress updates


Merge replication1

Merge Replication

  • SSMS

    • Create pub / Define subs

  • VS

    • Define sub

    • Initialize sub / Init sync


Looking ahead msf

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

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)


Ssce 3 5

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


Demos

Demos


Caveats getting started

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


Resources

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/


Questions

Questions?


Thank you

Thank You!


  • Login