Using SQL Server Compact for ASP.NET Web Applications
This topic provides information about how to use SQL Server Compact 4.0 in ASP.NET web applications. SQL Server Compact is a free, lightweight database engine. It supports a maximum database size of 4 gigabytes. It does not support stored procedures, triggers, views, or replication.
SQL Server Compact is the recommended database choice for both development and production in ASP.NET Web Pages applications that you develop by using WebMatrix. The advantages of SQL Server Compact make it an appealing choice in many other .NET web application scenarios also. However, before you decide to start using it, consider also its future prospects. Microsoft will continue to maintain and support SQL Server Compact, but ongoing development efforts are focused on the other SQL Server editions. There are no plans to add features or enhancements to SQL Server Compact. For example, support for it was not added to SQL Server Management Studio (SSMS) or SQL Server Data Tools (SSDT) and will not be added in the future.
This topic contains the following sections:
Installing SQL Server Compact 4.0
Deploying SQL Server Compact 4.0 Databases
Tools for Working with SQL Server Compact 4.0 Databases
Migrating or Deploying from SQL Server Compact to SQL Server
This topic does not cover the version of SQL Server Compact that is used in Windows Phone. For information about Windows Phone Local Database, see Local Database for Windows Phone.
Installing SQL Server Compact 4.0
SQL Server Compact does not have to be installed on a computer: you can include it in a project by installing the Microsoft.SqlServer.Compact NuGet package. If you want to use SQL Server Compact with the Entity Framework, install the EntityFramework.SqlServerCompact NuGet package.
When you use a NuGet package to include SQL Server Compact support in a project, the package installation helps set up your project so that the database engine assemblies are deployed with it. You can then deploy your SQL Server Compact database to any production environment, including a shared hosting environment in which SQL Server Compact is not installed on the servers.
If you want to install SQL Server Compact 4.0 on your computer, use this link: Microsoft SQL Server Compact 4.0 SP1.
Deploying SQL Server Compact 4.0 Databases
A SQL Server Compact database resides in an .sdf file, and file-based access is used in production as well as development. Therefore, to deploy a database you can just copy the .sdf file. If the destination server does not have SQL Server Compact 4.0 installed, you also have to copy the assemblies that contain the database engine to the destination server.
If your database file is in the project's App_Data folder and the connection string uses the |DataDirectory| option to specify the path to the file, you don't have to make any changes to the connection string in the production application.
For information about how to deploy SQL Server Compact 4.0 databases with web projects, see the following resources:
Deploying SQL Server Compact Databases (ASP.NET site. How to deploy both the database and the database engine assemblies.)
How to: Deploy a Web Project Using One-Click Publish in Visual Studio
Tools for working with SQL Server Compact 4.0 Databases
You can't use SQL Server Management Studio or SQL Server Data Tools (the SQL Server Object Explorer window in Visual Studio) to manage SQL Server Compact databases, but other options are available:
Server Explorer in Visual Studio 2012 offers limited database manipulation functionality. You can install this functionality in Visual Studio 2010 SP1 by clicking the following link: Microsoft Visual Studio 2010 SP1 Tools for SQL Server Compact 4.0.
The Databases tab in WebMatrix has more features than Server Explorer.
Relatively full-featured third-party or open source tools are available, such as the SQL Server Compact Toolbox and SQL Compact data and schema script utility.
You can write and run your own DDL (data definition language) scripts to manipulate a database schema.
Migrating from SQL Server Compact 4.0 to SQL Server
If your web application outgrows the limitations of SQL Server Compact 4.0, you can migrate the database to SQL Server.
You can also use SQL Server Compact in your development environment when you intend to deploy to SQL Server or SQL Database in production. For example, this strategy can be used to make it easier to share projects across multiple versions of Visual Studio (Visual Studio 2010 and Visual Studio 2012). With other editions of SQL Server, you have to make sure that each computer that runs Visual Studio has the right version of SQL Server installed. There are potentially some issues with this approach:
SQL Server Compact and the other SQL Server editions use different database engines and ADO.NET providers, which means the same code can generate different results. Therefore, consider testing with SQL Server Express, SQL Server, or SQL Database before deploying to SQL Server or SQL Database in production.
The Web Deploy provider that Visual Studio uses to automate both initial database deployment and schema updates for SQL Server databases does not work with SQL Server Compact. An older Web Deploy provider can be used to do initial deployment, but it does not handle incremental schema updates. (This limitation does not apply if you deploy the database by using Code First Migrations, because that method does not use a Web Deploy provider.) For more information, see Configuring Database Deployment in Visual Studio in Web Deployment Overview for Visual Studio and ASP.NET.
For information about how to migrate from SQL Server Compact 4.0 to SQL Server, see the following resources:
Migrating to SQL Server (ASP.NET site. How to migrate from SQL Server Compact to SQL Server or SQL Server Express by using Visual Studio.)
Migrate a Database to SQL Server (Microsoft.com site. How to migrate from SQL Server Compact to SQL Server or SQL Server Express by using WebMatrix.)
See Also
Concepts
Other Resources
Using SQL Server Compact (Visual Studio)