Caching with SQL Server Compact and the Microsoft Sync Framework

for ADO.NET (Cont.)

The Power of SQL Server in a Compact Footprint

While many IT-supported applications were successful deploying SQL Server Express, the
majority of applications didn’t have direct IT support. Many of these applications were
persisting their data as XML using serialized DataSets as they couldn’t use SQL Server
Express due to its deployment requirements. With our V1 scoping to relational data and our
design goals prioritizing non-admin, Web deployment models, we knew we had to enable a
local store that could deliver the power of SQL Server in a compact footprint.

Since 2001, SQL Server has had an embedded database engine available to the Windows
Mobile platform known as SQL Server CE. Targeted at devices, Microsoft designed it for
constrained environments, but it still had the power to handle relatively large amounts of
data in a compact footprint. With a footprint under 2 MB, it became a popular choice for
applications that need the power of SQL Server in a compact footprint. Several applications
in Windows Vista, Media Center PC, and MSN Client all embedded this engine within their
applications. You likely didn’t even know you had SQL Server Compact running, and that’s
one of its key advantages. It’s simply embedded cleanly within each application.

In 2005, SQL Server CE became SQL Server Mobile to recognize its expanded usage on the
Mobile platform of the Tablet PC. With Visual Studio 2005 focusing on client development,
we knew we couldn’t wait for Visual Studio 2008 to enable developers looking to cache data
for optimized online or those building offline applications. We knew we’d take a while to
complete Sync Services and SQL Server Mobile simply needed a licensing change. With
Visual Studio 2005 Service Pack 1 we released SQL Server Compact 3.1 as the successor to
SQL Server Mobile and SQL Server CE to address all our current Windows desktop operating
systems.

Some of the key benefits of SQL Server Compact include the following:

 ~1MB for the storage engine and query processor.

 Consistent engine and data format for all Windows operating systems from the
phone to the desktop.
 Runs in-process (i.e., embedded).
 Deployed as DLLs eliminating the need for a Windows service.
 Supports central installation with traditional MSI (requires administrative rights).
 Supports private deployment of the DLLs within the application directory (enabling
non-admin deployment).
 Full side-by-side support for multiple versions enabling different applications to
embed SQL Server Compact without fear that other applications using newer
versions may destabilize their application.
 Rich subset of query programming of SQL Server.
 Rich subset of data types of SQL Server.
 Robust data programmability through ADO.NET with native programmability through
OLE DB.
 ISAM-like APIs for bypassing the query processor, working directly against the data.
 Updatable, scrollable cursors eliminating the need for duplicate, in-memory copies.
 Support for up to 4GB of storage, with increasing storage in future versions.
  Rich sync technologies including Merge Replication, RDA, and Sync Services for
ADO.NET.
 Full support for SQL reporting controls.
 Single-file code-free file format enabling a safe document-centric approach to data
storage.
 Support for custom extensions enabling double-click association with your
application.
 Support for network storage, such as network redirected documents and settings.
 Concurrent application support from multiple processes or threads enabling sharing
of data on a single user’s machine.
 Best of all, it’s free to deploy, license, distribute, and embed within your applications.
 With SQL Server Compact 3.1 shipping with Visual Studio 2005 SP1, developers can
fill the need between XML storage and SQL Server Express. With Visual Studio 2008,
SQL Server Compact 3.5 ships as the default local database with integrated designer
support including the support of Sync Services for ADO.NET and LINQ to SQL. With
the release of the ADO.NET Entity Framework, SQL Server Compact will be used to
persist ADO.NET entities locally within your application.
 Future versions of SQL Server Compact will continue to take advantage of the unique
embedded nature to provide an integrated experience within your application
programming model. SQL Server Express will certainly continue for developers
requiring the same programming model with our data service SKUs, including
Workgroup, Standard and Enterprise. SQL Server Express and SQL Server Compact
are not meant to compete, but rather provide overlapping technologies enabling the
unique needs of custom applications. They are best when used together with SQL
Server Express as the free entry point to Microsoft’s data service platform and SQL
Server Compact as the local cache utilizing Sync Services to synchronize the shared
data and local cache.

Simple to Complex

As developers are continually challenged with new emerging technologies, it becomes

difficult to invest a lot of time learning a technology just to get the simple things done. To
enable the simple scenarios with the complexity as needed, Sync Services took an
incremental approach to its design. This includes simple snapshot caching, enabling
developers to add incremental changes, or ultimately the ability to send changes from the
local store back up to the server.

Additionally, many applications may start off as two-tier applications and over time they
expand requiring n-tier architectures. Rather than assume you must re-architect the
application to make this move, Sync Services was designed to easily move from two-tier to
n-tier architectures, including service-oriented architectures (SOA). Figure 3 shows how
the components are broken into groupings. You can see server components, client
components, and a SyncAgent which orchestrates the overall synchronization process.
Figure 3: High-level architecture for Sync Services for ADO.NET.

Building On Your ADO.NET Skills

One of the significant improvements between classic ADO and ADO.NET was the ability to
drill into and configure the commands which were executed against the server. With
ADO.NET, components such as the DataAdapter retrieve data from your database
populating a disconnected DataSet sending the data across the wire. As inserts, updates
and deletes are made, the DataAdapter can shred the changes based on the RowVersion in
the DataSet, executing insert, update and delete commands that may have been configured
for you, or customized to suit the needs of your specific application using custom T-SQL,
sprocs, views or functions. However the DataAdapter is designed for a one-time retrieval of
data. It doesn’t have the infrastructure to retrieve incremental changes. As seen in Figure
4, Sync Services uses a SyncAdapter, which is based on the DataAdapter programming
model. The SelectCommand is replaced with three new commands for retrieving incremental
changes.

Figure 4: SyncAdapters follow a familiar pattern to ADO.NET DataAdapters.

Configuring each command and its associated parameters can be quite tedious, especially
when you just need the basics. Similar to the SqlCommandBuilder, Sync Services adds a
SqlSyncAdapterBuilder which allows you to specify a few configuration options to create a
fully-configured SyncAdapter. While Sync Services can work with any ADO.NET provider, the
SqlSyncAdapterBuilder is specific for Microsoft SQL Server enabling the “Better on SQL
Server” design goal.
Server Configuration

Figure 3 shows the green portions representing the server-side configuration. As most
database servers are shared servers for several applications, each application may only
require a subset of the data, or the data may need to be reshaped or de-normalized before
it travels down to the client. For this reason, Sync Services exposes granular configuration
with ADO.NET DbCommands enabling any ADO.NET data provider. For each logical table
you wish to synchronize, a SyncAdapter is configured.

One of the challenges with DataAdapters is the ability to update parent-child relationships.
With common referential integrity patterns, child deletes must first be executed before the
parents can be deleted. Likewise, parents must first be created before children can be
inserted. The DbServerSyncProvider contains a collection of SyncAdapters that enables
the hierarchal execution of commands based on the order of SyncAdapters in the collection.

Client Configuration

The client has two main components. The client provider, in this case the relational store of
SQL Server Compact, and the SyncAgent which orchestrates the synchronization process.
You could sort of think of the SyncAgent as the thing that does the food shopping for you
once you’ve given it the shopping list, and location of the food store. While server databases
tend to be shared resources, client databases are typically specific to the application. With
this design simplicity there’s no need for configuration of the individual commands for each
local table. However as client applications get installed on potentially thousands of clients,
there are a number of other deployment options. Other configuration options such as what
subset of server data each client actually requires and the ability to group several tables
within a transactional sync operation can be achieved with a SyncGroup configured through
the Configuration object of the SyncAgent. Once the SyncAgent is configured with a
RemoteProvider, LocalProvider and the SyncTables, you can simply call the Synchronize
method.

In two-tier architectures the RemoteProvider is the DbServerSyncProvider. Later in this

article, in the section called “Going N-tier with WCF,” I’ll discuss how you move from a two-
tier architecture to something with multiple tiers. To enable different data sources ranging
from relational databases to files, as well as custom objects, Sync Services follows a
standard provider model. Sync Services 1.0 includes a relational provider for SQL Server
Compact, or the SqlCeClientSyncProvider.

Configuring the SqlCeClientSyncProvider only requires a connection string for the local
database with the default option to automatically create the local database and schema.
Similar to the auto creation of the database, the SyncTable configuration enables options
for creating the tables and whether local changes to the table should be synched back to the
server. Several events are available within the client and server providers enabling conflict
detection and business rule validation.

Designer Productivity

To meet our developer productivity design goal, Visual Studio 2008 adds a Sync Designer to
simplify configuration of Sync Services for ADO.NET. Similar to the design goals for Sync
Services, the Sync Designer starts simple and lets you incrementally add complex
requirements as needed. The Sync Designer focuses on the following:
  Configure SQL Server databases for change tracking, including tracking columns,
tracking tables (known as Tombstone tables) and triggers to maintain the tracking
columns.
 SQL scripts generation for later reuse and editing when moving from development to
production.
 Generation of typed classes for instancing the sync components, similar to the typed
Dataset and TableAdapter experiences.
 Auto creation of the local database and schema, including primary keys.
 Separation of client and server components to enable n-tier scenarios.
 Creation of WCF service contracts for n-tier enabled applications.

The Visual Studio 2008 Sync Designer is focused around read-only, reference data
scenarios, so you won’t see any configuration for uploading changes. However, the designer
does generate the upload commands-they’re just not enabled. Similar to typed DataSets,
you can extend the designer-generated classes through partial types enabling the upload
commands with a single line of code per table. By selecting “View Code” from the Solution
Explorer context menu on the .sync file you can reconfigure the Customers table to enable
bidirectional synchronization as seen in Listing 1.

Other features of Sync Services that aren’t enabled through the designer but can be
extended through partial classes are:

 Filtering of columns or rows

 Foreign keys, constraints, and defaults
 Batching of changes

To get a feel for the designer simply add a Local Database Cache item to any non-Web
project type. The Local Database Cache item aggregates the configuration of Sync Services
for ADO.NET and SQL Server Compact 3.5 for the local cache. The designer will enable
connections to SQL Server databases and will automatically create a SQL Server Compact
3.5 database. With the server database selected, you can add tables to be cached based on
the default schema of the user id in the SQL Server connection string. By checking one of
the tables the designer will configure the server tracking changes using “smart defaults”.

If you can’t make server-side changes to your database, and the data is small enough to
just retrieve the entire result each time you sync, you can change the “Data to download”
combo box to “Entire table each time,” also known as snapshot. When performing snapshot
sync, the tracking combo boxes become disabled as the SQL Server configuration options
aren’t required. Assuming you want incremental changes, you can then choose which
columns will be used to track the changes. The designer will default to adding an additional
DateTime column for LastEditDate and CreationDate. If your table already has TimeStamp
columns for last updates, you can select the existing column. Sync Services stores a
common anchor value for last edit and creation columns, so you’ll need to either use
DateTime or TimeStamp on both the last edit and creation tracking columns. Since you can
only have one TimeStamp column per table, you’ll notice that if you choose TimeStamp for
the LastEdit comparison, the creation column will default to a BigInt. Timestamps are really
just big integers serialized in binary form.

Tracking Deletes

Tracking deletes is an interesting problem. If you synchronize with the server on Monday,
and come back on Wednesday asking what’s changed, how does the server know what’s
deleted unless it keeps a historical record? There are a couple of different approaches to
tracking deletes. Prior to the Sarbanes-Oxley (SOX) compliance days, it was typical to just
delete aged data. However, between SOX and increased disk storage, applications are
keeping the deleted data around. While it’s important to keep the deleted data on the
server, it’s typically not necessary to clog up your clients. When each client synchronizes,
Sync Services simply requests the list of primary keys that should be deleted locally.

Tombstones

A standard model for tracking deletes is to create a separate table that contains the primary
keys for deleted rows. Just as cemeteries use tombstones to leave an indicator of what once
was, tombstone tables have become the standard in many sync-enabled systems. Another
interesting analogy is how tombstones, if not managed, could eventually cover the earth
leaving no room for the “active participants.” Luckily data is a lot less emotionally sensitive
so a standard model is to purge tombstone records after a period of mourning. How long
you keep your tombstones is related to a balance of disk space, database sizes, and how
long you expect your users to work offline. The pre-SQL Server 2008 model could utilize a
scheduled task to run daily to purge tombstones that are older than a configured number of
days. Furthering our goal to make Sync Services run “Best on SQL Server,” SQL Server
2008 adds a feature known as SQL Server Change Tracking that dramatically simplifies this
server configuration and reduces the overhead by tracking changes deep within the SQL
Server engine. Change tracking incorporates a change retention policy that automatically
purges historical changes based on the per-table configured value.

Since Visual Studio 2008 ships prior to SQL Server 2008, and developers may need to
target pre-SQL Server 2008 servers, the Sync Designer will default to creating a Tombstone
table and the associated triggers to maintain the tracking information. As with the rest of
the SyncAdapter configuration, if the designer-generated commands don’t fit your needs,
you can easily customize these commands using sprocs, views, and functions. Sync Services
simply needs a DbCommand with results for the specific command, in this case the list of
primary keys to delete. As you move to SQL Server 2008, your configuration becomes
easier, and your sync-enabled database will simply perform better.

Setting the Hierarchical Order

Once you’ve added the cached tables, the Sync Designer will configure your server and save
the creation and undo scripts for inspection and re-execution when moving from
development to production. Back in the Configure Data Synchronization dialog box, you’ll
see your list of tables. It may not be that important for reference data, but if you intend to
send your changes back up to the server, and you want to make sure parent records, (such
as the OrderHeader table) are inserted before OrderItems and OrderItems are deleted
before OrderHeader rows are deleted, you can use the arrows to shuffle the list of tables
sorting the parents above their children. The designer sets the order of SyncAdapters within
the DbServerSyncProvider. Clicking OK to finish the Sync Designer configuration will
configure the Sync Services runtime, generate the classes for use within your application,
and execute the Synchronization method to automatically generate the SQL Server Compact
database with the schema and data you’ve selected. As the Sync Designer completes it adds
the newly created SQL Server Compact database to your project which triggers Visual
Studio to automatically prompt to create a new typed DataSet for your newly added SQL
Server Compact data source.