dat413: sql server ™ ce: building mobile applications with sql server ce and smart device...

DAT413: DAT413:

SQL ServerSQL Server™™ CE: CE: Building Mobile Applications with Building Mobile Applications with SQL Server CE and Smart Device SQL Server CE and Smart Device ExtensionsExtensions

Kevin CollinsKevin CollinsSenior Program ManagerSenior Program ManagerMicrosoft CorporationMicrosoft Corporation

AgendaAgenda

• SQL Server CE OverviewSQL Server CE Overview• Deployment PlatformsDeployment Platforms• Smart Device Extensions (SDE) Overview Smart Device Extensions (SDE) Overview • SQL Server CE v2.0 Architecture and SQL Server CE v2.0 Architecture and

FeaturesFeatures• Deployment and scaling case studyDeployment and scaling case study

SQL Server CE OverviewSQL Server CE Overview

• Key ScenariosKey Scenarios– Disconnected databaseDisconnected database– Internet / Intranet connectivity / synchronizationInternet / Intranet connectivity / synchronization– Different from Mobile Web Forms / MMIT, Different from Mobile Web Forms / MMIT,

ADO.NET Data Set and MSMQ CEADO.NET Data Set and MSMQ CE– Managed and Unmanaged SupportManaged and Unmanaged Support

• Customer ScenariosCustomer Scenarios– Medical, Military, DSD, FSA, SFA, Real estateMedical, Military, DSD, FSA, SFA, Real estate

Nevalon – Real Estate MLSNevalon – Real Estate MLS

Windows CE PlatformsWindows CE Platforms• H/PC 2K – (Jornada 720, Intermec 6651)H/PC 2K – (Jornada 720, Intermec 6651)– No .NET CF support, Native supportNo .NET CF support, Native support– Typically have keyboardTypically have keyboard– Many have 32mb ROM/RAMMany have 32mb ROM/RAM– PC Card/USBPC Card/USB

• PocketPC 2000, 2002, Phone Edition – PocketPC 2000, 2002, Phone Edition – (Intermec 700, Compaq iPaq) (Intermec 700, Compaq iPaq) – No keyboardNo keyboard– .NET CF Support and Native support.NET CF Support and Native support– Many have 64MB RAM/ROM, USB and CF supportMany have 64MB RAM/ROM, USB and CF support

• Embedded – (Intermec 5020)Embedded – (Intermec 5020)– Varying CPU/RAM/ROMVarying CPU/RAM/ROM– PB 3.0 Native support PB 3.0 Native support – PB 4.0 .NET CF support PB 4.0 .NET CF support

http://www.hp.com/jornada/products/700/index.html

VS.NET/SDE Development SequenceVS.NET/SDE Development Sequence

VS.NETVS.NETSource CodeSource Code

(Hello.*)(Hello.*)

CompliersCompliers

.NET Compact.NET CompactFramework Framework Class LibsClass Libs

.NET Compact.NET CompactFramework Framework

CLRCLR


Hello.exeHello.exe

Windows CE Windows CE EmulatorEmulator


CLRCLR


Hello.exeHello.exe

Win32Win32


CLRCLR


Hello.exeHello.exe

DeviceDevice

Deployment OptionsDeployment Options

.NET CF Class Libraries.NET CF Class Libraries

SystemSystem

System.DataSystem.Data System.XmlSystem.Xml

System.WebSystem.Web

GlobalizationGlobalizationTextTextSecuritySecurityCollectionsCollections

ResourcesResourcesReflectionReflectionNetNetIOIO

ThreadingThreadingDiagnosticsDiagnosticsServiceProcessServiceProcessConfigurationConfiguration

DesignDesignADO.NETADO.NET

SqlServerCeSqlServerCeSqlClientSqlClient

Xslt/XPathXslt/XPathXmlDocumentXmlDocument

RuntimeRuntimeInteropServicesInteropServicesRemotingRemotingSerializationSerialization

SerializationSerialization

ConfigurationConfiguration SessionStateSessionStateCachingCaching SecuritySecurity

ServicesServicesDescriptionDescriptionDiscoveryDiscoveryProtocolsProtocols

UIUIHtmlControlsHtmlControlsWebControlsWebControls

System.DrawingSystem.Drawing

ImagingImagingDrawing2DDrawing2D

TextTextPrintingPrinting

System.WinFormsSystem.WinFormsDesignDesign ComponentModelComponentModel

Reader/WritersReader/Writers

Tech-Ed 2002 KeyNoteTech-Ed 2002 KeyNote

OLEDB CEOLEDB CECLR / .NET CFCLR / .NET CF

QP/Cursor Engine/ES QP/Cursor Engine/ES

SQL Server CE v2.0 Device Data AccessSQL Server CE v2.0 Device Data Access

ADOADOCE v3.1CE v3.1

Storage Engine / Repl TrackingStorage Engine / Repl Tracking

SQL Server CE v2.0SQL Server CE v2.0

eVB 3.0eVB 3.0eVC eVC 3.03.0

OLEDBOLEDB

OLEDBOLEDB

SQL Server CE 2.0 Data SQL Server CE 2.0 Data ProviderProvider

Native/UnmanagedNative/Unmanaged StackStack SDE / ManagedSDE / Managed StackStack

ADO.NETADO.NETVS .NET (VB.NET, C#)VS .NET (VB.NET, C#)

SQL Server CE v2.0 Platform SQL Server CE v2.0 Platform SupportSupport

Tool SetTool Set Data AccessData Access PlatformPlatformeMVTeMVT ADOCE / OLEDBCEADOCE / OLEDBCE HPC 2KHPC 2K

eMVTeMVT ADOCE / OLEDBCEADOCE / OLEDBCE PocketPC 2000PocketPC 2000

eMVTeMVT ADOCE / OLEDBCEADOCE / OLEDBCE PB 4.1 “Jameson”PB 4.1 “Jameson”

VS.NET / .NET CFVS.NET / .NET CF ADO.NETADO.NET PocketPC 2002PocketPC 2002

VS.NET / .NET CFVS.NET / .NET CF ADO.NETADO.NET PocketPC 2000PocketPC 2000

VS.NET / .NET CFVS.NET / .NET CF ADO.NETADO.NET PB 4.1 “Jameson”PB 4.1 “Jameson”

eMVTeMVT ADOCE / OLEDBCEADOCE / OLEDBCE PocketPC 2002PocketPC 2002

SQL Server 2000SQL Server 2000Windows CE Edition v2.0Windows CE Edition v2.0

• Upward compatibility with SQL ServerUpward compatibility with SQL Server– Same result setsSame result sets– Compatible data typesCompatible data types

• Right footprint size for devicesRight footprint size for devices– ~800kb for X86 CPU~800kb for X86 CPU

Database Feature SetDatabase Feature SetRich functionality for small footprintRich functionality for small footprint

• Multi-column indexesMulti-column indexes• Referential integrityReferential integrity• Sub-selects (IN)Sub-selects (IN)• DefaultsDefaults• Inner/outer joinsInner/outer joins• One file databaseOne file database• Null supportNull support• Unicode supportUnicode support• TransactionsTransactions

• Data-typesData-types– Real, Numeric, NVarChar, Real, Numeric, NVarChar,

NText, Image, Integer, NText, Image, Integer, DateTime, VarBinary, DateTime, VarBinary, UniqueIdentifier, MoneyUniqueIdentifier, Money

• Having/group byHaving/group by• Seek on indexSeek on index• Set functionsSet functions• Identity attributeIdentity attribute• Local security - DB Local security - DB

encryption (128bit) and encryption (128bit) and database passworddatabase password

New V2.0 FeaturesNew V2.0 Features

• Intrinsic functionsIntrinsic functions• ABSABS• ACOSACOS• ASINASIN• ATANATAN• ATN2ATN2• CEILINGCEILING• COSCOS• COTCOT• DEGREESDEGREES• EXPEXP

• FLOORFLOOR• LOGLOG• LOG10LOG10• PIPI• POWERPOWER• RADIANSRADIANS• RANDRAND• ROUNDROUND• SIGNSIGN• SQRTSQRT• NEWIDNEWID

• TANTAN• UNICODEUNICODE• CHARCHAR• CHARINDEXCHARINDEX• LENLEN• LOWERLOWER• LTRIMLTRIM• PATINDEXPATINDEX• REPLACEREPLACE• REPLICATEREPLICATE• @IDENTITY@IDENTITY

• RTRIMRTRIM• SPACESPACE• STRSTR• STUFFSTUFF• SUBSTRINGSUBSTRING• UPPERUPPER• CASECASE• COALESCECOALESCE• CONVERTCONVERT• DATALENGTHDATALENGTH


• UnionUnion– SELECT * FROM A UNION SELECT * FROM BSELECT * FROM A UNION SELECT * FROM B

• Parameterized QueriesParameterized Queries– INSERT INTO mytable (col1, col2) VALUES (?, ?);INSERT INTO mytable (col1, col2) VALUES (?, ?);

• Index PullIndex Pull– Brings indexes down with RDA PullBrings indexes down with RDA Pull

• Connectivity Setup WizardsConnectivity Setup Wizards– Ease installation of SQL CE Server componentsEase installation of SQL CE Server components

• Optional Error StringsOptional Error Strings– Extra DLL to provide error strings in applicationExtra DLL to provide error strings in application

• Increased index limit per tableIncreased index limit per table


• SQL Server Client Data ProviderSQL Server Client Data Provider– Direct Access to SQL ServerDirect Access to SQL Server

• Enhanced ISQLWEnhanced ISQLW

ISQLW CE, new SQLISQLW CE, new SQL

SQL Server CE V2.0 .NET Integration – ADO.NETSQL Server CE V2.0 .NET Integration – ADO.NET

• System.Data.SqlClientSystem.Data.SqlClient– Direct connection to SQL ServerDirect connection to SQL Server– Rich interaction with the serverRich interaction with the server• Full TSQLFull TSQL• Stored ProceduresStored Procedures

• System.Data.SqlServerCESystem.Data.SqlServerCE– Local Data ProviderLocal Data Provider– Rich data access while offlineRich data access while offline– Exposes full functionality of SQL Server CEExposes full functionality of SQL Server CE

SQL Server CE Error HandlingSQL Server CE Error Handling

• Utilizes Try/Catch Utilizes Try/Catch – System.Data.SqlServerCe.SqlCeExceptionSystem.Data.SqlServerCe.SqlCeException

OLEDB CEOLEDB CECLR / .NET CFCLR / .NET CF

QP/Cursor Engine/ES QP/Cursor Engine/ES

CE Data AccessCE Data AccessStorage ArchitectureStorage Architecture

Client Client Agent: Agent:

ReplicationReplicationandandRDARDA

ADO ADO CE CE v3.1v3.1

OLEDB / OLEDB / Replication APIReplication API

IISIIS

CLIENTCLIENT SERVERSERVER

Storage Engine / Repl Storage Engine / Repl TrackingTracking

SQL CE Edition v2.0SQL CE Edition v2.0

eVB eVB 3.03.0 eVC eVC

3.03.0

OLEDBOLEDB

OLEDBOLEDB

Server Server Agent: Agent:

ReplicationReplicationandand

Remote Remote Data Data

AccessAccessHTTPHTTP

XML/BizTalk/CS 2002XML/BizTalk/CS 2002

802.11b, 802.11b, CDPD, CDPD, GSM, GSM,

CDMA, CDMA, TDMA, etc.TDMA, etc.

Enterprise BackendEnterprise Backend

Oracle / db2 / etcOracle / db2 / etc

SQL Server SQL Server CE Data CE Data ProviderProvider

Native/UnmanagedNative/Unmanaged StackStack

Data ProviderData Provider

SQL Server SQL Server Client Data Client Data Provider Provider

ADO.NETADO.NETVS .NET (VB.NET, C#)VS .NET (VB.NET, C#)

..NET CF / ManagedNET CF / Managed StackStack

TDSTDS

EthernetEthernet

Well Well ConnectedConnected

Occasionally Occasionally ConnectedConnected

Connectivity – TransportConnectivity – Transport

• Utilize Web technologyUtilize Web technology– HTTP communicationHTTP communication• Internet and IntranetInternet and Intranet

– Security via IISSecurity via IIS• Authentication (anonymous, basic, NTLM)Authentication (anonymous, basic, NTLM)• AuthorizationAuthorization• Encryption for secure transfer (SSL)Encryption for secure transfer (SSL)

– Access through firewallsAccess through firewalls

Connectivity – Merge ReplicationConnectivity – Merge Replication

• SQL Server 2000 features crucial for SQL SQL Server 2000 features crucial for SQL Server CE:Server CE:– Vertical and horizontal partitioningVertical and horizontal partitioning– Dynamic horizontal filtersDynamic horizontal filters– Auto ranged identity columnsAuto ranged identity columns– Standard/custom conflict resolversStandard/custom conflict resolvers

Merge Replication API’sMerge Replication API’s

• System.Data.SQLServerCe.Engine.CreateDatabaseSystem.Data.SQLServerCe.Engine.CreateDatabase– Replaces AddSubscription MethodReplaces AddSubscription Method– Creates blank databaseCreates blank database

• System.Data.SQLServerCe.Replication.SynchronizeSystem.Data.SQLServerCe.Replication.Synchronize– Replaces previous methodsReplaces previous methods

• InitializeInitialize• RunRun• TerminateTerminate

– Initial SyncInitial Sync• Creates schema, downloads data, creates constraintsCreates schema, downloads data, creates constraints

– Subsequent SyncSubsequent Sync• Push modified data up, then bring modified data downPush modified data up, then bring modified data down

Connectivity – Remote Data Connectivity – Remote Data AccessAccess• Access to SQL Server 7.0 & SQL Server Access to SQL Server 7.0 & SQL Server

20002000• Download to device and disconnectDownload to device and disconnect• Disconnected tracking capabilityDisconnected tracking capability• Extremely easy to setup and useExtremely easy to setup and use• Effective scalingEffective scaling

Remote Data Access – API’sRemote Data Access – API’s

• System.Data.SQLServerCe.RemoteDataAccess.System.Data.SQLServerCe.RemoteDataAccess.PullPull– Brings data from server to deviceBrings data from server to device– Data selected via SQL statementsData selected via SQL statements– Creates and populates local table Creates and populates local table – Allows for tracked and untracked dataAllows for tracked and untracked data

• System.Data.SQLServerCe.RemoteDataAccess.System.Data.SQLServerCe.RemoteDataAccess.PushPush– Sends modified data to serverSends modified data to server

• System.Data.SQLServerCe.RemoteDataAccess.System.Data.SQLServerCe.RemoteDataAccess.SubmitSQLSubmitSQL– Pass SQL DML statementPass SQL DML statement

SDE, .NetCF, ADO.Net, SQL CE SDE, .NetCF, ADO.Net, SQL CE

NabiscoNabisco

Nabisco Case Study And Demo Nabisco Case Study And Demo

1500 Delivery 1500 Delivery TrucksTrucks

107 Distribution 107 Distribution CentersCenters

Web ServerWeb Server SQL Server 2000 SQL Server 2000 FailoverFailover

802.11b RF802.11b RF

IBM DB/2IBM DB/2

XMLXML

SAP Business ObjectsSAP Business Objects

Ruggedized Ruggedized PocketPC DevicePocketPC Device

with SQL Server CEwith SQL Server CE

Nabisco DeploymentNabisco Deployment

• Rolled custom CAB fileRolled custom CAB file– Originally used eMVT wizardOriginally used eMVT wizard

• Load CAB file and database on CF cardLoad CAB file and database on CF card– Allows for cold boot recovery in fieldAllows for cold boot recovery in field

• Used version flag in publicationUsed version flag in publication– If version changes, then it is replicated down to If version changes, then it is replicated down to

the devicethe device– This flags download of new CAB file from IIS This flags download of new CAB file from IIS

server and updates latest versionserver and updates latest version

SQL Server CE v2.0 LicensingSQL Server CE v2.0 Licensing

• Included with Smart Device Extension Included with Smart Device Extension (SDE)(SDE)– SQL Server CE and SQL Server CE server side SQL Server CE and SQL Server CE server side

componentscomponents– Developer and test licenseDeveloper and test license

• Downloadable from Downloadable from HTTP://Microsoft.COM/SQL/CEHTTP://Microsoft.COM/SQL/CE for use with for use with eMVTeMVT

• CAL/CPU license needed for connectivity to CAL/CPU license needed for connectivity to SQL ServerSQL Server

• Free distribution rights upon filling out Free distribution rights upon filling out registration formregistration form

http://microsoft.com/SQL/CE

SQL Server CE v2.0SQL Server CE v2.0

• eMVT Beta of SQL Server CEeMVT Beta of SQL Server CE– Goto Goto http://http://www.betaplace.comwww.betaplace.com– BetaID:SQLServerCEBetaID:SQLServerCE– Password:.NetCFPassword:.NetCF– Case sensitive, be sure to include “.” in passwordCase sensitive, be sure to include “.” in password

• Sign up for SDE beta (SQL CE included)Sign up for SDE beta (SQL CE included)– Goto Goto http://http://www.betaplace.comwww.betaplace.com– BetaID:SDEBetaBetaID:SDEBeta– Password:.NetCFPassword:.NetCF– Case sensitive, be sure to include “.” in passwordCase sensitive, be sure to include “.” in password

http://www.betaplace.com/




SQL Server CE SummarySQL Server CE Summary

• Enables data capture in the fieldEnables data capture in the field– Small size, robust functionality Small size, robust functionality

• Enables rapid developmentEnables rapid development– Utilizes new Visual Studio .NET tools Utilizes new Visual Studio .NET tools – Existing application with eMVT still runExisting application with eMVT still run

• Enable robust connectivityEnable robust connectivity– HTTP, IIS, connect anywhereHTTP, IIS, connect anywhere– RDA, ReplicationRDA, Replication

• Expand the enterpriseExpand the enterprise– Upwardly compatible with Upwardly compatible with

SQL Server 2000SQL Server 2000

Questions?Questions?

Resources from Microsoft PressResources from Microsoft Press

For more information please visit the TechEd Bookshop.For more information please visit the TechEd Bookshop.www.microsoft.com/mspresswww.microsoft.com/mspress

MICROSOFT .NET FRAMEWORK

Don’t forget to complete the Don’t forget to complete the on-line Session Feedback form on-line Session Feedback form on the Attendee Web siteon the Attendee Web site

https://web.mseventseurope.com/teched/https://web.mseventseurope.com/teched/

https://web.mseventseurope.com/teched/

dat413: sql server ™ ce: building mobile applications with sql server ce and smart device...

Documents