Sync Services For ADO.NET

My favorite embedded database for Windows Phones, laptops, tablets and desktops has been released to the Web along with Visual Studio 2010.

New features for SQL Server Compact 3.5 SP2 include:

Supports working with a SQL Server Compact 3.5 database using the Transact-SQL Editor in Visual Studio 2010. The Transact-SQL Editor can be used to run free-text Transact-SQL queries against a SQL Server Compact 3.5 database. The Transact-SQL Editor also provides the ability to view and save detailed estimated and actual query show-plans for SQL Server Compact 3.5 databases. Previously, the functionality provided by the Transact-SQL Editor was only available through SQL Server Management Studio.
New classes and members named SqlCeChangeTracking have been added to the System.Data.SqlServerCe namespace to expose the internal change tracking feature used by Sync Framework to track changes in the database. The SQL Server Compact change tracking infrastructure maintains information about inserts, deletes, and updates performed on a table that has been enabled for change tracking. This information is stored both in columns added to the tracked table and in system tables maintained by the tracking infrastructure. By using System.Data.SqlServerCe.SqlCeChangeTracking one can configure, enable, and disable change tracking on a table, and also access the tracking data maintained for a table. The API can be used to provide functionality in a number of scenarios. For example it can be used to provide custom implementations of client-to-server or client-to-client sync for occasionally connected systems (OCS) or to implement a custom listener application.
The managed assemblies of SQL Server Compact for use by the applications that privately deploy SQL Server Compact are installed in the folder %Program Files%\Microsoft SQL Server Compact Edition\v3.5\Private. Using these assemblies ensure that the application uses the privately deployed version of Compact even when a lower version of SQL Server Compact 3.5 is installed in the GAC.
Visual Studio 2010 installs both the 32-bit and 64-bit versions of SQL Server Compact 3.5 SP2 on a 64-bit machine. If a SQL Server Compact application is deployed using Click Once in Visual Studio 2010 then both the 32-bit and the 64-bit version of SQL Server Compact are installed on a 64-bit machine
SQL Server Compact 3.5 SP2 adds support for Windows Mobile 6.5, Windows 7 and Windows Server 2008 R2, and can sync data using Merge Replication and RDA with SQL Server 2008 R2 November CTP.
The SqlCeReplication object gets a new property called PostSyncCleanup which you can use to prevent SQL Server Compact from Updating Statistics after an initial Merge Replication initialization. This has the potential to shave a substantial amount of time off of your initial syncs depending on the size of your database.

In addition to these new features, the following hotfixes from SQL Server 2005 Compact Edition or SQL Server Compact 3.5 SP1 have been rolled up in SQL Server Compact 3.5 SP2:

http://support.microsoft.com/kb/953259: Error message when you run an SQL statement that uses the Charindex function in a database that uses the Czech locale in SQL Server 2005 Compact Edition: "The function is not recognized by SQL Server Compact Edition"
http://support.microsoft.com/kb/958478: Error message when you run a "LINQ to Entities" query that uses a string parameter or a binary parameter against a SQL Server Compact 3.5 database: "The ntext and image data types cannot be used in WHERE, HAVING, GROUP BY, ON, or IN clauses"
http://support.microsoft.com/kb/959697: Error message when you try to open a database file from a CD in SQL Server Compact 3.5 with Service Pack 1: "Internal Error using read only database file"
http://support.microsoft.com/kb/960142: An error message is logged, and the synchronization may take a long time to finish when you use an application to synchronize a merge replication that contains a SQL Server 2005 Compact Edition subscriber
http://support.microsoft.com/kb/963060: An error message is logged, and the synchronization may take a long time to finish when you synchronize a merge replication that contains a SQL Server Compact 3.5 subscriber: "UpdateStatistics Start app=<UserAppName>.exe"
http://support.microsoft.com/kb/967963: Some rows are deleted when you repair a database by using the Repair method together with the RepairOption.RecoverCorruptedRows option in SQL Server 2005 Compact Edition and in SQL Server Compact 3.5
http://support.microsoft.com/kb/968171: Error message when you try to create an encrypted database in SQL Server 2005 Compact Edition: "The operating system does not support encryption"
http://support.microsoft.com/kb/968864: Error message when you run a query in SQL Server Compact 3.5: "The column name cannot be resolved to a table. Specify the table to which the column belongs"
http://support.microsoft.com/kb/969858: Non-convergence occurs when you synchronize a SQL Server Compact 3.5 client database with the server by using Sync Services for ADO.NET in a Hub-And-Spoke configuration
http://support.microsoft.com/kb/970269: Access violations occur when you run an application under heavy load conditions after you install the 64-bit version SQL Server Compact 3.5 Service Pack 1
http://support.microsoft.com/kb/970414: Initial synchronization of a replication to SQL Server Compact 3.5 subscribers takes significant time to finish
http://support.microsoft.com/kb/970915: Error message when you synchronize a merge replication with SQL Server 2005 Compact Edition subscribers: "A column ID occurred more than once in the specification. HRESULT 0x80040E3E (0)"
http://support.microsoft.com/kb/971027: Error message when you upgrade a very large database to SQL Server Compact 3.5: "The database file is larger than the configured maximum database size. This setting takes effect on the first concurrent database connection only"
http://support.microsoft.com/kb/971273: You do not receive error messages when you run a query in a managed application that returns columns of invalid values in SQL Server Compact 3.5
http://support.microsoft.com/kb/971970: You cannot insert rows or upload changes into the SQL Server 2005 Compact Edition subscriber tables after you run the "sp_changemergearticle" stored procedure or you add a new merge publication article when another article has an IDENTITY column
http://support.microsoft.com/kb/972002: Error message when you try to create an encrypted database in SQL Server Compact 3.5: "The operating system does not support encryption"
http://support.microsoft.com/kb/972390: The application enters into an infinite loop when you run an application that uses Microsoft Synchronization Services for ADO.NET to synchronize a SQL Server Compact 3.5 database
http://support.microsoft.com/kb/972776: When the application calls the SqlCeConnection.Close method or the SqlCeConnection.Dispose method in SQL Server Compact 3.5, the application may stop responding at the method call
http://support.microsoft.com/kb/974068: Error message when an application inserts a value into a foreign key column in SQL Server Compact 3.5: "No key matching the described characteristics could be found within the current range"

Web downloads for SQL Server Compact 3.5 SP2 is as listed below:

SQL Server Compact 3.5 SP2 for Windows desktop (32-bit and 64-bit)

Note that the file available for download is a 6 MB self-extracting executable (exe) file that contains the 32-bit and the 64-bit Windows Installer (MSI) files for installing SQL Server Compact 3.5 SP2 on a 32-bit and a 64-bit Computer. It is important to install both the 32-bit and the 64-bit version of the SQL Server Compact 3.5 SP2 MSI on a 64-bit Computer. Existing SQL Server Compact 3.5 applications may fail if only the 32-bit version of the MSI file is installed on the 64-bit computer. Developers should chain both the 32-bit and the 64-bit MSI files with their applications and install both of them on the 64-bit Computer. Refer to the KB article for more information.

SQL Server Compact 3.5 SP2 for Windows mobile devices (all platforms & processors)

SQL Server Compact 3.5 SP2 Server Tools (32-bit and 64-bit)

SQL Server Compact 3.5 SP2 Books Online (Note that the books online will be available for download by the third week of April 2010)

SQL Server Compact 3.5 SP2 Samples

Visual Studio 2010 and .NET Framework 4

This is a great release for SQL Server Compact that adds some important new features, squashes a bunch of bugs and adds support for our newest operating systems. I strongly recommend you update your existing SSCE runtimes with SQL Server Compact 3.5 SP2.

Keep on Synching,

Rob

Who remembers using Remote Data Access to synchronize data between SQL Server and SQL Server Compact? I certainly do!

Before I dove head first into the world of Merge Replication, I always used RDA to get my customers up and running quickly. Mobilizing an organization’s workforce quickly and easily is what it’s all about so they can start reaping the benefits. In addition to a speedy time to market, there’s no faster or more scalable mobile sync technology on the market anywhere.

So why wouldn’t I always use RDA? Here’s a quick list:

You’re using Identity columns.
You want to replicate schema changes to the client.
You want change tracking on both the client and server to perform diffs of each of the tables during a sync instead of re-downloading the entire table.
You want to automatically resolve conflicts that arise when 2 people update the same data.
You want referential integrity constraints to be pushed down to the client database from SQL Server.
You don’t want to write code to perform synchronization or filter data.

If anything on the above list applied to you, you would shift to Merge Replication because it could manage ranges of Identity columns, push down schema changes, only sync data differences, resolve conflicts and push down a database’s referential integrity constraints. Merge requires almost no code to get started and tables and columns are filtered visually via a wizard.

So why might you choose to use RDA? Here’s another list:

Your Primary Keys use GUIDs instead of Identity columns.
Users don’t overwrite each other’s data so you don’t need conflict resolution. The rule of “Last in Wins” works for you.
While you want indexes to be pushed down, you don’t care if your local SSCE database has referential integrity constraints applied.
You want to wrap the changes you upload to SQL Server in a transaction so that all changes are applied or none of them are.
Change tracking on the client is good enough and re-downloading updated server tables doesn’t take too long.
You developers don’t mind writing some sync code.
Be able to execute SQL and Stored Procedures directly against SQL Server via IIS.
You’re downloading read-only data.

If your solution meets the criteria in the list above, you’re probably a good candidate for using RDA instead of Merge. Are there any other choices out there?

Back at MEDC 2007, we announced a new data replication technology for devices called Occasionally Connected Sync that would sit somewhere between RDA and Merge. OCS as it used to be called was renamed Sync Services for ADO.NET and then was eventually merged into the Sync Framework.

The Sync Framework is a developer-focused technology:

Supports conflict resolution.
Change tracking on the server as well as the client so that only data differences are exchanged.
Peer to Peer sync in the forthcoming v2 of Sync Framework.
Sync with databases other than SQL Server.
Best suited for SSCE running on a desktop or laptop.

The clearest differentiation that the Sync Framework has over Merge is its provider model which allows it to sync with other ADO.NET databases like Oracle or DB2. SQL Server supports built-in P2P Transactional replication and v2 of the Sync Framework will allow you to do this via WCF. If you development team doesn’t mind writing lots of sync code and needs to support scenarios like synchronizing with other databases from SSCE on the desktop, then the Sync Framework might be the way to go for you. I wouldn’t yet recommend the Sync Framework for device sync since its wire protocol is currently based on the DataSet which may cause out of memory errors on Windows phones with limited working sets.

So where does this leave RDA?

The reason I’m writing this blog post is because time and time again I run into customer sync scenarios that don’t always need the power of Merge or the extra flexibility of the Sync Framework. Most field service applications follow the same kind of pattern:

Lots of download-only lookup/reference tables that aren’t changed by the user.
Tables that are pushed down to the device that tell a user where to go and what to do.
Tables (sometimes empty) that are used to capture data from the user in the field that are upload-only.

These kinds of schemas don’t require conflict resolvers or server change tracking and are therefore well suited for RDA.

What’s the big benefit of using RDA if a sync scenario meets its criteria?

You won’t modify SQL Server’s schema with GUIDs and Triggers.
You won’t degrade the performance of SQL Server by having it track changes and maintain extra metadata.
You will have the fastest and most scalable sync solution with least amount of hardware.
Time to market is shorter.

The big takeaway here is that I want you to consider your sync solution carefully before choosing a technology. If your customer’s needs are met by RDA, then you should use it and reap the benefits of developing and deploying a simpler solution with fewer moving parts.

Remember Occam’s Razor.

-Rob