[Manifold-l] Another datastore possibility for Manifold

Wed Nov 22 09:30:32 CST 2006

I got a bit inspired on 7th November, and wrote this - it won't survive
conversion to plain text, so be warned. 

Microsoft SQL Server Compact Edition v3.1

I've been waiting for the publication of a good overview 'whitepaper' from
one of the Microsoft "SQL Mobile" team
<http://blogs.msdn.com/SQLServerEverywhere/>  (Steve Lasker). It's on his
blog now, here
<http://blogs.msdn.com/stevelasker/archive/2006/11/08/comparing-sql-server-e
xpress-and-compact-editions-whitepaper.aspx> . 

The paper
<http://download.microsoft.com/download/A/4/7/A47B7B0E-976D-4F49-B15D-F02ADE
638EBE/Compact_Express_Comparison.doc>  is called "Choosing Between SQL
Server 2005 Compact Edition and SQL Server 2005 Express Edition", but it has
some good general advice on choosing a database. 

I would guess that for many users of Manifold GIS, SQL Server 2005 Express
Edition is "too heavy", and MS Access is preferable to other databases. 

Here's a possible alternative: SQL Server CE (v3.1 release). 

SQL Mobile has always been a lightweight database for mobile developers. It
is near final (RTM) release, and has been renamed to SQL Server Compact
Edition (just to confuse things, that was its original name, and it was
known as SQL Server 2005 Everywhere Edition during the current product
development phase). 

Now that it is fully functional on the desktop*, you can deploy it alongside
your (mobile) application so that you can cache data, giving your
application offline capabilities on a 'mobile' computer.  In the past you
would have had to deploy MSDE or SQL Express which were difficult to
administer and a general PITA, as both of them run as a service even when
your application is not running.  

SQL Server CE runs in-process, yet still permits multiple connections,
making it a great solution for an application datastore. 

*  (except for web apps)

Why may this be of interest to users of Manifold GIS? 

*       The current release was free for developers, and those with an
existing (not-free) Microsoft SQL Server license (SQL Server 2000 and
later). 

*       The good news is that v3.1 (due soon) is "Free to download, develop,
deploy, and redistribute" - and that quotes Microsoft (see below). 

*       It is a viable substitute for Microsoft Access databases. 

*       It is lightweight - a 1.7Mb download (SQL Server Express Edition is
53Mb). 

*       It runs on Microsoft desktop and tablet PC computers. It works on a
wide range of 32-bit Windows versions (including Windows 2000 now). 

*       Does not need Admin rights for installation. 

*       It is compatible** with the rest of the Microsoft SQL Server line
(data types, etc) - which are a much larger and more complex install. 

*       It runs in-process, yet still permits multiple connections. 

*       It is even embeddable within an application. 

*       Runs in systems with small amounts of RAM (fits in about 2Mb of
memory). 

*       There is good integration with Microsoft development tools (eg, the
SQL Server Compact Edition Tools for Visual Studio 2005 SP1 Beta is also
available). 

However, it - 

*       Does not have Triggers and Stored procedures.

*       Cannot use nested SQL statements; and it is not a strict subset of
the existing T-SQL language. 

*       Has a 4Gb limit on the database size (like many other database
products have imposed) - but that will be relaxed. 

*       Does support network shares, but not from concurrent machines
(Microsoft does not plan to enable this, as it encroaches on the data
service scenarios supported by SQL Server 2005 Express, etc. A compelling
reason is that Jet - ie, Microsoft Access - was plagued with issues from
multiple users working from a shared file.). 

** (not entirely - but this may be being a bit pedantic)

Also of interest is the availability of a tool
<http://www.microsoft.com/downloads/details.aspx?familyid=B967347A-5DD0-445C
-8A9F-AEA3DB9EC4BC&displaylang=en>  to synchronize data between Microsoft
Access database on a desktop and Microsoft SQL Server CE database on a
device (or a desktop computer, of course). 

Here is the Microsoft summary of SQL Server 2005 Compact Edition, published
7 November 2006: 

http://www.microsoft.com/sql/editions/compact/default.mspx

Comparing SQL Server Compact Edition and SQL Server Express Edition

For a detailed comparison between SQL Server Compact Edition and SQL Server
Express, read the
<http://download.microsoft.com/download/A/4/7/A47B7B0E-976D-4F49-B15D-F02ADE
638EBE/Compact_Express_Comparison.doc>  white paper. For a quick comparison,
see the following table.

SQL Server 2005 Compact Edition

SQL Server Express Edition

When to use / benefits

.

Essential relational database functionality in a compact footprint

.

Ideal for mobile and desktop applications, including occasionally connected

.

Embeddable in applications

.

Free to download, develop, deploy, and redistribute

.

Ideal for small server deployments as well as desktop applications with more
rich requirements such as full text search and powerful XML query
processing.

.

Seamless upgrade to SQL Workgroup, Standard and Enterprise

.

Free to download, develop, deploy, and redistribute

When not to use

.

When you want to run as a service

.

When you need a multi-user database server

.

You need the full functionality of SQL Server

.

When the footprint and memory requirements need to be compact

.

When you want to run in process

.

When you want to target devices

This is the SQL Server CE development team's blog and the announcement: 

http://blogs.msdn.com/sqlservereverywhere/archive/2006/11/06/sql-server-comp
act-edition-rc1-release.aspx 

in part, it reads - 

SQL Server Compact Edition RC1 is released on web. 

You can download it from
http://www.microsoft.com/downloads/details.aspx?FamilyId=85E0C3CE-3FA1-453A-
8CE9-AF6CA20946C3
<http://www.microsoft.com/downloads/details.aspx?FamilyId=85E0C3CE-3FA1-453A
-8CE9-AF6CA20946C3&displaylang=en> &displaylang=en 

SQL Server Everywhere Edition is renamed to SQL Server Compact Edition.

SQL Server 2005 Compact Edition was previously named SQL Server 2005
Everywhere Edition during the product development phase.

Note that this is Release Candidate 1 (not the final, Release To Market -
RTM - version). It is a 1.7 Mb download. 

Much more on the SQL Everywhere Blog and on Steve
<http://blogs.msdn.com/stevelasker/default.aspx>  Lasker's Blog. Steve has
written a whitepaper presenting a feature comparison of SQL Server 2005
Express and SQL Server CE, but it delves into the differences in a local
database programming model.  You can download it here
<http://download.microsoft.com/download/A/4/7/A47B7B0E-976D-4F49-B15D-F02ADE
638EBE/Compact_Express_Comparison.doc>  (Word document). 

Ian Thomas

GeoSciSoft - Perth, Australia

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.directionsmag.com/pipermail/manifold-l/attachments/20061122/506d514f/attachment-0001.htm