Optimizely Content Cloud (CMS) and updates to the database schema

The Optimizely (formerly Episerver) products are continuously improved and weekly updates provide bug fixes, security updates, performance improvements, and occasional new features. Sometimes the database schema remains unchanged when you update the product, sometimes it does not.

When Optimizely makes changes to the database schema, those changes are incorporated into the NuGet package as ordinary SQL files. Each file has logic to make sure it only runs when required. The SQL files have to be executed in order. Since keeping track of these files and running them may be tedious work performed at regular intervals, a set of tools are provided.

Why does it matter?

If you update the NuGet package, but not the database schema. You will be greeted with this message.

If you roll back the NuGet package to a version using an older database schema (without rolling back the database), you will get the following message.

How can you tell if the schema will be updated?

There is a nice tool available at https://nuget.optimizely.com/ - but it's not visible unless you log in.

This tool lets you enter two versions of the NuGet package Episerver.CMS.Core and will tell you the database version of both versions, and any version in between.

In the above screenshot, you can see that an upgrade from 11.20.6 to 11.20.7 will require the database schema to be updated, while an upgrade from 11.20.7 to 11.20.8 will not require the database schema to be updated.

How to tell what version of the database you are currently running?

As always, the answer is found in the database. Simply execute the stored procedure sp_DatabaseVersion.

DECLARE	@db_version int
EXEC @db_version = [dbo].[sp_DatabaseVersion]
SELECT 'Database version' = @db_version

How to update the database schema?

You can update the database schema automatically or apply the changes manually.

Automatic updates of the database schema

When the site starts, the InitializableModule type DataInitialization is run, and it will call this method:

EPiServer.Data.SchemaUpdates.Internal.EnsureDatabaseSchema();

The method will check if the database schema should be automatically updated. It does so by checking if the attribute updateDatabaseSchema="true" is added to the episerver.framework element in the configuration as shown below.

<episerver.framework updateDatabaseSchema="true">

If automatic updates are enabled in the episerver.framework element, the next step is checking for implementations of  ISchemaValidator. This is a way to interact with the schema upgrade process.

The interface defines two methods

public class CustomDatabaseValidator : ISchemaValidator
{
   public bool IsDatabaseUpdateAllowed(ConnectionStringOptions connectionStringSettings)
   {

   }

   public void BeforeUpdating(ConnectionStringOptions connectionStringSettings)
   {

   }
}

• IsDatabaseUpdateAllowed This method is called to check if an automatic update should be allowed. You can implement this to allow automatic updates only on specific environments, for specific users, etc.
• BeforeUpdating If all validators allow automatic schema update, the schema calls the method before it performs the actual update, so you can perform some action such as a backup of the database, or alerting some users with an email.

If updating the database schema is prevented by the implementation of ISchemaValidator the following exception is thrown.

My personal preference for automatic schema updates is to set updateDatabaseSchema to false for local development, so the developer that updates the NuGet package Episerver.CMS.Core will notice that there is an actual change to the database schema. Then, either temporarily changing  updateDatabaseSchema to true while updating the database schema, or performing a manual update.

I will use configuration transformations to set updateDatabaseSchema to true for all other environments (preproduction, production, etc.) so that the schema is automatically updated after deployment.

Manual updates of the database schema, locally

If you for some reason would like more control over the schema update, you can handle this manually.

The command Update-EPiDatabase automatically detects all install packages that support the pattern for transformation used by Episerver and applies them against the configured database. Below are the steps to manually apply the database schema changes.

1. Open Package Manager Console in Visual Studio.
2. Make sure "Default project" points to the website.
3. Type Update-EPiDatabase in the console and press Enter.
4. All schema updates are now applied.

Manual updates of the database schema, in production (on-prem)

The recommended approach is using automatic schema updates, but when automatic schema updates are not enabled, then database schema changes can be deployed using the following steps:

1. Open Package Manager Console in Visual Studio.
2. Make sure "Default project" points to the website.
3. Type Export-EPiUpdates and press Enter.
4. Copy the exported files to the production server (the path to the exported files should be obvious from the log messages).
5. On the production machine: Open a command prompt as Administrators and execute update.bat c:\inetpub\mysite. The update.bat is just a simple readable batch file that executes the different transforms on a specific site. Change the parameter to the actual physical location of that specific site.  By default, only the database schema is updated, but it is possible (but not recommended) to use the same command to update configuration files too - see the documentation for details.

Manual updates of the database schema, in production (Azure)

In Optimizely DXP (Digital Experience Platform) manual schema updates are not an option. If you are hosting your site in Azure, automatic schema updates is the recommended approach - but manual updates are possible.

1. Open Package Manager Console in Visual Studio.
2. Make sure "Default project" points to the website.
3. Type Export-EPiUpdates and press Enter.
4. Copy the exported files to the production web app using the KUDU interface. This interface will give a file explorer where you can drag and drop the files to have them uploaded. To access the KUDU interface, just add the scm segment to the URL of your web app as shown below. Open the file explorer by selecting CMD from the Debug console menu item.
5. Open the CMD Console interface, open the folder containing update.bat and run the command with the relative URL of your site as a parameter. Example: update.bat ..\wwwroot

What changes are made to the database schema?

The actual scripts that are run on the database, can be found inside the NuGet package for Episerver.CMS.Core. Just open it like a zip file and browse to the folder /tools/epiupdates/sql and you will find a .sql file for every version that has a corresponding update to the database schema.

Can updates to the database schema be rolled back?

If you need to roll back your code to what it looked like before updating Optimizely/Episerver, there is no automatic way of rolling back the changes to the database schema. You have two options.

• Take a backup before updating the database schema. If you need to roll back, take a note of any changes to the content (use change log admin tool) and roll back to your backup.
• Manually evaluating the update scripts. Sometimes there is no need to roll back the actual schema update. It might be a change to an index, that will work equally fine. Then all you need to do is update the stored procedure sp_DatabaseVersion to return the previous database schema version number. If you need to roll back the actual change, read the update script carefully and revert what it does.

Key takeaway

Remember to back up your database before updating the database schema.