SQL Server Compact Edition
Download
Report
Transcript SQL Server Compact Edition
The What, Why, and How . . .
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
Why
Local store
Data store unification
Store options
What
SQL CE Introduction
Architecture
How
Deployment technologies
Replication technologies
Tools
• Connected
• Single data-source
• Database-driven
Local store
Server store
Connectivity-aware
Change reconciliation
• Occasionally Connected
• Multiple data sources
•Data driven
Local Store
Desktop apps have a local store?
Store unification
Benefits
▪ Architectural
▪ Data sharing
▪ Co$t
Challenges
▪ Architectural
▪ Functional
Text file (.txt, .csv, .ini, .xml)
Registry (What?!?!)
Access
FoxPro (R.I.P.)
SQL Express
Open-source DB
Oracle, Sybase
SQL CE
SQL Everywhere
SQL Mobile
Microsoft SQL Server 2005 Compact Edition
3.1
SQL CE
SQLce
SSCE
Win 32
Windows CE Device
Pocket PC
Smart Phone
Graphic courtesy Microsoft
SQL Server Compact
Tablet PC
SQL Mobile
Laptop
SQL Express
Desktop (Single User)
SQL
Multi
User
Server
Workgroup (Dozens of users)
Single User Scenarios
Server (1000’s of users)
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
1.4 MB runtime
Admin and Non-Admin deployment options
Single-file, code-free format (.sdf)
In-Proc with the hosting app
DOES NOT run 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
Native Stack
Managed Stack
Visual Studio 2005
(Visual Basic 2005 & C#)
Visual Studio
2005 (C++)
ADO.NET
SQL Server
CE Data
Provider
OLEDB
OLEDB
Provider
SQL Server
Client Data
Provider
CLR (.NET / .NET CF)
Ethernet
TDS
SQL Server
2000
SQL Server
2005
Well
Connected
OLEDB /
Replication API
SERVER
Data Provider
SQL Server CE
QP/Cursor
Engine
Storage Engine /
Replication Tracking
CLIENT
Graphic courtesy Microsoft
OLEDB
Client
Agent:
Replication
And RDA
802.11b/a/g,
CDPD, GSM,
GPRS, CDMA,
TDMA, etc.
HTTP
Occasionally
Connected
IIS
Server
Agent:
Replication
and RDA
SELECT IMEI, ProductCode, Quantity FROM (SELECT NULL AS IMEI, product AS
ProductCode, (physicalqty - allocatedqty) AS Quantity FROM importstock
WHERE (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 FROM
StockCountSchedule INNER JOIN StockCountProductCategories ON
(StockCountSchedule.ID = StockCountProductCategories.ID) INNER JOIN
importstock ON (StockCountProductCategories.Product_Type =
importstock.product_type) WHERE (StockCountSchedule.IsRecount = 0) AND
(StockCountSchedule.ID = 121231) UNION SELECT ProductCode FROM
StockCountSchedule INNER JOIN CrossDevice_ProductsToRecount ON
(StockCountSchedule.ID = CrossDevice_ProductsToRecount.StockCountID) WHERE
(StockCountSchedule.IsRecount = 1) AND (StockCountSchedule.ID = 121231)) AS
StockCountProducts) 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')) AND
importstock.product IN (SELECT ProductCode FROM (SELECT
StockCountSchedule.ID AS StockCountID, importstock. product AS ProductCode
FROM StockCountSchedule INNER JOIN StockCountProductCategories ON
(StockCountSchedule.ID = StockCountProductCategories.ID) INNER JOIN
importstock ON (StockCountProductCategories.Product_Type =
importstock.product_type) WHERE (StockCountSchedule.IsRecount = 0) UNION
SELECT StockCountSchedule.ID AS StockCountID, ProductCode FROM
StockCountSchedule INNER JOIN CrossDevice_ProductsToRecount ON
(StockCountSchedule.ID = CrossDevice_ProductsToRecount.StockCountID) WHERE
(StockCountSchedule.IsRecount = 1)) AS StockCountProducts)) AS
StockCountItems
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)
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
SQL CE Client Agent
OLE DB
SQL CE
Server
Agent
HTTP (S)
OLE DB
SQL Server
Provider
SSCE
Engine
IIS
SQL CE
Database
Application
SQL Server
Server
Mobile-aware data
▪ Specific
▪ Easily partitioned (time/date, geo)
Filter column indexes
Segregate data
▪ R-O vs R-W
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
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
SQL Server 2005 now ‘mobile-aware’
▪ Download-only tables
▪ Progress notification
▪ Partitioned groups
Multi-user access
Multiple subscriptions / same db
Background sync support
Sends only changed columns
Synchronization cancellation support
Progress updates
SSMS
Create pub / Define subs
VS
Define sub
Initialize sub / Init sync
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
Since SQLce will run on the ‘desktop’, can I use
it to back-end my ASP.NET website ?
No. (but..)
AppDomain.CurrentDomain.SetData(“SQLServerEver
ywhereUnderWebHosting”,true)
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
VS2005 SP1
SQL Server 2005 SP2
SQL
SQL
SQL
SQL
SQL
Server
Server
Server
Server
Server
2005 Compact
2005 Compact
2005 Compact
2005 Compact
2005 Compact
Edition
Edition
Edition
Edition
Edition
Tools for Visual Studio 2005
Developer SDK
Books Online (May 2007)
Runtime
Server Tools
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/tech
nologies/sqlcompact.mspx
http://blogs.msdn.com/SteveLasker
http://blogs.msdn.com/SQLServerCompact/