Upgrading SQL Server Mobile and SQL Server CE

Microsoft SQL Server 2005 Compact Edition (SQL Server Compact Edition) maintains two versions of the SQL Server Compact Edition Server Agent side-by-side on the server that is running Internet Information Services (IIS). This allows for data replication from SQL Server Compact Edition to SQL Server. The two versions of SQL Server Compact Edition Server Agent can coexist on the same computer that is running IIS. This enables a simple and gradual upgrade process. SQL Server Compact Edition connectivity requires that the client components on the supported devices and the server components installed on the computer that is running IIS be the same major-release version.

You can upgrade a database that was created by using an earlier version of SQL Server Compact Edition by using the SQL Server Compact Edition Database Upgrade tool (upgrade.exe). The Database Upgrade tool runs on a smart device.

Note:

A database file that is created in SQL Server Mobile can be used with SQL Server Compact Edition. The versions use the same database file format (*.sdf).

When you run the SQL Server Compact Edition Database Upgrade tool, the new SQL Server Compact Edition database is created on the smart device. The new database, with the file name extension .sdf, contains all the data that was in the source database. You will have to reinitialize the upgraded database to continue to use it for replication.

If, in a SQL Server CE database, a UNIQUE constraint is specified on a column without specifying NULL|NOT NULL attributes, the column is created with a NOT NULL attribute automatically. If you try to upgrade this database to SQL Server Compact Edition, the same column attributes are copied over. Therefore, you will be unable to insert NULL attributes to this table, even after you upgrade to SQL Server Compact Edition. For more information about NULL|NOT NULL, see ALTER TABLE (SQL Server Compact Edition).

Important:

If you are using replication or remote data access (RDA) as a connectivity solution before upgrading to SQL Server Compact Edition, you must synchronize the source SQL Server CE database (subscription database) with the SQL Server database (Publisher). The reason for synchronizing before upgrading to SQL Server Compact Edition is to make sure that any changes that exist in the tables on the SQL Server CE database are updated on the SQL Server database, because after the upgrade you must re-subscribe or re-pull using the new database.

When you upgrade, first install SQL Server Compact Edition Server Tools, and then install the client components. Do not deploy newer client applications if you still have an earlier version of SQL Server CE Server Tools on the computer that is running IIS. After all components are in place, redeploy the application to your smart devices.

Important:

Make Sure that the upgraded application is available and functioning correctly before you remove the previous release of your application and the SQL Server CE client components.

During the application upgrade process, you must change any references of SQL Server CE version 1.0, SQL Server CE version 1.1, SQL Server CE version 2.0 and SQL Server Mobile to SQL Server Compact Edition. When you upgrade the computer that is running IIS, you must install SQL Server Compact Edition Server Tools. You can then configure a new virtual directory for SQL Server Compact Edition clients on this computer. As you deploy SQL Server Compact Edition client components to your devices, you must update your application to use this new virtual directory for SQL Server Compact Edition by updating the InternetURL property of your replication or RDA objects. The new InternetURL property points to sqlcesa30.dll instead of Sscesa10.dll or Sscesa20.dll. Updating the InternetURL property is very important because the SQL Server Compact Edition Client Agent can communicate only with the SQL Server Compact Edition Server Agent.

Note:

SQL Server Mobile, the previous version of SQL Server Compact Edition, requires the InternetURL property to be set to sqlcesa30.dll. If you are upgrading from SQL Server Mobile to SQL Server Compact Edition, and the InternetURL property is already set to sqlcesa30.dll, no other change is required. After you upgrade to SQL Server Compact Edition, replication of data between SQL Server and SQL Server Compact Edition should continue unhampered.