Data Migration from SQL Server Compact

SQL Server Compact 4.0 is an embedded database and ideal for starter Web site development and light-usage scenarios. The maximum capacity of SQL Server Compact is 256 concurrent connections or 4 gigabytes of data. For better scalability and development, features such as stored procedures and for advanced data management capabilities; you can migrate the data to SQL Server Express, SQL Server or SQL Azure. Migration also helps in case of high-volume Web sites and applications. You can migrate data in the following ways:

To migrate data from SQL Server Compact

  • Using Microsoft Web Deployment Tool (MSDeploy.exe).

  • Using Visual Studio 2010 SP1.

  • Using Microsoft Webmatrix.

To migrate data in Visual Studio 2010 SP1

  1. Create a new Web application in Visual Studio.

  2. Connect to Microsoft SQL Server Compact 4.0 through a database connection. See How To: Connect to a SQL Server Compact Database by Using Visual Studio.

  3. In Solution Explorer, right-click the project, and then click Properties.

  4. On the Properties page, click the Package/Publish SQL tab.

  5. On the Package/Publish SQL tab, click Import from Web.config.

    Visual Studio reads the application Web.config file to find connection strings. For each connection string in the Web.config file, Visual Studio creates a row in the Database Entries grid. By default, the name in the Database Entries grid is the connection string name plus a -Deployment suffix.

    The ApplicationServices-Deployment row is created in the Database Entries.

    You can also create your own SQL Server Script that will run during deployment. For more information, see Walkthrough: Deploying a Web Application Project Using a Web Deployment Package

To migrate data in Microsoft Webmatrix

  • If you are using SQL Server Compact 4.0 database in Microsoft Webmatrix and want to publish to a SQL Server database, you must migrate the data to SQL Server before publishing. As the requirements grow to the level of enterprise databases, the schema and data can be migrated from SQL Server Compact to SQL Server using the Migrate option in the WebMatrix Editor. 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 SQL Server Compact to SQL Server. For instructions, see How to Migrate a Database to SQL Server.