[Manifold-l] Another datastore possibility for Manifold (TEXT)

[SCISOFT]

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 (Steve Lasker). It's on his blog
(http://blogs.msdn.com/SQLServerEverywhere/) now, here: 

http://blogs.msdn.com/stevelasker/archive/2006/11/08/comparing-sql-server-ex
press-and-compact-editions-whitepaper.aspx 
 
The paper 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.

http://download.microsoft.com/download/A/4/7/A47B7B0E-976D-4F49-B15D-F02ADE6
38EBE/Compact_Express_Comparison.doc 

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 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). 

http://www.microsoft.com/downloads/details.aspx?familyid=B967347A-5DD0-445C-
8A9F-AEA3DB9EC4BC&displaylang=en 

Here is the Microsoft summary of SQL Server 2005 Compact Edition, published
7 November 2006: 
http://www.microsoft.com/sql/editions/compact/default.mspx 

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&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 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 (Word document). 


Ian Thomas
GeoSciSoft - Perth, Australia