Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

A Closer Look At SQL Server Compact Edition

By Jacob Sebastian,

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:

  1. It supports Full Referential Integrity with Cascading Deletes and Updates
  2. Transaction Support
  3. Scrollable and Updateable Cursors
  4. Supports JOINS
  5. Supports GROUP BY, HAVING and AGGREGATE functions
  6. Password Protected Data File
  7. 128 bit file level encryption
  8. Supports multiple connections (from the same machine)
  9. Manage the database from SQL Server 2005 Management Studio
  10. 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.

  1. Designing the database schema
    How 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?
  2. Moving Data - import/export
    How 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?
  3. .NET programming
    How 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?
  4. Business Intelligence and Reporting
    Will 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?
  5. Schema Management
    How do I programmatically access the Database Schema? How do I Query Schema Information? What all schema management operations are possible programmatically?
  6. Pros and Cons
    What are the capabilities and what are the limitations of SQL Server CE?
  7. Deployment
    How 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?
  8. Web Applications
    Can I use SSCE in a Web Application?
  9. Administration
    What 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?
  10. TSQL Programming
    SSCE 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?
  11. Training
    SSCE 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?
  12. Synchronization
    How 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.

Total article views: 9845 | Views in the last 30 days: 8
 
Related Articles
FORUM

compact database backups

compact database backups

FORUM

A small app that utilizes SQL Compact database

cool use of sql compact database

FORUM

Sql Compact 3.5

Sql Compact Programmatically Installed????

FORUM

application hangs

application hangs on sql 2000 database which is running on windows 2003 server

FORUM

Database Security with Distribuatable Application

How can we secure a Database which is distributed with an Application - (using MS Sql Server 2005 / ...

Tags
 
Contribute

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.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones