Automatic schema updates

3 min

Schema updates can be done manually or automatically, as described here.

The Update-EPiDatabase cmdlet manually updates the database scheme as described in Updating configuration and database schemas. You can configure the site to apply updates to the SQL schema during site initialization automatically.

When the site starts, it compares the assembly and database versions. It applies the SQL schema updates if the database version is lower than the assembly version and automatic updates are enabled. (The SQL files are embedded resources in the assembly.) To enable automatic schema updates, add attribute updateDatabaseSchema="true" to the episerver.framework element in the configuration as: <episerver.framework updateDatabaseSchema="true">

If you enable automatic schema updates, you can interact with the schema upgrade process by registering an implementation of ISchemaValidator in the IOC container. The interface has two methods:

IsDatabaseUpdateAllowed – The schema calls this method first, where an implementation can check if an automatic update should be allowed.

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 a backup of the database.

📘 Note Validators are also called if automatic schema creation as described in Install database schemas was enabled and a new schema is about to be deployed.

The following example shows a basic implementation that allows updates but takes a database backup:

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

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

  public void BeforeUpdating(ConnectionStringOptions connectionStringSettings) {
    var sqlConStrBuilder = new SqlConnectionStringBuilder(connectionStringSettings.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 only automatic updates when running on LocalDB (a typical development environment):

public class LocalDBDatabaseValidator: ISchemaValidator {
  public void BeforeUpdating(ConnectionStringOptions connectionStringSettings) {}

  public bool IsDatabaseUpdateAllowed(ConnectionStringOptions connectionStringSettings) {
    var sqlConStrBuilder = new SqlConnectionStringBuilder(connectionStringSettings.ConnectionString);
    return sqlConStrBuilder.DataSource.StartsWith("(LocalDB)",
      StringComparison.OrdinalIgnoreCase);
  }
}

You can register several validators. For example, if both validators in the previous examples are registered, updates are allowed only when running on LocalDB, and it makes a backup before the update is applied.

Changes in 10.4.0 and later versions

The IDatabaseSchemaValidator was changed to ISchemaValidator in 10.4.0 and later versions.

The ConnectionStringSettings was changed to ConnectionStringOptions in 10.4.0 and later versions.