Views: 15087
Number of votes: 6
Average rating:

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.

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?

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.

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?

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.

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

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

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

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.

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

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

Any way of how to create a database from zero?

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.

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?

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.

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.

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.