Manage DB Changes with Embarcadero DB Change Manager
There are many different ways and solutions to keep changes to the database and manage them. It is important to find the most appropriate approach and apply the appropriate tool to help you increase the degree of automation of database versioning migration, improve the quality and reliability of your work, save staff resources and time. In the last article I tried with a life example to tell where the problems of managing changes in the database come from, what difficulties it creates, and what conclusions can be drawn from this, by and large, negative experience.
As a rule, for the successful implementation of database versioning migration technologies, it is desirable to have tools that facilitate and automate the following tasks:
It has already been mentioned that the version approach is not very effective to use without the use of any instrumental solutions. Let's see how you can solve these problems with Embarcadero's DB Change Manager. This utility for database administrators and database developers allows you to simplify and automate the introduction of changes to databases and create reports on changes. DB Change Manager ensures database consistency, compliance and data privacy.
The concept of the utility is based on the operations of comparison and "archiving". DB Change Manager allows you to analyze and compare database items taken from two different data sources. It generates a SQL script with which one data source will be modified to match the other.
All operations are performed in the form of separate "tasks". Tasks are used to organize reusable processes. The task can be executed by user directive or saved to disk in order to be launched according to the schedule in batch mode from the command line.
To increase the level of automation, all tasks can report the results of their work by e-mail, to system logs, or by messages in the system notification area.
')
After performing the comparison task, the DB Change Manager can generate a synchronization SQL script to make changes to the target data, configurations, or schemas. Before running the script there is an opportunity to make changes to it.
For the convenience of work and navigation tasks and scripts can be combined into projects. Provides access to source control system files.
To solve the above problems, you should use a combination of tasks of several types. Tasks may relate to one of the possible areas of changes in the database:
Since all synchronization operations occur through comparison, it is necessary to have a benchmark for comparison (baseline). At different stages of a database's life, the standard can be various versions or a “live” database. To save the next version, you need to run the task to create an archive from the current state of the database.
Suppose there is a database, it can be a working or some other version (version 0). Some caring soul (at least, neat and responsible) took care that there is a saved version of the database structure, let's call it “archive N”
How to get this version? To get archive versions in DB Change Manager, create and execute the “archiving” task. This task can save to the repository the next version of the database structure.
It is allowed to include in this series of versions-archives not all metadata, but only those selected by the user. In the example above, the only owner was specified, whose objects will be saved during archiving - GIM. You can also specify what kind of objects will be included in the archive.
Interestingly, archives-versions can have a “lifetime” - after what period of storage the version will be deleted.
To compare the current database and the specified version (in the example below it is the latest saved version), you also need to have the Schema Comparison task, which is created in much the same way as other types of tasks. It is necessary to indicate “archive N” as a source for comparison, and as a target, simply drag a source corresponding to the required database from the data source navigator. In the job options, you can choose between automatic or “manual” synchronization. The task can be performed directly from the interactive environment or from the command line.
As a result, the database structure will be transferred to the state corresponding to the required version - automatically or a synchronization script will be generated.
When a task is created, it is possible to specify the necessary settings for the comparison. First of all, specify the synchronization mode: automatic or manual. You can customize the set of objects that will be included in the synchronization script (Include Objects Options). There is a wide range of settings for the comparison of objects. There are settings for processing the parameters of the database file system, identifiers, etc.
After completing the task in interactive mode, a report on the detailed comparison results can be seen in a special window.
For each of the compared objects, the result of the comparison is reflected in the status. When there are a lot of objects, filtering becomes necessary. Leaving only the necessary checkboxes checked, we reduce the number of visible objects and make it possible to better concentrate attention.
There are situations when you need to explicitly specify the direction of synchronization. It is permissible to set synchronization from the source data source to the target one for most objects, and in the opposite direction for some. You can generally exclude a number of objects from the synchronization procedure.
The 'Generate A Sync Script' button will automatically create a synchronization script that opens in the ISQL editor.
You can make changes to the SQL script and execute it as needed.
If automatic synchronization has been selected, you can set to save the script generated during the process to the specified directory on disk or external media.
Now the path to create a new instance of the database based on the archive of any version should be clear.
It is necessary to create a task for synchronization of the scheme, specify the archive-version as the source, and the target is a live server. The only subtlety is that on this server you need to create in advance the appropriate "owner" of the scheme. Performing this task will give a script to create a new instance.
It is very convenient that DB Change Manager can migrate schemes not only to the same DBMS, but also to different platforms, for example: Oracle - SQL Server, Sybase - SQL Server, Firebird-Interbase. In these cases, it is reasonable to keep this process under control, rather than try to perform the migration in a fully automatic mode, since it is impossible to achieve a completely unambiguous correspondence of objects from one DBMS to another.
By the way, as a target data source, you can specify not one, but several different ones. In this case, the task will create the appropriate number of synchronization scripts.
DB Change Manager makes it easy to create “poster” database instances for testing and development. The peculiarity of such instances is that they should not completely duplicate the working databases in terms of volume and data, but at the same time should be identical in composition of the schema objects. In addition, in such databases, often placed outside the company's security zone, third-party developers, for example, cannot transfer protected data. The scenario of creating such “poster” copies of the database consists of two steps:
Having created a set of necessary tasks and a schedule for their automatic execution, you can conduct regular scheduled checks, create new versions or return to the reference state.
In addition to the difference in the schemes, configuration settings may vary within the enterprise. DB Change Manager allows you to get configuration settings for a data source. You can fix the initial state or archive of the configuration and then analyze the changes that occur. You can even create a configuration standard that defines the standards for each enterprise.
DB Change Manager independently creates and maintains archive versions, but if necessary, you can integrate with source control systems such as the Concurrent Versions System (CVS, parallel versions system), Visual Source Safe (VSS, version control software code) or plug-ins Eclipse Navigator in DB Change Manager provides an interface to access files under the control of a source control system. All source code management activities can be performed through the source code management system.
In this brief article, I hope I was able to acquaint everyone with the DB Change Manager, with its unique capabilities to work in a single way with many DBMS platforms, a high level of automation in performing all tasks and a wide range of functions at three levels: database schemas, DBMS server configurations and proper data to solve these problems.
DB Change Manager is a solution that simplifies and accelerates the management of changes in the database. In addition to versioning database migration, this utility makes it easier to monitor and audit unwanted changes in database instances, and, if necessary, automatically return to the reference state in a short time. DB Change Manager accompanies your actions to manage these process execution notifications, reports collecting key information, and building a change history.
As a rule, for the successful implementation of database versioning migration technologies, it is desirable to have tools that facilitate and automate the following tasks:
- Updating a database from a specific version to any other one in one step, both later and returning to the previous one;
- Easy receipt of migration scripts in automatic mode, with the possibility of "manual" corrections in a pinch;
- Creating from scratch a new instance of the database corresponding to the existing version of the application;
- Simple creation of test / developer database instances based on current working databases for developing on them that best fit these workers.
- Control and audit of undesirable changes in database instances, if necessary, automatic return to the reference state in a short time.
It has already been mentioned that the version approach is not very effective to use without the use of any instrumental solutions. Let's see how you can solve these problems with Embarcadero's DB Change Manager. This utility for database administrators and database developers allows you to simplify and automate the introduction of changes to databases and create reports on changes. DB Change Manager ensures database consistency, compliance and data privacy.
The concept of the utility is based on the operations of comparison and "archiving". DB Change Manager allows you to analyze and compare database items taken from two different data sources. It generates a SQL script with which one data source will be modified to match the other.
All operations are performed in the form of separate "tasks". Tasks are used to organize reusable processes. The task can be executed by user directive or saved to disk in order to be launched according to the schedule in batch mode from the command line.
To increase the level of automation, all tasks can report the results of their work by e-mail, to system logs, or by messages in the system notification area.
')
After performing the comparison task, the DB Change Manager can generate a synchronization SQL script to make changes to the target data, configurations, or schemas. Before running the script there is an opportunity to make changes to it.
For the convenience of work and navigation tasks and scripts can be combined into projects. Provides access to source control system files.
To solve the above problems, you should use a combination of tasks of several types. Tasks may relate to one of the possible areas of changes in the database:
- Configuration changes: obtain configuration settings for the data source, record the initial state or archive of the configuration, and then analyze the changes. You can create a configuration standard that defines standards for your enterprise with which you can then compare your databases.
- Changes in database structure: DB Change Manager allows you to read and compare schemas of various databases. During the life of a separate scheme, you can fix its initial state (or the archive of the scheme) and then analyze the changes occurring with it. In addition, DB Change Manager generates a SQL script that allows you to make changes to the target schema so that it matches the pattern.
- Data Changes: DB Change Manager allows you to analyze and compare data taken from two different data sources. It generates a SQL script with which one data source will be modified so that it coincides with another.
Since all synchronization operations occur through comparison, it is necessary to have a benchmark for comparison (baseline). At different stages of a database's life, the standard can be various versions or a “live” database. To save the next version, you need to run the task to create an archive from the current state of the database.
Updating the database from a specific version to any other in one step
Suppose there is a database, it can be a working or some other version (version 0). Some caring soul (at least, neat and responsible) took care that there is a saved version of the database structure, let's call it “archive N”
How to get this version? To get archive versions in DB Change Manager, create and execute the “archiving” task. This task can save to the repository the next version of the database structure.
It is allowed to include in this series of versions-archives not all metadata, but only those selected by the user. In the example above, the only owner was specified, whose objects will be saved during archiving - GIM. You can also specify what kind of objects will be included in the archive.
Interestingly, archives-versions can have a “lifetime” - after what period of storage the version will be deleted.
To compare the current database and the specified version (in the example below it is the latest saved version), you also need to have the Schema Comparison task, which is created in much the same way as other types of tasks. It is necessary to indicate “archive N” as a source for comparison, and as a target, simply drag a source corresponding to the required database from the data source navigator. In the job options, you can choose between automatic or “manual” synchronization. The task can be performed directly from the interactive environment or from the command line.
As a result, the database structure will be transferred to the state corresponding to the required version - automatically or a synchronization script will be generated.
Easy to get migration scripts in automatic mode
When a task is created, it is possible to specify the necessary settings for the comparison. First of all, specify the synchronization mode: automatic or manual. You can customize the set of objects that will be included in the synchronization script (Include Objects Options). There is a wide range of settings for the comparison of objects. There are settings for processing the parameters of the database file system, identifiers, etc.
After completing the task in interactive mode, a report on the detailed comparison results can be seen in a special window.
For each of the compared objects, the result of the comparison is reflected in the status. When there are a lot of objects, filtering becomes necessary. Leaving only the necessary checkboxes checked, we reduce the number of visible objects and make it possible to better concentrate attention.
There are situations when you need to explicitly specify the direction of synchronization. It is permissible to set synchronization from the source data source to the target one for most objects, and in the opposite direction for some. You can generally exclude a number of objects from the synchronization procedure.
The 'Generate A Sync Script' button will automatically create a synchronization script that opens in the ISQL editor.
You can make changes to the SQL script and execute it as needed.
If automatic synchronization has been selected, you can set to save the script generated during the process to the specified directory on disk or external media.
Creating from scratch a new instance of the database corresponding to the existing version of the application
Now the path to create a new instance of the database based on the archive of any version should be clear.
It is necessary to create a task for synchronization of the scheme, specify the archive-version as the source, and the target is a live server. The only subtlety is that on this server you need to create in advance the appropriate "owner" of the scheme. Performing this task will give a script to create a new instance.
It is very convenient that DB Change Manager can migrate schemes not only to the same DBMS, but also to different platforms, for example: Oracle - SQL Server, Sybase - SQL Server, Firebird-Interbase. In these cases, it is reasonable to keep this process under control, rather than try to perform the migration in a fully automatic mode, since it is impossible to achieve a completely unambiguous correspondence of objects from one DBMS to another.
By the way, as a target data source, you can specify not one, but several different ones. In this case, the task will create the appropriate number of synchronization scripts.
Simple creation of test / developer database instances based on current working databases
DB Change Manager makes it easy to create “poster” database instances for testing and development. The peculiarity of such instances is that they should not completely duplicate the working databases in terms of volume and data, but at the same time should be identical in composition of the schema objects. In addition, in such databases, often placed outside the company's security zone, third-party developers, for example, cannot transfer protected data. The scenario of creating such “poster” copies of the database consists of two steps:
- creation from scratch of a new database, on the basis of an existing working or any desired version, as described in the paragraph above. It is enough to transfer only the scheme.
- data comparison; a task is created in which the required subset of the transferred data, the protected data elements and their masking algorithms are indicated. As a result of this task, the data is either directly transferred to a new instance, or scripts are created for further batch execution.
Control and audit of undesirable changes in database instances, if necessary, automatic return to the reference state in a short time
Having created a set of necessary tasks and a schedule for their automatic execution, you can conduct regular scheduled checks, create new versions or return to the reference state.
Database Configuration Management
In addition to the difference in the schemes, configuration settings may vary within the enterprise. DB Change Manager allows you to get configuration settings for a data source. You can fix the initial state or archive of the configuration and then analyze the changes that occur. You can even create a configuration standard that defines the standards for each enterprise.
Integration with source control systems
DB Change Manager independently creates and maintains archive versions, but if necessary, you can integrate with source control systems such as the Concurrent Versions System (CVS, parallel versions system), Visual Source Safe (VSS, version control software code) or plug-ins Eclipse Navigator in DB Change Manager provides an interface to access files under the control of a source control system. All source code management activities can be performed through the source code management system.
Conclusion
In this brief article, I hope I was able to acquaint everyone with the DB Change Manager, with its unique capabilities to work in a single way with many DBMS platforms, a high level of automation in performing all tasks and a wide range of functions at three levels: database schemas, DBMS server configurations and proper data to solve these problems.
- Updating a database from a specific version to any other one in one step is solved using the task of comparing schemes. The versions of structures themselves can be automatically obtained with the help of regular execution of tasks for archiving schemes.
- Easy receipt of migration scripts in automatic mode, with the possibility of "manual" corrections as a last resort - all comparison tasks will provide the appropriate version synchronization scripts (in one or both directions) automatically or with the press of a single button.
- Creating from scratch a new instance of the database corresponding to the existing version of the application is solved by performing the task of comparing the archive version with the desired database server.
- Simple creation of test / developer database instances based on current working databases for developing on them that best fit these workers. We solve it by creating a new instance of the database based on the real database (and not the version) and then performing the comparison-data migration task.
DB Change Manager is a solution that simplifies and accelerates the management of changes in the database. In addition to versioning database migration, this utility makes it easier to monitor and audit unwanted changes in database instances, and, if necessary, automatically return to the reference state in a short time. DB Change Manager accompanies your actions to manage these process execution notifications, reports collecting key information, and building a change history.
Source: https://habr.com/ru/post/204932/
All Articles