he past 50 years of computer hardware have shown some obvious trends. For one thing, devices are getting a lot smaller. A half-century ago, mainframes occupied large rooms. The 1960s gave rise to mini-computers the size of refrigerators, and the 1980s brought the proliferation of computers the size of a medium-sized dog. Today, the widespread use of cell phones and the growing use of personal digital assistants (PDAs)—devices that fit in your pocket—mark milestones in the ongoing saga of the incredible shrinking machines.
      Amidst such changes, some things have remained constant. Computers exist primarily to handle data in some way, whether the data is in the form of text documents, spreadsheets, or databases. To handle data you need software, and each generation of new hardware is accompanied by new software that exploits its unique features. Microsoft created SQL Server™ 2000 Windows® CE Edition (SQL Server CE) to enhance the data-handling capabilities of mobile devices that are powered by Windows CE. See Figure 1 for a listing of the Windows CE-powered devices that can run SQL Server CE.
      To make sense of how this product can be used, we'll begin by framing some usage scenarios that highlight product features and benefits. For our readers who are new to Windows CE, we will describe the fundamentals of Windows CE data storage. We'll then provide a quick look at the tools you need to develop for SQL Server Windows CE Edition, and describe two major aspects of any remote database product: support for data manipulation, and connectivity between the remote device and a central data store.
      In our encounter with this product, our biggest challenge was setting up the two connectivity options: Remote Data Access (RDA) and merge replication. In our experience with teaching SQL Server to corporate clients, we often observe that dedicated database programmers often have little or no experience with building Web-based applications or setting up security, among other issues. For this reason, we devote the second half of the article to describing the obstacles we encountered and how to resolve them. Along the way, we present some code to help kick-start your database coding efforts.
      Note: For the remainder of the article, we'll use "SQL Server CE" to designate SQL Server 2000 Windows CE Edition. Unless otherwise noted, all references to SQL Server refer to the versions of SQL Server (SQL Server 6.5, SQL Server 7.0, and SQL Server 2000) that run on Windows NT® and Windows 2000. These are the versions of SQL Server that are compatible with SQL Server CE.

Usage Scenarios

      Three things make up the core of each scenario: a Windows CE-powered device, some data to be stored, and a user who is most likely—though not necessarily—mobile. Two other elements are critical: a central data store that is maintained by a SQL Server database and a transport mechanism for moving the data between the Windows CE-powered device and the SQL Server database.
      There are lots of options for transporting data, including both wired and wireless choices. A Windows CE-powered device can connect to the outside world using old-time favorites like modems, wire-based networks, and a device-docking cradle. There are other data transports, including cell-phone, wireless LAN, and Blue Tooth, which are not widespread but which promise better ways to move more data faster.

Scenario 1: A Mobile Salesperson

      The archetype of the mobile device user is a salesperson on the go. A favorite saying in sales is that "nothing happens until something gets sold." If that is true, then we should do whatever it takes to get sales data back as soon as it can be made available. In addition, we want to send data out to a mobile salesperson—order status, leads, and other critical sales data—to help focus their efforts on the things that really matter to a business.
      Since most salespeople rely on their cell phones, when they are equipped with a Windows CE-powered device it seems natural to connect it to the phone for data transport. Of course, a Windows CE-powered device could also be equipped with its own, separate cellular link. Between sales visits, a salesperson can be on the cell phone talking to a client. At the same time, the Windows CE-powered device can talk to the home office via its cellular connection—uploading order information and downloading new data points from headquarters.
      The idea of giving a cell phone to a computer might seem a bit strange at first, but it's no stranger than having a dedicated phone line for the modem of a desktop computer. As of this writing, several companies are planning to ship Pocket PC-based devices that have a built-in cell phone. As such devices begin to ship, we believe that more and more database connections will involve cellular-based connections.

Scenario 2: Product Delivery Trucks

      There are, of course, situations that don't require such up-to-the-minute reporting. Sometimes, a daily connection to a central data store is adequate. Consider a delivery truck—perhaps for products like bread, cookies, or potato chips—wired to collect delivery, inventory, and new order information. If the business needs are met by a daily update, the product delivery person can bring the Windows CE-powered device into the office at the start of each day. At that time, data on the previous day's deliveries can be downloaded, and details about the upcoming day's deliveries can be written to the device.
      This scenario is much like the first, except in the frequency of the connection to the central data store and in the technologies that would be used for establishing that connection. If a device is brought into an office, it could be connected to SQL Server through a docking station or a wireless connection. In either instance, the device makes the connection through TCP/IP. In fact, the protocol for SQL Server CE is HTTP. (Note that to allow a typical Pocket PC in a serial or USB docking cradle to access TCP/IP, you must be running SQL Server CE version 1.1 or later. This is available at https://www.microsoft.com/sql/productinfo/ceoverview.htm.)
      The benefit of using the HTTP protocol is that it is accessible from almost anywhere in the world. In addition, with a minimum of effort, access can be provided in a way that is compatible with most firewalls. This is the same approach taken by the emerging Microsoft® .NET architecture for building the next generation Internet—the so-called "programmable Web."

Scenario 3: The Wireless Warehouse

      Some devices will never wander far from home, but will still need to be mobile within a limited geographical area. Such is certainly the case for barcode readers from companies like Intermec and Symbol Technologies. Barcode readers are commonly deployed in warehouses for inventory, order tracking, and other database-centric needs. Often, the barcode readers are on a wireless RF network. This allows data to be moved back and forth between the central data store and individual devices as it's read.
      There are several ways to deploy a Windows CE-powered device in this scenario. One would be for the barcode readers to serve as dumb terminals, with central servers to perform all processing. There are problems with this approach, however. From time to time the network might be unavailable, either because a device is out of range or because the network radio signal is blocked by some physical obstruction. A second problem with this approach is that the central server can become swamped during peak work hours when all the devices clamor for its attention.
      SQL Server CE is an ideal solution in such an environment because each barcode reader can operate independently while its user roams the warehouse. All the data that's needed can be kept on the device itself, in a SQL database. Periodically, when the device is in range and the server is available, the database on the Windows CE-powered device can be synchronized with the central data store. Such a configuration provides a maximum of flexibility, with continual updating of all relevant databases.

Scenario 4: A Point of Sales Terminal

      SQL Server CE can even be used when the Windows CE-powered devices are not mobile. Consider a permanent database accessed by a stationary army of clerks with permanent network connections. That is one way to describe a retail point of sales (POS) terminal where the devices and the network connections are all fixed in place. Such devices can plug into a wall socket for power, instead of being battery-powered like many devices that run Windows CE. Perhaps the terminals are deployed in a large department store, perhaps in a chain of coffee shops.
      Retail terminals rely on central servers to do all the work. But problems—from the unavailability of a network to the overloading of the central servers—can be encountered. And downtime is tantamount to closing down the store. To get the maximum reliability, it makes sense for each POS terminal to be self-sufficient.
      Deploying SQL Server CE in such circumstances allows self-sufficiency. A snapshot of the product price database can be kept on each device. Retail clerks can ring up sales whether or not the network or the central servers are available. Periodically, the sales can be uploaded to the central servers. As the price database is updated, the new price information can be downloaded to a local copy of the data store on the sales terminal.

Scenario 5: Personal Databases

      All of the scenarios that we've discussed so far involve connecting to a central server. That is certainly the primary scenario for which SQL Server CE was created. But it's also possible to have a Windows CE-based database without any central data store. For example, consider a traveling salesperson who tracks restaurants he encounters on his trip. He might track the availability of good wines, vegetarian menu selections, or the portion sizes at each restaurant. Such a collection of information would be valuable for future sales and could even provide the salesperson with a second career as a travel writer.
      The key element is that while the salesperson might send order records to a central database, the restaurant database is not meant to be uploaded anywhere. It is strictly for the personal use of the salesperson. SQL Server CE can still be used here. While it doesn't offer all the features of the server-based edition, SQL Server CE supports the core set of SQL commands for creating and updating a database. It is more than adequate for a standalone database.

Data Storage in Windows CE

      If you are new to Windows CE, you might wonder about the support it provides for data storage. Windows CE was created for a wide range of platforms, and supports a correspondingly wide range of storage devices. A Windows CE-powered device could, for example, be configured to use all of the devices that any desktop PC uses—hard drives, tape drives, DVD readers/writers, and so on, given the appropriate device drivers. However, the actual storage devices on a mobile Windows CE-powered device are much different than this theoretical list.
      Consider a Pocket PC, like the HP Jornada 548. Out of the box, it came equipped with 16MB of ROM to store the operating system image, and 32MB of RAM for my data, for a total of 48MB of memory. To conserve battery power and keep device weight to a minimum, a typical mobile Windows CE-powered device has no hard drive.
      Since there's no drive-based storage, Windows CE supports a storage area called the object store. This RAM-based storage space contains three parts: a file system, a registry, and Windows CE databases. The file system supports the same long paths of desktop Windows, so that a file path can be up to 260 characters. The hierarchical registry is very similar to the Windows registry on desktop machines, although in practice the Windows CE registry is considerably smaller. The database support can best be described as a flat-file manager. The built-in database support is somewhat clunky, and access tends to get very pokey when you work with more than 1,000 records. It was meant to store small databases such as personal phone lists rather than full-blown data stores. A SQL Server CE database resides as a file in the file system of the Windows CE-based device, and makes no use whatsoever of the built-in Windows CE database support.
      Because the object store is RAM-based, whatever RAM you have on the device ends up having two basic uses: program memory and object store. A system setting allocates available RAM for these two uses. This can be set either programmatically or through the Control Panel. Memory in the object store gets stretched a bit further through a minimal compression mechanism. It's fast but weak, providing a meager 2:1 compression ratio on all data in the object store.
      One thing worth noting about Windows CE databases is that they can be stored in two ways: mounted in the object store or unmounted. To take advantage of object store compression (despite the performance degradation), a Windows CE database must be stored as a mounted database.
      So how much real database storage space does a typical Pocket PC have? Splitting the 32MB available RAM into equal parts would leave 16MB for program memory and 16MB for data storage. Factoring in for the compression gives you 16MB back, for a total of 32MB data storage capacity.
      Today, when RAM is measured in hundreds of megabytes and hard-drive storage space is measured in gigabytes, the memory budget of Windows CE must seem pretty small. You can, however, add to available storage through the use of installable file systems. (It's important to keep in mind that such devices are purely storage devices and do not extend available program memory.) On my Pocket PC, I have a Compact Flash (CF) slot that takes the same memory card that you find on many digital cameras. For a few hundred dollars, I can extend available storage space by several hundred megabytes (as of this writing, the largest CF storage card we have found is 256MB).
      There are even hard drives that fit in the CF slot. IBM has a 340MB CF spinning media card and a 1GB spinning media CF card. This is ironic, since rotating media was eliminated from the design of mobile Windows CE-powered devices in general. Spinning magnetic media have managed to sneak back in, masquerading as CF memory cards.
      On the downside, such a drive consumes a bit more power than a CF card, simply because that device needs to be spun. At the same time, it's nice to know that a user can make a personal choice between battery life, storage capacity, and cost (the cost per megabyte of a hard drive is quite a bit lower than the cost per megabyte of a CF memory card). Since both memory cards and spinning media cards are FAT-based, there is also a performance penalty in translating between the FAT and the Windows CE file system.
      A SQL Server CE database lives as a file anywhere that Windows CE can see it. This means, of course, that it lives within the object store, but as a regular file, not as a Windows CE database. Such a database can reside on any of the various types of installable file systems or devices: hard drives, CF flash memory card, or even—assuming you have the right device drivers installed—on a writable CD or DVD drive.
      Each SQL Server CE database is stored as a single file on the device. You may associate a password and encrypt the physical file using 128-bit RSA encryption with the database for security purposes. Your applications will then need to specify the password when connecting to the database. The recommended naming convention for the file extension is .sdf. Note that the name of the file is the name of the database. Thus, you open a connection to mydb.sdf, not to mydb.

Architecture of SQL Server CE

      When you run SQL Server CE, it is considered a client to the SQL Server server. (In fact, SQL Server CE is an integrated OLE DB-wrapped DLL that runs in-process unlike SQL Server which is a real server and runs out-of-process.) This design philosophy resulted in several significant implementation decisions by the SQL Server team. One is that two devices running SQL Server CE cannot interact with each other on a peer-to-peer basis. (Developers who have worked with SQL Server on Windows NT and Windows 2000 know that peer-to-peer interactions are well-supported on SQL Server.) Another difference between the two is the fact that SQL Server CE is a single-user DBMS, while SQL Server supports multiuser databases.
      Most applications written for SQL Server CE will be concerned with two things: manipulating the data while disconnected and transferring the data to SQL Server when the machine is connected. The first is very simple and intuitive for anyone who has written an application that accesses SQL Server data. The second, the transfer of data between SQL Server and SQL Server CE, involves not only the participating database engines but also Microsoft Internet Information Services (IIS). Database programmers and administrators will be delighted with how familiar the data manipulation is. Both data manipulation on the Windows CE-powered device and data transfer will be covered in greater detail later, but some introductory words are appropriate here.

Getting Started with SQL Server CE

      To develop applications using SQL Server CE, you must purchase the SQL Server 2000 Developer Edition, which comes with a license to download SQL Server CE. Once your application is ready to be deployed, you'll need either SQL Server 2000 Standard Edition or SQL Server 2000 Enterprise Edition.
      SQL Server CE comes with several sample applications, written in both eMbedded Visual Basic® and eMbedded Visual C++®, illustrating both RDA and merge replication. They are excellent examples, and a starting point for your own apps.
      Like all samples, these have a few issues. First, some of the code has been hardwired to expect installation on the C: drive. If your installation was done to some other drive, you will have to modify the code accordingly. Also, depending upon your particular environment, the connection strings that are built by the sample applications may not work correctly.
      We have chosen to write the samples in this article using eMbedded Visual Basic and ADOCE 3.1, because that combination produces the most readable code. Our sample code is simpler (and with less error handling) than the provided samples because we're focusing on specific aspects of application development, rather than presenting an entire application.

Data Manipulation in Brief

      Manipulating the data in a SQL Server CE database is simple because it requires the use of only one programming component, either a COM object (ADOCE) or an API (OLEDBCE). These components match up with their desktop counterparts, ADO and OLE DB. If you have already written applications using either ADO or OLE DB, you have a head start on accessing SQL Server CE. Programming for SQL Server CE is also simplified because the SQL language available with SQL Server CE is a reduced subset of the SQL available with SQL Server 2000. (And SQL Server 2000 includes SQL grammar that is not included in SQL Server 6.5 or 7.0.) For instance, SQL Server CE has no T-SQL grammar.

A Quick Look at Connectivity

      Two separate mechanisms are provided to move data and schemas between SQL Server CE and SQL Server: RDA and merge replication. Both are used in conjunction with IIS. The most complex part of a SQL Server CE-based application is generally the transfer of data between SQL Server CE and SQL Server, especially if you have never used replication in your development nor developed IIS-based applications.
      One of the design benefits of both RDA and merge replication is that they will move not only data but also schemas. This transfer can be as broad as an entire database or as narrow as a single table. This control of the schema resides on SQL Server, and in most cases will eliminate the need for explicit data definition statements in SQL Server CE-based applications altogether.
      The primary benefit of RDA is that it has a smaller footprint on the device and is somewhat faster due to reduced functionality and tracking. The key disadvantage of RDA is that it provides little support for handling conflicts. Merge replication allows for the establishment of database update rules, including rules for conflict resolution when synchronizing new data into an existing database. RDA is supported for all versions of SQL Server, but merge replication requires SQL Server 2000. Figure 2 lists the data replication support provided for different versions of SQL Server. Whichever data-moving mechanism you use, the underlying network protocol for both is always the same: HTTP. Using an ISAPI extension DLL is the only way for SQL Server CE to connect to SQL Server.
      There were some good reasons that the SQL Server CE team chose this IIS-based conduit. A very important segment of the user base will consist of mobile users, so it was important to allow the connectivity options to be as broad-based as possible. By building SQL Server CE around HTTP connectivity, anyone who can gain access to an Internet connection can connect to their SQL Server home world. In addition to its widespread availability, an IIS connection also provides authentication and authorization so that a user's data can get through a corporate firewall. And finally, IIS provides the ability to encrypt data when using a certificate, so private data can remain private.
      When data gets transmitted between SQL Server CE and SQL Server, one of the nice features you get for free is compression. Data is automatically compressed between the Windows CE-powered device and the HTTP site where the data gets sent. In one benchmark, using the Northwind database, the compression ratio was approximately 8:1. While compression is always enabled, encryption is not. You can, however, enable encryption of the data that's being transferred between SQL Server CE and the server running IIS, thanks to the standard encryption capability of IIS. Since it's all IIS-based, the encryption is entirely transparent to the two database engines.
      It's worth pointing out that a typical Pocket PC in a docking cradle has a serial (or better, a USB) connection to the computer that it is connected to. If you want the desktop connection to proxy the HTTP request, you should know that Microsoft ActiveSync® 3.1 does not directly support this. SQL Server CE 1.1 addresses this problem by providing software that will proxy the HTTP requests through the desktop to the IIS server.

The Connectivity Challenge

      The hardest thing about using SQL Server CE is getting connectivity working. To understand why this is so, and to simplify your own application development efforts, you need to understand that you cannot write a Windows CE-based application that connects directly to SQL Server because there are no OLE DB drivers that communicate directly with it. Your application must let SQL Server CE do the transfer of data to SQL Server.
      SQL Server CE does not open a direct connection to SQL Server. Rather, it connects by using HTTP to access an ISAPI DLL file, sscesa10.dll, located at the URL of your choice. It is this DLL that actually makes the connection to SQL Server and transfers the data from SQL Server CE to SQL Server. When moving data from SQL Server CE to SQL Server, you must rely on a middleman: IIS. This means that two connections are being made; one from Windows CE to sscesa10.dll and one from sscesa10.dll to SQL Server (see Figure 3).
      People familiar with IIS will recognize that the device-to-IIS connection can be made in Anonymous, Basic Authentication, or Integrated Windows Authentication mode; the IIS-to-SQL Server connection can be made in Windows Authentication or SQL Server Authentication mode. All of this results in a connectivity and security chain that involves your SQL Server CE-based application, the network, the operating system, IIS, NTFS, SQL Server, and, in the case of one data transfer method, it involves a shared directory.
      You should address a number of standard Web development issues before you begin to code the application—things such as where to place DLLs, where to locate SQL Server, and which OS users should have execute rights on the DLL and SQL Server.
      As we mentioned earlier, IIS exposes three security contexts, of which SQL Server CE can use two (Basic and Integrated). When using integrated security for both IIS and SQL Server in an Internet scenario, both servers must be running on the same machine. The reason for this is that NTLM does not support proxying security credentials through machines. Kerberos does support this in Windows 2000, but Windows CE does not currently support Kerberos. Like any Web-based application, if Basic Authentication is chosen, Secure Sockets Layer (SSL) should be used to prevent the user name and password from being deciphered from the device and the box running IIS.

Data Manipulation in Depth

      SQL Server CE has a subset of SQL Server functionalities; its database engine is smaller, too. This was accomplished by leaving out functionality like the SQL Admin service. This was inevitable because since there is no such thing as a service in Windows CE, there are no agents, jobs, alerts, operators, or schedules to administer. Since SQL Server CE is a single-user product, there are no linked servers, and since its security is controlled by a file password, there is no need for GRANT, DENY, or REVOKE. Also missing are SQL Server's Transact-SQL extensions; there are no stored procedures, triggers, multi-statement batches, DECLARE, SET, IF, WHILE, string functions, numeric functions, or niladic (no-argument) functions.
      However, the date functions do exist in SQL Server CE. Even some standard SQL had to be left out; there are no views or UNIONs. Not all the SQL Server data types could be included either, but most of those that are missing can be converted to data types that do exist. For example, Windows CE only supports Unicode, so only Unicode versions of character data types (such as NCHAR, NVARCHAR, NTEXT) are available. SQL Server data of type CHAR will be converted to NCHAR as it is moved to SQL Server CE. Incidentally, if you are using an ASCII-based localization such as English, then all text and character data types, which must be Unicode, will be compressed into ASCII (UTF-8) to save space. It should be noted that the choice of Unicode for SQL Server CE was driven by the fact that this is the default character set of Windows CE itself. Unlike Windows NT and Windows 2000, which support both ANSI and Unicode function calls (MessageBoxA and MessageBoxW), on Windows CE only the Unicode versions of Win32® function calls are available (MessageBoxW).
      What SQL Server CE does provide is support for tables, indexes, defaults, and referential integrity. It also has the ability to add, modify, and delete rows in those tables using standard SQL Data Manipulation Language (DML). Therefore, your application will manipulate its data in SQL Server CE by connecting to the database file and submitting SELECT, INSERT, UPDATE, and DELETE statements to the database, or by opening a recordset and using the various recordset methods to modify data in the resultset. Your SQL Server CE-based application may be more elementary than what you've come to expect from a typical SQL Server application, but it should provide enough functionality to be useful in mobile situations. The lack of the complete Transact-SQL syntax is an inevitable limitation given the available memory of a typical Windows CE-powered device. And yet in spite of this size limitation, SQL Server CE provides a more than adequate set of SQL commands for just about any Windows CE-based application that you are likely to need.
      Two programming object models exist for accessing SQL Server CE databases: ADOCE and ADOXCE. The ACOXCE objects are used for database definition language (DDL) actions such as creating, altering, and dropping databases, tables, and indexes. We don't cover ADOXCE in this article for three reasons. First, ADOXCE objects are maintenance-oriented (versus data-manipulation oriented). Second, most of what you can do with ADOXCE you can also do using ADOCE. Third, most SQL Server CE database definition and creation will be done via RDA or replication. If you are interested in ADOXCE, you can find several sample applications in the SQL Server CE download.
      The ADOCE objects are used by an application to connect to a SQL Server CE database and to submit and process SQL statements. As their name implies, this object model is analogous to the ADO object model; it contains objects such as Connection and Recordset, with properties and methods such as MoveNext, EOF, and Execute.

Supported SQL

      The ANSI standard SQL language is divided into three parts; DDL, DML, and Data Control Language (DCL). ADOCE objects are normally used for processing the four DML verbs: SELECT, INSERT, UPDATE, and DELETE. However, as mentioned earlier, they can also be used to process the three DDL verbs: CREATE, ALTER, and DROP. In this way, ADOCE can do most of what ADOXCE can do, and most developers can concentrate on ADOCE without needing to know ADOXCE. ADOCE cannot be used to process the three DCL verbs: GRANT, DENY, and REVOKE because these verbs do not exist in SQL Server CE.
      To connect to a SQL Server CE database your application needs a connection object and a connection string. Since the only two mandatory pieces of information needed to make this connection to the SQL Server CE database are the provider name and the database file name, both the connection string and the code are easy to write. For example:

  Dim refConn As ADOCE.Connection
  

  Set refConn = CreateObject("ADOCE.Connection.3.1")
  

  refConn.Open _
  

      "Provider=Microsoft.SQL Server.OLEDB.CE.1.0; " & _
  

          "Data Source=Northwind.sdf"
  

      Once the connection to the database has been opened, the code to submit a data-modifying statement could look like any one of these three snippets:

  refConn.Execute "DELETE Categories WHERE CategoryID = 3"
  

  

  refConn.Execute "INSERT Categories " & _ 
  

  "(CategoryName, Description) " & _
  

  "VALUES ('Taco', 'Fast Food')"
  

  

  refConn.Execute "CREATE TABLE MyTable (" & _
  

                  " PKCol int not null IDENTITY PRIMARY KEY, " & _
  

                  " AttrCol nvarchar(100) not null DEFAULT 'NA')"
  

      The code to submit a statement that produces a recordset would look like this:

  Dim refRS As ADOCE.Recordset
  

  Set refRS = refConn.Execute("SELECT * FROM MyTable")
  

The loop to process that recordset might look like the following:

  Do Until refRS.EOF
  

      Print refRS.Fields(0) & "  :  " & refRS.Fields(1)
  

      refRS.MoveNext
  

  Loop
  

Finally, the code to do cleanup might look like this:

  refRS.Close
  

  Set refRS = Nothing
  

  refConn.Close
  

  Set refConn = Nothing
  

Replication

      As mentioned previously, SQL Server CE-based applications transfer data between SQL Server and SQL Server CE using one of two mechanisms: SQL Server's merge replication or RDA. RDA was designed so that SQL Server CE would run with older versions of SQL Server (namely, versions 6.5 and 7.0) or for administrators who did not want to set up merge replication.
      It is easier to explain RDA if you already understand merge replication, so we will begin our discussion with merge replication in SQL Server. SQL Server CE uses the merge replication support provided by SQL Server 2000.

Merge Replication

      Those of you who have worked with replication know that prior planning and design are important. When first tackling replication, people often think that keeping data that exists in two places in sync is an easy task. But consider just two examples of the complexity involved in replication.
      In the first example, SQL Server provides a capability called triggers; a change in TableX will automatically cause a change to occur in TableY. But SQL Server CE does not support triggers. If TableX and TableY are replicated down to a SQL Server CE database, a change in TableX will not cause an automatic change to TableY. Thus, using the same statement executed on what appears to be the same table, TableX, will produce different results, and the tables will no longer be in sync. Therefore, it is important to duplicate the trigger logic from SQL Server in your application code on the SQL Server CE side of the equation.
      Here's another example. If you replicate a table that has a foreign key, you need to replicate the primary key table as well as validate the rows that are inserted into the foreign key table. Perhaps you want to replicate only some of the rows in the foreign key table, such as the rows for Delivery Route West92. You need to replicate only those rows from the primary key (the Product table) table that are relevant to Delivery Route West92, but the delivery route code is not a column in the Product table. This operation can be accomplished by publishing all the tables involved in enforced relationships, but also by adding a horizontal filter supported by join filters to the tables related to the publication definition.
      After you have taken such considerations into account, there are some key differences between merge replication and RDA that make merge replication quite attractive. Even though, as you'll see, RDA is simpler to set up, it also involves substantially more code than merge replication. Merge replication provides the ability to have the server control much of the application logic, instead of having the logic distributed to the device. For instance, ranged IDENTITY columns and dynamic horizontal partitions are two key examples of logic residing on the server, not on the device. This can substantially reduce the amount of code on the device, and allow for application maintenance to be performed on the server rather than on the device.
      Any replication scheme must be well-planned and well-designed, including those you plan to use with your SQL Server CE-based applications. Therefore, we will take a moment to review the basic concepts of merge replication in SQL Server.
      In merge replication, as it is used with a SQL Server CE-based application, SQL Server is referred to as the Publisher with one or more defined publications on one or more databases. The SQL Server CE databases are referred to as the Subscribers. Publications are made up of a defined set of tables, columns, and filters. These chosen tables are referred to as the Articles of a Publication; and the definition of a subset, such as "only columnA, columnB, columnC of TableX" or "only those rows of TableY where route code equals 'West92'" are referred to as filters. Filters allow you to replicate a vertical subset of a table, such as the first example in the previous sentence, or replicate a horizontal subset of table, such as the second example, or both.
      Subscriptions are directly correlated to the concept of publications as defined in SQL Server. The eMbedded Visual Basic or eMbedded Visual C++-based application uses the SQL Server CE ActiveX® control to create the subscription by using the same method and properties that are defined when managing a subscription for a merge client on the desktop.
      An example of the code for doing an initial synchronization is shown in Figure 4. The code for doing a resynchronization looks much the same (see Figure 5). Since the initial synchronization has already been done, the AddSubscription method call is not needed. The object property values are the same as they were in Figure 4; however, the number of comments have been reduced to make the code sample smaller.
      The code in Figure 4 and Figure 5 illustrates three points. First, executing replication programmatically involves creating the object, setting several properties, and calling three methods: Initialize, Run, and Terminate—just as is done on the desktop. Second, when doing an initial synchronization, the SQL Server CE database can be automatically created. Third, the connectivity issues mentioned earlier, such as properly configuring the IIS server, affect how you code your application.
      We'll take a moment to address these connectivity issues at this time, and then examine some typical application scenarios. It is not our intention to repeat the details that can be found in SQL Server CE Books Online. Rather, we'll take a high-level look at the SQL Server CE-based application-to-IIS-to-SQL Server chain, referencing the appropriate Books Online entries as we go. Then, as we examine the individual scenarios, we'll take a detailed look at implementing each one. Since both merge replication and RDA connect to SQL Server through sscesa10.dll, almost everything covered here will be applicable to RDA programming.
      For SQL Server CE-to-IIS-to-SQL Server connectivity to work, you must do the following:

1. Create a virtual directory on the server running IIS for the application. Point the SQL Server CE server-side installation program to install the program files to the virtual directory that was created on IIS (see "Configuring IIS" in SQL Server CE Books Online).
   
2. If they do not already exist, add the operating system users and groups for each of the applications. (Use the OS's user management client tool such as User Manager for Domains in Windows NT. This task is normally done by the network administrator.)
   
3. Add the OS users and groups as SQL Server logins and assign the necessary database permissions to them. (Use SQL Server Enterprise Manager client program. This is normally done by the SQL Server administrator.)
   
4. If you're using merge replication, run a stored procedure to upgrade SQL Server to work with SQL Server CE. This will not be required on future Service Pack versions of SQL Server. (See "Updating SQL Server System Stored Procedures" in SQL Server CE Books Online.)
   
5. If you're using merge replication, assign read rights to the directory where the snapshot files will be stored so that IIS can access them. This step is necessary only if you are using NTFS. In FAT(32) this is not (and in fact can't be) done. (Granting NTFS access permissions on that directory must be done separately.)
   
6. If you�re using merge replication as the data transfer mechanism, create the SQL Server publications. (See "Creating the Publication" in SQL Server CE Books Online.)
   

      Now let's examine some typical application scenarios and look at how to implement each.

Scenario 1: Read-only Access to Public Data

      Your Windows CE-based application will do read-only access of SQL Server data that is public in nature. You need to provide generic access to the data, but you do not need to track that access.
      Configure the IIS virtual directory properties to allow anonymous access through the default user account, IUSR_machine-name. Figure 6 shows how this is being done under Windows 2000 for the Northwind database.


Figure 6 Allow Anonymous Access

      Add IUSR_machine-name as a SQL Server Windows Authenticated login and grant it read access on data that would be considered public. Figure 7 shows the permissions being granted in the SQL Server authentication configuration property sheet.
      Add code to the client application to specify Windows Authentication mode for the sscesa10.dll-to-SQL Server connection by setting the PublisherSecurityMode property value to NT_AUTHENTICATION. Sample code to do this (taken from the example in Figure 5) is shown here:

  Attribute VB_Name = "ReplWindowsAuth"
  

  

  '  Declare and create the SQL Server CE Replication Object.
  

     Dim refRepl As SSCE.Replication
  

     Set refRepl = CreateObject("SSCE.Replication.1.0")
  

              •••
  

  '  Instruct sscesa10.dll to connect to SQL Server using
  

  '     Windows Authentication.
  

     refRepl.PublisherSecurityMode = NT_AUTHENTICATION
  

      Now, whenever a Windows CE-based application connects to the DLL, the DLL will run as IUSR_machine-name and will connect to SQL Server as IUSR_machine-name. This will give the application access to all data that IUSR_machine-name can access, without the application needing to provide any SQL Server login information to the server.

Scenario 2: Read-only Access to Private Data

      In this scenario, your Windows CE-based application will have read-only access to SQL Server data that is private to the application. You want the application to be able to access certain information, but no user should be able to access that information unless they use the application to do so.
      To accomplish this, add an OS user or group for use by the application when it's attempting read-only data access. You'll do this using the regular administrative tools that you use to create and manage local users and groups. (On Windows 2000, the path to this screen is as follows: Control Panel | Administrative Tools | Computer Management | Local Users and Groups. On Windows NT, the path is much simpler: Start | Programs | Administrative Tools | User Manager for Domains.)
      Configure the IIS virtual directory properties to allow anonymous access through this user. An example of this is shown in Figure 8. Then add the user as a SQL Server Windows Authenticated login and grant it read access on the applications data (see Figure 7 again).


Figure 8 Anonymous User Account

      As in the previous scenario, code the Windows CE-based application to specify Windows Authentication mode for the sscesa10.dll-to-SQL Server connection by setting the PublisherSecurityMode property value to NT_AUTHENTICATION.
      Both scenarios use anonymous access for the SQL Server CE-to-IIS connection. This will cause sscesa10.dll to run as the specified user regardless of any additional information provided by the client application. Thus, the identity of the user who is running the client application cannot be used to control access to the SQL Server data. This situation is usually acceptable in a read-only environment, but is generally not advisable when data is being modified by the application.

Scenario 3: Read/Write Access to Private Data

      Your application needs to access and update SQL Server data that is private to it. Modifications need to be tracked on a per-user basis. For instance, you'll need to know which driver created which delivery records.
      The OS setup is pretty straightforward. Add the OS users—perhaps one for each driver. Add perhaps one OS group for each application class. Finally, add the users to their groups.
      Things get a bit trickier on the IIS security side of the equation. First, configure the IIS virtual directory properties to disallow anonymous access in favor of either Integrated Windows Authentication or Basic Authentication. Integrated Windows Authentication, which is best applicable to intranet applications, requires Windows CE 3.0 plus a secure network environment; Basic Authentication is more suitable for Internet applications, and more likely to succeed. If you are having trouble with Integrated Windows Authentication, try Basic Authentication. You can see an example of what the screen looks like when setting this up in Figure 9.


Figure 9 Basic Authentication

      The final setup step, as before, involves SQL Server access. Add the OS group as a SQL Server Windows Authenticated login and grant it access to the application's data.
      On the device running Windows CE, code the application to specify Windows Authentication mode for the sscesa10.dll-to-SQL Server connection and specify the security context. Normally, you use the OS user name and password of the person running the application. This is the identity under which sscesa10.dll runs, as well as the identity of the connection to SQL Server. You can see an example of the code you need to write in Figure 10.
      You need to add only one login (belonging to the OS user group) to SQL Server to ensure that all the OS users in that group can connect to SQL Server. Although only the group name is added as a login, SQL Server can still track connection activity by individual user name.

Remote Data Access

      As mentioned earlier, the second method for moving data between SQL Server CE and SQL Server is Remote Data Access. While RDA is simpler to set up, it may require substantially more code, especially when attempting to duplicate the kinds of functionality (such as ranged identity columns and dynamic horizontal partitions) that are inherent in merge replication. Its chief benefit is that it allows a device that's running SQL Server CE to connect with the older versions of SQL Server—namely, SQL Server 6.5 and SQL Server 7.0. Only SQL Server 2000 supports merge replication. RDA allows a SQL Server CE-based application to:

• Directly modify SQL Server data without first moving that data to SQL Server CE.
  
• Use a SELECT statement to pull data from a SQL Server database into a SQL Server CE table.
  
• Push data from a SQL Server CE table up to the corresponding SQL Server table.
  

      Most RDA programming for SQL Server CE involves using just one programming object, the RDA object. This object has three methods corresponding to SubmitSQL, Pull, and Push. It also has a set of properties for providing connection information and handling errors. For instance, the Pull method specifies the SELECT statement to be executed in SQL Server, the name of the table on SQL Server CE that will receive the data, and the SQL Server connection string, all as parameters; the OS user name and password must be provided as properties. Let's look at each method in detail.

SubmitSQL

      The SubmitSQL method bypasses SQL Server CE completely and simply submits any SQL statement that does not return rows to SQL Server for processing. For example, the code in Figure 11 instructs sscesa10.dll to open a Windows Authenticated connection to the Northwind database located on the SFD00 SQL Server and insert a row into the Region table.
      If you've worked with the merge replication object before, you might be familiar with the InternetXxx properties: InternetURL, InternetLogin, and InternetPassword. What is new is SubmitSQL's second parameter, the connection string. We will look at it in more detail after we have examined the remaining two RDA methods, as they also have connection string parameters.

Pull and Push

      Pull is similar to SubmitSQL; it submits a SQL statement to the SQL Server for processing. However, the statement must return rows, the name of a SQL Server CE table to hold those rows must be specified, and that table must not already exist.
      For example, the code in Figure 12 will pull information from the Northwind.Categories table and store it in a table named SQLCETargetTable in Northwind.sdf.
      To understand the Push method, you must understand RDA's change-tracking capability. When the Pull method is used to retrieve rows from SQL Server, the TRACKINGON parameter can be specified; when it's on, subsequent changes to the pulled rows will be tracked. Like merge replication, this tracking requires some system information to be carried in the pulled table, which is why the new SQL Server CE table will contain more columns than appeared in the SELECT statement. Since the Pull method places the rows into a new table, with tracking information for each row, you now have a SQL Server CE table whose rows can be traced back to their SQL Server source tables. The Push method depends upon this, for it passes all changes made to the table since the Pull occurred back to the SQL Server database.
      For example, the code in Figure 13 would upload all changes that had been made to the SQL Server CETargetTable after the Pull occurred. It is identical to the code in Figure 12 except for the use of Push instead of Pull.
      All three of the RDA methods require a connection string parameter. This the same connection string that any SQL Server client application must provide. It specifies the provider name, connection mode, server name, database name, and (optionally) login name and password. Most development environments that let you create SQL Server clients, such as Visual Basic 6.0, provide tools such as a Data Environment, that will generate connection strings for you. They are the best way to generate sample strings and to learn the syntax for each provider.
      The presence of this parameter in all three RDA methods reaffirms the disconnected metaphor of SQL Server CE processing. All methods require a SQL Server connection string parameter to be specified so that they can open the connection, perform the operation, and then close the connection. Changes that are made to the SQL Server CE data between a Pull and a Push are made while the SQL Server CE database is disconnected.

Choosing RDA or Merge Replication

      An important design issue that you will have to address is which of the two connectivity solutions to use. If you must work with SQL Server 6.5 or 7.0, then the choice is easy: you must use RDA, since merge replication is not supported. With SQL Server 2000, however, you can use either method.
      The biggest difference is that merge replication lets you specify the relationship between the desktop data and the SQL Server CE data declaratively, through the definition publications, while RDA requires that you write SQL code to define the data that is to be transferred. Thus, with merge replication, the central data store controls data movement, while RDA allows the Windows CE-powered devices to run the show.
      Initially, this might make RDA seem the better choice. You just code a SELECT statement, use the PULL method, and SQL Server CE creates a table and transfers the selected data into the table. No need to bother setting up replication.
      For a simple or one-time application this might be true. But for a major application, merge replication probably will be the way to go because it provides capabilities that are not matched in RDA. Conflict resolution, which was mentioned earlier, is one. Another is ranged identity columns, which allow for identity column values to be automatically assigned on SQL Server CE databases throughout the enterprise, without fear of conflicts arising when those rows are uploaded to the SQL Server database. Dynamic horizontal partitions are another example of the very useful built-in functionality available to you with merge replication.
      These declarative capabilities, which are not built into RDA, are extremely difficult to hand-code into an RDA application. The deeper you go into enterprise application development, the more you'll need merge replication. Although a good replication scenario requires thought, planning, and design up front, the resulting code reduction makes it worth the effort when developing full-scale applications.

Error Handling

      The programming objects for SQL Server CE, like those for SQL Server, provide error information in the form of an errors collection. In the case of the ADOCE objects used to connect to SQL Server CE, the errors collection is contained in the Connection object. In the case of the SQL Server CE objects used to connect to SQL Server through the ISAPI DLL, there's an errors collection in the RemoteDataAccess object and in the Replication object, and they are named ErrorRecords rather than Errors.
      As in ADO programming, a failure during a method call causes one or more error objects to be added to the collection and causes a standard error, such as a Visual Basic error, to be raised. Handling the error is done by iterating through the collection and presenting information to the user.
      Two things make SQL Server CE error handling different from SQL Server error handling. First, eMbedded Visual Basic has different error handling capabilities than Visual Basic. It is easiest to relate the eMbedded Visual Basic error handling to what is available with VBScript in ASP pages. Second, in order to keep the footprint small, SQL Server CE does not have error strings. Instead, it just returns error numbers and the test message for that error number needs to be found in the supporting Books Online.
      So, if your application tries to do a SQL Server CE Pull of data into a SQL Server CE database while it still has an ADOCE connection open to that database, it will receive an Unspecified Error message, instead of a more expected message such as "Cannot open multiple connections to the same database."
      The second cause of difficulty when programming SQL Server CE error handling is the limitation of the development environment. For example, eMbedded Visual Basic does not have the error trapping capability that Visual Basic does. It does not allow you to go to specific lines on an error.
      Your only On Error options are GoTo 0 and Resume Next, making eMbedded Visual Basic more comparable to VBScript. Wherever your application might encounter an error that it would need to handle, it must follow these steps:

1. Turn off error detection.
   
2. Call the SQL Server CE method.
   
3. Test for an error.
   
4. If an error occurred, examine the errors collection.
   
5. Turn on error detection.
   

You can see an example of this in Figure 14.
      Your app must turn off the normal error detection before making the method call because this normal error detection halts the execution of the program. Error handling in eMbedded Visual Basic is more limiting than some of the environments you may have programmed in, but it is still adequate for the task at hand.

Conclusion

      Developers who are experienced with building SQL applications on the Windows desktop will find much that is familiar with SQL Server 2000 Windows CE Edition. There are a host of old standbys like the SQL database statements, as well as the ADOCE and the OLEDB CE programming interfaces to work with. What is new is the connectivity model that SQL Server CE provides for the merging of mobile data into a central data store. For developers who have built Web-based applications, you'll find much that is familiar here. What this product provides is a high-powered, client-oriented database engine that lets you coordinate the movement of data between mobile, sometimes connected devices and a central data store. And, of course, it also can run as a standalone database quite nicely.