Migrate a database to SQL Server

This walkthrough shows you how to migrate a Microsoft SQL Server Compact 4.0 database to SQL Server Express or SQL Server.

What you'll learn

  • How to use the Migrate feature in the WebMatrix Databases workspace.

  • How to install SQL Server Express if it's not already on your computer.

  • How to perform the migration and verify its success.

What you'll need

To run the steps in this procedure you must be running Windows XP SP3 or a later version of Windows. You'll also need to have WebMatrix installed. For information about how to install WebMatrix, see Microsoft Web Platform Installer.

Overview

Migrating to SQL Server allows you to take advantage of SQL Server features such as stored procedures and of its additional capabilities, such as handling more than 256 concurrent connections or databases larger than 4 gigabytes. This walkthrough shows you how to convert a Microsoft SQL Server Compact 4.0 database (in an .sdf file) to SQL Server Express or SQL Server. This is different from publishing the database to a public web server as part of your web site. For information about that, see How to publish a web application using WebMatrix.

Migrating to SQL Server

  1. In WebMatrix, open the website that has the database you want to migrate. If you do not have a website with a database and you just want to try the migrate feature, click Site From Template on the Quick Start screen, and then choose the Bakery template. This template website includes a database.
  2. Select the Databases workspace and select the database to migrate. In this example, we've selected bakery.sdf. (If you don't see your database listed, right-click the site and then click Refresh.)
  3. In the Home tab, click Migrate to display the Migrate Database dialog box.

    image01 

  4. If you do not already have SQL Server installed, the Migrate Database dialog box will display a link allowing you to install SQL Server Express 2008 R2 now. If you need to install it, click the link. During the installation process, you will be prompted to select the authentication mode. For now, select Windows Authentication. You can change this later.

    After you have finished the installation, change the default settings in the Migrate Database dialog box only if you are migrating to an instance of SQL Server Express on another computer, or you are migrating to SQL Server (not Express), or if you need to use a SQL Server login. By default, migration is to a local instance of SQL Server Express.

    image02

  5. Click OK.

    WebMatrix starts the process of migrating your schema (tables) and data. It displays a status message in the notification bar at the bottom of the screen while it performs the migration and when the migration completes.

    image03

That's it. To verify that the site runs correctly after the migration, click Run in the ribbon.

Here's what WebMatrix did to convert the database and make your website ready to use it:

  • Created the SQL Server Express database and migrated your schema and data.
  • Renamed the original bakery.sdf file to bakery.sdf.backup.
  • Updated the connection string in the Web.config file. If you didn't have a connection string, WebMatrix created one and named it the same as the .sdf file name without the extension. (In this case, "bakery".) If you use Database.Open to connect to your database, that still works because WebMatrix automatically looks for a connection string named bakery if it can't find a bakery.sdf file.

You can discuss this article using the adjacent Facebook talkback.

For technical questions please visit our discussion forums, where we have a vibrant community of developers like you, as well as Microsoft engineers who are ready to answer your questions!