Database Schema Change and DevOps

 

Traditionally, the DBA is the custodian of database changes. The DBA is the information technician responsible for ensuring the ongoing operational functionality and efficiency of an organization’s databases and the applications that access those databases.

However, the DBA is not usually the one to request a change; a programmer or user typically does that. There are times, though, when the DBA will request changes, for example, to address performance issues or to utilize new features or technologies. At any rate, regardless of who requests the change, the DBA must be involved in the change process to ensure that each change is performed successfully and with no impact on the rest of the database.

In an organization that has embraced DevOps, a shift occurs that places more of the responsibility for database change on the developer. However, the DBA still must be involved to oversee, analyze, and approve any changes. As with all things in the world of DevOps, it is desirable to automate as much of the process to remove manual, error-prone tasks and increase the speed of delivery. But without a tool that automates complex database changes and integrates into the DevOps toolchain, incorporating database changes into application delivery and deployment remains a slow, mostly manual process.

To effectively make database changes, the DBA needs to consider multiple issues, the most important of which are the appropriateness of the change in terms of the database design and the impact of the change on all other database objects and applications. Additionally, the DBA must determine if the change conforms to standards (for your shop and the industry), how best to make the change, and the timing of the change in terms of its impact on database availability while the change is being made.

The ideal arrangement (in a DevOps shop) is for database schema changes to be incorporated into the DevOps toolchain using a tool that allows developers to request changes. Those changes should be analyzed and compared against standards and rules for conformance. Non-compliant changes should automatically be referred back to the developer for modification and resubmission. Compliant changes should be accepted and cause a script to be generated using the most appropriate mechanisms to implement the change. This is a non-trivial activity which if done properly can eliminate a lot of manual downtime. The generated script should be presented to the DBA for review and upon acceptance, be implemented.

It is worth mentioning here that today’s major DBMS products do not support fast and efficient database structure changes for all types of change. Each DBMS provides differing levels of support for making changes to its databases, but none easily supports every type of change that might be required. One quick example: try to add a column to the middle of an existing row. To accomplish such a task, the DBA must drop the table and recreate it with the new column in the middle. But what about the data? When the table is dropped, the data is deleted unless the DBA was wise enough to first unload the data. But what about the indexes on the table? Well, they too are dropped when the table is dropped, so unless the DBA knows this and recreates the indexes too, performance will suffer. The same is true for database security: When the table is dropped, all security for the table is also dropped. And this is but one example of what seems like a simple change becoming difficult to implement and manage.

Adding to the difficulty of making schema changes is the fact that most organizations have at least two, and sometimes more, copies of each database. There may be copies of the database at different locations or for different divisions of the company. And at the very least, a test and a production version will exist. But there may be multiple testing environments—for example, to support simultaneous development, quality assurance, unit testing, and integration testing. Each database change will need to be made to each of these copies, as well as, eventually, to the production copy. So, you can see how database change can quickly monopolize a DBA’s time.

The bottom line is that a robust, time-tested process that is designed to automate and enable database changes — with DBA oversight — is required. Do not minimize or discount the importance of database schema change management when planning and implementing DevOps at your organization.

 

About craig@craigsmullins.com

I'm a data management strategist, researcher, and consultant with over three decades of experience in all facets of database systems development and implementation.
This entry was posted in DBA, DBMS, DevOps, standards. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s