Provide guidance for deploying migrations

[ajcvickers]

This would primarily be:

• Best practices (For example, don't run migrations on app startup, generate scripts, etc.)
• How to generate scripts
  • Possibly also how to use ef.exe
• How to deploy scripts to common places--e.g. Azure
  • Possibly pros and cons of doing this a maintenance window as opposed to against a running app, and strategies for doing it against a running app if this is needed.

[johnnyreilly]

Really pleased to see this has been raised - looking forward to hearing more!

[divega]

Copying the address of @johnnyreilly’s blog post here:

https://blog.johnnyreilly.com/2018/06/vsts-and-ef-core-migrations.html

[codekoenig]

Looking forward to "official" guidelines.

Until then, here's what I'm doing which i find to be a quite nice and straightforward solution without creating an additional console app:

• Adding DotNetCliToolReference to NuGetPackage Microsoft.EntityFrameworkCore.Tools.DotNet
• In my Build, I add an additional .NET Core task where I use the custom command ef with the following arguments: migrations script -p $(Build.SourcesDirectory)\Project\Project.csproj -o $(build.artifactstagingdirectory)\migrations\migrations.sql -i
• This generates an idempotent migration script containing all migrations so it can be executed at any migration state of the database and it is published to my build's artifacts in it's own migrations direcotry
• I execute this script file now in my Release with a SQL Server Database Deploy task. I set the Sql File property as follows based on the path i provided in the Build task: $(System.DefaultWorkingDirectory)\**\Migrations\migrations.sql

That's it - it's straightforward and works nicely, fits perfectly in a Continous Deployment scenario and allowing us to use a deployment user with all necessary rights to change database schemas - while during app runtime, I can run with a different user that has only datareader/datawriter rights.

There is just one drawback: The generated script does not create the database if it does not exist on the first deployment. So the DB has to be there and prepared - or, and that is what I have done, I added an additional SQL Server Database Deploy task with inline SQL to create the database if it does not exist.

I think it's an ok thing to do, but I'd love to have the possibility to optionally add create database SQL with dotnet ef script tooling.

[ajcvickers]

See also dotnet/efcore#13339

[pekspro]

I’m also looking forward to some good documentation about how to create database migrations in Azure Pipelines with ASP.NET Core. It was a lot of trial and error before I got it right.

That said, I wrote a little task to make this easier :-) I created Entity Framework Core Migrations Script Generator:

https://marketplace.visualstudio.com/items?itemName=pekspro.pekspro-efcore-migration-script-generator

It takes care of the part of generating script, the hardest part according to me.