Per Bjurström
Jun 9, 2014
  20557
(6 votes)

NuGet: A new database version for CMS

We are releasing an update to CMS that contain database schema changes, since this will be the first time we release schema changes via NuGet I thought I spend some time describing how it works and why we made certain choices. If you are using Commerce and been updating the past weeks you will already be familiar with these commands.

We started some time ago building support for shipping database updates in NuGet packages based on some design principals:

  • Never modify the database without consent: You should be able to update packages without being “scared” that schema changes are applied to a shared database or a database without backups that you need to revert.
  • Use ordinary SQL files: Make sure environments that have high auditing requirements can run them manually when needed.
  • Simple to update for devs: Make it super-easy to update the database schema cross products, assuming both CMS and Commerce starts shipping schema changes more often you don’t want any manual trickery.

The first thing you notice after applied the latest update is the more helpful error message when you start the site for the first time.

image

To apply the updates open up the ‘Package Manager Console’ and run the command Update-EPiDatabase:

image

This command will update database schema for the database defined in the connection string EPiServerDB for CMS (and EcfSqlConnection for Commerce), the printout shows which files it finds that are potential updates.

The files are by convention:

  • Stored in a folder tools/epiupdates/sql in each NuGet package.
  • Files are named after the package version they were introduced in (ie 7.8.0.sql)
  • Each file begins with a validation statements that determines if the file should be executed or not (you can run this statement manually if need to very if a file has been applied or not).

For troubleshooting, or if you just want to see more details, you can add -Verbose:$true.

image

Our recommended approach to deploy changes to production is to run Export-EPiUpdates to export the schema changes to a folder that is independent of Visual Studio, this command basically does the same thing as Update-EPiDatabase but dumps the commands into a bat file to make them portable and easy to run in any environment. If you have a look inside that file you will notice a simple command line tool epideploy.exe that used to execute scripts, same tool that is executed when you update Update-EPiDatabase.

image

image

You can also run this command on the development site to test it out, if there are no changes nothing will be modified so it’s safe to run multiple times.

image

 

A final note. We have discussed building automatic database schema updates into the products but based on the feedback we got early on it was more important to get the simple and controlled semi-automatic approach right first. Maybe based on your feedback and ideas we will get back to this area and offer more automation, let us know what you think.

Note: All of this is documented in the SDK under “Updating EPiServer via NuGet”, including schema changes to configuration which I did not cover in this post.

Jun 09, 2014

Comments

Tore Lervik
Tore Lervik Jun 10, 2014 09:56 AM

This feature cripples our octopus deployment. Now we have to manually fix the db when there is a sql-change.

What about a way to tell it it's ok to auto-migrate to 7007?

Jun 10, 2014 01:24 PM

We are considering auto-migrate, but octopus should be able to run a script on deploy? Run Export-EPiUpdates and checkin the folder to source control. The run the update.bat via octopus on each deploy and it will take care of making sure you have the correct version.

Njål Gjermundshaug
Njål Gjermundshaug Jun 14, 2014 01:27 AM

I have installed the latest version (EPiServer 7.5.394.2 - CMS + Alloy MVC Demo) and upgraded using Nuget. When opening the site I get the yellow screen of death telling me to run 'update-epidatabase'.

When I run this from the Package Manager Console in VS2013 - I get the following error: http://screencast.com/t/KbYzFl1T

Using standard/default paths for the installation. PS version 4.

Got any tips?

Jun 16, 2014 08:46 AM

Hi,
first make sure the "Default Project" inside Package Manager console points to the correct project. The error "Get-ChildItem : Cannot find drive. A drive with the name 'http' does not exist" can happen if you open the site as a "Web Site" inside Visual Studio. Open as File->Open->Project/Solution. If that does not solve your problem please contact support to get assistance.

robert.linde@nansen.se
robert.linde@nansen.se Jun 28, 2014 09:42 PM

I've published a blog post on how to make these schema changes work with octopus deploy: http://robertlinde.se/post/episerver-sql-scripts-and-continuous-integration-with-octopus-deploy

Joel Peterson
Joel Peterson Jun 30, 2014 01:34 PM

Hi,

I get a similar error after updating from 7.5.394.2. Do you have any idea how to solve this?

PM> update-epidatabase -Verbose:$true
\\VM08\WebSites\Testsite\trunk\packages\EPiServer.Framework.7.9.0\tools\epideploy.exe -a sql -s \\VM08\WebSites\Testsite\trunk\Site
-p \\VM08\WebSites\Testsite\trunk\packages\EPiServer.CMS.Core.7.9.0\tools\epiupdates\* -c EPiServerDB
epideploy.exe : Directory does not exist: '\\VM08\WebSites\WebSites'
At \\VM08\WebSites\Testsite\trunk\packages\EPiServer.Framework.7.9.0\tools\upgrade.psm1:188 char:4
+ & <><><>< $epiDeployPath -a $action -s $sitePath -p $updatePath\* -c $settings["connectionStringName"] -d (GetVerboseFlag($PSBoundParameters))
+ CategoryInfo : NotSpecified: (Directory does ...Sites\WebSites':String) [], RemoteException
+ FullyQualifiedErrorId : NativeCommandError

Jun 30, 2014 02:38 PM

Yes, known issue #115088 when you run on a file share, copy the files locally instead.

Jesper Hultén
Jesper Hultén Oct 24, 2014 12:10 PM

Hello!
Updating to EPiServer.Cms.UI.7.9.0 works good but after upgrading to EPiServer.Cms.Core.7.8.0 and EPiServer.Framework.7.8.0 and running the database upgrade to v7007 I get a server 503 error when trying to save content in edit mode. The service /cms/Stores/contentversion returns "Network error 503 service unavailable".

https://www.dropbox.com/s/qjnqlepdw8jemuj/error.png?dl=0

After a few tries the application pool crashes.

I tried doing the upgrade on a Alloy Templates website and that works as expected.

What changed in the upgrade of EPiServer.Framework and EPiServer.Cms.Core to have caused this?

Would be nice to solve this, as it prevents us from updating to any newer versions.

Oct 24, 2014 01:21 PM

Jesper, I have no idea without seeing the stacktrace. But why do you not upgrade to the latest version directly instead?

muhammad shamsheer
muhammad shamsheer Jan 20, 2015 05:22 AM

I am not able to update the EPiServer DB . when I run the command Update-EPiDatabase I get the error invalid Invalid database version detected [7.8.0.sql]. When I see my data base version it is 7003. When I export the update script using the command Export-EPiUpdates, I can see that I get the script version from 7.8.0 to 7.19.1 but not the script version before 7.8.0. Can any one please help me to proceed further

Oleksandr Kucherenko
Oleksandr Kucherenko Jan 28, 2015 12:33 PM

Any way of how to create a database from zero?

Luc Gosso (MVP)
Luc Gosso (MVP) Feb 9, 2015 10:44 AM

What I'm i missing out? as @[muhammad shamsheer] i get "Invalid database version detected [7.8.0.sql]" as i don´t have 7006. How do i upgrade db version from 7003 to 7006 on production database?
I have run the 7.5 episerver deploy center upgrade on the database/code just before runing an Export-EPiUpdates on 7.9.2 in VS Package Manager Console. It is an upgrade from cms6r2 in production environment.

Luc Gosso (MVP)
Luc Gosso (MVP) Feb 9, 2015 03:11 PM

Seems that 7003 i Episerver 7.0 database version. And 7006 is 7.5. But what i can see, i cant run EPiServerRelease75.sql more than one time. It is not made to run serveral time. And upon error in the first upgrade thru Deploment center, it does not upgrade to 7006. Am i right? does anyone know?

Feb 9, 2015 04:21 PM

Deployment Center should upgrade database to a version that is 7.5 compatible, those scripts are named [from version]-[to version].sql and shipped with Deployment Center. From there you can run NuGet based upgrade.

Luc Gosso (MVP)
Luc Gosso (MVP) Feb 10, 2015 03:31 PM

Is there a way of updating the production db the from 6r2 to 7.0 to 7.5, without Deploymentcenter running all configs and files transfer (or with a minimal mode)? just by runing the scripts named [from version]-[to version].sql .... We do have Commerce, Xforms in our solutions. ?? if not, is it important to run the site in 7.0 and .5 version? because now we do have our 6r2 version and a development solution in 7.19.2.

Ross Petev
Ross Petev Apr 15, 2015 03:57 PM

Database upgrade incremental scripts can be found in the Upgrade folder under Episerver / Program Files. On my computer this is: C:\Program Files (x86)\EPiServer\CMS\7.5.394.2\Upgrade\Database\sql.
The database can be upgraded from 7003 to 7006 using three of these scripts (examine the version numbers in if statements within the scripts)

Please login to comment.
Latest blogs
Introducing Optimizely Graph Source .NET SDK

Overview Of Optimizely Graph Optimizely Graph is a cutting-edge, headless content management solution designed to integrate seamlessly with any...

Jake Minard | Oct 10, 2024

Content Search with Optimizely Graph

Optimizely Graph lets you fetch content and sync data from other Optimizely products. For content search, this lets you create custom search tools...

Dileep D | Oct 9, 2024 | Syndicated blog

Omnichannel Analytics Simplified – Optimizely Acquires Netspring

Recently, the news broke that Optimizely acquired Netspring, a warehouse-native analytics platform. I’ll admit, I hadn’t heard of Netspring before,...

Alex Harris - Perficient | Oct 9, 2024 | Syndicated blog

Problem with language file localization after upgrading to Optimizely CMS 12

Avoid common problems with xml file localization when upgrading from Optimizely CMS 11 to CMS 12.

Tomas Hensrud Gulla | Oct 9, 2024 | Syndicated blog

Optimizely Autocomplete (Statistics)

A user starts typing in the search input, and it returns suggestions for phrases they might be searching for. How to achieve this?

Damian Smutek | Oct 9, 2024 | Syndicated blog

Optimizely Forms: You cannot submit this form because an administrator has turned off data storage.

Do not let this error message scare you, the solution is quite simple!

Tomas Hensrud Gulla | Oct 4, 2024 | Syndicated blog