Automatically migrate databases for DevOps


For successful organizations, a critical differentiator is to deliver relevant applications faster and with higher quality with fewer resources is an important differentiator. Consequently, organizations need to improve all parts of the development lifecycle of applications. To deliver applications of higher quality faster with fewer resources and to deal with continuously changing requirements, organizations use agile development to plan, code, and build applications. Organizations use continuous integration to integrate quality assurance better into agile development, and continuous delivery to better incorporate releases into continuous integration. To better integrate deployment and operations into continuous integration, organizations use DevOps to connect application development with IT operations. For efficient DevOps, organizations use lifecycle automation for applications for all layers of the technology stack to avoid manual work.

Migrate databases with DevOps

Application development and IT operations benefit from agile development, continuous integration, continuous delivery, and especially DevOps. Many DevOps teams have developers who work across applications and databases. Such developers need their copies of databases to use in development environments. The most prominent driver for including databases in DevOps is to increase the speed of delivery of changes to databases. It is essential to deploy databases as fast and efficiently as the automation of the releases of applications while eliminating the risks of application downtime and vulnerabilities to the security of data. Despite the benefits of integrating changes to databases into DevOps, the most significant challenge is synchronizing modifications to applications and databases, and overcoming different development approaches. Consequently, DevOps often excludes databases.

Databases versus applications

The process of updating databases is different from the process of updating the source code of applications. The existing database cannot just be overwritten (as opposed to application software libraries). Instead, it is necessary to incorporate changes into the current database structure. That is, it is not feasible to replace an existing database structure while preserving database data. It is also necessary to integrate changes to database data into the work environments. Unlike applications, databases do not consist of a collection of files that cannot just copy from between different work environments (such as development, quality assurance, staging, and production). Instead, databases consist of a container of data that requires preservation. It is necessary to develop a transition code to promote database changes. Such a transition code consists of codes that handle changes to database structure and database data.

Treat databases as code

Databases contain structural elements (such as the schema, stored procedures, and triggers) and data. While the structural elements may be straightforward to change in a development environment, those same changes in a production environment require an entirely different set of rules. Applying DevOps to databases allows developers to treat databases as code, to check database changes into the source repository, to execute the code to make the changes to databases, and to automate the changes to databases. Lifecycle automation for databases leads to the standardization of the changes to databases across all of the phases of the lifecycle (that is, development, quality assurance, staging, and production). The same change or rollback is applied to every phase and is tested thoroughly before reaching production. Lifecycle automation for databases adds a level of maturity to the development lifecycle of systems. That is why it is crucial to educate IT teams about tools for database automation to fully implement DevOps into organizations.

Manage database code in version control systems

It is possible to manage codes for changes to databases in traditional systems for version control for the source code of applications. However, this approach is inefficient and risky to automate since the two concepts are unaware of each other.

Compare and synchronize databases

A better solution is to create the transition code between database environments via comparison and synchronization. The comparison examines database objects in a source environment and compares this to the target environment. Automatically create code to change the target object to represent the source object when discovering differences.

Manually migrate databases

To migrate changes to a database between two database environments (for example, from a production environment to a development environment, or vice versa):

  1. Manually compare database schema between the target environment and the source environment to identify differences.
  2. Manually generate the SQL statements that are necessary to synchronize the target environment.
  3. Manually execute the SQL statements in the target environment to synchronize it.

Alternatively, manually generate and execute SQL statements to revert to a previous version of the database schema.

Manually migrating changes to databases is a resource-intensive and error-prone process. A single incorrect change to a database can cause application downtime or compromise the security of data. When performing such a migration manually, this can cause problems with the quality of databases due to human error, the slow delivery of development projects, the lack of visibility into what changes are deploying to which target environments, and an overall lack of control.

Automate the migration of databases

Automating the migration of databases between different database environments (such as development, quality assurance, staging, and production) provides several benefits:

  1. Migrate faster than what is possible with manual migration.
  2. Eliminate the need for multiple teams to be involved in the migration of databases between their database environments.
  3. Perform that the same type of migration between different environments.
  4. Provide consistency via repeatable results for every migration.
  5. Ensure a high probability of a successful migration by setting up and testing the automation between internal environments (such as development, quality assurance, and staging) before applying the automation to migrate to or from a production environment.
  6. Ensure that the migration was performed successfully via consistent verification.
  7. Be able to handle varying scopes of migrations (from single task-based migrations to substantial migrations of multiple databases) with a similar level of manageability and complexity of the automation codes.
  8. Allow other professionals to review, modify, and reuse the automation codes.

That is, the automated comparison and synchronization of database changes eliminate most of the disadvantages of manual processes.

Integrate the migration of databases

The automation of the migration of databases also enables the integration with other software tools and processes. For example, fully integrate the migration of databases with systems for application lifecycle management, build servers, change management, issue tracking, and release managers. With such integration, organizations can achieve complete end-to-end automation for DevOps. By integrating the migration of databases, it is possible to deploy databases automatically with changes to the application, deliver smaller and less risky deployments, discover mistakes earlier, enable developers to write better code, and respond faster to organizational needs.

IDERA’s solution

Use IDERA’s database comparison and synchronization tool to migrate databases automatically for DevOps. The tool compares and synchronizes changes to database schema and database data. The tool migrates databases between database environments and verifies that the migration was successful. The tool automatically creates code to compare and synchronize databases. Its command line interface (in addition to its graphical user interface) enables automation and integration into DevOps workflows.

To migrate a database between two database environments, apply these steps (either manually via the graphical user interface or automatically via the command line interface):

Migrate database schema:

  1. Customize the comparison and synchronization options.
  2. Create a baseline or snapshot of database schema at given point in time in the two environments.
  3. Compare the baselines between the two environments to identify differences.
  4. Generate the data definition language statements that are necessary to synchronize the target environment.
  5. Execute the data definition language statements in the target environment to synchronize it.
  6. Verify the success of the migration by comparing database schema between the two environments.
  7. If necessary, roll back to a previous version via the stored baselines.
  8. Track and report on what changes were migrated to which environment.

Migrate database data:

Repeat the above eight steps for database data instead of the database schema.

More information

For more information concerning the database comparison and synchronization solution for DevOps from IDERA, visit the product web pages for IDERA SQL Comparison Toolset. Download a fully functioning 14-day trial of SQL Comparison Toolset, request a one-on-one demonstration, or request a price quotation.