Which tool to use for SQL Server Database migrations and why as of 2017?

Question

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?

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

Answer 2

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);
    }
}

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

4. 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.
5. 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

6. All the changes are committed (including migration sql script), so generated SQL can be checked during code review.
7. 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.

Answer 3

I would check out dbatools.io

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