Update schemas automatically

Describes how to work with database schema updates, when upgrading Optimizely websites.

Update database schemas manually or configure automatic updates during site initialization.

Use the EPiServer.Net.Cli command to update the database schema manually (see Install a sample site). Or configure the site to apply SQL schema updates automatically during initialization.

At startup, CMS compares the assembly version to the database version. If the database version is lower and automatic updates are enabled, CMS applies the SQL schema updates. (SQL update scripts are embedded in the assembly as resources.)

UpdateDatabaseSchema defaults to true in DataAccessOptions, so automatic schema updates are enabled by default. To disable automatic updates, set UpdateDatabaseSchema to false in appsettings.json:

{
  "EPiServer": {
    "Cms": {
      "DataAccess": {
        "UpdateDatabaseSchema": false
      }
    }
  }
}

Or configure it in code:

public class Startup {
  public void ConfigureServices(IServiceCollection services) {
    services.Configure<DataAccessOptions>(o => {
      o.UpdateDatabaseSchema = false;
    });
  }
}

To interact with the schema upgrade process when automatic updates are enabled, register an ISchemaValidator implementation in the IOC container. The interface has two methods:

IsDatabaseUpdateAllowed – CMS calls this method first to check whether to allow the automatic update.
BeforeUpdating – If validators allow the update, CMS calls this method before applying the update. Use it to perform actions such as a database backup.

📘
Note
CMS also calls validators when automatic schema creation (see Install database schema) is enabled and CMS is about to deploy a new schema.

The following example allows updates and takes a database backup before applying them:

public class BackupDatabaseValidator: ISchemaValidator {
  private readonly string _backupFolder;
  public BackupDatabaseValidator(string backupFolder) {
    _backupFolder = backupFolder;
  }

  public bool IsDatabaseUpdateAllowed(ConnectionStringOptions connectionStringOptions) {
    return true;
  }

  public void BeforeUpdating(ConnectionStringOptions connectionStringOptions) {
    var sqlConStrBuilder = new SqlConnectionStringBuilder(connectionStringOptions.ConnectionString);
    var backupFileName = String.Format(CultureInfo.InvariantCulture, "{0}-{1}.bak", sqlConStrBuilder.InitialCatalog, DateTime.Now.ToString("yyyy-MM-dd"));
    var backupFilePath = Path.Combine(_backupFolder, backupFileName);

    using(var connection = new SqlConnection(sqlConStrBuilder.ConnectionString)) {
      var query = String.Format("BACKUP DATABASE {0} TO DISK='{1}'",
        sqlConStrBuilder.InitialCatalog, backupFilePath);

      using(var command = new SqlCommand(query, connection)) {
        connection.Open();
        command.ExecuteNonQuery();
      }
    }
  }
}

The following example allows automatic updates only when running on LocalDB (a typical development environment):

public class LocalDBDatabaseValidator: ISchemaValidator {
  public void BeforeUpdating(ConnectionStringOptions connectionStringOptions) {}
  public bool IsDatabaseUpdateAllowed(ConnectionStringOptions connectionStringOptions) {
    var sqlConStrBuilder = new SqlConnectionStringBuilder(connectionStringOptions.ConnectionString);
    return sqlConStrBuilder.DataSource.StartsWith("(LocalDB)", StringComparison.OrdinalIgnoreCase);
  }
}

Register multiple validators to combine their logic. For example, if you register both validators from the previous examples, CMS allows updates only when running on LocalDB and backs up the database before applying the update.

Updated 12 days ago

Did this page help you?