Chapter 22. Placing SQL Server in your pocket
SQL Server is a database engine that can scale to cope with the largest data centers and astronomically sized datasets, but it can also scale down to support incredibly small and resource-constrained scenarios.
Many workforces are increasingly becoming mobile and working outside the fixed confines of an office environment. This presents a challenge to system architects, as they need to consider how to make their company data accessible to field workers while coping with issues such as the high costs and slow speeds (and potential unavailability) of network connectivity.
In building a traditional three-tier distributed application, you may have used SQL Server 2008 to store your data on a large centralized server and had clients retrieve data over the network on an as-needed basis.
When building mobile applications for devices such as laptops, Tablet PCs, and PDAs, you’re more likely to place a local data store on each client. This is required to store data so that the application can continue to function while offline. It may also be desirable to locally cache large fairly static datasets (such as customer or product lists) to avoid repeatedly transferring data over expensive or slow networks.
SQL Server Compact Edition is the smallest member of the SQL Server family and is optimized to be an embedded database for single-user applications running on the Tablet PC, Windows Mobile PDA, and desktop platforms. It’s designed to fit in your pocket with a disk footprint of less than 4 MB.
Design goals
Providing a complete relational database engine that also supports replication and synchronization in less than 4 MB of disk space does lead to some trade-offs. For starters, SQL Server Compact Edition has a completely separate implementation that doesn’t share a common code base with any other edition of SQL Server.
SQL Server Compact 3.5 has been designed with the following concepts and goals in mind:
- Cross-platform— SQL Server Compact Edition runtimes are available for a wide range of devices running the Windows CE and Windows Mobile operating systems on ARM, MIPS, and SH4 platforms. This is in addition to the traditional Windows operating system running on x86 CPUs found in your desktop or Tablet PC machine.
- Small— The typical runtime size is 2 to 3 MB of disk space with less than 5 MB of RAM required—although the exact requirements depend upon OS and processor type.
- Secure— The contents of database files can be encrypted with 128-bit encryption that complies with industry standards such as RSA and AES.
- Data synchronization— Mobility gives the freedom to work anywhere at the expense of making it harder to provide a consistent view across the enterprise. Although a local data store is useful, it typically needs to be synchronized back to a centralized server at some stage to ensure that all users share a common set of data. SQL Server Compact Edition supports merge replication and remote data access (RDA) synchronization technologies.
- Multi-user support— Although there’s no concept of database users or access roles, SQL Server Compact Edition supports multiple applications accessing one database file at the same time. The storage engine provides row- and page-level data locking, along with various isolation levels to help guarantee data integrity during concurrency.
- Familiar tools— The database engine is integrated into familiar development tools such as SQL Server Management Studio (SSMS) and Visual Studio to give developers an experience as close to the full version of SQL Server as possible while promoting the reuse of existing techniques and knowledge.
- Simplified deployment— As well as traditional CAB- and MSI-based installations that require administrative privileges, SQL Server Compact Edition supports XCOPY-style deployment. You can include the database engine as part of your own setup and deploy it within your application folder.
The underlying design goal is to provide a complete SQL relational database management system that allows the reuse of existing developer experience and training while being mindful of the resource constraints and runtime environment typical of smaller mobile and embedded systems.
Architecture
SQL Server Compact Edition doesn’t run as a Windows service that clients connect to via inter-process communication mechanisms. Instead, when an application wants to access a SQL CE database, it creates an instance of the database engine directly within the application process. Internally the database engine is a set of dynamic link libraries as demonstrated in figure 1.
Figure 1. An overview of the architecture of SQL Server Compact Edition showing the various DLLs that make up the database engine. These DLLs are loaded directly into the process space of the client application wanting access to the database.
Because the database engine is hosted within the client application, traditional data access penalties such as the number of server round trips and network latency don’t have the same impact on performance. Microsoft has used this fact to their advantage in an effort to further reduce the size of the core database engine. Traditional RDBMS features such as views, triggers, and stored procedures aren’t implemented. Instead the developer is encouraged to implement these features within the client application via procedural code in languages such as C++, C#, or VB.NET while accessing the database via standard ADO.NET functionality. Unique database engine features such as low-level row and index access mechanisms further reduce the penalty of implementing these features outside of the core database engine.
A SQL Server Compact Edition database is physically implemented as a single file with a 4 GB size limit. The traditional file extension is *.SDF. Database files are portable, allowing a database file to be generated on a server and then copied for use by one or more PDA devices.
Deployment
You can use a number of mechanisms to deploy SQL Server Compact Edition onto client machines and devices. If you intend to distribute the database engine as part of your own product, the first step is to sign up for a redistribution license by filling in a form available at http://www.microsoft.com/Sqlserver/2005/en/us/compact-redistribute.aspx. This enables Microsoft to keep track of the number of ISVs distributing SQL Server Compact Edition and potentially allows them to notify you of issues such as critical security updates.
Deploying on a desktop
The SQL Server Compact 3.5 runtime components support both 32-bit (x86) and 64-bit (x64) environments, and are required to run SQL Server Compact 3.5–based applications on the desktop. The runtime components can be downloaded from the web in the form of an MSI-based installation[1] named SSCERuntime-ENU-x86.msi or SSCERuntime-ENU-x64.msi for 32-bit and 64-bit systems, respectively. Service Pack 1 or above will be required for true 64-bit support.
1 Runtime components in the form of an MSI-based installation available at http://www.microsoft.com/downloads/details.aspx?FamilyID=dc614aee-7e1c-4881-9c32-3a6ce53384d9&displaylang=en.
The MSI installation requires administrative privileges, as it installs a single copy of the database engine in a system-wide location. Having a sole installation of the database engine reduces disk usage when it’s used by multiple applications and enables administrators and mechanisms such as Windows Update to easily perform systemwide updates.
Deploying on a device
Deployment to devices that run the Windows Embedded CE or Windows Mobile operating systems is slightly more complicated, due to the wider range of platforms and processors available. The SQL Server Compact 3.5 for Devices installation (SSCEDeviceRuntime-ENU.msi)[2] installs a series of CAB files within the %PROGRAMFILES%Microsoft SQL Server Compact Editionv3.5Devices folder that can then be used to install the database engine on each supported combination of platform and processor type.
2 SQL Server Compact 3.5 for Devices installation available at http://www.microsoft.com/downloads/details.aspx?FamilyId=FCE9ABBF-F807-45D6-A457-AB5615001C8F&displaylang=en.
XCOPY deployment
If database functionality isn’t a central part of your application, or will be installed by consumers rather than IT administrators, it may be more convenient to install SQL Server Compact Edition behind the scenes as part of your own installation procedure rather than having a separate installer. The downloads outlined previously also provide a series of loosely bundled .DLL files such as sqlceoledb35.dll. These files implement the database engine and can be packaged and deployed within your own application folder. No COM-style registration is required. The disadvantage of this deployment model is that system updates won’t be able to update your instance of the database engine. In some cases, the advantage of a streamlined application installation is worth the complexity of handling security patches yourself.
Tool support
The SQL Server Compact database engine is supported by the same set of development tools used by desktop editions of SQL Server. This enables you to use the tools you’re already familiar with to create, maintain, and manipulate SQL Server Compact databases. As an example of this tight integration, figure 2 shows the Connect to Server dialog box within SQL Server Management Studio.
Figure 2. The Connect to Server dialog box within SQL Server Management Studio allows a developer to connect to a SQL Server Compact Edition database by selecting SQL Server Compact Edition as the server type.
All you need to modify in order to connect to a SQL Server Compact Edition database is to set the Server Type to SQL Server Compact Edition. This enables you to connect to a Compact Edition database file located on your current machine or any ActiveSync-connected device.
Once connected to a database file, you can use most SQL Server Management Studio features. For example, figure 3 demonstrates the Object Explorer pane, and executing SQL queries via a query window. Even more advanced features, such as the display of estimated and actual query plans, are supported. But many of these features have simplified or reduced feature sets reflecting the limited capabilities of the underlying database engine. As an example, most dialog boxes don’t support scripting their changes to a query window.
Figure 3. An example session with SQL Server Management Studio demonstrating the Object Explorer and Query panes accessing a SQL Server Compact Edition database.
Most programmers will be more familiar with working within the Visual Studio IDE, and SQL Server Compact Edition has the same richness of support within this tool. For example, a new SQL Server Compact Edition database file can be added to a project by selecting the Local Database option within the Add New Project Item dialog box.
Once a database file has been added, double-clicking on it within Solution Explorer will open up the Server Explorer pane. This pane provides some of the same functionality available within SQL Server Management Studio and enables the user to modify the database schema and perform queries against it.
The seamless integration with SQL Server Management Studio and Visual Studio also makes it easier to migrate a solution between the full SQL Server database engine and the Compact Edition. In many cases, it’s enough to switch ADO.NET data providers in code and verify that your SQL statements don’t rely on features or optional T-SQL syntax unique to one RDBMS implementation.
Developer tool integration is unfortunately tied to a particular combination of tool and database engine versions. In order to use SQL Server Compact 3.5 and get full support of the IDE features, you’ll need Visual Studio 2008 and SQL Server Management Studio 2008. If you try to open a database file from an older version of the database engine (such as SQL Server 2005 Mobile Edition), you may be prompted to upgrade the file to the latest file format. This is an irreversible process that’ll render the database file unreadable by the GUI tools within earlier versions of the IDE.
Programming support
SQL Server Compact Edition provides both a native OLE DB provider and a managed ADO.NET data provider in order to support a wide range of development tools and environments.
To use the managed ADO.NET data provider, you need to add a reference to the System.Data.SqlServerCe.dll assembly and make use of the classes within the System.Data.SqlServerCe namespace. The developer experience is similar to using the standard System.Data.SqlClient namespace for SQL Server 2008. The main difference is that instead of a Sql prefix, the ADO.NET data provider classes use a SqlCe prefix.
As well as standard ADO.NET data provider functionality, the SQL Server Compact Edition provider includes a number of extensions that can potentially provide more efficient access to data. These features tend to make up for the lack of views, triggers, and stored procedures. For example, listing 1 demonstrates how the query processor can be bypassed completely to more efficiently implement a select * from customers where cust_name like 'F%' style query.
Listing 1. Bypassing the query processor
The code sample starts off by creating a SqlCeCommand instance 
. This uses a previously established (and opened) database connection called cnn. The command string is the name of the Customers table. This is further reinforced by setting the CommandType property 
to TableDirect. TableDirect mode allows you to access all rows within the specified table, and in effect behaves similar to a traditional database cursor.
When using TableDirect mode, you can also optionally provide an index range to filter the rows that will be returned. In this example, we restrict
the result set 
to only include rows where the IDX_CustName index has a value beginning with the letter F.
Once we’ve specified which rows from the table we want to access, we’re ready to send the command to the database engine via
a call to the ExecuteResultSet method 
. Because we’ve explicitly provided the database engine with the required table and index names, the database engine will
have a lot less difficulty processing this query than if we passed it an opaque string containing T-SQL.
The call to ExecuteResultSet will return quickly, as it has in effect opened the equivalent of a database cursor. It’s only when we start stepping over
each row with successive calls to the Read method that data is fetched. While processing each row, you can access the columns within the result set by using the indexer
operator 
. Because the result set was created with the ResultSetOptions.Sensitive flag, it’s even possible to assign columns new values, and these changes will make their way back to the database engine.
Although ADO.NET provider features such as TableDirect mode can provide immense speed and performance benefits, the disadvantage of such techniques is that they tie the application logic closer to database implementation details such as the chosen indexing strategy. Depending upon how stable your database schema is, this could lead to brittle and hard-to-maintain code.
Summary
SQL Server Compact Edition has grown into a mature and comprehensive database platform for mobile and disconnected environments. It can be used for a wide range of scenarios, including local online and offline data caches, client-side data stores, or even as a structured document format for data transfer between devices.
A major advantage of SQL Server Compact Edition over other mobile database products is the ability to leverage many of the same tools and development practices familiar to desktop SQL Server developers. Not only does this shorten the time required to come up to speed with the new database engine, but it also provides a reasonable transition path between SQL Server Express Edition and SQL Server Compact Edition, should an application grow to exceed the capabilities of SQL Server Compact Edition, or conversely need to be migrated to a PDA-based application.
I encourage you to give SQL Server Compact Edition a try the next time you need an easy-to-deploy-and-maintain database engine for a mobile or offline based application.
About the author
Christopher Fairbairn is a Device Application Development MVP from Christchurch, New Zealand, with more than eight years experience in mobile application development on a range of platforms including Palm OS, J2ME, Windows Mobile, and the iPhone. Christopher actively blogs at http://www.christec.co.nz/blog/ and loves to get feedback from the community.