One of the great things about Umbraco 5 is that you can choose the SQL CE option when installing to avoid having to do any database configuration whatsoever. The database created by default is in ~/App_Data/Umbraco.sdf. This means that you can get up and running with v5 in no time and get stuck in.

But what about when you want to deploy your site to production? SQL CE is fine for developing but it's not designed to run a production website with high traffic volumes. So if your site is going to be dealing with a lot of traffic, at some point you're going to want to migrate your data to SQL Server 2008. What a pain! I hear you cry. Actually - it couldn't be simpler.

With Microsoft's WebMatrix tool (which is available using the Web Platform Installer) it's dead simple. Here's how.

1. Open up Webmatrix and if your website isn't in the list already, then simply create a new one. You can do this by clicking on New Site -> Site from folder. Choose the folder and click OK and you're done.

2. When viewing your site, click on the Databases tab on the bottom left.

3. Select Umbraco.sdf from the left hand navigation.

4. Click the Migrate button in the top navigation.

5. Enter the details of your new server, i.e. where you want to migrate your database to. You'll need to enter the authentication details for the database, either Windows authentication or using a SQL Server login.

6. Click OK.

And that's it. You'll see the progress at the bottom of the screen and with any luck you'll get the "Migration Successful" message.

UPDATE: WebMatrix thinks it's being clever by adding a connection string to your new database to the web.config in the root of your site. However, Umbraco 5 requires the connection string in \App_Data\Umbraco\HiveConfig\web.config to be updated to point at your new database. It's called "nhibernate.ConnString" in the connectionStrings section. You also need to update the umbraco/persistenceProviderSettings/nhibernate entry. Here is my App_Data/Umbraco/HiveConfig/web.config with the old (SQLCE) settings commented out, and the new (SQL Server 2008) settings active:

 <configuration>
  <configSections>
    <sectionGroup name="umbraco">
      <sectionGroup name="persistenceProviderSettings">
        <section name="nhibernate" type="Umbraco.Framework.Persistence.NHibernate.Config.ProviderConfigurationSection, Umbraco.Framework.Persistence.NHibernate, Version=5.0.310.16, Culture=neutral, PublicKeyToken=null" requirePermission="false" />
      </sectionGroup>
    </sectionGroup>
  </configSections>
  <connectionStrings> 
    <add name="nhibernate.ConnString" connectionString="Data Source=davidslaptop\express2008r2;Initial Catalog=MyDatabase;User Id=umbracoUser;Password=umbraco" providerName="System.Data.SqlClient" />
    <!--<add name="nhibernate.ConnString" connectionString="Data Source=|DataDirectory|Umbraco.sdf" providerName="System.Data.SqlServerCe.4.0" />-->
  </connectionStrings>
  <appSettings />
  <umbraco> 
    <persistenceProviderSettings>
      <nhibernate useNhProf="false" connectionStringKey="nhibernate.ConnString" outputNhMappings="false" sessionContext="web" driver="MsSql2008" />
      <!--<nhibernate connectionStringKey="nhibernate.ConnString" outputNhMappings="false" sessionContext="web" driver="MsSqlCe4" />-->
    </persistenceProviderSettings>
  </umbraco>
</configuration>

FURTHER UPDATE: Okay so it turns out that the database schema is slightly different between SQL CE and SQL Server Express 2008. This is because if they were the same Umbraco would have to default to the lowest common set of database features. Anyway, in my case I'm upgrading to SQL Server Express 2008 R2, and I used Redgate's excellent SQL Compare tool to create a script which will update your database schema after you have followed the instructions above. To summarise:

1. Migrate your data with WebMatrix as described above.

2. Run this SQL script against your new database.

3. Update your web.config settings as described above.

I have only tested this with SQL Server 2008 Express R2 and I'd love to know how it works with other versions of SQL Server, so please leave a comment with your experiences. Thanks!

A big thank you to Alex for his help with this and the idea for the solution.