How to update the database structure on a production server?

Question

Database administration

How to update the database structure on a production server? Now all creations/deletions/changes of tables, columns, etc. performed manually using SSMS. How is versioned database migration organized?

Answer 1

Yes, migrations, that's right. Look towards Flyway or Liquibase . I didn’t use the second one, I just read it, so I’ll briefly talk about Flyway. It is free in basic functionality, and it is quite enough.
Migrations are SQL files named according to certain rules, with a version and a short description. Personally, I use the current date and time instead of the abstract version. For example, V20210601_1200__init.sql or V20210602_1015__alter_products.sql. It's boring to create files manually, I wrote a batch file. Using a datetime allows multiple developers to create independent migrations in parallel and not have to pay too much attention to their order. Everything is in the version control repository along with the rest of the project.
When updating the code branch (on the bench or on the prod), we run flyway migrate. It checks the contents of the subdirectory with migrations against its service table. New migrations are applied in turn. Unfortunately, there is no way to roll back a migration like in Yii or Rails frameworks.
Briefly, the work looks like this:

• We create a subdirectory in the repository, say, db with the flyway.conf configuration file (more precisely, of course, flyway.conf.example goes to the repository, and a specific config is configured for each database stand locally)
  
• We configure the config for the desired database instance and the subdirectory where the migration files will be located, for example db/migrations
  
• We describe the current state in the very first migration, I usually call it init. It should provide the structure and directories with an empty scheme to roll up to the current state. Sometimes it is better to split migrations into two: the structure is separate, the inserts are separate.
  
• We execute flyway migrate on the test database and ensure that the migration rolls in correctly. You will have to roll back manually by deleting the record from the service table.
  
• If everything is fine, commit the results, run flyway migrate on the prod and enjoy :)
  

For good, of course, you don’t need to call the migration mechanism by hand, this should be done by CI / CD (for example, Jenkins) upon updating the corresponding repository branch. But for now, this is enough to test the process.

Answer 2

You can see how it's done in EntityFramework.
https://docs.microsoft.com/en-us/ef/core/managing-...