Automatic schema updates

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

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

The EPiServer.Net.Cli cmd manually updates the database scheme described in Install a sample site. You can configure the site to apply updates to the SQL schema automatically during site initialization.

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 appsetting.json configuration as shown below:

{
  "EpiServer": {
    "Cms": {
      "DataAccess": {
        "UpdateDatabaseSchema": "true"
      }
    }
  }
}

Or you can do it by code like this:

public class Startup {
  public void ConfigureServices(IServiceCollection services) {
    services.Configure<DataAccessOptions>(o => {
      o.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 validators allow automatic schema update, the schema calls the method before it performs the actual update so that you can perform some action such as a database backup.

📘
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 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.

Updated 6 months ago

📘Note

📘
Note