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.