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.