Access to information within and from outside the enterprise is critical to competitiveness in today’s market. Connectivity to enterprise data thru a variety of devices such as mobile phones, tablet PCs, desktop PCs, and palm-top devices is now required. Many business situations such as sales teams or service technicians require mobility that leaves these enterprise users disconnected from corporate data. Bi-directional data exchange and merging data into central corporate data stores is often critical to making real time business decisions. Traditionally, disconnected users gained connectivity thru the use of analog modems. Wireless has now entered the connectivity picture providing greater flexibility. However it also presents new challenges. Many corporations are looking to connect their corporate information stores to palmtop PDAs because of they are light and mobile. But how do you store relational data on that Palm-Top device? Moreover, how do you replicate data to and from these devices to corporate data stores?
Recently, at my company Bit-Wizards Custom Software Solutions, we had a customer come to us with this very problem. Our customer needed a solution to solve an enterprise business scenario that involved merging data between a central data store and mobile disconnected clients. These clients were using the Compaq iPaq 3870 PDA running Windows CE 3.0 & PocketPC 2002. The system requirements dictated that the solution be scalable from a shrink wrapped product version usable with a single desktop containing the data store and a single PDA to an enterprise data store accessible by numerous PDAs. Fortunately, there is a new product from Microsoft called SQL Server CE (SSCE) that works in concert with SQL Server 2000 (SS2K). SSCE uses SS2K merge replication features and another replication data access technology for CE called Remote Data Access (RDA). In this article we will explore SS2K Merge Replication, SSCE, as well as, RDA technologies.
SQL Server CE
SSCE is a scaled down version of SS2K that provides storage for relational data on Windows CE powered PocketPC devices. SSCE is relatively new and is currently at version 1.1, and has a single service pack. SSCE has a relatively small footprint and does not require SS2K. In fact on the PocketPC you have only two alternatives for relational data: Pocket Access and SSCE. One of the great advantages to SSCE is that it can work in concert with SS2K through Merge Replication or RDA. It will also work with earlier versions of SQL Server, but only using RDA. Merge Replication is a feature that only exists in SS2K. So when choosing which of the two data access methods to use, if you have to merge data between earlier versions of SQL Server such as 7.0, your only choice is RDA. However if you plan to use SSCE to merge data with SS2K you can use Merge Replication, RDA, or both. We will discuss how each of these works later in the article.
It is also important to note that you can use SSCE standalone. You can use SSCE simply as a data store on the PocketPC device, just like you would Pocket Access, but with the scalability and much of the robustness of SQL Server. The SSCE database is stored in a binary file in the Windows CE file system with an SDF extension. When using SSCE standalone the hardest part is getting the initial SDF file deployment on the CE Device. You see, there is no way to create the file on the Desktop PC. It must be created on the device. So, what many developers do is write and small utility application that runs on the device, creates the database structure, and populates the database with any initial data. Once created, the developer can then take the SDF file and include it in their setup and deployment application. Some developers will actually include the database creation code in their application and have it run once the first time their application starts up. I personally prefer the utility application method and simply deploying the SDF, rather than bloat my CE application with code that will only be used one time. But there may be reasons to use either method depending on your requirements.
Data Access is accomplished with ADOCE, ADOXCE, or OLE DB/CE. Developers familiar with desktop development will be right at home with ADOCE and OLE DB/CE. The ADOCE and OLE DB/CE APIs are scaled down versions of their big brother counterparts on the desktop: ADO and OLEDB. By scaled down, I simply mean that some features such as the Stream Object in ADO are not present in the CE version. Another example of one of the limitations you will run into with ADOCE is that forward only cursors are not updatable. ADOXCE will be foreign to most developers. ADOXCE is an extension of ADOCE that has extensions for Data Definition Language and security. You will use ADOXCE to manipulate database schema objects such as tables, columns, indexes, and properties.
Basically, SSCE is accessed and manipulated just like SQL Server. Most all the features of SQL Server exist in SSCE. So if you are familiar with SQL Server programming you will be right at home. There is even a scaled down version of Query Analyzer. There are a few things lost but many things are gained. Perhaps the biggest advantage is the way SSCE and SQL Server can exchange or merge data using the Replication Data Access Methods.
Replication Data Access Methods
The most interesting thing about the Replication Data Access Methods: Merge Replication and RDA is that they utilize Internet Information Services (IIS) as the interface mechanism in the form of an ISAPI DLL. This means that the connection between the portable device with SSCE and SQL Server is an HTTP connection. Why an HTTP connection? Well this allows connectivity via a wide array of methods to include: Ethernet, Wireless LANs, and Wireless WANs using TCP/IP. This means that a wide variety of protocols are supported such as: GSM, 802.11, Proxim, ARDIS, RAM, AMPS, N-AMPS, CDMA, CDPD, PDC, TDMA, and D-AMPS. Once the user gains connectivity, they simply let SSCE know the URL Address where the SSCE Server Agent ISAPI DLL, Sscesa10.dll is located. Once you have done that, you can Merge Replicate or use RDA. Additionally, Remote Data Access can use an additional component called SQL Server Relay to support IrDA, USB, and Serial links. Let’s examine the two replication data access methods by reviewing the high level architecture in the figure below and discussing them.
Merge Replication
The best feature about Merge Replication is that it is the easiest to code on the device. At the device you need only make 3 core function calls: Initialize() – Run() and Terminate(). All of the complexity and setup resides centrally at the SQL Server 2000 machine. Getting Merge Replication setup at the server is the difficult part; however, once setup it is maintained from a central location and reduces the complexity and code required on the client. Merge Replication is setup in publisher and subscriber relationships. The central SQL Server is designated the publisher and you can merge replication to a variety of subscribers including both SSCE subscribers and other SQL Server subscribers. Replication is configured through a series of wizards in SQL Server Enterprise Manager that will easily step you through the setup of the Merge Replication process, allowing you to choose what tables and even what columns or rows you wish to merge. From this a snapshot is created and periodically updated by SS2K based on the schedule you provide. When the subscriber connects though IIS and the SSCE Server Agent ISAPI DLL, the snapshot agent decides if a merge needs to take place. If it does it validates, merges, and resolves conflicts based upon the settings you described in the merge replication setup. Merge Replication has failsafe methods in case a connection is dropped during update so that the next time the subscriber connects the merge will pick up from the last successful block of data transferred. Merge Replication is only available for SQL Server 2000.
Remote Data Access
RDA requires very little setup other than the SSCE Server Agent ISAPI DLL and IIS. However, the onus is on the developer to write the code and logic that validates, merges, and resolves conflicts for the tables, columns, and rows they wish to merge. This increases the size, complexity, and potential for errors in the program running on the palmtop device. It also increases the time and cost of development. If you are using SQL Server 6.5 or 7.0, RDA is your only choice. RDA is also available for SQL Server 2000. In fact on our project at Bit-Wizards, we ran into a particular technical issue that required we use both Merge Replication and RDA to bring about a successful solution for our client.
Conclusion
Replication to remote mobile frequently disconnected clients requires careful planning. However SQL Server CE combined with Merge Replication and or Remote Data Access makes designing a replication solution easier. You need only understand your choices. For this article a complete exploration could not be done, but hopefully I have given you insight into this technology.