Pre/Post migrations from EF core migrations to ensure zero downtime

Question

We have a production setup of our solution with 2 webservers, these two webservers are loadbalanced, and connects to the same database.

We utilize Octopus deploy, and perform a rolling deploy where we disable the webserver under update in the loadbalancer.

We use entity framework code first migrations, and upon deploy we run dotnet ef migrations script, to generate a SQL script that we can execute against the database. This is where our struggles begin.

It happens that the migrations that have been made has breaking changes, that causes the webserver that is still online, to fail due to database mismatch.

So we somehow need to have a pre and post migration

• pre migration run before deployment, that puts the database in to a state where the old version of the application works, together with the new version.
• post migration run after deployment, that cleans up the database.

I have been unable to find a tool that can create these pre-migrations for me, based on the sql script that i have, and i am starting to think that this is a dream, is it possible? and if not, what do people do in their deployment pipeline?

I have looked at Liquibase, DbUp, Roundhouse, but none of them supply the functionality that i wish for.

Cysto

Although this question is quite old, it still covers a rather relevant topic. So my question would be: if you don't know how long a rollout really takes (5m or 1h, depending on load, etc.), do you really need a post migration? Or would it be suitable to apply the post migration as part of the next update (i.e. holding back the PR which contains it)? – Stringpiece

Answer 1

You could try utilising DbUp, we do it with pretty good results, although it does mean that we are manually creating pre & post scripts that run. Each .sql file has either Pre_ or Post_ as the starting name and they are bundled into a single folder together for that change.

We then have a separate project, that builds into an .exe that we can pass either pre or post to as an argument. So our deployments go:

Run pre scripts Run deployments Run post scripts

This also allows to have a significantly longer timeout on commands that are running because we know we won't be causing any disruptions (i.e. sometimes adding a new index could take several minutes)

static void PerformUpgrade(string dbConnection, string prePostDeployOption)
        {
            // DbUp is a .NET library that helps you to deploy changes to SQL Server databases. It tracks which SQL scripts have been run already,
            // and runs the change scripts that are needed to get your database up to date.

            // If you want your application to create the database for you, add:
            // EnsureDatabase.For.SqlDatabase(dbConnection);

            // Upgrade database if required
            var upgrader = DeployChanges.To.SqlDatabase(dbConnection)

                // Retrieves upgrade scripts or IScript code upgrade scripts embedded in an assembly:
                // - .sql files where 'Build Action' is set to 'Embedded resource'
                // - Code inheriting from IScript which returns SQL string to run
                .WithScriptsAndCodeEmbeddedInAssembly(
                    Assembly.GetExecutingAssembly(),
                    (string s) =>
                        // Only run SQL and code scripts beginning with "Pre_" or "Post_", depending on selection made
                        s.Contains($".{prePostDeployOption}_", System.StringComparison.CurrentCultureIgnoreCase)
                )

                // Allow for a longer timeout on long running scripts
                .WithExecutionTimeout(TimeSpan.FromMinutes(10))

                // By default, DbUp adds a table to your SQL Server database called SchemaVersions, which tracks the scripts that have already been executed.
                // Before running, DbUp checks this table to work out which scripts should be skipped. After running, it inserts journal records to record
                // the scripts that have been run.
                .JournalToSqlTable("dbo", "_DbMigrations")

                // By default, the output of the scripts run by DbUp do not show up in the logs. To also display the script output (e.g. text displayed by PRINT statements in SQL Server), use this method.

                .LogScriptOutput()

                .Build();

            // Performs upgrade if necessary
            var result = upgrader.PerformUpgrade();

            if (result.Successful)
            {
                // Always outputs task summary to Octopus Deploy even if no scripts were run (i.e. "Ran 0 scripts")
                result.WriteExecutedScriptsToOctopusTaskSummary();
            }
        }

Leiva