Find out the steps to upgrade a .NET Compact Framework 1.0 application with an associated SQL Server CE 2.0 database to the .NET Compact Framework 2.0 and SQL Server 2005 Compact Edition. This article also explores new features that are now available to maintain the health and optimize performance of your SQL Server 2005 Compact Edition databases. (10 printed pages)

Microsoft® SQL Server™ 2000 Windows® CE Edition 2.0

Microsoft SQL Server 2005 Compact Edition

Microsoft .NET Compact Framework version 1.0

Microsoft .NET Compact Framework version 2.0

Microsoft Windows Mobile® 2003–based Pocket PCs

Microsoft Windows Mobile 5.0–based devices

Introduction

Coexistence

Database Upgrade Tool

Upgrade a SQL Server CE 2.0 Application

Upgrade Merge Replication and Remote Data Access Architectures

Database Maintenance Plans and Performance

Conclusion

SQL Server 2005 Compact Edition represents the next evolutionary step of the powerful, lightweight relational database for Windows Mobile applications. Its predecessors, SQL Server Mobile Edition and SQL Server CE 2.0, functioned as both on-device persistent data repositories and an offline data caches for mobile architectures that involve synchronizing data by using merge replication or remote data access (RDA) with larger SQL Server 2000 or SQL Server 2005 databases.

Because databases created with SQL Server Mobile Edition work seamlessly with SQL Server Compact Edition, this paper focuses on upgrading SQL Server CE to SQL Server Compact Edition. You can upgrade your physical SQL Server CE 2.0 database and data synchronization architecture to SQL Server 2005 Compact Edition. In addition, new features are available in SQL Server 2005 Compact Edition that represent an upgrade to SQL Server CE 2.0 in terms of achieving on-going database health and maintenance. This article first introduces the topic of running SQL Server CE 2.0 and SQL Server 2005 Compact Edition in a side-by-side coexistence model.

You can install SQL Server CE 2.0 and SQL Server 2005 Compact Edition on the same device as long as the version of Windows Mobile software on that device is a supported platform for both SQL Server CE 2.0 and SQL Server 2005 Compact Edition. Coexistence platforms include devices that run Windows Mobile 2003, Windows CE 5.0, and Windows Mobile 5.0. Note that while SQL Server CE 2.0 is compatible with Windows CE .NET 4.2, SQL Server 2005 Compact Edition is currently not supported on Windows CE.NET 4.2, so coexistence on this platform is currently not supported. By having both SQL Server CE 2.0 and SQL Server 2005 Compact Edition installed on your device, you allow earlier .NET Compact Framework 1.0 applications (which use SQL Server CE 2.0) and later .NET Compact Framework 2.0 applications (which use SQL Server 2005 Compact Edition) to run on a single device without conflict. Coexistence applies to the database engines themselves, and to the SQL Server CE 2.0 and SQL Server 2005 Compact Edition client and development tools.

For applications that use RDA or merge replication, you must direct the SQL Server CE 2.0 application and the coexistent SQL Server 2005 Compact Edition application to a different virtual directory on the Internet Information Services (IIS) server by using the InternetURLproperty on the SqlCeReplicatonor SqlCe RemoteDataAccessobject. Each of these virtual directories must contain the server agent ISAPI DLL that is provided by the corresponding version of the SQL Server server-side tools. In other words, the virtual directory that is serving the SQL Server CE 2.0 application must contain Sscesa20.dll, and the virtual directory that is serving the SQL Server 2005 Compact Edition application must contain Sqlcesa30.dll.

While the Pocket Query Analyzer (SQL Server CE 2.0, Isqlw20.exe) and the Query Analyzer 3.0 (SQL Server 2005 Compact Edition, Isqlw30.exe) development tools can coexist on a single device, the common SQL Server CE 2.0 and SQL Server 2005 Compact Edition database file name extension (.sdf) is associated with the development tool that the user installed last on device. If you install Query Analyzer 3.0 last, and then tap a SQL Server CE 2.0 database file name from File Explorer on your device, Query Analyzer 3.0 launches automatically because of the .sdf file association and reports an error in opening the earlier SQL Server CE 2.0 database. To prevent this error, launch the specific version of the SQL Server CE 2.0 or SQL Server 2005 Compact Edition development tools from the Programsmenu on your device and then connect to your database.

SQL Server CE 1.0, 1.1, and 2.0 share the same physical database format, but SQL Server 2005 Compact Edition has a completely new storage engine and database format. To upgrade a SQL Server CE 2.0 database to SQL Server 2005 Compact Edition, a command prompt utility is provided with the SQL Server 2005 Compact Edition product that performs the conversion on your mobile device.

The SQL Server 2005 Compact Edition upgrade utility is located at: drive:\Program Files\Microsoft Visual Studio 8\SmartDevices\SDK\SQL Server\Mobile\v3.0\ platform\ processor\Upgrade.exe.

To use the upgrade utility, first ensure that you have SQL Server CE 2.0 and SQL Server 2005 Compact Edition installed on the same device that has the source SQL Server CE 2.0 database. Then, copy the Upgrade.exe file from its current location to the same directory on the device in which the source SQL Server CE 2.0 database is located.

The Upgrade.exe command-line utility accepts command-line parameters that specify the source and destination database names, password, and encryption values, and whether to display any user interface feedback during the upgrade. For a reminder about usage, simply tap the Upgrade.exe file on your mobile device, which displays the following information as shown in Figure 1.

Figure 1. Usage dialog box

The upgrade utility will not affect the source database—the original SQL Server CE 2.0 database remains intact following an upgrade, and a new SQL Server 2005 Compact Edition database with the name that you specify on the command line is created. You need approximately 300 KB of memory on the device to run the upgrade tool and sufficient storage memory for the resulting SQL Server 2005 Compact Edition database.

As an example, the following procedure outlines how to upgrade the IBuySpyStore SQL Server CE 2.0 sample database.

To upgrade the IBuySpyStore sample database

1. Open the Runmenu on the device. (Press and hold the device’s Enter or Action key, tap and hold the clock in the upper-right corner of the screen, release the Enter or Action key, and then choose Run.)
   
   
2. Type upgrade /s IBuySpyStore.sdf /d ibs30.sdf.
   
   The upgrade utility creates a log file, named Upgrade.log, which records the execution steps of the upgrade attempt. This log file is placed in the same directory from which the Upgrade.exe utility is launched. The log file is automatically deleted and recreated with each successive run of the upgrade utility.
   
   If the upgrade succeeds, the message in Figure 2 appears.
   
   
   

Figure 2. Successful upgrade message

The resulting SQL Server 2005 Compact Edition database is slightly smaller in size than the source database as a result of the new storage optimizations in SQL Server 2005 Compact Edition. If the source database contains columns of type NTEXT, it is likely that the resulting SQL Server 2005 Compact Edition database will be larger in size due to the way NTEXTvalues are paged across storage memory in the new SQL Server 2005 Compact Edition storage engine.

Finally, it is important to understand that the upgrade utility only considers the schema, data, and indexes of the source database. It does not upgrade or migrate any merge subscriptions or RDA tracking information about tables in the source SQL Server CE 2.0 database. If your SQL Server CE 2.0 database contains merge replication subscription or RDA tracked tables, you will want to upload all changes from the SQL Server CE 2.0 database to your server before performing the upgrade, and then reinitialize the subscription or RDA tracking in the new SQL Server 2005 Compact Edition database.

SQL Server 2005 Compact Edition is designed to work with .NET Compact Framework 2.0 applications that are created by using Microsoft Visual Studio® 2005. You can upgrade your .NET Compact Framework 1.0 applications to Visual Studio 2005 and the .NET Compact Framework 2.0 with the following two-step procedure.

1. Upgrade your .NET Compact Framework 1.0 solution and project files to Visual Studio 2005 compliance.
   
   To accomplish this upgrade, simply open your solution in any version of Visual Studio 2005 with smart device development support. A conversion wizard walks you through the process. Anything that the wizard could not convert automatically is logged and reported when the wizard concludes. This process is a one-way conversion, so it is a good idea to create a backup of the original solution if you need to use Visual Studio .NET 2003 to view or edit the application in the future.
   
   
2. Update the project references to the .NET Compact Framework 2.0 and SQL Server 2005 Compact Edition.
   
   The conversion wizard does not attempt to automatically bring your earlier solution to the .NET Compact Framework 2.0 and SQL Server 2005 Compact Edition level in terms of references, content conversions, partial classes, and so on.
   
   

If you want your solution to remain at the .NET Compact Framework 1.0 and SQL Server CE 2.0 levels, Visual Studio 2005 supports those scenarios. The SQL Server 2005 Compact Edition installation includes a version of SQL Server CE 2.0 alongside the SQL Server 2005 Compact Edition libraries. You can find the SQL Server CE 2.0 libraries at: drive letter:\Program Files\Microsoft Visual Studio 8\SmartDevices\SDK\SQL Server\Mobile\v2.0. During deployment or packaging of your smart device application within Visual Studio 2005, the appropriate SQL Server CE 2.0 libraries are automatically included.

If you want to upgrade your application to the .NET Compact Framework 2.0 and SQL Server 2005 Compact Edition level, you need to update the references in your application to the .NET Compact Framework 2.0 and SQL Server 2005 Compact Edition assemblies. Be sure to verify the specific versions of any references your project has to the "System.Data," "System.Data.Common," and "System.Data.SqlServerCe" namespaces, and then update them if necessary to the current versions of the namespaces that are designed to work with the .NET Compact Framework 2.0. For example, after the conversion wizard is complete, you can use Solution Explorerin Visual Studio 2005 and open the Referencestree node to check the specific versions of the .NET Compact Framework and SQL Server CE assemblies that are referenced in the project, as shown below in Figure 3.

Figure 3. Checking the version of References in Visual Studio 2005

In Figure 3, you can see that the runtime version of the System.dll reference is 1.1.4322, which indicates that the project is still at the .NET Compact Framework version 1.1 level.

If your .NET Compact Framework 1.0 application included any physical SQL Server CE 2.0 files as project content, you need to upgrade these files to SQL Server 2005 Compact Edition. To upgrade the SQL Server CE 2.0 files included in your project, you can use the upgrade utility, which this article's Database Upgrade Tool section outlines, or you can recreate the SQL Server 2005 Compact Edition database either manually in Visual Studio 2005, or automatically using SQL Server 2005 Management Studio, by using merge replication with SqlCeReplication.AddSubscription (AddOption.CreateDatabase), or through SQL scripts, third-party tools, and so on. For more information about these options, see Performing Common Database Tasks (SQL Server 2005 Compact Edition)

Getting your .NET Compact Framework 1.0 and SQL Server CE 2.0 application to the .NET Compact Framework 2.0 and SQL Server 2005 Compact Edition level is only the beginning. While the code will likely compile without error because the managed provider (System.Data.SqlServerCe version 2) is reasonably backward compatible, there are a host of new features in the .NET Compact Framework 2.0 and in SQL Server 2005 Compact Edition—such as the SqlCeResultSetclass and many new methods on the SqlCeEngineobject—that you should explore to get the most from SQL Server 2005 Compact Edition.

Both SQL Server CE 2.0 and SQL Server 2005 Compact Edition share a common data synchronization architecture. Figure 4 shows the common SQL Server CE and SQL Server 2005 Compact Edition data synchronization architecture.

Figure 4. Data synchronization architecture for SQL Server 2005 Compact Edition

SQL Server CE 2.0 and SQL Server 2005 Compact Edition provide two out-of-the-box data synchronization options: remote data access and merge replication. To achieve data synchronization, a client agent is required on the mobile device and a server agent is required within IIS to broker the process with clients and the server-side database. Table 1 shows the combinations of client and server components that RDA and merge replication support.

Table 1. Supported combinations of client and server components

As the Coexistence section of this article mentions, you can also have more than one of these combinations running from the same mobile device.

To determine the correct versions of the SQL Server CE 2.0 or SQL Server 2005 Compact Edition client and server tools to support any of the supported scenarios, follow these guidelines:

1. The server tools have versions to match a specific service pack of SQL Server. You need to select the correct version of the SQL Server CE 2.0 or SQL Server 2005 Compact Edition server tools to match the specific service pack level of the SQL Server database with which you intend to synchronize data. This guideline is equally true when IIS and SQL Server are collocated and also when they are on separate servers. For example, to replicate SQL Server 2005 Compact Edition to a SQL Server 2000 SP4 database, you need to install Sql2kensp4.msi, which is located at drive:\Program Files\Microsoft Visual Studio 8\SmartDevices\SDK\SQL Server\Mobile\v3.0.
   
   
2. The client tools installed on the device must match the major version number of the server tools that are located in the IIS virtual directory and that your replication or RDA objects reference by using the InternetURLproperty.
   
   

If you are upgrading your current replication or RDA architecture from SQL Server CE 2.0 and SQL Server 2000 to SQL Server 2005 Compact Edition and SQL Server 2005 (or just upgrading the server from SQL Server 2000 to SQL Server 2005), be sure to upload all changes to tracked tables or replication subscriptions to the server before you perform the upgrade. For merge replication, you need to reinitialize all of your mobile subscriptions after the new SQL Server 2005 publication is configured for Web replication and a valid snapshot is available.

Database maintenance with SQL Server CE 2.0 is limited to compacting a database using the SqlCeEngine.Compactmethod and perhaps making backup copies of .sdf files at some interval. Compaction reclaims unused space, recalculates index statistics, and verifies the integrity of the database file itself, but if a SQL Server CE 2.0 database becomes inconsistent, compaction cannot always repair the database.

For SQL Server CE 2.0 solutions that implement a maintenance plan with a periodic call to the SqlCeEngine.Compactmethod, moving to SQL Server 2005 Compact Edition introduces many new features that can greatly improve your database maintenance plans. The storage engine in SQL Server 2005 Compact Edition is redesigned to reduce the need for maintenance and the risk of data corruption that was possible with SQL Server CE 2.0. SQL Server 2005 Compact Edition database files are divided into 4 KB logical units or pages. As data is stored, indexed, and removed from the database over time, there can be pages that contain unused space and pages that are entirely empty in the database. To optimize performance and minimize space on the client device, unused space must be reclaimed, index statistics must be periodically recalculated, and transaction buffers must be purged. SQL Server 2005 Compact Edition still supports the Compactmethod on the SqlCeEngineobject, but now allows in-place compaction, which means no destination database needs to be specified in the Data Sourceproperty, and the original database is overwritten in-place. With SQL Server CE, a sample database compaction might look like the following C# code example.

	Copy Code
string originalDB = "AdventureWorks.sdf"; string compactedDB = "AdventureWorks.sdf.tmp"; SqlCeEngine engine = new SqlCeEngine("Data Source = " + originalDB); try { engine.Compact("Data Source = " + compactedDB); engine.Dispose(); File.Delete(originalDB); File.Move(compactedDB, originalDB); } catch(SqlCeException e) {}

With SQL Server 2005 Compact Edition, your code can now call the Compactmethod without deleting the original database and renaming the destination database to the original, as shown in the following code example.

	Copy Code
string originalDB = "AdventureWorks.sdf"; SqlCeEngine engine = new SqlCeEngine("Data Source = " + originalDB); try { engine.Compact(); engine.Dispose(); } catch(SqlCeException e) {}

Before you perform a Compactmethod on a SQL Server CE 2.0 or SQL Server 2005 Compact Edition database, ensure that all database connections are closed and sufficient free storage space exists on the mobile device (usually about twice the size of the source database is required) to complete the operation.

The SQL Server 2005 Compact Edition SqlCeEngineclass still supports the Compactmethod, but it also introduces new Verify, Repair, and Shrinkmethods. SQL Server 2005 Compact Edition also introduces new AutoShrinkand AutoFlushfeatures, which are specified by using the AutoShrink Thresholdand Flush IntervalADO.NET connection string properties, respectively.

The following code example, written in C#, illustrates how the Verifyand Repairmethods can work together to periodically check and tend to the health of a SQL Server 2005 Compact Edition database.

	Copy Code
SqlCeEngine engine = new SqlCeEngine("Data Source = AdventureWorks.sdf"); if (!engine.Verify()) { engine.Repair(null, RepairOption.RecoverCorruptedRows); MessageBox.Show(“SQL Server 2005 Compact Edition Database has been repaired.”); }

Note that in the code example an enumerated RepairOptionvalue is passed to the Repairmethod on the SqlCeEngineobject. There are two RepairOptionsnow available in SQL Server 2005 Compact Edition:

1. RepairOption.DeleteCorruptedRowsremoves any rows from the SQL Server 2005 Compact Edition database that does not pass the integrity checking, which is performed during a call to the Repairmethod.
   
   
2. RepairOption.RecoverCorruptedRowsattempts to restore (to their original state) any rows in the SQL Server 2005 Compact Edition database that fail integrity checking during a call to the Repairmethod.
   
   

For more information about the new SqlCeEnginemethods available with SQL Server 2005 Compact Edition, see SqlCeEngine .

AutoShrinkis a new feature in SQL Server 2005 Compact Edition that automatically reorganizes the pages in a SQL Server 2005 Compact Edition database file so all empty pages are contiguously arranged at the end of the file and truncates them to reclaim space. You can tune the AutoShrinkoperation by specifying the AutoShrink Thresholdproperty in your connection string. This property expresses the percentage of free space in the database file to allow before executing the AutoShrinkoperation. Do not hesitate to set this property to an aggressive value because the AutoShrinkoperation has little impact on processor resources or performance. The default threshold for the AutoShrinkoperation is 60, meaning that after the percentage of free space in a SQL Server 2005 Compact Edition database reaches 60 percent, a Shrinkoperation is automatically performed. Setting the threshold for the AutoShrinkoperation to a value of 100 disables the AutoShrinkoperation altogether.

If you want to explicitly shrink a SQL Server 2005 Compact Edition database, the SqlCeEngineobject now supports a Shrinkmethod that effectively performs an immediate AutoShrinkoperation, regardless of the percentage of free space in the database.

Finally, SQL Server 2005 Compact Edition provides you with control over the interval at which pending transactions are written from the transaction buffer-pool to the database through a connection string property called Flush Interval. The value you set for this flush interval corresponds to the maximum number of seconds before committed transactions are written to physical SQL Server 2005 Compact Edition database storage. The default value for the Flush Intervalproperty is 10 seconds.

The following code example is a sample SQL Server 2005 Compact Edition connection string in C# that illustrates the use of the AutoShrink Thresholdand Flush Intervalproperties.

	Copy Code
SqlCeEngine engine = new SqlCeEngine("Data Source=AdventureWorks.sdf; autoshrink threshold=30; flush interval=5”);

This connection string instructs the SQL Server 2005 Compact Edition engine to perform an AutoShrinkoperation when free space in the AdventureWorks database reaches 30 percent of the overall database size and to flush committed transactions to SQL Server 2005 Compact Edition physical storage at 5-second intervals.

For more information about the new connection string properties available with SQL Server 2005 Compact Edition, see SqlCeConnection.ConnectionString .

While SQL Server 2005 Compact Edition represents the next step in the evolution of SQL Server CE 2.0, earlier Windows Mobile applications based on SQL Server CE 2.0 can either coexist with SQL Server 2005 Compact Edition or be physically upgraded to the newer version. SQL Server CE 2.0 applications that use merge replication or RDA can synchronize data with either SQL Server 2000 or SQL Server 2005. SQL Server CE 2.0 databases can also be upgraded to SQL Server 2005 Compact Edition while still replicating or performing RDA with SQL Server 2000 SP3a or later. There are significant new maintenance and administrative features in SQL Server 2005 Compact Edition that can ensure the ongoing health and optimum performance of your SQL Server 2005 Compact Edition databases.