Database migrations and Code deployment

Zero-downtime releases using Azure App Service slots and a single database shared by Staging and Production are possible - but you need to make sure that all database changes are backwards compatible, such that the current and new versions of the web app can run simultaneously in the Staging and production slots.

Some rules that ensure this works:

• Any new database columns should be nullable or have default values
• Renaming columns is not permitted
• Dropping columns is not permitted

When you do need to make destructive changes, such as renaming or dropping columns, you need 2 releases to do this:

1. The new version of the web app should be released, which removes the dependency on the renamed/dropped columns
2. An additional release is made that performs the destructive changes

While this sounds a bit complicated, in practice you likely won't be making destructive changes very often.

Since you mentioned SQL server: according to this former DBA.SE post, schema changes can (and should) be put into transactions. This gives you the ability to design your migrations just like any other form of concurrent writes to your DB - you start a transaction, and when it fails, you roll it back. That prevents at least some of the worst database corruption scenarios (though transactions alone will not prevent from data loss when there are destructive migration steps like deleting a column or table).

So far, I am sure you will also need some migrations table where already applied migrations are registered, so an application process can check if a specific migration was already applied or not. Then utilize "SELECT FOR UPDATE" to implement your migrations like this (pseudo code):

• Start a transaction
• SELECT FROM Migrations FOR UPDATE WHERE MigrationLabel='MyMigration42'
• if the former statement returns a value, end the transaction
• apply the migration (roll back if it fails, log the failure and end the transaction)
• INSERT 'MyMigration42' INTO Migrations(MigrationLabel)
• end the transaction

That builds the locking mechanism directly into the "was the migration already applied" test.

Note this design will - in theory - allow to let your migration steps be unaware of which application actually applies it - it can be possible that step 1 is applied by app1, step 2 by app2, step 3 by app 3, step 4 by app1 again, and so on. However, it is also a good idea not to apply migrations as long as other app instances are in usage. Parallel deployment, as mentioned in your question, may already care for this constraint.