OakLeaf Systems: SQL Server Compact Edition and Synchronization Updates

Monday, April 02, 2007

SQL Server Compact Edition and Synchronization Updates

Steve Lasker's Creating your SQL Server Compact Edition database and schema in code post proposes to create your local SSCE database on the fly from T-SQL commands that you store as an application resource. SSCE's DDL doesn't accept multiple (batched) commands but SQL Server Management Studio [Express] (SSMS[X]) SP2 hides this limitation from you by allowing GO to separate batched commands.

Steve suggests the neat trick of adding the batch file that you tested and saved in SSMS[X] to the project's resources, and then use String.Split in code like the following to obtain an array of commands:

Dim commands() As String
commands = My.Resources.DatabaseCreation.Split( _
   New String() {"GO"}, StringSplitOptions.RemoveEmptyEntries)

If you use Orcas's Sync Designer to generate the local database cache from the server database's metadata, you must add foreign key and other constraints, as well as indexes, to SSCE with code. So Steve's approach applies to the SSCE Sync Framework, too.

Click here for a table that describes the most important SQL Server 2005 [Express] features that are missing in SSCE. My "Lighten Up Your Local Databases" article from the March 2007 Visual Studio Magazine issue shows you how to take full advantage of SSCE as a local data cache.

SyncGuru Rafik Robeal's sixth Sync Framework demo, Decoupling of Change Tracking Metadata, separates tracking information (for example, CreationDate, LastEditDate, and ClientID fields) into a separate tracking table. Using a separate tracking table minimizes the effect of synchronizing client caches on a production database's schema. Rafik says:

The good news though is that the building a decoupled change tracking layer is not a big deal. In the decoupled model, the base table schema remains intact. As the demo shows, three triggers (insert, update, and delete) and separate tracking table are enough to build equivalent change tacking model. Although, the sync commands need to change since it needs to grab corresponding tracking information by joining the base table with the tracking table, the changes are simple and easy to understand.