Microsoft ASP. NET site deployment guide (2): deploy the SQL Server Compact database

1. Summary

For database access, the Contoso University program requires that the following software be deployed along with the program, because it does not belong to. NET Framework:

• SQL Server Compact (database engine)
• ASP. NET Universal Providers (enable ASP. NET membership to use SQL Server Compact)
• Entity Framework 4.1 (Code First)

The database structure and related data of the two databases must be deployed. Generally, some test data is generated during system development. The test data cannot be deployed to the production environment. Of course, you may also need to enter data in the production environment for deployment to the production environment. This section describes how to configure the Contoso University project to include necessary software and data for deployment.

Reminder: check the Troubleshooting page if an error message or some functions are abnormal during operations in this section.

2. SQL Server Compact and SQL Server Express

The Sample program uses SQL Server Compact 4.0. This database engine is a new choice for web websites. The old version of SQL Server Compact cannot be used in the web environment. SQL Server Compact provides many common new functions, just like developing and deploying SQL Server Express and SQL Server. Depending on the host provider you choose, SQL Server Compact may be cheaper, because the full version of SQL Server requires additional charges. SQL Server Compact does not require additional fees because you can deploy the database engine as part of your program. Another advantage is that backup and restoration are very simple, because all the data is in A. sdf file in the app_data folder of the deployment site. Backup and restoration are just copy files.

 

However, you still need to pay attention to its limitations. SQL Server Compact does not support stored procedures, triggers, attempts, and replication. (For the complete list of functions supported by SQL Server 2005 and SQL Server 2008 but not supported by SQL Server Compact, visit: Differences Between SQL Server Compact and SQL Server ). Also, some tools that operate SQL Server Express and the full version of SQL Server database schema and data do not support SQL Compact. For example, you cannot use SQL Server Management Studio or Visual Studio database project to process SQL Server Compact databases. When you use SQL Server Compact, Entity Framework Code First automatically creates a database based on your data model. However, after deployment, it is very difficult to keep the development environment database and production environment database synchronized. It would be easy to use tools like SQL Server Management Studio.