Efcore: Question: How to run EF migrations on production server (azure website)?

[f0rt]

Hi team,

I'm wondering what is the recommended way to run EF migrations on a production DB. I didn't find anything in the reference except this: http://ef.readthedocs.org/en/latest/platforms/aspnetcore/new-db.html. I've posted the question on stack http://stackoverflow.com/questions/36680268/what-is-the-best-way-to-run-ef-migration-on-production-server

Thank you in advance,
Nedko

[gdoron]

It has very little to do with EF.
You're basically asking how to run DDL scripts on production database.

[f0rt]

I'm asking about something like this that works with the latest EF: http://www.codeproject.com/Tips/814618/Use-of-Database-SetInitializer-method-in-Code-Firs

[gdoron]

I would never use it on production, but you can use something like this:

using (var serviceScope = app.ApplicationServices.GetRequiredService<IServiceScopeFactory>()
    .CreateScope())
{
    serviceScope.ServiceProvider.GetService<RegistryDbContext>()
         .Database.Migrate();
}

But don't.

You want to backup data/scheme on production DB, manually read and apply one by one the migration scripts.

But that's EF tool to auto migrate.

But don't.

[f0rt]

I promise I won't use it on production. :) I'm already using sql script generation for the migrations. I can see the benefits. Thank you.

[rowanmiller]

In many cases you will want a script, potentially to hand off to a DBA etc. But there are also scenarios where letting EF run migrations can be acceptable - in the context of ASP.NET Core, the code @gdoron shared is the right way to do this. The built-in publish functionality in Visual Studio is also going to enable the ability to run migrations as a deployment step - rather than at application startup (that should ship in RC2). And then of course you can also use the migrations commands to get a SQL script if you have a more formalized workflow.

[f0rt]

Thank you for the reply. I'm glad to read that it will be integrated in the publish wizard.

[gdoron]

Just don't forget your promise @f0rt ... 👮
You don't want to be famous like this fellow

😋

[Costo]

Since this was not mentioned in the discussion:

You can generate a SQL script that you can then apply to the production database or any environment by running this command in a command prompt:

dotnet ef migrations script --idempotent --output "script.sql" --context MyDbContext

You need to specify the --context parameter only if you have multiple DbContexts in your application.

Run dotnet ef migrations script --help for usage information

[billmoniz]

Thanks @Costo , that was very helpful.

[VictorioBerra]

@rowanmiller

The built-in publish functionality in Visual Studio is also going to enable the ability to run migrations as a deployment step - rather than at application startup

Did this every become a thing? What about migrating as a part of dotnet publish?

[bricelam]

@VictorioBerra AFAIK, it is an option from VS if you are using MSDeploy/IIS.

dotnet publish just copies the assemblies to a directory. You could run dotnet ef database update separately as part of your deployment process.

[VictorioBerra]

@bricelam is this advised to do on every deployment? Or maybe just on dev and then manually on stage/prod? What are other people doing?

[bricelam]

It's a no-op if there are no new migrations, so yes, it's safe. Another option being used is to generate the SQL script and apply it using your deployment tool of choice. (This is what Web Publish does behind the scenes) You can generate it using:

• dotnet ef migrations script --idempotent (if you don't know what version the database is at)
• or dotnet ef migrations script <from-migration> (to get the changes after from-migration)

[d5omino]

well here's a kicker.... so i'm using environment variables to connect to production vs development databases... in development i'm using the user secrets functionality in .net core and in production i'm using an environment variables on an azure web app... my startup class simply checks for environment name then engages the appropriate db-context(s) so in this scenario there is nothing for VS to do as the code base has no clue about connection strings until run-time ...during design time all is peachy since the connection string is Local therefore migrations run without problem(on dev)... but once the code base goes into production it finds a virgin database and proceeds to go kaboom... suggestions besides using a sql script? i have faith that MS saw this coming and given the desired use case of everything MS meaning visual studio to azure webapp using azure sql dbs etc... they would have thought of a method to automate the deployment and get these migrations run ... i'm thinking perhaps at design time somehow but then that defeats the whole keeping secrets isolated from development and production... no one in production wants anyone in development having the keys to the castle. and we don't want secrets for development db's being committed to the code base... so what do we do! LOL

[VictorioBerra]

@d5omino I don't understand the problem. Call .Migrate() on all your contexts every single time. Its a noop if you are already on the latest migration.

so in this scenario there is nothing for VS to do as the code base has no clue about connection strings until run-time

Why is this the case?

If you always have a connection string available to your app, then there should never be an issue with migrating.

[Bogatinovski]

@d5omino I don't understand the problem. Call .Migrate() on all your contexts every single time. Its a noop if you are already on the latest migration.

@VictorioBerra Migrate() still needs to go to the database and check if there are any new migrations that needs to be applied. Am I right ? If this is the case imagine the new application is deployed to 10 instances and each of this instances invokes the Migrate() call so we get 10 parallel database accesses. And imagine having 100 instances to which the new version is deployed.
Now imagine the other case where there actually are new migrations and we have 10 instances that try to migrate the database in parallel. I guess there is some kind of a lock mechanism that will allow only one of the Migrate() calls to execute but this would also impact the performance of the application.
My opinion is that migrations should only be applied as part of the CI/CD deployment process only once in a dedicated stage right after successful build.
So I am looking for a best way how to do implement this "DB Migrations" step as part of the CI/CD pipeline.
One solution is to manually run the dotnet ef database update command as part of this step.

[voroninp]

@rowanmiller , @bricelam
Is there any reason why I cannot run for published project?

dotnet ef database update --startup-project startupAssembly --project migrationsAssembly
or
dotnet ef migrations script --startup-project startupAssembly --project migrationsAssembly - migrations.sql

Would be nice to have this option with this one

[bricelam]

@voroninp, see Deploy Entity Framework Core 2.1 Migrations from a DLL by @benday

[Perogy]

@voroninp, see Deploy Entity Framework Core 2.1 Migrations from a DLL by @benday

Absolutely insane that this is what needs to be done to run migrations on a dll.

[erictor]

I had no luck running any of the dotnet ef migrations console commands posted above, for a very typical reason -- I was trying to utilize help that had been posted two years prior to my trying to use it. Life changes over the course of two years. https://stackoverflow.com/questions/57066856/dotnet-ef-not-found-in-net-core-3 Now I can run the commands.