For database development and deployment there are primarily 2 delivery mechanisms — State based and Migration based approach.
State Based Approach
In State based database delivery, you will need to store a snapshot of the current state of the database in your source control. Every table, stored procedure, view, trigger will be saved as separate sql files which will be the real representation of the state of your database objects.
The most important thing to note is that in State based approach the system of truth is the Source Code itself. Application developers would love this because it is very similar to making changes to your class files, compile and build it. Another good thing is that you do not have to deal with ALTER scripts with State based approach
Migration Based Approach
In Migration Based Database Delivery, when you start working on a project you capture the current state of the database in a single migration script stored inside source control and that becomes the base/initial state.
As the project evolves over time, you will need to make changes to your database schema and reference data. On every instance, you will need to create a migration script with an incremental version number. If you want to determine the current state of your database or recreate the database in another environment, you will need to run all the migration scripts in the correct order.
The system of truth in Migration based approach is the Database itself. This approach provides more fine grain control on your scripts if you have to deal with data intensive operations and TSQL querying. You have the authority to review the scripts which gets executed against your destination database and modify as required.
No comments:
Post a Comment