One of the cool WebMatrix features is the ability to manage other databases from within the WebMatrix Databases Workspace. This post describes a scenario where you can take an existing SQL Compact Edition database from a template site, migrate it to a SQL or SQLExpress database and then connect to your live database and manage it after you’ve published.
Step 1: Migrating from SQL Compact to SQLExpress/SQL
From a site with a SQLCE database:
- Navigate to the Databases workspace
- Select the Home tab and click “Migrate”
- If you want to install SQLExpress, you can click the link in the dialog
- Fill out the information to migrate your database.
- Click OK to start the migration
- When the migration completes, a web.config file is generated so that content is being served from the SQL/SQLExpress database and the original SDF file is backed up
Fig. 1: Migration Dialog
Fig. 2: Migration Status
Step 2: Connecting to the Live Database
After you publish your site to a hoster, you can use the information that they provide to connect to your live database.
Starting from the Database Workspace:
- Click New Connection
- Fill out the information that you used for the Publish connection string
- Click OK
- Now you should be connected to your live database
- If someone goes through and registers for your Photo Gallery site, you can now see their registered info on the live database
Once you’ve connected to the live database, you can make changes just as if you were modifying the database for the development site.
Fig. 3: Remote Database Connection Dialog
Fig. 4: Local and Live Connections in the Databases Workspace
Now you can migrate a SQL Compact Edition database to SQLExpress or SQL and monitor the live database server after you’ve published the migrated development database. Once you connect to the live, remote database you can manage it like any other database within WebMatrix.