April 12, 2015 3 Orchard CMS

Updating a live Orchard Database

Lately, I have done a number of projects with Orchard, which was not only easy to work with but also quite a lot of fun. But usually when the site goes live the work doesn’t stop. You need to fix a few bugs and add new features over time. But especially with the database updates I had a really hard time dealing with.

Normally you start by getting the latest database snapshot from the live site and start working on with that. In my case a feature started out to be more complex than I had thought in the beginning. In the end, I had a lot of changes in both the code and the database and the development went for over 2 weeks. In this time not only my development database changed but also the live database. So I had to get both databases back in sync. After searching the internet for a suitable solution I found a useful article about a data and schema comparison tool built in to Visual Studio 2013.

I started thinking how I could use this for my problem. As far as I can see there is no way to use this comparison with SQL CE, which unfortunately is used by Orchard. Luckily, Orchard CMS supports a lot of different database types.

As a first step, I exported my current SQL CE database to a file with the help of the Visual Studio Add-in “SQL Server Compact Toolbox”.

  

Next, I created a new localDB. If there isn’t localDB installed, you can download it here.

With the ExecuteQuery dialog I imported the database from the file and I got an exact copy of my SQL CE database running on localDB.

Now, I had to tell Orchard to use this newly created database. I had to change the DataProvider to SqlServer and added a new connection string.

DataProvider: SqlServer
DataConnectionString: Data Source=(localdb)\ProjectsV12;Integrated Security=True;Encrypt=False;TrustServerCertificate=False;AttachDbFileName=D:\Orchard Projects\neuhold.pro\src\Orchard.Web\App_Data\Sites\Default\Orchard.mdf;

My development site is now running on localDB and my live site is running on a MS SQL Server. Everything is set up to use the built-in data and schema comparison of Visual Studio 2013.

Go into your Visual Studio 2013 and open your SQL Server Object Explorer (View – SQL Server Object Explorer). It is important to have both the localDB and the live Database in your Object Explorer.

In order to get all changes made in the dev database to the live database, click right on the dev database and select Schema Compare.

In the next step, select the target in the Dialog box and press the "compare" button.

After a few minutes, the comparison should be finished and a report should appear on the screen.

This report shows all the differences between the two database schemas and gives the opportunity to deselect changes that are not wanted. It is possible to either write all changes to the target database by pressing the "Update" button (1) or to generate an update script by pressing the small script-button (2). This generates a script with all the necessary operations which can be executed in the live database.

The schema of both databases are now in sync. Let’s take a look at the data stored in the database.

For this reason there is a data comparison tool. Go to the Object Explorer again and start the Data Comparison with a right click on your database and select Data Comparison.

  

In the wizard dialog choose your target database and hit the "Next" button.

An overview of all your tables and views will be displayed and it is possible to choose which tables and views should be compared. In my case I wanted to compare every table so the default settings were fine. Click "Finish" to start the comparison.

A report appears at the end of the comparison. It displays all the changes that are needed to get both databases in sync. There is the possibility to deselect all changes that should not be applied to the target database. After that, all changes can be applied to the target database by pressing the "Update Target" button (1) or a database script can be generated (2) with all the needed changes in it.

The live database will be merged with the development database. The main advantage of doing it this way was that I could choose which changes should be applied and which should not. In my opinion it is currently one of the most effective workflows to update your live site database.

3 Comments

Zoltán Lehóczky 2015-04-15 1:19

This is _really_ not the Orchard way of doing this :-). Such DB compare is always risky and error-prone (and has very limited use for some sites). To move content changes from local to live it's best to use export/import, and to move schema changes there are migrations.

admin 2015-04-15 4:25

Thanks for the comment. I didn't know this problems coming with and this workflow has worked for me for the last year. Usually I have a test site (exact copy of the live site) where I compare databases with and after making sure everything works fine these site get switched with the live one. But I like your approach as well. Do I need any preparations for the import/export feature (use Identity part?) and is there a way to update content items?

Zoltán Lehóczky 2015-04-16 11:45

For small sites this is fine, especially if you're the only one who can edit content, but it gets unusable with anything larger.

Good question about import/export: yes, you'll need an identity for every content item that you want to deal with (that can be IdentityPart but e.g. AutoroutePart supplies an identity with the URL as well). I don't think you can update existing content though, but I'm not sure about that.

Leave a comment

* These fields are required

Partner