A Closer Look At SQL Server Compact Edition

Introduction

In my previous article, An Introduction to SQL Server Everywhere, I had presented a basic introduction to SQL Server Everywhere, which was later on renamed to SQL Server Compact Edition. In this session, let us have a closer look into the product.

SQL Server Compact Edition - Facts

SQL Server CE 1.0 was launched in 2000 along with Embedded VB and Embedded VC. SQL Server CE 2.0 was released and shipped along with Visual Studio 2003. The next version of the product was SQL Server Mobile 3.0 which was shipped with Visual Studio 2005. In 2006 November, SQL Server Mobile became SQL
Server Compact Edition which still runs on the SQL Server Mobile engine but without the limitation to the Mobile Platform. With this version, SQL Server Compact Edition could run on desktops too.

From many of the MS blogs, white papers and documentation, I see that Microsoft wants to push SQL Server Compact Edition to be the default/primary choice for desktop/client applications. By making it FREE, light-weight (really light weight), in-process and taking away all the hurdles of deployment/redistribution, they suggest it as the primary choice for the local data store of many of the client applications.
SQL Server Compact Edition can handle database files as big as 4 GB, which is the same size as SQL Server Express. It supports up to 256 simultaneous connections (Only from the host machine). Thought SQL Server Compact Edition
is not really meant for multi user applications, the support for multiple connections could be effectively utilized to create highly responsive client applications.

SQL Server CE does not have anything to do with Windows CE as the name might suggest. It is in fact a Compact edition of SQL Server. You will really be convinced that it is Compact when you will see that it has a memory foot print of approximately 5 MB and a disk foot print which is less than 2 MB. Further, it is in process which will enable us to embed the database engine to the application. Though SQL
Server CE comes with an MSI installer, you don't really need
an installer to install SQL Server CE. What the installer does is just to copy the required
DLL files to a given folder. The whole SQL Server CE edition is composed
of 9 DLL files which you can simply copy to your computer and you are ready develop
SQL Server CE applications.

Though SQL Server 2005 Express and SQL Server Compact Edition
are FREE and meant for client applications, there are a few factors that might make
SQL Server Compact Edition as a preferred choice in certain scenarios. I could see that the biggest advantage is the ease of deployment. One could deploy an SQL Server CE application by just copying the folder which contains the referenced assemblies. This operation does not even need administrative rights on the machine. However, installing SQL Server Express edition needs administrative rights as it will be configured and run as a System Service. Moreover, the installable of SQL Server Express is close to 60 MB where as SQL Server Compact Edition is just close to 2 MB only.

A SQL Server Compact Edition database file, usually has an .SDF extension. This file contains the data which can be modified on a desktop, tablet PC or on a smart device. This means that you can work on a database file on a smart device on Monday. On Tuesday, copy the file to your desktop and continue to work and on Wednesday you can move the file to a Tablet PC and can still continue to work. SQL Server Compact Edition can work with the same database file without making you bother about the platform you are working.

What is in the box?

The fact is that, SQL Server CE is not really new. Just a few lines up, I have shown some facts that shows the evolution of this cute little database engine.
The feature set of SQL Server CE includes the following:

It supports Full Referential Integrity with Cascading Deletes and Updates

Transaction Support

Scrollable and Updateable Cursors

Supports JOINS

Supports GROUP BY, HAVING and AGGREGATE functions

Password Protected Data File

128 bit file level encryption

Supports multiple connections (from the same machine)

Manage the database from SQL Server 2005 Management Studio

Synchronize with Central server, either with Replication or with Remote Data Access (RDA)

Both Administrators and Developers may benefit from SQL Server CE. SQL
Server CE is tightly integrated to Visual Studio 2005. Developers can use their existing knowledge to develop SQL Server CE applications as it it supports the same SQL syntax and ADO.NET programming model. Developers can use the same code base to build applications targeting multiple platforms. The database engine is embedded in to the applications, and hence no administrative activity is needed.

Who should use it?

SQL Server CE should be the first choice for developers writing single-user desktop applications. This is ideal for occasionally-connected applications, where data from the central server is cached locally and made available for the applications to work in an offline fashion.

Many of us must be excited about the freedom that Outlook gives us for working offline. You can compose and answer emails without worrying about connectivity. This shows the power and productivity that an offline application can give you. Irrespective of where you are (In an aero plane or at a mountain camp), irrespective of the environment (Connected to the network or not), you can still continue to work. Your local cache has all the data that you need. As soon as you are connected to the network, your application will automatically synchronize your data with the central server. Doesn't it make you more productive?

Another area where you should consider using SQL Server CE is to store
your application's cache. Most applications use a cache for better performance.
A cache is usually created as a collection of objects identified by a key. To access a given object in the collection the application needs to iterate over all the items
in the cache. Using an in-memory database to store the cached data will give better performance in terms of common operations like searching and sorting etc.

Another category of people who would be interested in SQL Server CE may the hosting providers. Since there is no database server, no administrative activities are required. Users can simply copy their database file and the required DLLs and
the application is ready.

Before jumping in...

So far I am very excited about this new database engine. I see that my development
team is working on a couple of applications, which could really take advantage of
the SQL Server CE embedded database engine. However, I guess that
there will be several questions coming up from the team when I present it to them.
I wanted to make sure that I have all the answers before I jump in. Here are the questions that I wanted to get answered.

Designing the database schemaHow do I design my database schema, modify and manage the database objects? What designer can I use? Is it really handy? What are the tools that I can use to connect to my SQL Server CE database to view and manage my data? How do I create
a database diagram?

Moving Data - import/exportHow do I create a SQL Server CE database from my SQL Server 2005
database? How do I upgrade my SQL Server CE database to SQL Server 2005?
How do I import data from or Export data to SQL Server 2005?

.NET programmingHow easy it is to write a .NET application which connects to my data and operates on it? What kind of programming interfaces, class libraries, language choices do I have? Can I still write ADO.NET programming for SQL Server CE?
Will there be a learning curve?

Business Intelligence and ReportingWill I be able to create Business Intelligence applications on top of my database? Will I be able to use Reporting services? Will the ReportViewer work with my database?

Schema ManagementHow do I programmatically access the Database Schema? How do I Query Schema Information? What all schema management operations are possible programmatically?

Pros and ConsWhat are the capabilities and what are the limitations of SQL Server CE?

DeploymentHow do I deploy my application? What are the dependencies that I need to ship with my application? Do I need any runtime installed on the target machine?

Web ApplicationsCan I use SSCE in a Web Application?

AdministrationWhat if my database grows? Do I need to worry before it is 4 GB? Will the performance be good enough? What do I do if the database still grows? Does it work on a multi-user environment? What are the administrative activities needed?

TSQL ProgrammingSSCE does not support Stored Procedures, Triggers and Views. Will it affect me and my development team in any manner? How do I organize and manage my database queries/code for better maintainability and productivity?

TrainingSSCE is new to my team. What all should they need to know to write correct queries? What are the changes/differences to be noted? Where can I find training materials/Tutorials? How do I get help if I land into a problem?

SynchronizationHow do I synchronize my SSCE database with a central Server? Can I synchronize with an external server (outside my local network)? Do I need direct access to the server? What if the server is behind a firewall? Is there a way to do a sync through a Web Service? Can I sync with a different database server, say Oracle
or DB2? What is Remote Data Access and How do my applications benefit from it?

The rest of my efforts were to find answers to the above questions. I will share my findings in the next article. If you have a few more questions and wants to get
them answered, please share them with me in the discussion forum.

Conclusions

We have had a closer look into SQL Server CE. Microsoft is recommending
SQL Server CE as the default choice for single user desktop applications. I will keep sharing my findings about this inprocess-lightweight database engine in the future articles.

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands
of articles and SQL scripts, a library of free eBooks, a weekly database news roundup,
a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals
that makes it such a success.