Upgrading from Earlier Versions (SQL Server Compact)
Microsoft SQL Server Compact 4.0 is the latest version of SQL Server Compact. The other versions of this product are as follows:
Version 4.0: SQL Server Compact 4.0.
Version 3.5 SP2: SQL Server Compact 3.5 SP2.
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 4.0 can be installed side-by-side with 3.0/3.1 and 3.5, on desktop computers. 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, as the Database File Format for SQL Server Compact 4.0 is different than the earlier versions. To open the database file by using SQL Server Compact 4.0 , 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 4.0 files cannot be used with the earlier versions. However, version 2.0 files are not compatible with 3.5, 3.1, or 3.0.
SQL Server Compact 4.0 offers the following:
Improvements for ASP.NET Web Applications.
Enhanced Encryption algorithms.
Setup enhancements
Designer Support in Microsoft Webmatrix and Visual Studio 2010 Service Pack 1
New Transact-SQL Syntax
API Enhancements
Upgrading SQL Server Compact 3.0, 3.1 or 3.5 Database Files to SQL Server Compact 4.0
To open the database file from an earlier version of SQL Server Compact (version 3.5), use the Add Connection dialog box in Visual Studio 2010 Service Pack 1.
Open Visual Studio, click View and then click Server Explorer.
Right-click Data Connections.
Click Add Connection, and the Add Connection dialog box appears.
Type the path to the database file in the Data source text box (optional) or click on Change to select from available data sources. The default value in the text box is the path of the database file you were connecting to.
In the Password text box, type the password you wish to use to protect the database file (optional).
Click OK and the file is upgraded to SQL Server Compact 4.0. 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.5 database file is encrypted, during the upgrade to SQL Server Compact 4.0, it will be encrypted with the encryption mode of platform default.
Note
Visual Studio 2010 Service Pack 1 supports development for both SQL Server Compact 3.5 and SQL Server Compact 4.0. Using Visual Studio 2010 SP1 you can either upgrade the SQL Server Compact 2005 Compact Edition database to SQL Server Compact 3.5 or SQL Server Compact 4.0.
Encryption modes available to a SQL Server Compact 4.0 database are as follows:
Platform Default. Using this option the database is encrypted using AES128_SHA256, where AES128 is the encryption standard and SHA256 is the secure hash algorithm.
Engine Default. Using this option the database is encrypted using AES128_SHA512, where AES128 is the encryption standard and SHA512 is the secure hash algorithm.
Before you upgrade a SQL Server Compact 3.5 database file to version 4.0 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.