Page 1
Page 2
Page 3
Page 4
Page 5
Page 6
Page 7
[Note about the authors]
This is a joint effort by both Jarick
Rager and John Burdett. Jarick Rager (jrager@askcts.com) is a Senior
Software Engineer for Computer
Technology Solutions, Inc. based in Birmingham, AL.
John Burdett (jburdett@askcts.com) is a Software
Engineer for Computer Technology Solutions, Inc.
Table of Contents
0. Introduction
1. System Overview
2. System Architecture
3. Preparation
4. SQL Replication on
PocketPC
5. Infrared Transfer
between PocketPC and Laptop
6. Conflict Resolution
between SQL Server 2000 and SQL Server CE
7. Conclusion
Appendix A: frmMain
(PocketPC) Interface Code
Appendix B: Database
conflict_ContactManager_Contact
Appendix C: modGlobal
(PocketPC)
Appendix D: modSQLServer
(PocketPC)
Appendix E: Replication
Object Properties
Appendix F: modActiveSync
(PocketPC)
Appendix G: Stored Procedures
spGetConflictSummary
spGetContactByContactID
spGetContactConflictByContactID
spResolveContactConflict
Appendix H: frmMain
(Laptop) Interface Code
0. Introduction
SQL Server allows a myriad of options to establish
replication and each is discussed in the SQL Server CE Books Online.
The problem is determining where to find information suitable
for a particular situation. This article describes some of the
searches we made and some of the successes we found in building
a replication architecture for a common business problem.
The model discussed in this article is not
intended to be the sole architecture for replication. Instead,
it is provided as a model for getting your feet wet in replication
and conflict resolution. Our architecture uses eMbedded Visual
Tools (eVB), SQL Server 2000 Developer Edition, SQL
Server 2000 for CE, IIS 5.0, and ActiveSync 3.1®.
We cite references for the sources of information that were used
in order to help you pinpoint critical information in creating
and configuring this architecture.
1. System Overview
We were tasked with proving the concept of
using handheld devices to gather information and synchronize that
information with a laptop. As a starting point, we solved technical
issues by developing a system called ContactManager. This system
includes a central repository of contact information stored on
a SQL Server database on a laptop. This contact information is
downloaded to a SQL Server for CE database on handheld devices.
Clients then use the handheld devices to edit and enter new contact
information. The edited contact information is uploaded to the
laptop, with the laptop application resolving database conflicts
and synchronizing the database between the laptop and the handheld
devices.
The overall scope of this system allows a
client to:
- Create a replicated database of contact
information on the PocketPC.
- Edit contact information (First, Middle,
and Last Name, and Notes) on the PocketPC.
- Synchronize contact information with the
central database on the laptop.
- Resolve conflicts on the laptop.
2. System Architecture
The following diagrams illustrate the overall
hardware architecture and functionality for the handheld and laptop
applications. Figure 1 illustrates infrared communication between
the laptop and iPAQs.
Figure 2 summarizes functionality contained
within the handheld application.
Figure 3 summarizes functionality contained
within the laptop application.
3. Preparation
The hardest aspect to creating the replication
architecture used was to accurately install and configure the
various software applications. At the time, we implemented the
application following topics discussed in "SQL Server CE
Books Online" to determine proper configuration. Since then,
Derek Mitchell has written an article "SQL
Server CE Configuration" that takes you through the necessary
steps.
* Note: For purposes of this article, use ContactManager instead
of Northwind.
Next Page