Wibke Carstensen

7 November 2016

1 Comment

Why put your database into version control?

Software development is a difficult and demanding discipline. It gets even more difficult when you bring teams of developers and database administrators together on a single project.

One of the fundamental issues arises around the code itself. Who is responsible for what piece of it? What changes have they made? How do you get those changes from one member of the team to the next? How do you keep a history of each change, in case it causes problems later that you need to track down and fix? How do you package all the changes up for deployment?

The answer to all these questions, and many more, is to manage your code through a version control system. As Part I of our Version Control and the Database blog series demonstrates, huge advances have been made in version control practices, from manufacturing through to software development.

Version control systems (VCS), or revision control systems, date back to the birth of modern computing and getting application code into version control has gone beyond being an industry standard practice to simply a part of programming.

But databases are different. In this blog post, we’ll explore why this is the case – and why it’s also important to include them in the same change management processes as application code.

Why is the database different?

Use of a version control system has long been an established part of the development process for application code but not, sadly, for database changes.

To some degree this is because the history of databases diverges from the history of code. At some point developers were no longer responsible for databases. Instead they moved into the hands of system administrators or dedicated database administrators, who looked at the world differently from developers.

Because their primary concerns were other critical matters such as backups, availability, integrity and performance, not all things that became best practice in development were necessarily applied to databases.

But there are also technical reasons for why keeping database changes in version control is more challenging than application code.

Unlike application code, database code is all about persistence. You can’t simply recompile the database into a new form. You must take into account the data and structures that already exist. It’s also more difficult to manage schema changes because version-controlling tends to work well for text files only, where it’s easy to tell what’s changed.

To solve this problem, tools like Redgate SQL Source Control generate a version of your database schemas as text files, and link these files to your version control system. This means that, when you make a schema change and commit it to version control, it’s easy to see what the change was.

Why does database version control matter?

The benefits of version controlling database changes are the same as for application code: the ability to identify which changes have been made, when, and by whom, and having a single source of truth to rely on.

The version control system is at the heart of the change management process, and it allows you to protect the integrity of the database design throughout the development process.

The key benefits to almost all development processes include:

• The ability to share code, allowing multiple people/teams to access pieces of code, or a database, at the same time
• Having a way to manage and protect the code generated
• Versioning each piece of code, so a history of changes can be kept
• Versioning, or labelling, whole sets of code, so you can deploy from a known state, or revert to a previously known state
• The introduction of change management, so new software or new functionality is carefully tracked and approved

Every version control system will provide a means by which a developer can retrieve a particular version of a given piece of code and a way to put the changed code back into the system, thereby creating a new version of the code.

A good version control system should have a mechanism in place for merging multiple changes to the same code. In other words, if more than one developer worked on the same procedure, a mechanism must exist for reconciling their individual changes.

Furthermore, in order to support multiple development streams with, for example, one stream writing service patches for code already in production, and another stream creating new functionality for the system, the version control system should allow developers to create branches of the codebase for testing or for releases. The alterations in the branches can then be merged back into the main trunk of development.

In the database context, there are also several other reasons why version control is important.

Backups

While we would never suggest you shouldn’t have a reliable backup strategy in place, getting a database into version control also provides an efficient mechanism for backing up the SQL code for your database. Retrieving a previous version of a stored procedure entails simply inspecting the history of changes within your VCS. Retrieval is nearly instantaneous.

Auditing

Within most database management systems, it’s possible to find out when an object was created or last modified, and which login performed that action. However, there is usually no historical record of any previous modifications to that object.

Further, depending on the security mechanism within the database, you may simply see that a system administrator or database owner made the change, with no indication as to the actual identity of the person working within that role.

If you have your database in a VCS, and use the VCS as a fundamental part of your development and deployment mechanisms, it will provide exactly that type of tracking. All changes originate in the VCS and are not made directly against the production system outside the process around your VCS. You’ll know who made what change and when it was made.

Automation

As organizations increasingly face pressure to release useable software and new features to customers faster and more frequently, version control lays the foundation for practices that increase the speed of delivery through, for example, continuous integration (CI) and automated deployments.

With tools such as Redgate DLM Automation, you can configure your CI processes to include an automated build of your database upon every check-in to version control. This results in a more reliable mechanism for catching issues, and also means they’re caught earlier in the development cycle, removing delays due to integration issues close to release time and greatly reducing the risk of deployment errors.

Having the database in version control directly alongside the application will also integrate database changes with application code changes, so you’ll always know the version of the database being deployed directly corresponds to the version of the application being deployed. This direct integration helps to ensure better coordination between teams and can help when troubleshooting issues.

If all changes needed for a production system are in a development database somewhere instead of inside a VCS, deployments are necessarily going to be a manual effort. You’ll need a process to generate changes from your development database in order to make the production database mirror the newer design.

When you have database version control in place, you can choose whether a manual or automated deployment approach is the right way to go for your organization.

Once you start generating your deployment scripts from version control, you’ll be able to differentiate the database objects into known versions which will allow you to control what is deployed.

Once you can control what is being deployed, you can also introduce automated deployments. If you’re using Redgate DLM Automation, for example, your CI process will provide a NuGet package that can then be automatically deployed across environments using a release management tool like Octopus Deploy.

In Part III of our Version Control and the Database blog series, we’ll explore how Redgate SQL Source Control helps to get database changes version controlled alongside application code.

To find out more about the advances made in version control practices, from manufacturing through to software development, you can also read Part I in the series, A brief history of version control.