Database Version Control — State-based or Migration-based

Modern databases must keep up with dynamic business demands, and it is often a real challenge. The most important requirement is to ensure regular database updating that will preserve all the data. A solution is a well-planned approach to database development and deployment via either a state-based or migration-based approach. However, you need to understand both these approaches thoroughly.

• Why should you treat database updating as a separate challenge?
• What is state-based database deployment?
• What is migration-based database deployment?
• State-based vs migration-based: comparison
• Updating the database with Devart dbForge tools
• Conclusion

Why should you treat database updating as a separate challenge?

Dealing with databases means constantly keeping in mind that the database has two sides: the data it stores and the structure to organize that data.

The database updating approach requires the following issues to be considered thoroughly:

• A database includes the table structure, the code in stored procedures, the data kept in those tables, and all interrelations between the database objects. It sets more sophisticated challenges for implementing changes.
• The database sets more strict demands for synchronization, primarily when several developers work on some object in that database.
• It is critical to preserve all business data as well as the entire database code.

After the update, the data must remain safe and sound. That’s why it is impossible to update the database in the same way as the application code – we can’t simply delete the old database and create a new one for every change.

On the other hand, there is no need to reinvent the wheel – there are already methods tested and approved by all developers: the state-based and the migration-based database delivery models. As a database developer, you will most likely use both methods according to your project’s needs.

What is state-based database deployment?

The state-based approach suggests keeping the database schema in the ideal end state in the code repository. This approach was popularized by Microsoft which implemented it in its Visual Studio solution.

The entire idea of the state-based approach is simple: you keep a snapshot of the ideal database structure and work on your actual database project to match that ideal. All database objects (tables, views, stored procedures, functions, triggers, etc.) are state-based scripts, each is in a separate SQL file in the final form.

When database developers work on the database schema and need to update it, they deploy the database on the server locally and implement the necessary changes. Further, all the hard work is done by the compare tool that generates scripts to synchronize your actual database with that “etalon” database. Finally, the version control system uploads these changes to the server.

Changes are implemented in a sequential manner, “from lower to higher,” – from Development to Testing, and further, until it comes to Production.

The advantages of the state-based database delivery method:

• Store the database schema inside the Source Control to conveniently monitor the database state.
• Detect compile time errors in SQL files immediately.
• No need to create numerous scripts for the same entity.
• Watch all changes deployed to the database at any moment and thus manage the process better.
• Generate and execute the ALTER scripts automatically by the dedicated tool.

The disadvantages of the state-based database delivery method:

• It requires generating a new script for every new environment.
• It pushes changes only forward (source to target), and it can only be reverted manually. This model can cause issues for automated processes.

The state-based approach is the default choice for the new project development (from scratch). It suggests that the database or the entire application based on that database is in work until the final step of releasing the application to the Production area.

What is migration-based database deployment?

The migration-based method works under a different approach. Instead of having a single snapshot of an ideal database, you have a collection of migration scripts that transfer your actual database from one version to another.

A separate script is created for every step. Each such script contains specialized DDL statements, and all these migration scripts are stored in the repository. Using these migration scripts, you can update both the database schema and data.

You must create every migration script with the incremental version number. If you need to transfer the database to another environment, you have to execute all these migration scripts in the correct order. That’s why it is crucial to track these version numbers.

The most common scope for the migration-based database delivery method is database testing, updating databases with new features and enhancements, or creating a database from the scripts used in the state-based approach.

Many developers prefer the migration-based database delivery method because they can do the tasks faster and deploy the scripts quickly. On the other hand, in most cases, we have to create these migration scripts manually.

The advantages of the migration-based database delivery method:

• The possibility to change both the database schema and data simultaneously.
• Better alignment with the DevOps best practices of incremental changes.
• The same code is executed in all environments.
• Better testing and control of features.
• The possibility to write migration scripts in other programming languages besides SQL.

The disadvantages of the migration-based database delivery method:

• It often requires the developers to write all migration codes manually.
• It may provoke risks of having the code overridden by other developers’ changes in case of synchronization failures.
• It is inefficient to work with stored procedures and functions.

The migration-based approach is usually applied to the existing database that has been delivered to the end user. The application evolves in time and requires updates and enhancements. Such changes are mostly delivered through the migration scripts.

State-based vs migration-based: comparison

The main difference between these two approaches is what you consider the “source of truth” – the ideal database you strive for or the scripts you create to upgrade your database. When you as a database developer need to apply this or that approach, you will consider how it meets your project requirements in practice.

Some developers say that the state-based approach ensures more reliable testing and immutability. Other specialists prefer the migration-based approach because it suits the deployment challenges better. Besides, there is the question is you have to write the upgrade scripts manually, which is a complicated and time-consuming task.

• The state-based approach allows you to use computer-generated upgrade scripts 95% of the time.
• The migration-based approach suggests that you give custom directions in most cases.

Also, the stated-based delivery makes it much easier for the team to work together on complicated databases with various sophisticated dependencies.

Have a look at other essential differences between these two approaches in the below comparison table:

In practice, database developers need to use both state-based and migration-based approaches. During the early stages of a project, the state-based approach offers more agility for the evolving system. However, after the product launch, the migration-based method can be more efficient, providing greater control over changes and increased flexibility in workflow.

Updating the database with Devart dbForge tools

As previously mentioned, databases need to be continually upgraded to keep up with ever-changing business requirements. In the past, developers could dedicate significant time and effort to writing and maintaining scripts, but this is no longer a feasible approach. As the number of releases increases, these processes require more automation, which can be achieved through the use of specialized tools.

Devart offers a set of dedicated solutions designed to perform all database-related tasks including database change management. Such a tool as Source Control for SQL Server – a popular add-in for SSMS – makes a valuable component in DevOps automation providing database version control functionality to SQL Server developers. This tool allows the users to track and compare changes easily, synchronize the database versions, roll the changes back if needed, and benefit from many other options.

dbForge Source Control for SQL Server works in the state-based mode. If your project requires migration-based delivery, you can refer to another tool – Schema Compare for SQL Server that allows you to compare and synchronize database schemas between different SQL Server databases and scripts. This tool can generate the upgrade scripts, so you won’t need to write these migration scripts manually.

Whatever database delivery model you choose, the good news is that you can save your time and energy by automating routines – and here dbForge SQL Tools come in handy providing you with all the necessary functionality.

Conclusion

So, we have reviewed the two database deployment models – state-based and migration-based. We have explored their scopes and features and identified the pros and cons of both methods. However, it is impossible to apply only a state-based or only a migration-based approach. Both are necessary, and even one project may require them both.

Besides, with professional tools, you can achieve your goals faster and easier, letting the software handle the database changes over the course of time. As for the dbForge SQL Tools that we mentioned, you can try them free of charge – the fully-functional Free Trial is provided for 30 days for you to evaluate all the powers of every tool.