Version control for databases

Version control for databases

The Case

You have a team of developers working on database changing schema and the data. To prevent a situation when several developers make simultaneous changes to the schemas objects during the development process, you use a source control program.

Source control programs are designed for source code, rather than for databases.

When you need to revert to an earlier version of a schemas object in order to consider dependencies, you need to account for the entire database, and not just line-by-line differences. However, a source control program provides only the line-by-line differences in the script for the object, without getting references on dependencies.

Reverting to an earlier version of a schemas object based on only line-by-line differences in the script for a schemas object may entail dramatic consequences of an ineffective database.

Main goals to be achieved

• Keeping snapshots of different versions of the database throughout the whole development cycle, instead of recording the entire database each time.
• Maintaining the integrity of the database while reverting to a previous version. 

Our solution is

Use Cross-Database Studio in order to generate SQL scripts that can be used to revert back to a previous schemas objects state or data contents:

1. Select schema objects and/or data, the changes in which you want to keep an audit trail.
2. Configure synchronization strategy in order to produce synchronization SQL scripts, session scheduling etc.
3. Run synchronization session in order to produce synchronization SQL scripts without actual execution;
4. Archive and keep those SQL scripts in order to revert back to a previous schema objects state or data contents;

Advantage of our solution

• Snapshots are smaller and easier to operate than database backups.
• The integrity of the database is maintained, and the dependencies are considered.
• Easier way to revert to a previous version or release.
• Enables to record the complete history of the entire database development process.