Idempotent SQL Scriptsįortunately, if you pass the -idempotent parameter to the dotnet ef migrations script, then it will generate a script that will only run those migrations that need to run. Therefore, I wouldn't generally recommend this approach. Worse, if each environment is at a different version, as often happens when not every PR is deployed through to production, then it could be impossible to produce a single artifact that would work for all environments because those values would be different for every environment. However, figuring out which values to use for oldmigration and newmigration would be tricky. Unlike some other options, this approach produces an asset that can be reviewed by a DBA. If you have your build server run the command dotnet ef migrations script, it'll produce a single file that you can save as an artifact in a build pipeline and then execute for each environment. The other approaches below are usually preferable. Source code is a pain for a variety of reasons, not the least of which is you'd need to get the exact version of code for the deployment you're trying to run. This option is inconvenient in a DevOps world because it requires source code. Command Line ToolsĪs a developer, you're probably already familiar with the dotnet ef database update command. There are several ways to approach it, each with pros and cons. However, the alternative, when you're ready for it, is to have your build server run EF migrations. Migrating via app may be good enough if you you're still in dev, intentionally deferring security risks, or are just generally comfortable with the risk. Doing so will generate a flag in a security center audit. To put it more concretely: if an app is connecting to a SQL Server, then it's ok to grant the account the app is running under db_datareader and db_datawriter, but not db_ddladmin, and definitely not db_owner. Thus, by having an app run database migrations we inadvertently grant attackers the ability to cause more mayhem than is necessary should they gain access to the database through an app. However, that's exactly the type of permission an app needs if it is going to run database migrations. In other words, don't grant a system any permissions it doesn't need.įor instance, as part of their day-to-day operations apps don't generally need to delete database tables, therefore they should not be granted that permission. In order to minimize the damage caused by security incidents, a system should be granted the minimum level of access required. It references the following security best practice: The principal of least privilege Violates the principle of least privilege.Timeout problems for long-running migrations.Running migrations on startup is convenient and a time saver because it piggybacks off of an existing database connection and firewall rule. The thing is, it's so easy to run the migrations when an app starts for the first time with the () command. The question of when to run migrations is. In this post I'll show you six ways to run EF Database Migrations, explain in which circumstances each would be most helpful, and then show how to set up Active Directory (AD) Authentication and set your connection string correctly when connecting from a build server. Each approach has pros and cons, and what may work for one project may not for another. However, in practice, there are a lot of different ways to automate the execution of those deltas. If used correctly the technology can be a huge help. Theoretically, it can even grant the ability to revert migrations if a deployment goes poorly. The migrations feature of Entity Framework can help immensely with its small, independently executable, PR-friendly delta. Managing stateful data is typically one of the trickier parts of a DevOps strategy.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |