SQLCE Synchronization

In the previous chapter the discussion focused on the issues surrounding, and techniques involving, simple file-based synchronization using ActiveSync. In those scenarios for which it makes sense (typically where the application can use XML or other file formats to exchange data and the device will be regularly cradled for synchronization), using ActiveSync can be a very effective means of synchronizing data.

However, there are a number of other scenarios in which the application requires more robust data storage, as discussed in Chapter 5, and where the device needs to synchronize when not connected to a specific PC. In these scenarios, which typically involve a large number of devices using data from a single back-end data store (for example, a field-service automation application, where all the drivers in an organization download delivery and route information, and then later synchronize updates to the corporate database), the devices connect instead using 802.11 on a WLAN or GPRS over a WAN. For these applications, a more sophisticated synchronization process is required.

Fortunately for architects and developers, SQLCE includes two mechanisms for connecting to and exchanging data with back-end systems. These mechanisms, RDA and merge replication, also have support built in to the SqlServerCe .NET Data Provider, which makes it easy for those using the Compact Framework to take advantage of this prebuilt infrastructure. After a brief overview of the connectivity architecture and configuration of SQLCE, this chapter will focus on these two methods of synchronization to round out our discussion of the final essential architectural concept of synchronization.

Connectivity Architecture

Before applications can utilize the synchronization features of SQLCE, the SQL Server CE Server Tools shipped with VS .NET 2003 and included in the Compact Framework SDK directory (for example, sqlce20sql2ksp1.exe and sqlce20sql2ksp2.exe^[1]) must be installed on a server running IIS.^[2]

^[1] A version for service pack 3 of SQL Server 2000 is also available on MSDN at www.microsoft.com/sql/downloads/ce/sp3.asp.

^[2] See the SQL Server CE Books Online for more information.

The server tools are required because they include the SQLCE server agent shown in Figure 5-2. It is the job of the server agent^[3] to process HTTP(S) requests from the SQLCE client agent to IIS. The server agent then connects to SQL Server and either executes commands or returns rows to the client agent for processing.

^[3] This is actually implemented as an ISAPI DLL (Sscesa20.dll) that processes the HTTP request through IIS.

In order to allow the client agent to make requests of the server, one or more virtual directories in IIS must be configured with the server agent to accept requests. This is accomplished using the SQL Server CE Connectivity Management MMC snap-in shown in Figure 7-1. Using this utility, an administrator not only can specify the virtual directory, but can additionally configure the level of authentication required (anonymous, basic, or integrated Windows) by IIS on the virtual directory, additional NTFS permissions on the directory, and the NTFS permissions required on the share pointing to the snapshot folder used in merge replication.^[4] For example, if basic authentication is configured, as shown in Figure 7-1, an administrator must also add the appropriate NTFS permissions for valid accounts (groups or users) using the NTFS Permissions tab.

^[4] The snapshot folder is a folder on the SQL Server computer containing the schema and data for tables published through merge replication.

To make it easier to configure and when an administrator needs to create a new virtual directory to use with SQLCE, the Connectivity Management snap-in also includes the SQL Server CE Virtual Directory Creation Wizard. This wizard can be accessed immediately when the Server Tools are installed or by double-clicking the icon in the Connectivity Management snap-in.

Connectivity Features

Several advantages to the SQLCE connectivity architecture just described include the following:

• Accessibility: Because the communication protocol used is HTTP over TCP/IP, devices can make requests for data that pass through firewalls. In addition, connectivity is supported through WLANs, WANs, and even a pass-through mechanism, using a directly connected device (serial, infrared, USB, or Ethernet) with ActiveSync 3.5 and higher or SQL Server CE Relay.

• Security: Because SQLCE connectivity uses IIS for its communication mechanism, the IP address and domain name restrictions, encryption of the communication using Secure Sockets Layer (SSL), and the authentication methods in IIS (except for digest, Kerberos, and client certificates not supported by Windows CE 3.0) can be used. This also implies that credentials can be authenticated against a Windows NT domain or Active Directory.

• Performance: In order to support wireless devices in an optimal way, both RDA and replication use compression when transmitting data between the client and server agents. This compression is on the order of 8:1 and serves to greatly improve the performance of applications exchanging large amounts of data. This is contrasted with communication to a back-end SQL Server using the SqlClient .NET Data Provider, which is not compressed.

• Reliability: The simple protocol used by SQLCE connectivity^[5] can recover from communication failures of approximately two minutes by restarting from the last successfully transferred block of data. This allows synchronization to occur even when the underlying communication transport is erratic.

  ^[5] The protocol is patterned after various file-transfer protocols.

In addition to these primary advantages, of course, is that developers within your own organization don't have to reinvent the wheel to provide these basic infrastructure requirements.