Database Compare and Synchronize Methods as a Part of the Software Release Management Process

By testing migration processes between environments, bugs are able to be found earlier, and before the migration to production. Using automatic packages, and well-thought out staging areas, migration can be a less painful, and more informative process than it may have been for you in the past.

By nature, the software lifecycle is circular; as such, it demands recurring implementation of versions and updates in different work environments, deploying our changes and updates to testing and production environments, as well as among the customers that buy the software systems we develop. When releasing new software or updates, it is required to package them in a setup package. Professionals know how to build setup packages of software libraries, files, objects (OCX), etc., and it is easy for them to do so. Moreover, the task can also be accomplished using a wide range of automatic software packages.

From our own painful experience and that of others, the problem becomes more critical when the environments that have to be migrated are database based. In most cases, the existing database cannot be overwritten, as is the case with software libraries, but rather structural changes need to be incorporated into the existing structure. Moreover, content changes must be integrated into the production environment’s data.

The plot thickens even more in many enterprises that do not maintain clearly defined and separate work environments. We are all familiar with the traditional distribution into three work environments: development, testing/QA and production. However, companies do not always implement these three environments and sometimes the testing environment is a “live” one. As a result, testing and R&D personnel make frequent changes and fixes that cause the testing environment to become a continuation or extension of the development environment.

It is very easy and tempting to fix, change and play with the testing environment to be able to detect software malfunctions and problems, thus saving precious time. This is not a bad approach if one is aware of its downside and makes sure that future short and long-term damages (frequently hidden) are minimized.

The main problem with this type of activity is that the testing environment draws closer to the development environment and moves away from the production environment. As a result, faults that should be detected earlier in the process (during testing) are found only later, during migration to production, or when the application is already in operation. Due to the changes and dynamism of the testing environment, it less effectively filters and shows potential problems; more specifically, it prevents testing of the migration processes between environments.

Improving Control and Work Processes

There are two main approaches for improving control and work processes:

    • Creating a “staging” environment – this is an additional environment on top of the three classic ones that simulates as much as possible the organization’s production environment. It comprises byproducts of real data – legacy or partial – rather than random test data. This environment therefore increases the probability of finding malfunctions that may appear later in the production environment.
    • Maintaining a sterile testing environment in which no changes or fixes are made.

Both approaches are actually similar. However, the implementation of a staging environment enables users to enjoy the benefits of a dynamic, less formal testing environment while still taking advantage of an additional testing environment that is as reliable as possible.

But maintaining sterility or creating an additional environment is only the first step. Taking optimum advantage of them is what will provide maximum benefits.

The following are some important issues that, when properly addressed, will help take utmost advantage of each environment:

    • Automatic “migration setup packages” should be built. They should contain all the changes that have to be made during the migration from the development to the testing environment (when a sterile environment is in place), and from the testing to the staging environment (when four environments are implemented). The package can comprise subpackages, usually topic based (for example, building work directories and copying files, centralized recording of components, changes to the structure of a database, etc.). The package can also be a neat collection of scripts, batch files or CAB files installed with applications like Install Shield, Wise, etc.
    • Migration packages must be built as if they were provided to remote clients; that is, tested and closed. Obviously, no changes or additions should be made in the target environment after the migration packages are running. If needed (and it will be needed…) there should be packages built to remove updates from the target environment. (The emphasis should be on zero manual changes that are not included in the migration package.) When detecting malfunctions, all fixes should be concentrated and executed together in a new, updated migration package.
    • To ensure enforcement of work procedures, companies should encourage developers to refrain from running modifications and updates in the staging environment, just as they are prevented from doing so in the production environment.
    • Putting emphasis on upgrade packages that make changes to the databases:
      1. For each set of changes to the database’s structure, a parallel package should be built for carefully removing the changes – adding or removing a column in a table should not “overwrite” the table itself. If it is necessary to delete tables or columns, an automatic backup of the relevant data should be initiated to ensure a rollback is feasible.
      2. One should not forget that, usually, in addition to the changes to the database’s schema, it is also necessary to make changes to relevant data like lookup tables or dictionary data.
      3. Using automated compare and synchronize tools for migrating database components between the environments has major direct benefits. These tools raise confidence as they perform comparison of differences between the database environments, resulting in much better coverage of changes, and not relaying on manual change documentation. They also perform much or all of the automatic coding required to synchronize the database structure and data, resulting in time and error saving.
    • It is also very important to use automated tools to verify deployments. Once we have finalized a deployment of the database, we should perform an additional database comparison, to make sure all was deployed correctly.

Migration Package

The following are the main outputs of this method, which although somewhat complex is systematic and tidy:

    • Reuse of the migration package that was used for migrating from the test environment to the staging environment, to migrate into the production environment. This ensures a very high probability of a smooth migration because the packages were already tested during the migration between internal environments.
    • A regression package of the migration can be used in emergencies, when the company decides to rollback from updates made in the production environment. It is always better to solve problems with ease, without the painful pressure of a production environment downtime.

The main costs involved in the creation of an additional environment are those of hardware and additional resources, which are doubled (especially the time invested testing an additional environment). These costs can be partially reduced by:

    • Developing scripts or using tools for automatic regression/cover tests that will automatically run a wide variety of system integrity checks (also useful in the traditional test environment itself)
      • Using automatic tools for developing migration packages between environments to reduce the precious time invested in a relatively technical task. These tools will supply a compare and synchronize process, saving time and eliminating most of the pitfalls resulting in manual work.
        • Using virtual servers to reduce the hardware that has to be purchased and managed for an additional environment.

        To sum up, the use of a more intelligent QA/testing environment or an additional staging environment using automatic tools for the migration between environments (scripts/installations), can significantly improve the number of bugs found in earlier stages, while drastically reducing the rate of malfunctions that may appear during the migration to production.

        About the author

        CMCrossroads is a TechWell community.

        Through conferences, training, consulting, and online resources, TechWell helps you develop and deliver great software every day.