Why put your database into version control_

Software development is a difficult and demanding discipline. Data recovery richmond va 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.

Data recovery software 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. Data recovery advisor As Part I of our Version Control and the Database blog seriesdemonstrates, 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. Database host name 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. Database performance 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. Data recovery broken hard drive At some point developers were no longer responsible for databases. Database xe 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.

Unlike application code, database code is all about persistence. Database yml mysql You can’t simply recompile the database into a new form. 5 database is locked You must take into account the data and structures that already exist. Database fundamentals 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. Database concepts This means that, when you make a schema change and commit it to version control, it’s easy to see what the change was. Database icon 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.

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. Database versioning 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. Database 2013 The alterations in the branches can then be merged back into the main trunk of development.

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. Database cursor Retrieving a previous version of a stored procedure entails simply inspecting the history of changes within your VCS. Database list Retrieval is nearly instantaneous.

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. Database queries must be 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. Database journal All changes originate in the VCS and are not made directly against the production system outside the process around your VCS. Data recovery boston You’ll know who made what change and when it was made.

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. Database connection 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. S memo data recovery 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. Database structure 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. Data recovery iso 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.