How do I upgrade Umbraco CMS from using SQL Server CE to an upgraded version of SQL Server?

Question

I would like to upgrade my Umbraco project on my localhost (and eventually my live website) from running on SQL Server CE to either SQL Server 2014 or SQL Server 2016.

The reason for the upgrade is simple: I may at some point want to manage a website that has more than 4GB of data in the database, is scalable with multiple servers, and I'd like to back things up. Otherwise I'd be lazy and leave Umbraco.sdf alone.

I have not found consistent documentation on this process anywhere. Perhaps one of you might be more experienced with SQL or Umbraco and could help out.

(Aside: For those less familiar with Umbraco, Umbraco is a Content Management System written in C# and JavaScript. There's a SQL file in here named Umbraco.sdf which contents all of the website's contents. )

Answer 1

I figured this out, in case anyone else gets stuck.

Here's how to do it:

Step 1: Port over your current database. With Umbraco, there's an easy way of doing this. Simply install the Export SQL Server Compact package, an addon to your Umbraco CMS. Once installed, follow the directions and generate your SQL file.

Step 2: Import the generated script to SQL Management Studio and run it in a new database. In order to do this: create a new database and give a new user permission settings to access that database (don't use your server login - you can, but it's better to create a user so you can access remotely). After that, go ahead and copy and paste your entire file (yes, that entire file) into a new query (right click the database -> run query), paste the script, and run it.

Step 3: Change the connection string. This is in your web.config. Within the XML tags of , configure something akin to this (remove the {}):

<add name="umbracoDbDSN" 
     connectionString="Data Source={the ip of your database};Initial Catalog={theDatabaseName};User Id={theUserId};Password={yourPassword}" 
     providerName="System.Data.SqlClient" />

The nice thing about this connection string is that you can edit locally, as well as on site, with the same connection string. This allows you to test code changes on your localhost, without changing the code on the website. The only thing that is linked automatically is CMS Content.

Make sure not to delete your old Umbraco connection string, in case you want to revert back to it. Simply comment that out.

Step 4: Encrypt the web.config. Clearly it's not wise to keep your database password in plaintext on your website. This MSDN on encrypting your web.config is invaluable.

There are a few things you will lose with this:

1. Portability of your database. Sometimes you'll want to only have a file be a database instead of a whole server. We only recommend doing this step when your site is essentially done, as configuring a SQL Server and keeping it secure is an extra challenge you shouldn't worry about.

2. Occasionally, images don't transport. You might have to reinput all of your images manually.

3. Possible security. Every time you publish your website, you may have to reencrypt the web.config manually. This can be exceedingly dangerous - so make sure your website always has the web.config encrypted, even on new publishes.