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.

Answer 2

SQLCE ToolBox is the best tool for the job. Just install the extension then the steps are as follows:

1. In Server Explorer add the connection to your new database (Connect To Database button)
2. Right click on Umbraco.sdf file in in the SQLCE ToolBox and choose Migrate To SQL Server
3. Choose your server and export.
4. Modify the umbracoDbDSN connection string to point at new database.

The target database should be empty (or at least not have any conflicting table names).

Answer 3

It seems easiest to do it in webmatrix, have you tried that?

From: https://our.umbraco.org/forum/umbraco-7/using-umbraco-7/53818-Convert-Umbraco-SQL-CE-database-to-SQL-Express

or: https://our.umbraco.org/forum/umbraco-7/using-umbraco-7/49519-Changing-database-in-Umbraco-7

First you need setup a database in SQL Server. Once that is done, then open the Webmatrix, and in the lower left corner in the webmatrix UI you can select the databas. Then browse to the SQL Server CE database, it's located in the \App_Data folder after that a "migrate button" will appear in the upper menu options. By press that button a migrate dialogue appears asking for connection details to the SQL Server database that you have created.