Microsoft SQL Server Compact 4.0 is available for download

  • Article

The SQL Server Compact team is happy to announce that the next version of Microsoft SQL Server Compact 4.0 has been released, and is available for download with the Microsoft Web Platform Installer 3 or from the Microsoft Download Center.

SQL Server Compact 4.0 has been designed, developed, tested and tuned over the course of last year and the release has been also vigorously verified by the vibrant MVP and developer community. The feedback from the developer community has helped improve the quality of the SQL Server Compact 4.0 release and the Compact team would like to thank all community members who participated in the release.

The main value proposition of SQL Server Compact 4.0 is the ability to use it as a data store for ASP.NET web applications. Please take a moment to go through the scenarios in which SQL Server Compact 4.0 will not work. For these scenarios SQL Server Compact 3.5 SP2 is the right choice.

Reasons for using SQL Server Compact 4.0:

  1. Database for ASP.NET Web Applications and Web Sites: Compact 4.0 has been optimized and tuned for use for ASP.NET web applications and has been field tested to ensure that Compact 4.0 can handle the load of starter websites and scale up to a database size of 4 GB.
    1. Default database for Microsoft WebMatrix: Compact 4.0 is the default database for Microsoft WebMatrix, which is the web stack that encapsulates all the technologies like ASP.NET, IIS Express, Editor and SQL Server Compact that are needed to develop, test and deploy ASP.NET websites to third party website hosting providers.
    2. Rapid website development with free, open source web applications: Popular open source web applications like mojoPortal, Orchard, Umbraco etc. support Compact 4.0 and can be used to rapidly develop, test and deploy websites.
    3. Partial trust and virtual memory: Compact 4.0 works in partial trust that is the mainline scenario in which the ASP.NET web applications are deployed. The virtual memory used by Compact 4.0 has been optimized to ensure that that maximum allowed 256 connections can be opened for the database.   
    4. One click migration to SQL Server: As the requirements grow to the level of enterprise databases, the schema and data can be migrated from Compact to SQL Server using the migrate option in the WebMatrix IDE. This also adds a web.config xml file to the project that contains the connection string for the SQL Server. Once the migration completes, the website project seamlessly switches from using Compact to SQL Server.
    5. Removes the soft block for use in ASP.NET web applications: In the earlier versions of Compact a flag, namely, SQLServerCompactEditionUnderWebHosting had to be flipped to use Compact in ASP.NET web applications. The flag has been removed in Compact 4.0.   
  2. Enhancements of the fundamentals (setup, deployment, reliability, and encryption algorithms) : The bases of Compact 4.0 have been strengthened to ensure that it can be installed without any problems, and can be deployed easily, and works reliably while providing the highest level of security for data. 
    1. Consistent setup and install experience and small download: There are separate MSIs for x86 and x64 platforms, and the x64 MSI installs the Compact components in both the WOW mode and the native mode in - %Program Files (x86)%\Microsoft SQL Server Compact Edition\v4.0, and in %Program Files%\Microsoft SQL Server Compact Edition\v4.0. The x86 MSI has been blocked from installing Compact components on an x64 platform, and similarly x64 MSI has been blocked from installing on x86 platform. This leads to a consistent installation and setup experience and also avoids future issues in Service Pack releases, where a higher version of SP1 x86 MSI can overwrite the GACed assemblies with a higher version on an x64 platform. The issue was faced in the Compact 3.5 SP2 release and the knowledge base article no 974247 describes the issue in more detail. All these setup improvements have been done while keeping the small download size of Compact intact at 2.5 MB.
    2. Easy private deployment: All the x86 and x64 Compact assemblies and DLLs that need to be privately deployed are in the folder - %Program Files%\Microsoft SQL Server Compact Edition\v4.0\Private. The contents of the folder can be copied in the application directory to privately deploy Compact. Both the x86 and x64 MSIs install the x86 and x64 Compact assemblies and DLLs in the Private folder and there is no need to install the x64 MSI on an x86 platform to get the x64 DLLs. Note that the native DLLs of SQL Server Compact need the Microsoft Visual C++ 2008 Runtime Libraries (x86 and x64) SP1 to function properly. The assemblies for the Visual C++ 2008 runtime are present in the Private folder, and deploying all the DLLs and folders in the Private folder deploys all the files needed for Compact to work properly.
    3. Higher reliability: The ASP.NET web applications have a different workload than the desktop applications. Compact has been tested for handling the workload of starter websites, and this has made the product more reliable than the earlier versions. 
    4. Highly secure: Compact 4.0 uses SHA2 algorithm to secure data and provide a high level of security. Compact 4.0 is also FIPS compliant and can be used in applications that need FIPS compliance.
  3. ADO.NET Entity Framework, API and T-SQL syntax enhancements: Compact 4.0 also has enhancements for Entity Framework, APIs and T-SQL syntax as described below:
    1. ADO.NET Entity Framework 4 (.NET FX 4) code-first and server generated keys: Compact 4.0 works with the code-first programming model of ADO.NET Entity Framework. In addition, the columns that have server generated keys like identity, rowguid etc. are also supported in Compact 4.0 when used with ADO.NET Entity Framework 4.0 (this is the version of ADO.NET Entity Framework that released with .NET FX 4). Support for the code-first and for the server-generated keys rounds out the Compact support for ADO.NET Entity Framework and fulfills one of the main asks from the development community.
    2. T-SQL syntax for OFFSET & FETCH: Compact 4.0 supports the T-SQL syntax for OFFSET & FETCH, and due to this paging queries can be run against the database file.
    3. API enhancements: Compact adds in the support for two new APIs.The first one is the System.Data.SqlServerCe.SqlCeConnection.GetSchema() that can be used to get the schema from a Compact database file. The API is also used to provide the support for System.Data.Common.DbConnection.GetSchema. The second new API is the  System.Data.SqlServerCe.SqlCeConnectionStringBuilder() that let developers programmatically create correct connection string for Compact 4.0, and to parse & rebuild existing connection strings. The API is also used to provide the support for System.Data.Common.DbConnectionStringBuilder.
  4. Easy application development in Visual Studio 2010: Visual Studio 2010 provides for rich designers and easy debugging that can be used to develop ASP.NET web applications for Compact 4.0. See the Visual Studio 2010 SP1 Beta help page for information on installing the required components to develop applications for SQL Server Compact 4.0.

Scenarios not enabled by SQL Server Compact 4.0:

The SQL Server Compact was focusing on enabling the new scenarios for ASP.NET Web Applications, and due to time and resource constraints the following scenarios have not been enabled in SQL Server Compact 4.0. For these scenarios the SQL Server Compact 3.5 SP2 is the right choice.

  1. Data replication with SQL Server: Compact 4.0 does not support data replication with SQL Server using Sync Framework, merge replication or remote data access (RDA). 
  2. LINQ to SQL: The LINQ to SQL does not work with Compact 4.0.
    1. The ADO.NET Entity Framework 4 that ships in .NET FX 4 works with Compact 4.0, and additional features like support for code-first and server generated keys have been added in Compact 4.0 for EF 4.
  3. Windows Mobile, Windows Phone and Windows CE devices: Compact 4.0 does not have a release for Windows Mobile, Windows Phone or for Windows CE devices.
  4. Designers in the VB or C# Windows projects in Visual Studio 2010 SP1 Beta: The following wizards do not work with Compact 4.0 in the Windows project system. Developers can manually add reference to the ADO.NET provider for Compact 4.0 (System.Data.SqlServerCe) to develop programs for Compact 4.0 in the Windows projects:
    1. The Data Source Configuration Wizard that is used to configure datasets.
    2. The Configure Data Configuration wizard that is used to setup the syncing of data and schema with SQL Server using Sync FX.
    3. The Entity Data Model wizard that is used to generate entities from a Compact database.

Known Issues:

The SQL Server Compact 4.0 Books Online and the updated read me is available on the Microsoft Download Center and at MSDN Online. The Books Online and the read me provides the required information for developing applications with SQL Server Compact 4.0 and the list of known issues and supported platforms. For more information about Microsoft WebMatrix refer to the links below:

Thanks,

Ambrish Mishra

Program Manager - SQL Server Compact