1

I want to know a tool to use for SQL Server Database migrations and why as of 2017, since I worry about this topic, so I can automate this process as much as possible.

What do you think about Entity Framework Code First Migrations, assuming I'm using .Net?

1

There are two main products that I'm aware of in the relatively new field of database migration.

Flyway

Flyway is basically a tool to organize, track, and automatically apply a collection of SQL scripts you write.

Can be used from within Java app, and from the command-line for non-Java environments.

Free of cost. Open source.

Liquibase

Liquibase is a competitor to Flyway, serving the same purpose.

6
  • Flyway is good, but it doesn't generate the migration scripts, while Entity Framework Code First Migrations do. – Miguel Domingos Apr 22 '17 at 14:45
  • 1
    @MiguelDomingos There are no diff tools that I know of that can do a perfect 100% job of writing scripts to recreate the entirety of a complex database. The only perfect approach is to write all of your database creation and modification in SQL scripts that you then collect and apply through a tool like Flyway exclusively. – Basil Bourque Apr 22 '17 at 15:23
  • .@BasilBourque Entity Framework can scaffold all migrations with accuracy because there is a model made of classes in C# and that model is keept in sync with the database. Since Flyway doesn't know anything about the application classes, with Flyway you have to write your own migration scripts from the scratch. – Miguel Domingos Apr 22 '17 at 15:51
  • @MiguelDomingos There is more to a database than the entity tables. But good luck to you. – Basil Bourque Apr 22 '17 at 16:28
  • .@BasilBourque The entity tables are an important part of a database and at least it's better to generate that than nothing. – Miguel Domingos Apr 22 '17 at 17:32
1

In our project (Azure hosted dotnet core application) we use EF Core to manage DB migrations.
The setup looks like this:

  1. There's a separate C# project with entities (EF Code First models).
  2. There's a separate C# project with Repositories (EF Core related code), also containing migrations (we don't have tons of these). It's called Infrastructure.AzureSql
  3. In the Infrastructure.AzureSql project we have a README.md file with hints:
To add migration:

1. Open Visual Studio Package Manager Console
2. Add-Migration <migration-name> -Verbose -Project src\Infrastructure.AzureSql -StartupProject src\SampleProject

To generate sql script:
 1. Open Visual Studio Package Manager Console
 2. Script-Migration -Verbose -Idempotent -Output "deploy\sql\SampleDB.sql" -Project src\Infrastructure.AzureSql -StartupProject src\SampleProject

And the process of making the change to the DB schema looks the following way:

  1. Perform the change in the Code First model, e.g. add new fields
  2. Tweak the data context by adding the necessary setup to the DataContext class:
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Product>(builder =>
    {
        builder.ToTable("Product");
        // Configuring new property
        builder.builder.Property(product => product.Сategory).HasColumnType("nvarchar(32)");
        builder.HasIndex(product => product.Category);
    }
}
  1. Add new migration using Add-Migration command:
    The command will create a new migration file with the name containing migration name: 20210323165355_Product-Add-Category.cs. It will automatically detect the changes in the model and include the corresponding statements in the file, e.g. migrationBuilder.AddColumn<string>(name: "Category", table: "Product", type: "nvarchar(32)", nullable: true);
Add-Migration Product-Add-Category -Verbose -Project src\Infrastructure.AzureSql -StartupProject src\SampleProject
  1. When needed custom steps can be added manually to the migration file 20210323165355_Product-Add-Category.cs either using predefined MigrationBuilder fluent API or using custom SQL using migrationBuilder.Sql(fillInProductCategoryScript); - just add the code side-by-side with automatically generated one.
    Warning: EF Core keeps the current DB schema described via fluent API in the file called <DbContextName>ModelSnapshot.cs. If any manual change to the schema is done via SQL, it needs to be reflected in the snapshot (and also in the file 20210323165355_Product-Add-Category.Designer.cs which represents the schema snapshot after applying migration). Normally we rely on automatically generated schema changes and only add custom data transformation scripts via SQL.
  2. When migration logic is complete the script needs to be re-generated. Again, we don't have too many changes, so we are using the same file for all migrations.
Script-Migration -Verbose -Idempotent -Output "deploy\sql\SampleDB.sql" -Project src\Infrastructure.AzureSql -StartupProject src\SampleProject

The command re-generates the migration script based on migrations surrounding the changes with conditional statements:

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20210323165355_Product-Add-Category')
BEGIN
    ALTER TABLE [Product] ADD [Category] nvarchar(32) NULL;
END;

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20210323165355_Product-Add-Category')
BEGIN
    CREATE INDEX [IX_Product_Category] ON [Product] ([Category]);
END;

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20210323165355_Product-Add-Category')
BEGIN
    INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
    VALUES (N'20210323165355_Product-Add-Category', N'3.1.3');
END;

GO
  1. All the changes are committed (including migration sql script), so generated SQL can be checked during code review.
  2. The CI/CD pipeline contains the commands to apply migration script. It's idempotent, so it's safe to apply it on each deploy. If migration script contains very heavy statements and we want to have more control over the process (and its time), we can run the same script manually. As it's idempotent, the next deploy will skip the parts that are completed earlier.
0

I would check out dbatools.io

They have powershell scripts that can do exactly what you need. And it's free.

1
  • Any update / feedback on my answer? – Matt McDonald Apr 29 '17 at 19:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.