SQL Server Magazine

In this age of rapidly proliferating data stored on large arrays of disks, the idea of working with a database measured only in tens of megabytes and stored on a device that fits in the palm of your hand might seem strange. However, Microsoft built SQL Server 2000 Windows CE Edition (which Microsoft expects to release later this year) specifically for managing small databases on portable devices. With SQL Server CE, your users can take a piece of the company database with them in a mobile device and access the database with familiar tools. And SQL Server CE is many times faster than CEDB, the database system built in to Windows CE devices today. Let's explore SQL Server CE's features and see where you might put them to use in your organization.

Programming for the Enterprise
What can a Windows CE-based database offer that other solutions can't? SQL Server CE lets users who need to access only small parts of a larger database work with a subset of data on Windows CE. In many cases, SQL Server CE and a mobile device can replace an application that currently runs on a laptop computer.

Consider salespeople who spend their working hours on the road visiting customers. Sales representatives don't need access to the entire company data warehouse. All they really need is customer information, customer order and payment history, and inventory information. In most cases, you can measure the total amount of data that a salesperson needs on a given day in thousands or millions—not billions—of bytes. In addition, many sales transactions involve using forms—usually paper forms—to gather information about customers and orders. When the salespeople return to the office, they (or their assistants) have to enter the form information into company databases. SQL Server CE lets you give salespeople the data they need in a format that integrates well with SQL Server 2000 and existing applications.

A Windows CE-based solution might answer mobile users' needs for data access and integration, what does it mean for the SQL Server programmers who have to build and maintain such a solution? The development tools available for Windows CE haven't been as easy to use as the tools for developing desktop applications. Database performance also has been unsatisfactory. Two changes to the Windows CE development environment have fixed these problems.

In June, Microsoft released the new eMbedded Visual Tools 3.0 suite as a replacement for the previous set of tools, which were secondary additions to Visual Studio 6.0. Now, you can develop applications for Windows CE by using versions of Visual C++ (VC++) and Visual Basic (VB) specifically designed for that OS. eMbedded Visual C++ includes versions of both the Microsoft Foundation Classes (MFC) and Active Template Library (ATL) for Windows CE. eMbedded Visual Basic (eVB) is a port of the VBScript runtime engine. In both cases, the integrated development environment varies only slightly from the environment in Visual Studio 6.0. I found the tools simple enough to use that I had a sample Windows CE program compiled, transferred, and running on my Windows CE 3.0 device in about 10 minutes. The SQL Server CE sample programs took only another 10 minutes.

The combination of eMbedded Visual Tools and SQL Server CE lets database application programmers reuse existing code from desktop VB or VC++ applications or build new code without having to learn a new set of skills. SQL Server programmers will find only a few SQL syntax differences between SQL Server 2000 and SQL Server CE. The standard SELECT, INSERT, UPDATE, and DELETE statements that run on SQL Server 2000 run on SQL Server CE, as do most data definition language statements, such as CREATE TABLE, ALTER TABLE, and DROP TABLE. SQL Server CE supports inner and outer joins, GROUP BY/HAVING clauses, ORDER BY clauses, aggregate functions, and inner queries, as well as most SQL Server 2000 data types. However, SQL Server CE doesn't support SQL Server 2000's ability to use the results of a query in the FROM clause or to use data pulled from a remote server.

Technically, SQL Server CE is an OLE DB provider, not a service like SQL Server 2000 or SQL Server 7.0. But SQL Server CE supports transactions, multicolumn indexes, seek on index, and referential integrity. The initial release doesn't support views, stored procedures, triggers, or T-SQL, but it does offer cost-based query optimization as SQL Server 2000 does. Each table can have as many as 32 indexes, which should be more than enough for the kinds of applications you would run on a Windows CE device. The CE edition features almost all the SQL Server 2000 constraints, including the identity, default, check, and Declarative Referential Integrity (DRI) constraints. Although SQL Server CE doesn't implement SQL Server 2000's database access and object security model, it does offer password access to the database. You can code the database password into the application to control what users can do with the data, or you can let users enter their password at runtime. For high-security applications, you can encrypt the entire database file with 128-bit encryption.

SQL Server CE is small—approximately 700K for Intel StrongARM x86 architecture CPUs and a little more than 1MB for other CPUs. To keep SQL Server CE small, Microsoft left out some SQL Server 2000 features, usually choosing to exclude those that were redundant or didn't apply to the Windows CE environment. For example, SQL Server CE supports only the Unicode character types nchar, nvarchar, and ntext because Windows CE is a Unicode-only OS. In addition, the CE edition doesn't support variations on main data types, such as smalldatetime, smallmoney, and timestamp. The "small" versions convert to the bigger types—datetime and money, respectively—but the timestamp data type just doesn't exist. However, SQL Server 2000 CE implements all other important data types, including integer, numeric, float, real, double, bit, bigint, binary, and uniqueidentifier.

Client application programmers also will find few differences between SQL Server 2000 and the CE edition. Windows CE applications use ADOCE 3.1 and OLEDBCE 3.1 to access SQL Server CE databases. In addition, Microsoft has extended ADOXCE support so that programmers can create SQL Server CE databases, tables, and so on by using ActiveX Extensibility Objects (ADOX) instead of SQL data definition statements. Perhaps the main difference between ADOCE and ADO object models is that ADOCE doesn't offer the Command or the Parameter object.

Keeping Data in Sync
Programming ease is only one factor to consider when you evaluate SQL Server CE. Another, perhaps more important, factor is the ease with which you can keep the databases on the Windows CE devices synchronized with your main server. SQL Server CE has two ways to implement synchronization: merge replication and Remote Data Access (RDA).

Since merge replication appeared in SQL Server 7.0, I've considered it a solution looking for a problem. In the past two years, none of my 200-plus students and none of my clients has found merge replication useful. However, merge replication is ideal for mobile database applications.

Merge replication manages changes to data when subscribers either are disconnected from the network or operate independently of each other and the publisher. This replication model is perfect for databases on Windows CE devices because Windows CE is designed to adapt to changing hardware and connectivity configurations. For example, my Hewlett-Packard Jornada 545 can synchronize with my desktop computer through the serial port, the IrDA port, the USB port, or the Compact Flash Ethernet adapter, depending on which method is available. If I used a wireless network card, whenever the device was in range, it would automatically connect to my network. In its initial release, SQL Server CE supports replication through both wired and wireless LAN/WAN connectivity. A later release will support replication through a desktop's network connectivity.

SQL Server CE's merge replication uses Microsoft IIS as the conduit to a SQL Server 2000 server. Because Windows CE supports only Windows NT client-side security, SQL Server CE uses IIS's security mechanisms to validate logins. If you need complete security for both data and passwords, SQL Server CE supports Secure Sockets Layer (SSL) encryption between the Windows CE device and IIS, with IIS acting as a proxy for validating the user's identity and securing the data transfer to SQL Server CE.

Implementing SQL Server CE merge replication is a topic worthy of another article, but let's look briefly at the basic architecture. An Internet Server API (ISAPI) DLL, called the SQL Server CE Server Agent, runs on the IIS server. That agent communicates both with the SQL Server CE Client Agent, which runs on the Windows CE device, and the SQL Server 2000 publisher. The Client Agent sends a list of changes to the Server Agent, which stores them in a file on the IIS server. When the Client Agent has sent all the changes, the Server Agent reconciles the changes with the data on the publisher by using either the default or a custom conflict-resolution mechanism. At the end of the reconciliation process, the Server Agent reads all changes on the publisher that have occurred since the last synchronization and stores the changes in another file, which it sends to the client. The SQL Server CE Client Agent then applies the changes one by one to the local database.