Walkthrough: Migrating a SQL Compact Database to SQL Server During Deployment

  • Article

This walkthrough shows how to migrate from a SQL Server Compact database to a full SQL Server database on a hosted server while deploying a Web application. In this scenario, you have developed your Web application using Visual Studio 2010 SP1 using a SQL Server Compact database. Your hosting provider supports ASP.NET Web applications, SQL Server, and Web Deployment 2.0. This enables you to use Visual Studio's Web publishing capabilities to quickly and easily publish your Web application to your hosting provider.

This walkthrough assumes that you are deploying your application for the first time.

Prerequisites

In order to complete this walkthrough, you will need:

  • Visual Studio 2010 SP1 installed on your computer.

  • An ASP.NET Web application that uses a SQL Server Compact database, such as the Web application that you create if you follow the steps in Walkthrough: Working with SQL Server Compact in Visual Studio.

  • An account with a hosting provider that supports ASP.NET and Web Deploy 2.0.

Specifying Package and Publish Settings

Your first step in deploying your Web application is to select the settings for packaging your application and deploying it to the hosting provider.

To specify the package and publish deployment settings

  1. In Visual Studio, and open the Web application that contains a SQL Server Compact database.

  2. On the Project menu, click Package/Publish Settings.

    The project settings are displayed and the Package/Publish Web tab is selected.

    Package/Publish dialog box

  3. In the Configuration list, select Release.

  4. Under Items to deploy (applies to all deployment methods), select Only files needed to run this application.

  5. Under Items to deploy (applies to Web Deploy only), select Include all databases configured in Pack/Publish SQL tab.

Specifying Database Settings

You now need to specify the settings for deploying your database.

To specify the database settings

  1. Open or switch to the Package/Publish SQL tab for project settings.

    Package/Publish SQL tab of Project Properties

  2. In the Configuration list, select Release.

  3. Under Database Entries, click Import from Web.config.

    The Database Entries table is updated with one row for each connection string that is found in the Web.config file.

  4. In the Database Entries table, select the entry for your SQL Server Compact database.

  5. Under Destination Database Information, next to the Connection string from destination database box, click the ellipsis (...) button.

    The Destination Connection String dialog box is displayed.

  6. In the Server name box, enter the name of the destination server as provided by the hosting provider.

  7. Under Log on to the server, enter the authentication information provided to you by the hosting provider.

  8. Select Select or enter a database name, and then enter the name SQL Server database that you are migrating to.

  9. Click OK.

  10. Under Source Database Information, select Pull data and/or schema from an existing database.

  11. In the Connection string for the source database box, select the SQL Server Compact database file to migrate.

  12. In the Database scripting options box, select Schema and Data.

Publishing the Web Application and Database

The last step is to create a publishing profile and publish the Web site to your hosting provider.

To publish the Web application and database

  1. In the project settings, select the Build tab.

  2. In the Configuration list, select Release.

  3. In the Build menu, select Publish Project.

    The Publish Web dialog box is displayed.

    Publish Profile dialog box

  4. In the Publish profile box, enter a name for this profile.

  5. In the Publish method list, select Web Deploy.

  6. In the Service URL box, enter the URL for the Web Deploy service on the destination computer at the hosting provider site. The URL might look like the following example:

    https://servername/msdeployagentservice

  7. In the Site/application box, enter name of the Web site, a slash (/), and the application name to use on the destination server.

  8. Select Mark as IIS application on destination.

  9. Under Credentials, enter your user name and password for the account on the hosting provider server.

  10. Click Publish to migrate your database and publish your Web application.

See Also

Tasks

How to: Deploy a Web Application Project Using One-Click Publish and Web Deploy

How to: Deploy a Database With a Web Application Project

Concepts

What's New in Visual Web Developer

How to: Transform Web.config When Deploying a Web Application Project