A Guide to Identifying and Handling Database Changes Efficiently

by Jun 16, 2021

Recovering in the aftermath of unexpected database changes can be a nightmare for database administrators (DBAs) and related support teams. Database changes that have not been fully tested and verified can result in well-behaved systems suddenly experiencing performance or availability problems that cannot be easily identified or explained.

The introduction of changes is particularly problematic in mission-critical production systems that affect the business. Failed or unapproved changes often result in system outages and high-stress recovery operations. Most DBAs could find much more productive ways to spend their time than recovering from someone’s unscheduled and untested change. 

In addition to ensuring the proper channels and procedures are being followed by individuals authorized to make database changes, tracking change activity can be an essential component of maintaining security. External and internal actors who gain access to a database may make changes that compromise the security of the information it stores. Visibility into those changes can help teams keep sensitive data safe from unauthorized access.

Manual vs. Automated Database Change Management

Database teams can track and manage changes to their systems in two very different ways. The method chosen can have a dramatic impact on the reliability of the information, team productivity, and the health of the database environment.

The vast majority of database platforms have some capability of reporting on changes made with their data manipulation language (DML) or data definition language (DDL). The DML is used to update, add, and delete data in a database. The DDL is concerned with creating, deleting, and modifying tables in a database. Analyzing the specific statements executed in a given database provides a picture of changes made to its data and structure. 

Manual database change management with native database functionality

Native database constructs and functionality provide some level of evidence regarding database changes but cannot address record retention for audit requirements. The facilities built into a database platform may provide more information than is needed in some situations, resulting in noise that obscures items demanding closer inspection.

Teams can write scripts to perform change management and tracking activities using a platform’s available functionality. For example, in SQL Server, extended events, triggers, and audit objects are all constructs that can be used by DBAs to obtain information about their systems, including what has changed and who changed it.

The problem is that many of these built-in facilities have limitations. Log files can contain a lot of noise, making it difficult for teams to extract relevant information from them. Files tracking the creation of temporary databases may be overwritten, allowing a newly created rogue database to escape detection. Triggers can slow down performance and may need to be manually turned on and off to allow emergency, or approved, unscheduled changes to be performed. 

Automated database change management with third-party tools

Automating database change management with third-party tools is more efficient than its manual alternative. It’s not a matter of third-party tools presenting information not available through manual methods. The apps are not manufacturing data with which to supply reports and identify database changes. They are merely using the available information more efficiently and analyzing it to produce informative alerts. 

The developers of dedicated third-party tools such as those to manage database changes are aware of the native functionality limitations of various platforms. Armed with this information, they can concentrate development efforts on addressing the immediate concerns of DBAs as well as requirements to accurately provide audit evidence. A quality third-party tool makes it easier to stay on top of changes in complex database environments. 

A Solution for Database Change Management

DB Change Manager offers database teams a versatile tool for tracking and managing changes on SQL Server, IBM Db2, Oracle, and Sybase ASE systems. It provides teams with a unified interface for increased productivity and simplifies the activities required to ensure database changes are tracked and managed. 

DB Change Manager enables teams to take a snapshot of target databases before applying changes so they can be rolled back if necessary. Alerts can be sent to relevant personnel when changes are discovered so proactive measures can be taken to avoid impacts to production systems. Databases can be configured and audited against regulatory standards to ensure compliance when faced with an audit.

Try DB Change Manger for free!

DB Change Manager is part of the DB PowerStudio suite of database tools. 

Try DB PowerStudio for free!

A recording of an IDERA Geek Sync Webcast is available that talks about the issues that can arise from unexpected or undocumented database changes and how DB Change Manager can help minimize the problems. The webcast uses SQL Server to demonstrate limitations in the native database functionality related to tracking and managing changes. It also shows how DB Change Manager simplifies the job of tracking and managing database changes across multiple platforms. It's recommended viewing for all DBAs.