Share via

Upgrading from Earlier Versions (SQL Server Compact)

  • Article

Microsoft SQL Server Compact 3.5 Service Pack 1 (SP1) is the latest version of SQL Server Compact. Versions of this product are as follows:

  • Version 3.5 SP1: SQL Server Compact 3.5 SP1.
  • Version 3.5: SQL Server Compact 3.5.
  • Version 3.1: SQL Server 2005 Compact Edition (SQL Server Compact Edition).
  • Version 3.0: SQL Server 2005 Mobile Edition (SQL Server Mobile).
  • Version 2.0: SQL Server 2000 Windows CE Edition 2.0 (SQL Server CE).

SQL Server Compact 3.5 can be installed side-by-side with 3.0, on desktop computers and mobile devices. However, if you try to open a database file created using an earlier version, you will get a message that the file cannot be opened. This is because support for data types such as rowversion are not available in the earlier version. To open the database file by using SQL Server Compact 3.5 SP1, it first must be upgraded.

The database file of SQL Server Compact 3.5, 3.1, 3.0, and 2.0 have the same file extension (*.sdf). Version 3.5 files cannot be used with the earlier versions. However, SQL Server Compact version 3.1 can be used with version 3.0, and vice versa. Version 2.0 files are not compatible with 3.5, 3.1, or 3.0.

SQL Server Compact 3.5 offers the following:

  • Support for synchronization with SQL Server by using Microsoft Synchronization Services for ADO.NET.
  • Implementation of newer encryption algorithms.

Upgrading SQL Server Compact 3.0 or 3.1 Database Files

To open the database file from an earlier version of SQL Server Compact (version 3.1 or 3.0), use the Upgrade to SQL Server Compact 3.5 Database dialog box in Visual Studio 2008.

  1. Open Visual Studio, click View and then click Server Explorer.
  2. Right-click Data Connections.
  3. Click Add Connection and the Upgrade to SQL Server Compact 3.5 Database dialog box appears.
  4. Type the path to the database file in the Database text box (optional). The default value in the text box is the path of the database file you were connecting to.
  5. Type the password in the Password text box you wish to use to protect the database file (optional).
  6. Click OK and the file is upgraded to SQL Server Compact 3.5. After the upgrade, the original data file will be available as a backup (.bak) file in the same folder. If the SQL Server Compact 3.0/3.1 database file is encrypted, during the upgrade to SQL Server Compact 3.5, it will be encrypted with the encryption mode of platform default.

Encryption modes available to a SQL Server Compact 3.5 database are as follows:

  • Platform Default. This option assigns the default encryption for the platform that was used to create the SQL Server Compact database. For example, a database created on Windows XP with Platform Default selected will use encryption for Windows XP. If an encrypted database was created (and has this option selected) on any one of the following platforms, you will not be able to open it on either Windows CE 4.2 or Windows Mobile 2003 for Pocket PC.
    • Windows CE 5.0
    • Windows CE 6.0
    • Windows Mobile 5.0
    • Windows XP
    • Windows Server 2003
    • Windows Vista
    • Windows Server 2008
  • Engine Default. This option provides encryption that works on most SQL Server Compact supported platforms (but not on Windows CE 4.2 or Windows Mobile 2003 for Pocket PC).
    • Windows CE 5.0
    • Windows CE 6.0
    • Windows Mobile 5.0
    • Windows XP
    • Windows Server 2003
    • Windows Vista
    • Windows Server 2008
  • PPC2003 Compatibility. This encryption mode option works on all SQL Server Compact supported platforms.
    • Windows CE 4.2
    • Windows Mobile 2003 for Pocket PC
    • Windows CE 5.0
    • Windows CE 6.0
    • Windows Mobile 5.0
    • Windows XP
    • Windows Server 2003
    • Windows Vista
    • Windows Server 2008

Before you upgrade a SQL Server Compact 3.0 or 3.1 database file to version 3.5 or later, consider the following:

  • After the database file is upgraded, it can no longer be used with earlier versions. The original database file will be available as a backup (.bak) file in the same folder.
  • An earlier version of the database file can be also upgraded to the latest version programmatically by using the managed API SqlCeEngine.Upgrade or by using the native API ISSCEEngine.Upgrade.

Upgrading SQL Server Compact 2.0 Database Files

You can upgrade a database that was created by using SQL Server CE (version 2.0) by using the SQL Server Compact 3.5 Database Upgrade tool (Upgrade.exe). The Database Upgrade tool can be run on a Windows Mobile device. For using Upgrade.exe, both the SQL Server Compact 3.5 and SQL Server CE runtime must be installed on the mobile device.

When you run the SQL Server Compact 3.5 Database Upgrade tool, the new SQL Server Compact 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. To continue to use the upgraded database for replication, you must reinitialize it.

For more information, see How to: Upgrade SQL Server Compact 2.0 By Using the Database Upgrade Tool and Database Upgrade Tool (SQL Server Compact).

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 that has a NOT NULL attribute automatically. If you try to upgrade this database to SQL Server Compact 3.5, 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 3.5. For more information about NULL|NOT NULL, see ALTER TABLE (SQL Server Compact).

Important

If you are using replication or remote data access (RDA) as a connectivity solution when you upgrade to SQL Server Compact 3.5, you must synchronize the source SQL Server Compact 3.5 database (subscription database) with the SQL Server database (Publisher). The reason to synchronize before you upgrade to SQL Server Compact 3.5 is this: Any changes to tables on the SQL Server CE database must be updated on the SQL Server database because, after the upgrade, you must re-subscribe or re-pull, by using the new database.

Upgrading Distributed Applications

SQL Server Compact 3.5 Server Agent does not install with earlier versions of the Server Agent on servers that are running Internet Information Services (IIS).

SQL Server Compact 3.5 Server Tools can replicate data between SQL Server Compact 3.5 and SQL Server 2008. The server tools also support data replication between SQL Server Compact 3.0/3,1 and SQL Server. SQL Server Compact 3.5 Server Agent does not support data replication between SQL Server Compact 3.5 or earlier versions, with SQL Server 2000 or earlier versions.

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

Important

   Before you remove the previous release of your application and the SQL Server Compact 3.5 client components, make sure that the upgraded application is available and functioning correctly.

During the application upgrade process, consider the following:

  • You must change any references to earlier versions of SQL Server Compact to SQL Server Compact 3.5.
  • When you upgrade the computer that is running IIS, you must install SQL Server Compact 3.5 Server Tools. You can then configure a new virtual directory for SQL Server Compact 3.5 clients on the computer.
  • As you deploy SQL Server Compact 3.5 client components to your devices, update your application to use this new virtual directory for SQL Server Compact 3.5. To do this, update the InternetURL property of your replication or RDA objects. The new InternetURL property points to Sqlcesa35.dll instead of Sscesa10.dll, Sscesa20.dll, or Sqlcesa35.dll. SQL Server Compact 3.5 Server Tools is installed in the location %ProgramFiles%\Microsoft SQL Server Compact Edition\v3.5\sync\SQL. Updating the InternetURL property is important because the SQL Server Compact 3.5 Client Agent can communicate only with the SQL Server Compact 3.5 Server Agent.

Note

SQL Server 2005 Compact Edition Server Tools or SQL Server 2005 Mobile Edition Server Tools requires the InternetURL property to be set to Sqlcesa35.dll. If you are upgrading from one of these versions of the server tools to SQL Server Compact 3.5 Server Tools, update the InternetURL property to Sqlcesa35.dll. Note that SQL Server Compact 3.5 Server Tools does not support replication of data with SQL Server 2000 SP3a or SQL Server 2000 SP4.

See Also

Tasks

How to: Install SQL Server Compact on a Device

Concepts

Installing and Deploying on a Device (SQL Server Compact)

Other Resources

Installing SQL Server Compact

Help and Information

Getting Assistance (SQL Server Compact 3.5 Service Pack 1)