JP2014219868A - Database migration method - Google Patents

Abstract

PROBLEM TO BE SOLVED: To migrate a database without stopping a service.SOLUTION: After an AP server 11 is set to refer to a virtual DB device 15 which can access both a migration source DB13 and a migration destination DB14 and has a migration table 26 storing data migration, the virtual DB device 15 refers to the migration table 26 in response to a request from the AP server 11 to determine whether data associated with a main key of the request has been migrated or not. When the request is one made for a main key which has not been migrated, the request is transmitted to the migration source DB 14 to process it and the data associated with the main key is added to the migration destination DB 14. Information on the main key migrated is recorded on the migration table. When a request is made to the migrated main key, the request is transmitted to a server having the migration destination DB 14 to process it.

Description

  The present invention relates to a database (DB) migration method.

  Even if the system is to be used for a long period of time, the sales and support of the server equipment will eventually end, and the server equipment will have to be replaced with new hardware. For this reason, the database must be migrated at any stage.

  In recent years, the database of open source software (OSS) has been enriched, and the opportunity to migrate the database is increasing for the purpose of cost reduction.

  However, since the data is always searched and updated while the service that uses the database is running, it is necessary to stop the service and then migrate the data when switching the connection destination in the end . As the scale of the database increases, the time required for this data migration becomes enormous and the time for stopping the service becomes longer.

  On the other hand, a method like patent document 1 is proposed. While the service is in operation, the snapshot data of the database device before the migration is acquired and written into the migration destination database device, and the database device before the migration is continuously operated. Once the snapshot data at the previous time point has been written to the migration destination database device, the snapshot data of the database device before the migration after further operation is acquired, and the data at the previous time point remains as it is The snapshot data of the migration destination database device is also acquired, and this difference data is acquired. In the migration stage, only this difference data needs to be written into the migration destination database device.

JP 2011-232866 A

  However, even in the apparatus of Patent Document 1, the service must be stopped during the last time to write the difference data. Although this time can be shortened compared to the conventional method in which all data is written in a lump, the stop time cannot be ignored as the database update frequency increases.

  SUMMARY OF THE INVENTION Accordingly, an object of the present invention is to enable database migration without service stop time.

This invention
A database migration method for switching a database (DB) referred to by an application server from a server having a migration source database to a server having a migration destination database,
Changing the reference destination of the application server to a virtual database device accessible to both the migration source database and the migration destination database;
In response to a request from the application server, the virtual database device refers to a migration table that records the migration history of data in the migration source database and determines whether or not the primary key of the request has been migrated. ,
If the request is for a primary key that has not been migrated, the request is sent to the server having the migration source database for processing, and a record associated with the primary key is added to the migration destination database, and the migration is performed. Record the migration history for the primary key in the table,
If it is a request to the primary key that has been migrated, the process of sending the request to the server having the migration destination database and processing it to repeat the response to the request and record the migration and history,
The above problem has been solved by a database migration method that performs a step of changing the reference destination of the application server from the virtual database device to the migration destination database.

  Each time the virtual database device processes a request, the data is sequentially transferred to the transfer destination database. Therefore, most data can be transferred while operating without interrupting the service of the application server.

  Although the request is transferred while processing the request over a predetermined period, only the data associated with the primary key without the request is not transferred. The remaining amount can be determined by referring to the above migration table. Therefore, if the data of unmigrated items remains even after a predetermined period of time has passed, the data is left before changing the reference destination of the application server from the virtual database device to the server of the migration destination database. A step of migrating only data of unmigrated items from the migration source database to the migration destination database is performed. For data migration of unmigrated items, the service does not stop because the virtual DB sequentially accesses and migrates to the migration source database in the same manner as a request from the application server.

  According to the present invention, the data migration can be realized while suppressing the load on the system by migrating the data every time the data is accessed. Even if data is not accessed frequently, all data can be migrated without service interruption by accessing the virtual DB.

(A) Logical configuration example diagram in the transition process when the present invention is implemented, (b) Physical configuration example diagram in the transition process when the present invention is implemented Functional block diagram of a virtual database device introduced in the present invention The chart which shows the whole image of the transfer method concerning this invention Example flowchart in the migration process (A) Procedure diagram of processing example with transition in transition process, (b) Procedure diagram of processing example to migrated item in transition process Table group showing examples of migration tables Processing chart by the difference of SQL statement Final migration flowchart

  The present invention will be described in detail below. The present invention is a migration method for migrating a database referenced by an application server from a server having a migration source database (old server) to a server having a migration destination database (new server).

  In practicing the present invention, it is desirable that the database referred to by the application server be in an environment where it can be quickly changed by changing the address. This may be realized by changing the address of the database to which the application server itself is destined, or may be realized by rewriting the address specified by the load balancer existing in the network. If the server with the database is multiplexed, the migration process proceeds by rewriting the address specified by the load balancer so that only one of the multiplexed servers is connected. It is preferable to make it easier.

  An example of a logical configuration of a system that implements the present invention is shown in FIG. 1A, and an example of a physical configuration is shown in FIG. The application server 11 (sometimes abbreviated as “AP” in the figure) is a server that executes a command statement on the following database in response to an external request. The application server 11 may be multiplexed under the control of the load balancer 12 (sometimes abbreviated as “LB” in the figure). In the figure, the two servers (11a and 11b) are multiplexed. However, the number is not particularly limited, and may be multiplexed by a larger number of servers.

  The database used in the present invention is stored in a server having an address different from that of the application server 11. From an old server having a migration source database 13 (sometimes abbreviated as “migration source DB” in the figure) to a new server having a migration destination database 14 (sometimes abbreviated as “migration destination DB” in the figure). It is an object of the present invention to shorten the time for stopping the service of the application server 11 as much as possible when migrating data. In the stage before the start of the migration work, the new server having the migration destination database 14 is not attached, and the old server having the migration source database 13 is removed after the migration work is completed. Here, the old server and the new server are extracted and described as the migration source database 13 and the migration destination database 14 respectively.

  At the time of data migration in the present invention, the old server having the migration source database 13 and the new server having the migration destination database 14 receive access from the application server 11 via the virtual database device 15. The virtual database device 15 does not have a copy of the data itself as the database that the migration source database 13 and the migration destination database 14 have, but which items of the data of the migration source database 13 have been migrated. Is stored as table data, and is a server that makes a request to the migration source database 13 and the migration destination database 14 with reference to this migration table. It is desirable for this migration table to record not only whether or not it has been migrated, but also the date and time of migration indicating when it has been migrated, since it is easy to determine the final process to be described later. Further, when the data after migration is further updated, it is more desirable to record the update date and time because it can be referred to when returning data when an error occurs in the migration operation.

  As the actual physical configuration of the network having the logical configuration as described above, as shown in FIG. 1 (b), the application server 11a is connected to the routing device 17 which is composed of a router, an L3 switch, etc. and is connected to the external network. 11b, load balancer 12, old server having migration source database 13, new server having migration destination database 14, and virtual database device 15 are connected.

  A functional block diagram of the virtual database device 15 is shown in FIG. The input / output unit that is an interface on the software is a first input / output unit 22 as a virtual database that responds to the application server 11 and a second input / output that is a virtual application server for the migration source database 13 and the migration destination database 14. It has two with the part 23. In the figure, since they are functionally described, they are separated, but one actual physical interface may be used. In addition, the storage unit 24 including the above-described migration table 26 is provided. It is desirable that at least the migration table 26 is stored by a storage means such as a magnetic disk or a non-volatile memory that does not disappear even when the power is cut off. Furthermore, it has the control part 25 which is CPU which controls these input / output parts 22 and 23 and the memory | storage part 24. FIG. Upon receiving the request from the first input / output unit 22, the control unit 25 accesses the storage unit 24 and checks the migration table 26, and then the migration source database 13, the migration destination for the second input / output unit 23. An appropriate request is transmitted to the database 14 or both, the response is returned to the first input / output unit 22, and writing to the migration table 26 is performed as necessary. The migration table 26 may be stored in a server different from the virtual database device 15.

  However, if the database format is different due to different types or versions of the database software of the migration source database 13 and the migration destination database 14, the data of the migration source database 13 is migrated to the migration destination database 14 as it is. I can't do it. Therefore, when the database format of the migration source database 13 and the migration destination database 14 is different, a conversion unit 28 that is software for converting the data format of the migration source database 13 to the data format of the migration destination database 14 is provided. The data to be migrated is once converted from the format of the migration source database 13 by the conversion unit 28 and then written to the migration destination database 14.

  An outline of the migration procedure is shown in FIG. 3 (S100-). Before starting the work, the new server having the migration destination database 14 and the virtual database device 15 are not connected, and these are connected to the network for the work (S101). Specifically, it is physically connected to a terminal of the routing device 17 or a hub connected to the terminal using a cable. Since it is only necessary to simply connect to the terminal, the service disconnection time of the application server 11 necessary for this connection work becomes zero.

  Then, the destination address of the database to which the application server 11 sends the request is changed from the old server having the migration source database 13 to the virtual database device 15 (S102). This change may be realized by rewriting the address referred to by the application server 11 itself. Further, when the load balancer 12 exists in the network, the load balancer 12 can also be realized by rewriting an address that is a distribution destination of a shared address that receives a request from the application server 11 as a database destination. In order to eliminate the service disconnection time of the application server 11, it is more preferable to change the address assigned by the load balancer 12.

  When the reference destination of the application server 11 is changed to the virtual database device 15, thereafter, every time the application server 11 sends a request, the virtual database device 15 responds and sequentially proceeds with the migration work (S103). Further, after a certain period of time, a final migration process is performed as necessary (S104). These will be described in detail later.

  When the data migration from the migration source database 13 to the migration destination database 14 is completed, the destination address of the database to which the application server 11 sends a request is changed from the virtual database device 15 to a new server having the migration destination database 14 (S105). . This change may also be realized by rewriting the address referred to by the application server 11 itself. Further, when the load balancer 12 exists in the network, the load balancer 12 can also be realized by rewriting an address that is a distribution destination of a shared address that receives a request from the application server 11 as a database destination. As with the start, it is more preferable to change the address assigned by the load balancer 12 in order to eliminate the service disconnection time of the application server 11.

  When the destination of the application server 11 is changed, the old server having the virtual database device 15 and the migration source database 13 may be disconnected from the network (S106). In this case, the cable is physically pulled out from the terminal of the routing device 17 or a hub connected thereto, and then cut. Since it is only necessary to simply disconnect from the terminal, the service disconnection time of the application server 11 required for this disconnecting operation becomes zero. Thus, the migration work is completed (S107).

  Of the above steps, the actual migration processing of the virtual database device 15 in S103, which starts after the destination of the application server 11 is changed to the virtual database device 15, is shown in the flow of FIG. 4 and FIGS. 5 (a) and 5 (b). This will be described with reference to a schematic diagram.

  The virtual database device 15 that has received the request from the application server 11 (S201) searches the migration table 26 in which the history of data migration from the migration source database 13 to the migration destination database 14 is recorded with the primary key of the request (S202). . An example of the migration table 26 is shown in FIGS. The migration table 26 has at least a flag that can determine whether or not the primary key data has already been migrated for each primary key used in the migration source database 13 at the start of the migration operation. It is necessary. This flag may also serve as a transition date and time. In this case, if the migration has not been performed, the item of migration date / time is null. Moreover, it is preferable to have the same number of tables as the tables constituting the migration source database 13 at the start of the migration operation. In the migration table 26, a plurality of tables included in the migration source database 13 may be integrated into a smaller number of tables, but in this case, which data in the migration source database 13 corresponds to the primary key. Must be distinguishable.

  If the migration date / time is recorded in the item of the primary key in the migration table 26 or the dedicated migration flag is “completed” (S203 → YES), the data associated with the primary key is migrated. It is determined that it has been completed, and no transition is made (S221 to S221). On the other hand, if the migration date / time is not recorded or the dedicated migration flag is not migrated (S203 → NO), it is determined that the data associated with the primary key is not migrated (S211). ~), Perform the migration process. A schematic diagram of the flow after S211 is FIG. 5A, and a schematic diagram of the flow after S221 is FIG. 5B.

  The processing after S211 for performing the above-described migration will be described. That is, this is a case where the data of the item associated with the primary key has not been migrated. The second input / output unit 23 of the virtual database device 15 transmits a request as a virtual application server to the migration source database 13 (S211). When the second input / output unit 23 receives a response from the migration source database 13 (S212), the first input / output unit 22 responds to the application server 11 as a virtual database (S213). Before or after the response, or in parallel therewith, the second input / output unit 23 adds the contents corresponding to the response to the migration destination database 14 to the corresponding table as the main key item (S214). Then, a migration flag is set on the primary key item of the migration table 26 corresponding to the table, or the migration date / time is written to record that the migration of the data of the primary key item is completed (S215). Thereafter, every time there is a request for an item before migration, the same processing is repeated and the migration proceeds.

  On the other hand, the processing after S221 will be described. That is, the data of the item associated with the primary key has been migrated. The second input / output unit 23 of the virtual database device 15 transmits a request as a virtual application server to the migration destination database 14 (S221). When the second input / output unit 23 receives a response from the migration destination database 14 (S222), the first input / output unit 22 makes a response as a virtual database to the application server 11 (S223). That is, no particular request is transmitted to the migration source database 13. Although not shown, the update date and time provided for the item of the main key in the migration table 26 may be updated around S223. Thereby, the update date management of the migration destination database 14 is also possible.

  More specifically, the migration process (S103) according to the above request differs depending on the SQL statement. An example of the flow is shown in FIG.

  If the SQL statement is INSERT and additional processing, there is no corresponding item in the migration source database 13, so a value is added to the migration destination database 14 without checking the migration table 26. Then, the value is added to the migration table 26 and responds to the application server 11.

  If the SQL statement is DELETE, the migration table 26 is searched using the primary key, and if it is determined that the migration flag has already been established, the migration destination database 14 value is deleted. At the same time, the primary key value in the migration table 26 is also deleted, and the response to the application server 11 is made. If it is determined that the primary key has not been migrated yet, such as the migration flag is not set in the migration table 26, the value of the migration source database 13 is deleted and the application server 11 is responded.

  If the SQL statement is SELECT and search processing, the migration table 26 is searched using the primary key, and if it can be determined that the migration flag has already been set, the value in the migration destination database 14 is retrieved. Then, the result is returned to the application server 11. If it is determined that the primary key has not been migrated yet, such as the migration flag is not set in the migration table 26, the value of the migration source database 13 is searched and the result is returned to the application server 11. Further, a value is added to the table of the migration destination database 14 and the item of the primary key in the table of the migration table 26 is migrated.

  If the SQL statement is UPDATE update processing, the migration table 26 is searched with the primary key, and if it can be determined that the migration flag has already been set, for example, the migration key 26 in the migration destination database 14 is found. The data is updated, and the result is returned to the application server 11. If it is determined that the primary key has not been migrated yet, such as the migration flag is not set in the migration table 26 for the primary key, the primary key data in the source database 13 is updated and the primary key is stored in the destination database 14. To the application server 11 with the primary key item in the table of the migration table 26 as migrated. Here, the data in the migration source database 13 can be used without being updated, but the migration source database 13 can also be returned to the migration source database 13 when a problem such as an error occurs during the migration. It is desirable to update.

  By continuing the procedure as described above for a certain period, for the items that have been requested even during that period, the data recorded in the migration source database 13 is transferred to the migration destination database by the procedure as shown in FIG. 14. Also, new items are written in the migration destination database 14. As a result, most data can be transferred, and in some cases, there is a request for all data without waiting for a predetermined period, and the entire transfer may be completed. However, some data that is requested less frequently is included, and data may not be completely transferred. Therefore, even when a predetermined period has elapsed since the last data migration, when the migration of unmigrated data does not proceed and the migration work has practically converged, the virtual database device 15 sends a request from the application server 11. The next final migration process may be performed while processing. The final migration process flow will be described with reference to FIG.

  First (S104, S300), the migration date and time of the migration table 26 is confirmed. It is determined whether or not the latest date / time of the transition is before a predetermined period. The predetermined period is appropriately selected according to the scale and frequency of the database, such as one week, one month, or half a year. If the latest migration date and time is closer than the predetermined period, the database migration is still in progress and it is determined that the database has not yet converged (S301 → NO), and the final migration process is not performed (S309). On the other hand, if the latest migration date / time is earlier than the predetermined period (S301 → YES), items in the migration table 26 that are not migrated, such as the migration flag is not set or the migration date / time is not registered. Extract (S302). In place of this work, it is possible to extract unmigrated items by comparing data between the migration source database 13 and the migration destination database 14, but the migration table is likely to have a huge load and time on the server. It is preferable to easily extract unmigrated items by 26 search extractions. As a result of the extraction, if there is no unmigrated item, that is, if all data in the migration source database 13 has been migrated (S303 → NO), the process proceeds to a switching process described later without performing the final migration process (S308, S105, S106).

  If an unmigrated item remains (S303 → YES), for the extracted unmigrated item (S304 to S307), the data of the item in the source database 13 at that time is written in the destination database 14 ( (S305), the process of registering the migration flag and the migration date and time in the migration table 26 (S306) is sequentially executed. This completes the migration of all unmigrated items. After these steps are completed, as described above (S105), the setting of the application server 11 or the load balancer 12 is changed, and the reference destination address of the application server 11 is transferred from the virtual database device 15 to the migration destination database 14. Switch to a new server (S308). When switching is completed, the old server having the virtual database device 15 and the migration source database 13 may be disconnected from the network (S106). Thus, the migration work is completed (S107).

11, 11a, 11b Application server 12 Load balancer 13 Migration source database 14 Migration destination database 15 Virtual database device 17 Routing device 22 First input / output unit 23 Second input / output unit 24 Storage unit 25 Control unit 26 Migration table 28 Conversion unit

Claims (3)

A database migration method for switching a database referred to by an application server from an old server having a migration source database to a new server having a migration destination database,
Changing the reference destination of the application server to a virtual database device having a migration table that is accessible to both the migration source database and the migration destination database and records data migration records;
For the request from the application server, the virtual database device refers to the migration table to determine whether the data associated with the primary key of the request has been migrated,
If the request is for a primary key that has not been migrated, the request is sent to the source database for processing, the data associated with the primary key is added to the destination database, and Record that the primary key has been migrated,
If the request is for a primary key that has been transferred, send the request to the server that has the destination database and process it.
Steps to repeat the response to the request and record the transition and history,
A database migration method, comprising: changing a reference destination of the application server to a new server having the migration destination database. After the above transition and history recording steps,
Extracting data of unmigrated items and transferring them from the source database to the destination database in a batch;
The database migration method according to claim 1, wherein: In response to a request from the application server, a first input / output unit that operates as a virtual database server;
A migration table that records the presence or absence of migration from the migration source database to the migration destination database for the database data that processes the request,
For data that has not been migrated in the migration table, a second application that operates as a virtual application server that sends the request to the migration source database and records data that is a response to the request in the migration destination database. A virtual database device having an output unit.

Images