Automatic database schema updates

3 min

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

The EPiServer.Net.Cli cmd manually updates the database scheme as described in Install Optimizely. You can configure the site to automatically apply updates to the SQL schema 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 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.

Note Validators are also called if automatic schema creation as described in Install database schemas has been 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 a backup is made before the update is applied.