SVR201 SQL Server 2005 Mobile Edition: The Evolution Of SQL Server CE

1
SVR201SQL Server 2005 Mobile Edition The
Evolution Of SQL Server CE

• Scott Smith
• Program Manager
• Visual Studio for Devices
• Microsoft Corporation

2
(No Transcript)
3
Agenda

• Introduction
• SQL Server CE Evolved
• New Features and Demos
• Release Schedule
• Resources

4
SQL Mobile Introduction

• Fully-Relational Database in 1-2 MB Footprint
• Off-Line Data Cache for Occasionally Connected
  Mobile Users
• Powerful Built-In Data Synchronization
  Technology
• Security features
• Tight Integration with Visual Studio .NET 2005
  and SQL Server 2005

5
SQL Mobile Supported Platforms

• PocketPC 2003, Phone Edition
• Smart Phone 5.0
• Windows CE.NET 5.0
• TabletPC

6
SQL Server CE Evolved

• Leapfrog feature release
• Integration with SQL Server 2005
• Integration with Visual Studio 2005 and .NET CF
  v2.0
• Desktop Development and Deployment
• Integration with Enterprise Apps (DTS)
• Significant New Functionality
• Evolution
• Reliability and Performance
• Multiple Connections, Multiple Subscriptions
• SmartPhone, TabletPC Support
• Upgrade Tool for SQL Server CE DBs

7
SQL Mobile Data Access
Native Stack
Managed Stack
VS 2005 C
VB .NET / C
OLEDB CE
ADO.NET
SQL Server CE Data Provider
OLEDB Provider
CLR / .NET CF
QP / Cursor Engine / ES
Storage Engine / Replication Tracking
SQL Server Mobile Edition
8
SQL Mobile New Features Storage Engine

• Storage engine re-written to further optimize for
  mobile applications
• Multi-User support
• Row level locking data
• Page level locking index
• Lock Escalation
• Improved stability
• ACID transaction support
• Auto re-use of empty pages

9
SQL Mobile New Features Query Processor

• Cost-Based Optimization
• QP uses statistics support in storage engine to
  improve cost-based planning
• Execution Plan and Query Hints
• View the plan and refine your query
• Named parameters
• Updateable scrollable cursor via SqlCeResultSet
• Scrolling access
• Update in place
• Random access

10
SqlCeResultSet AndMutiple Connections To DB
11
SQL Server 2005 Management Studio Integration

• Same user experience, SQL Server or SQL Mobile
• Functionally equivalent
• Object Explorer
• Query Editor
• Management Dialogs
• Capabilities
• Create SQL Mobile Databases
• Show Plan and Hints
• Faster Optimization
• Enhanced Publication
• Subscription Wizard
• DTS Support

12
SQL Server 2005 Management Studio Integration
13
Visual Studio 2005 Integration

• Integrated with Server Explorer
• Connect and enumerate SDF on device, emulator, or
  desktop
• Manage database schema, view / edit data
• Drag and Drop Generation
• Drag tables and columns onto form to create
  databound controls
• SmartTasks accelerate creating Master / Details
  forms
• Typed ResultSet class baked into generated code
• SqlCeResultSet class, enables performant, type
  safe, cursor style development
• Enabled in the DataSource window for drag and
  drop development
• End to End development.
• Add SDF to your project
• If the database hasnt changed, it wont get
  deployed
• Ensures the database is R/W for databases under
  source control
• Database in Server Explorer shows the database
  management tools

14
Visual Studio .NET 2005 Integration
15
Synchronization
Enterprise Backend Oracle / db2 / etc
Native Stack
Managed Stack
XML/BizTalk/CS 2002
Visual Studio 2005 (VB.NET C)
Visual Studio 2005
ADO.NET
Ethernet
SQL Mobile Data Provider
SQL Server Client Data Provider
OLEDB CE
TDS
Well Connected
CLR / .NET CF
OLEDB Provider
OLEDB / Replication API
802.11b/a/g, CDPD, GSM, CDMA, TDMA, etc.
Data Provider
SQL Server Mobile
IIS
Server Agent Replication and Remote Data Access
Client Agent Replication and RDA
QP/Cursor Engine/ES
HTTP
Storage Engine / Repl Tracking
Occasionally Connected
CLIENT
SERVER
16
Remote Data Access

• Leveraged in .NET CF via SqlCeRemoteDataAccess
• Pull Operation
• Values may come from a table or stored procedure
• Tracking off, return results of any SQL statement
• Tracking on, pull directly from a table
• Can filter data to reduce data size
• Push Changes Back to Server

17
Merge Replication

• .NET CF provides managed wrapper SqlCeReplication
• SQL Server 2005 Mobile Edition receives initial
  snapshot from SQL Server
• Both SQL Server 2005 Mobile Edition and SQL
  Server can modify the data
• Conflict resolution can be customized
• Changes reconciled on next synchronization
• Local SQL Server 2005 Mobile Edition changes sent
  to SQL Server
• SQL Server changes sent to SQL Server 2005 Mobile
  Edition

18
SQL Mobile Synch Enhancement

• Sync Performance
• Partitioned Groups
• Dynamic Snapshots (BCP)
• Article Partition Options
• Article Upload Options
• Column level tracking
• Improved Sync Control
• Multi-user sync
• Progress notifications
• Multiple Subscriptions in the same db
• SQL Workbench integration
• Improved Web Configuration Wizard
• New Subscription Wizard
• Subsequent Sync Support

• Supportability
• Re-sync to restored publisher
• Server Agent Diagnostics
• Server Agent Statistics
• 15 min summary
• Per client sync summary
• Dr Watson
• Agility
• Managed custom resolver
• Article Ordering
• SQL 2K support
• Connection retry timeout
• Compression level

19
Synchronizing Data
20
Multi-User Considerations

• Multiple threads accessing database, possibly
  while synchronizing
• More developer responsibility
• Understand locking levels
• Manage underlying data changes during
  synchronization
• Avoid RI Errors _at_compensate_for_errors
• Understand muti-user RDA limitations
• http//msdn.microsoft.com/library/default.asp?url
  /library/en-us/dnsql90/html/sql2k5mobilesynch.asp

21
SQL Mobile Schedule

• Ships with SQL Server 2005 and Visual Studio 2005
  
• Schedule
• SQL Mobile Beta 1 included in
• Whidbey Beta 1
• Yukon Beta 2
• SQL Mobile Beta 2 included in
• Whidbey (VS2005) Beta 2
• Yukon (SQL SERVER 2005) April CTP
• VS2005B2 and SQL Server 2005 April CTP work well
  together!

22
Resources

• MSDN
• http//msdn.microsoft.com/SQL/SQLCE/default.aspx
• http//www.microsoft.com/sql/ce/mobility/beta1.asp
  
• http//www.microsoft.com/sql/ce/default.asp
• Newsgroups
• microsoft.public.sqlserver.ce
• microsoft.private.whidbey.sqlmobile
• microsoft.private.whidbey.smartdevices
• Case Studies
• http//www.microsoft.com/SQL/ce/productinfo/casest
  udies.asp

23
Related MEDC 2005 Sessions

• CLI338 Data Centric Scenarios in CF Jim
  Wilson
• SVR304 Synchronizing Data with .NET CF 2.0 and
  SS2005 Jim Wilson
• CLI336 Database Driven Apps with VB and SQL
  Mobile Neil Enns
• SVR408 SQL Server CE Performance Tuning
  Darren Shaffer
• HOL336S Developing a SQL Mobile Application
  with VS2005 and SS2005

24
Questions?
25
After The Conference
Build
Install Build Join
Full-featured trial versions of Windows CE
and/or Windows XP Embedded Cool stuff tell us
about it msdn.microsoft.com/embedded/community
Windows Embedded Partner Programwww.mswep.com
Develop
Install Enter Join
Windows Mobile 5.0 Eval Kit including Visual
Studio 2005 Beta 2 Mobile2Market Contest and win
up to 25000 mobile2marketcontest.com
Microsoft Solutions Partner Programpartner.micr
osoft.com
26
Tools Resources
Build
Develop
Websites
msdn.microsoft.com/ embedded
msdn.microsoft.com/ mobility
Newsgroups
microsoft.public. windowsxp.embedded
windowsce.platbuilder
windowsce.embedded.vc
microsoft.public. pocketpc.developer
smartphone.developer
dotnet.framework.compactframework
Blogs
blogs.msdn.com/ mikehall
blogs.msdn.com/ windowsmobile
vsdteam netcfteam
Tools
Windows CE 5.0 Eval KitWindows XP Embedded Eval
Kit
Windows Mobile 5.0 Eval Kit
27
(No Transcript)