Table of Contents
Migrate via app
least privileged subject
Migrate via build server
Command line tools
Idempotent SQL script
Bundle?Edit
command line application
Certification
Summarize
Managing stateful data is often one of the trickier parts of a DevOps strategy. Entity Framework’s migration capabilities can greatly help with small, independently executable, PR-friendly increments. In theory, it could even grant the ability to revert a migration if the deployment goes poorly. If used correctly, this technology can be a huge help.
However, in practice, there are many different ways to automate these increments. Each method has advantages and disadvantages, and what may work for one project may not work for another.
In this post, I’ll show you six ways to run EF database migrations, explain in what situations each method is most helpful, and then demonstrate how to set up Active Directory (AD) authentication correctly when running migrations from a build server. Set the connection string.
Migrate via app
The question of when to run migrations is… interesting. The problem is that when the application is first started using the DbContext.Database.MigrateAsync() command, it is very easy to run migrations.
Running migrations on startup is convenient and saves time because it piggybacks on existing database connections and firewall rules. But it also has some disadvantages:
- Slow down application startup speed
- Difficult to restore
- Timeout issue with long-running migrations
- Violates the principle of least privilege
This last point deserves explanation. It cites the following security best practices:
Least privileged principal
To minimize the damage caused by a security incident, systems should be granted the minimum required level of access.
In other words, don’t grant any unnecessary permissions to the system.
For example, apps typically do not need to delete database tables as part of daily operations, so they should not be granted this permission. However, this is exactly the type of permissions the app requires when running database migrations. Therefore, by having the application run the database migration, we inadvertently give the attacker the ability to cause more chaos than is necessary to access the database through the application.
More specifically: If the application is connecting to SQL Server, the account running the application can be granted db_datareader and db_datawriter, but not db_ddladmin and definitely not db_owner. Doing so will generate a flag in the Security Center audit.
Migrate via build server
If you’re still developing, intentionally deferring security risks, or are just generally comfortable with the risks, passing application migration may be enough. However, once you’re ready, another approach is to have the build server run EF migrations. There are several ways to approach this problem, each with advantages and disadvantages.
Command Line Tool
As a developer, you may already be familiar with the dotnet ef database update command. This option is inconvenient in the DevOps world because it requires source code. Source code is a pain for a variety of reasons, not the least of which is that you need to get the exact code version of the deployment you’re trying to run. The other methods below are usually preferable.
SQLScript
If you let the build server run the command dotnet ef migrations script [oldmigration] [newmigration], it will generate a file that you can save as a project in your build pipeline and then execute it for each environment. Unlike some other options, this method generates assets that can be viewed by the DBA. However, determining which values to use for old and new migrations will be tricky. Even worse, if each environment is on a different version, as often happens when not every PR is deployed to production, then it may not be possible to generate a single project that works for all environments, since these values will be different for each The environments are all different. Therefore, I generally don’t recommend this approach.
ImpotentSQLScript
Fortunately, if you pass the –idempotent parameter to the dotnet ef migration script, it will generate a script that will only run the migrations that need to be run. The resulting file looks like a bunch of if statements, like this:
BEGIN
CREATE INDEX [IX_AbpAuditLogActions_AuditLogId] _
ON [AbpAuditLogActions] ([AuditLogId]);
END;
GO
While this option does not grant the option to revert a bad deployment, it is ideal as a published build artifact that can be viewed by the DBA and works when each environment is on a different version. However, this method is not transactional, so a failed migration may leave the database in an inconsistent state.
Bundle
The bundle is great. They solve the problem of applying transactions to migrations. Just ask the build server to run dotnet ef migration bundle –self-contained -r [linux-x64|win-x64] to get a single file binary (called efbundle.exe by default >), you can publish it as a project, run migrations in a transaction, and only run the migrations that need to be applied. You can even specify specific migrations so you can revert them. The generated file looks like this: E
Entity Framework Core Migrations Bundle 7.0.1
Usage: efbundle [arguments] [options] [[--] <arg>...]]
Arguments:
<MIGRATION> The target migration. If '0', all migrations will be reverted.
Defaults to the last migration.
Options:
--connection <CONNECTION> The connection string to the database.
Defaults to the one specified in AddDbContext or OnConfiguring.
--version Show version information
-h|--help Show help information
-v|--verbose Show verbose output.
--no-color Don't colorize output.
--prefix-output Prefix output with level.</span></code>
This option is almost my favorite. The only thing it’s missing is the ability to run custom code outside of migrations.
Command Line Application
Once upon a time, one of my projects stored encrypted data in a field in a database. After we went into production (of course) we realized that the encryption algorithm was too strong and was causing performance issues. We need to reduce the strength of the encryption, but this means we need to perform complex data migration involving methods written in C#.
We have to run a query to read each row, decrypt it using the old algorithm in C#, re-encrypt it using the new algorithm in C#, and then update it back into the database. Should be simple enough for EF migration, right?
Surprisingly, it turns out that EF migrations can’t do such a job. They are designed to run insert or update SQL statements or create or drop DDL statements, but not retrieve data. Check this yourself: see if you can find a single way to retrieve the data on the MigrationBuilder.
So, since the algorithm is in a C# method and it is not accessible since EF migrations, we have to run the code outside of EF migrations.
Fortunately, since we are using ASP.NET Boilerplate (the predecessor of the ABP framework), it includes a command line application that runs our migrations. This provides the flexibility to run the code before or after the migration, thus solving our problem The problem.
Command line applications (or the main application with command line options) running DbContext.Database.MigrateAsync() from the build server can run within a transaction and use –self-contained -r [linux-x64|win-x64] Compiled using almost a single file (must contain Microsoft.Data.SqlClient.SNI.dll). The disadvantage is that they do not allow reverting migrations. However, they are my personal favorite because they offer the most flexibility when facing a tough move. Additionally, they are particularly effective in per-tenant database multi-tenant scenarios.
If you’re interested in the details of this approach, check out the DbMigrator sample project I created that compiles and publishes a command-line app in one stage of a multi-stage build pipeline, and then executes it in another stage.
Certification
It’s time to acknowledge the elephant in the room. How can I get a connection to the database from the build server without running migrations on app startup? Also, assuming you are using SQL Server on Azure and following the best practice of using AD only authentication, how do you authenticate with an Active Directory account in a headless environment?
Generally speaking, there are four steps. First, create an app registration. Second, add the application registration to the database using CREATE USER [{userName}] FROM EXTERNAL PROVIDER; and grant it permission to run DDL using EXEC sp_addrolemember ‘ddl_admin’, ‘{userName}’. Third, create a secret for app registration:
Finally, add a firewall rule to allow access to the database from the custom build agent, or if you are using a managed agent, add “Allow Azure services and resources to access this server” as shown below in Bicep:
resource firewallRule_AzureIps 'Microsoft.Sql/servers/firewallRules@2021-11-01' = {
name: 'AllowAzureIps'
parent: sql
properties: {
startIpAddress: '0.0.0.0'
endIpAddress: '0.0.0.0'
}
}
Finally, you should be able to use a connection string that looks like this: “Server=tcp:{sqlServerName}.database.windows.net,1433; “Database={dbName}; Encrypt=True; User Id={servicePrincipalAppId}; Password ={servicePrinicpalSecret}; Authentication=’Active Directory Service Principal’;”
That’s all. Enter the app registration app ID and generated secret to start the competition.
Summary
In this article, I discuss six ways to run migrations, followed by how to complete authentication. The following table will help summarize the pros and cons:
As I mentioned, my personal favorite is the last command line app because even though it doesn’t support autorestore, it will run within a transaction and has the flexibility to perform complex migrations outside of EF.
What methods have you tried? Did I miss something? Feel free to chime in in the comments.
This article was originally published at http://www.leerichardson.com/feeds/posts/default
https://www.codeproject.com/Articles/5355897/How-to-Deploy-Entity-Framework-Database-Migrations