SQL migration in Azure Pipelines with transactions

Question

We have a build pipeline running that creates a sql script using dotnet ef migrations script -Idempotent and this is executed in our release pipeline using the task "Azure SQL Database deployment" using Invoke-Sqlcmd -ServerInstance "XXXXXXXXX" -Database "XXXXXX" -Username "XXXXXXX" -Password ****** -Inputfile "XXXXXXXXXX" -ConnectionTimeout 1800

However we would like to run the migration in a transaction, because now, if the migration fail, we end up in a state were the migration is half-completed.

Any ideas?

Does this answer your question? [Entity Framework rollback and remove bad migration](https://stackoverflow.com/questions/22680446/entity-framework-rollback-and-remove-bad-migration) — Troopers, Feb 17 '20 at 15:51
No Im afraid not. That questions covers how to rollback a migration. Im not looking for how to rollback manually. Im looking for how to ensure that the migration either does everything or nothing to the db using the Invoke-Sqlcmd from a script generated. But, really I would settle for any workflow that will migrate my db in my pipelines with automatic rollback on failure. — Erik83, Feb 17 '20 at 22:14
Are you looking for ways to use ef migrations to generate sql script wrapped with a transaction? If so, please check [this thread](https://stackoverflow.com/questions/32014118/can-entity-framework-6-migrations-include-a-transaction-around-scripts) which should be the solution. — Yang Shen - MSFT, Feb 18 '20 at 07:12

Erik83 · Accepted Answer · 2020-02-19T13:45:44.510

There is something called SqlServerMigrationsSqlGenerator that helps you to customize the migration script generation. Thank you @yang-shen-msft!

This is the code required in EF core:

    public class SqlMigrationScriptGenerator : SqlServerMigrationsSqlGenerator
{
    public SqlMigrationScriptGenerator(MigrationsSqlGeneratorDependencies dependencies, IMigrationsAnnotationProvider migrationsAnnotations) : base(dependencies, migrationsAnnotations)
    {
    }

    public override IReadOnlyList<MigrationCommand> Generate(IReadOnlyList<MigrationOperation> operations, IModel model)
    {
        var beginTransaction = new SqlOperation { Sql = "BEGIN TRANSACTION" };
        var overriddenOperations = operations.ToList();
        overriddenOperations.Insert(0, beginTransaction);
        overriddenOperations.Add(new SqlOperation { Sql = "COMMIT TRANSACTION" });
        return base.Generate(overriddenOperations, model);
    }
}

score 0 · Answer 2 · answered Feb 19 '20 at 01:21

If you are looking for ways to use EF migrations to generate sql scripte wrapped with transaction, please check this thread which create a MigrationScriptBuilder class inherited from the SqlServerMigrationSqlGenerator.

Below is the code from above thread, you can see the "BEGIN TRAN" and "COMMIT TRAN" around the migrationstatement.

public class MigrationScriptBuilder : SqlServerMigrationSqlGenerator
{
#if !DEBUG
    protected override void Generate(SqlOperation sqlOperation)
    {
        Statement("GO");

        base.Generate(sqlOperation);

        Statement("GO");
    }

    public override IEnumerable<MigrationStatement> Generate(IEnumerable<MigrationOperation> migrationOperations, string providerManifestToken)
    {
        yield return new MigrationStatement {Sql = "BEGIN TRAN"};

        foreach (var migrationStatement in base.Generate(migrationOperations, providerManifestToken))
        {
            yield return migrationStatement;
        }

        yield return new MigrationStatement {Sql = "COMMIT TRAN"};
    }
#endif
}

SQL migration in Azure Pipelines with transactions

2 Answers2