Knowledgebase: Shared Hosting
Migrating to SQL Server using WebMatrix.
Posted by Simon (Netcetera) on 08 February 2011 11:28 AM
WebMatrix includes SQL Server Compact, which is a lightweight version of Microsoft SQL Server that lets you create databases for your websites. When you create a database, it's added as an .sdf file in the App_Data folder of your website. Using SQL Server Compact is convenient, because the database is deployed simply by publishing or copying the .sdf file.
If you do not have SQL server express installed on your local machine, then you will be prompted to install SQL Server Express 2008 R2 as given in step 4.
1. In WebMatrix, open the website that has the database you want to migrate.
2. Select the Databases workspace and select the database to migrate.
3. In the Home tab, click Migrate to display the Migrate Database dialog box.
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.
5. 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.
6. Click OK.
7. 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.
8. That's it. To verify that the site runs correctly after the migration, click Run in the menu.