Introduction

The benefits source code control brings to application development are so well and so long understood that even lone hobbyist developers will tend to employ a source control system. Database source control on the other hand is not yet common practice everywhere. This article will look at the benefits that source controlling database code brings to Oracle development teams.

What is source control?

Source control (or version control), is a system which tracks and retains an incremental history of changes to a set of files. Source control systems can be used in many types of project, ranging from application development to website development. The content may include source code within application development, or files such as architecture diagrams and documentation.

The fundamental value of source control is that it allows teams to maintain a single, authoritative, shared repository of project resources, while enabling multiple individuals to work on the same codebase, without having to constantly send files back and forth.

The problem for databases is that source control rests on the sharing and monitoring of a set of files. Database development does not typically involve source files, and so database source control has historically been impossible. This is no longer the case, and source code control should now be an essential process for all Oracle database teams.

Benefits of database source control

Keep your team in sync

Successful database development depends on successful coordination of the development team. Database source control removes many obstacles from team collaboration, allowing your team to ship higher quality releases on time.

Change management

Without source control, workarounds are needed to ensure that your developers have the latest database changes. Typically, this means discussing updates face-to-face, or through IM or email and stopping development work until other team members reply. Work may be shared using backups and restores, or by constructing a custom, home-grown solution. Inevitably, these processes are time-consuming and prone to human error. Using database source control allows developers to get the latest changes and commit their own quickly. Furthermore, automatic versioning makes it easier for teams to find and resolve conflicts and to document dependency chains.

Resuming partially completed work

Without source control, resuming partially completed work can be slow and cumbersome. Developers must spend time learning each other’s code and understanding the status of the work before it can be restarted. These problems are amplified for large or distributed development teams. Source controlled database development makes the process significantly more efficient by maintaining detailed change histories and allowing developers to easily document their changes as they work.

Database source control minimizes downtime and removes inefficiencies from the development process, allowing your team to work together much more effectively. Ultimately, this will result in higher quality development in a shorter time.

Preventing disasters

The term ‘disaster’ is relative, but for a development team, days, or even hours of lost time can be disastrous, particularly if the consequences extend to missed deadlines or downtime of production systems. There are many circumstances where an innocent database change can lead to an overwritten table or data loss, or a difficult-to-find bug. Even in a project where a single developer is modifying a simple database, changes can introduce bugs and regressions. This problem is compounded within team-based development; if one developer accidentally overwrites another’s work, development can grind to a standstill. Multiple developers working simultaneously on a database can routinely encounter conflicts and broken dependencies, resulting in wasted development time and a slower, less efficient development cycle.

A properly implemented database source control system reduces the likelihood of disasters by increasing the transparency of the development process and helping developers catch bugs earlier. Moreover, if problems do occur, source control allows your team to quickly reverse their effects.

Improved visibility

Without source control it’s difficult to track database changes, so sharing them becomes an uncertain, manual procedure. Source control helps developers prevent problems such as overwriting colleagues work by providing a detailed history of database changes. Developers can easily track who changed what, when they did it, and why. If they find a conflict, they can compare the differences before deciding whether to commit their change. If important code is inadvertently overwritten, it’s a quick procedure to undo a change or revert a database to a previous version.

Revert to previous versions

With the improved visibility provided by database source control there is less likelihood of changes being overwritten, or dependencies broken. However, if this does occur, developers can instantly undo a particular change or revert the database to a chosen previous version.

Conform to best practice

Regulatory compliance is likely to affect many areas of your organization, and database development and administration is arguably amongst the areas most seriously affected. Your organization’s databases are where much, if not all, of your critical business information lives. Consequently, compliance auditors will usually require you to account for all changes to a database, and detail all those with access to it.

As a minimum, it’s best practice to ensure you have processes in place to:

  • Manage how schema and data changes are made
  • Document schema changes
  • Maintain a detailed history of who made which changes
  • Document database schema and access permissions
  • Revert to previous versions if problems occur

Database source control and compliance

Conforming to best practice and regulatory requirements is a particularly difficult task for database developers, due to stringent requirements and the limitations of developer tools. However, implementing database source control can greatly simplify the process of maintaining a robust audit trail and is the first step to getting your database ready for compliance.

Implementing database source control

This article has highlighted some important benefits of source control: it enables your team members to work more effectively with each other, helps prevent costly development disasters, and helps you conform to best practice. However, there is still the issue that source controlling your database code is difficult to do.

The latest version of Schema Compare for Oracle goes some way to help you source control your schemas. In a few clicks you can generate creation scripts for each object and store the files in your existing source control system (e.g. Subversion, TFS etc.) to share or revert changes. Schema Compare for Oracle groups these creation scripts into folders by object type meaning it’s simple to compare your database to what’s in source control. Differences can be examined in the same way as running a database diff through Schema Compare: your live/local database will be displayed side-by-side with what’s in source control with highlighted differences. Synchronizing them is as easy as clicking a few buttons to generate a deployment script to export and execute through your IDE or running the Deployment Wizard.

If you’re not currently source controlling your database code, or your current process is time consuming and difficult, download a free 14 day trial of Schema Compare for Oracle and give this a go.

Source Control for Oracle

Red Gate is also working on a separate Oracle database source control tool, which will connect directly to your existing source control system (e.g. Subversion, TFS etc.). Source Control for Oracle will automate the process of pushing database changes into your source control system, and keep everyone in sync with the latest changes. By automatically storing a complete history of your Oracle database schema changes, Source Control for Oracle will finally make implementing source code control easy for Oracle database development teams.

If you’re interested in helping Red Gate develop Source Control for Oracle you can join the free beta program. In return, you’ll get access to new releases before anyone else.

Designing Source Control for Oracle

The development of Red Gate’s new Oracle source code control product started in a unique way. The entire Red Gate exhibition booth at the ODTUG Kscope ’12 conference was transformed into a mini development lab, and we built a prototype of the tool live on our stand. You can read about the process we went through over 3 days to take an idea and turn it into a working prototype here: Red Gate Live Lab – Source Control for Oracle

Article references:
Red Gate whitepaper: The Business Benefits of Database Source Control
Red Gate whitepaper: 3 Reasons to Source Control your Database