Data replication in a database environment

Embodiments generally relate data replication in databases. In some embodiments, a method includes accessing transaction information from a staging store at a first database. The method further includes determining one or more records to be prefetched from a hard disk of a second database based on the transaction information. The method further includes prefetching the one or more records to be prefetched from the hard disk. The method further includes storing the one or more prefetched records in a memory of the second database. The method further includes updating the one or more records stored in the second database based on the transaction information.

BACKGROUND

Database replication involves frequent, incremental copying of data from one database to another database in a continuous manner. This enables all end users to access, share, and update the same information. A result of database replication is a distributed database, where users can access relevant data without interfering with the work of other users. Database replication is also useful for database recovery and migration.

SUMMARY

Disclosed herein is a method for replicating data in databases, and a system and computer program product as specified in the independent claims. Embodiments are given in the dependent claims. Embodiments can be freely combined with each other if they are not mutually exclusive.

Embodiments improve data replication and user access to data in databases. Data replication in a database is improved by intelligently prefetching replicated data from a hard disk of a target database. User access to data in a database is improved by storing prefetched data in memory for immediate access.

In an embodiment, a method includes accessing transaction information from a staging store at a first database. The method further includes determining one or more records to be prefetched from a hard disk of a second database based on the transaction information. The method further includes prefetching the one or more records to be prefetched from the hard disk. The method further includes storing the one or more prefetched records in a memory of the second database. The method further includes updating the one or more records stored in the second database based on the transaction information.

In another embodiment, the transaction information includes data changes and database operations associated with one or more records stored at the first database. In another aspect, the transaction information includes database operations associated with one or more records stored at the first database, and where the database operations include one or more of selecting, inserting, updating, and deleting data associated with the one or more records stored at the first database. In another aspect, the updating of the one or more records stored in the second database includes determining a sequence of database operations and data changes associated with each record that has been updated in the first database, and updating the one or more records stored in the second database in a same sequence that corresponding records stored in the first database were updated. In another aspect, the prefetching of the one or more records to be prefetched further includes prefetching a plurality of records substantially simultaneously. In another aspect, the prefetching of the one or more records to be prefetched is performed before runtime. In another aspect, the prefetching of the one or more records to be prefetched is performed during a mirroring phase.

DETAILED DESCRIPTION

Embodiments described herein facilitate data replication and user access to data in databases. As described in detail herein, various embodiments improve data replication in databases by intelligently prefetching replicated data from a hard disk of a target database. This improves user access to data in a database because accessing prefetched data stored in memory is many times faster than accessing data from a hard disk.

In some embodiments, a system such as a database server associated with a target database accesses transaction information from a staging store at a first database. The transaction information includes historical information associated with data updates at a source database. As described in more detail herein, the system determines records to be prefetched from a hard disk of the target database based on the transaction information. The system then prefetches the records to be prefetched from the hard disk, and then stores the one or more prefetched records in a memory of the target database. Such prefetching occurs before runtime so that a cognitive target apply unit may immediately access desired data during runtime. The system updates the records stored in a target database based on the transaction information. For example, the system updates records at the target database in the same manner (e.g., same updates, same update sequences, etc.) as corresponding records at the source database. In various embodiments, the system updates the records in the memory of the target database for immediate access. The system may asynchronously update the records the hard disk of the target database.

FIG. 1is an example replication environment100including a source datastore102with a source database104, and a target datastore106with a target database108, according to some embodiments. Also shown is an access server110and clients114,116, and118. In various embodiments, the source datastore102, the source database104, the target datastore106, the target database108, the access server110, and the clients114,116, and118may communication with each other via communications network112, or any other suitable network.

In various embodiments, users may use client devices (e.g., clients114and116, etc.) to access the source database104to enter and update data in parallel. Users may also use client devices (e.g., client118, etc.) to access the same data in the target database108, and may use the data to run various applications such as business applications, analytics applications, etc., without interfering with usage of the source database104. Example embodiments and operations are described in more detail herein.

In some embodiments, the access server110controls all non-command line access to the replication environment. An administrator user logs into a management console associated with the access server110in order to configure or manage the replication processes. The access server110may manage replication processes without affecting active data replication activities between the source datastore102and the target datastore106.

While some embodiments described herein may be described in the context of a database server associated with the target database108performing various actions, other servers such as access server110may also perform various embodiments described herein. In various embodiments, any suitable component or combination of components associated with the target database108, the target datastore106, and/or the access server110, or any suitable processor or processors associated with these servers may facilitate performing the embodiments described herein. In various embodiments, the environment100may not have all of the components shown and/or may have other elements including other types of components instead of, or in addition to, those shown herein.

FIG. 2is an example environment200for updating the source database104, according to some embodiments. As indicated herein, in various embodiments, users may access the source database104to enter and update data in parallel. For example, a business may sell products online and may utilize the source database104to manage customer accounts, product sales, etc. A user such as a consumer may use a client device202to access the source database104to set up an account, purchase products, etc. There are processes (e.g., client process204, server process206, etc., that enable a variety of transactions during a particular session208. Such transactions result in information or data in the source database104being selected, added, changed, deleted, etc.

As indicated herein, multiple users may access the source database104in parallel. For example, another user may use a client device212to access the source database104to also set up an account, purchase products, etc. Again, there are processes (e.g., client process214, server process216, etc., that enable transactions during a particular session218, which result in information or data in the source database104being selected, added, changed, deleted, etc.

In various embodiments, the environment200may not have all of the components shown and/or may have other elements including other types of components instead of, or in addition to, those shown herein.

FIG. 3is an example environment300for updating the source database104and replicating data at the target database108, according to some embodiments. Also shown are the source datastore102and the target datastore104, which communicate with each other and with the access server110via the communications network112. The following descriptions detail embodiments associated with the updating of the source database104and replicating data at the target database108.

In various embodiments, when data is added, deleted, changed, etc. at the source database104, these transactions are tracked and logged in transaction logs302. Also shown are a log reader304, a log parser306, transaction queues308, and a staging store310.

In various embodiments, the environment300may not have all of the components shown and/or may have other elements including other types of components instead of, or in addition to, those shown herein.

In various embodiments, transaction information in the transaction logs302includes information on each record of the tables of the source database104that have been modified. In various embodiments, the tables are tables at a subscription level, where the system maintains transaction queues, which are described in the transaction information. Information in these transaction logs302may be scraped for data replication, as well as other purposes.

The log reader304reads the entire transaction log and sends in-scope logs or entries (e.g., logs for replication) to the log parser306. In some embodiments, the log reader304filters the log records from the transaction logs302in order to send just the in-scope log entries to the log parser306. A transaction log may also be referred to as a database log, database recovery log, archive log, or redo log.

The log parser306reads the log entry queues from the transaction logs302, parses the in-scope logs, parses row level operations, and stages transactions including data changes and associated database operations as log entries in a queue until complete (e.g., until an entry for a commit operation is shown). The log parser306then decodes the operations into the parsed entry queue. The log parser306puts the transactions into the transaction queues308.

The transaction queues308store all of the data for all in-scope transactions (both committed and uncommitted), and once committed, send the data to the staging store310. Each transaction queue of the transaction queues308includes data manipulation language (DML) statements that modify stored data but not the schema or database objects. For example, the DML statements may be used for selecting, inserting, updating, and deleting data in a database such as source database104. DML provides a straightforward approach for managing records by providing simple statements to insert, update, merge, delete, and restore records.

In various embodiments, the system records a particular sequence of data manipulations in transaction queues, including, for example, inserting, updating, deleting, etc. for each and every transaction that occurs in the source database. Once a transaction is committed, the transaction is recorded in the transaction logs, which are stored in the staging store310. The terms staging store, staging store repository, and change log may be used interchangeably. The staging store310holds committed transactions on the source database104waiting for the subscriptions to confirm that the target database108has applied the transactions.

Also shown are source subscriptions312,314, and316. In various embodiments, a subscription is a connection that is used to replicate data between the source database104and the target database108. Each of subscriptions312,314, and316contains details of respective transactions (e.g., a set of tables and their columns along with other change data capture (CDC) properties such as filter/user exit, expressions, etc.) that are being replicated; and each of subscriptions322,324, and326contains details of how the source data is applied to the target.

In various embodiments, CDC replication captures database changes as they happen and delivers the database changes to the target database108. CDC replication replicates the source data at the target database108while maintaining the transactional consistency. CDC replication to the target database108results in the same changes that occurred at the source database104.

In various implementations, a shared scrape or single scrape reads parsed log entries, and encodes the operations into network transport packets and sends them to the target database108through the communications network112. Transmission may utilize transmission control protocol/Internet protocol (TCP/IP) or any other suitable communication protocols.

When the data is transferred from the source database104to the target database108, the data can be remapped and/or transformed in the target environment. For example, if there are 100 tables in the source database104, and a user selects 10 or 20 tables to replicate, these 10 or 20 tables will be called in-scope tables, which are part of the replication and which are to be replicated from the source database104to the target database108.

In various embodiments, subscriptions are mapped between source tables to target tables. Subscriptions on the source side are called source subscriptions (e.g., source subscriptions312,314, and316) and subscriptions on the target side are called target subscriptions (e.g., target subscriptions322,324, and326). Subscriptions also hold information about the type of mapping, for instance, standard, adaptive apply, or live audit, etc.

In various embodiments, for each subscription, tables stored in the source database104are associated with and mapped to tables stored in the target database108. The table structures are the same on both mapped tables, though may differ depending on the particular implementations and requirements. For example, the number of columns can be more or less the same depending upon the requirements.

FIG. 4is an example target database108, according to some embodiments. The following descriptions detail embodiments associated with operations of the target database108, including data replication and prefetching. Also shown are an image builder402, a cognitive target apply unit404, a database server406, a hard disk408, records410,412, and414, and a memory416.

In various embodiments, the target database108may not have all of the components shown and/or may have other elements including other types of components instead of, or in addition to, those shown herein.

The image builder402formats DML statements to be applied at the target database108. The cognitive target apply unit404maintains a history of transactions and their records from the staging store at the source datastore102, and prefetches those records just before applying the transactions to the target database108.

In various embodiments, the database server406may function to fetch the transaction logs302and any other information (e.g., CDC information, etc.) from the source datastore102and/or source database104. The database server406may perform various functions associated with replicating data from the source database104to the target database108. For example, database server406physically locates a requested record on data files stored on the hard disk408, reads from the hard disk408, and copies the record into the memory416.

The memory416refers to random access memory (RAM). The memory416can be accessed hundreds of times faster than a hard drive, which is why active programs are loaded into the memory. The database server406can access records stored in the memory416nearly instantly. In contrast, items stored on the hard disk408need to be located, read, and then sent to the memory416before being processed. Data records are typically stored on the hard disk408(in data files). The terms hard disk and disk are used interchangeably. The hard disk may also be referred to as non-volatile memory. If a record has not been accessed and is not present in the memory416, the database server406performs physical disk input-output (TO) operations, which include locating the record on the hard disk408, reading that record (e.g., record410,412, or414), and then copying the record into the memory416for processing. The database server406can then read the record directly from the memory416, also referred to as a logical IO operation. A logical IO operation is hundreds of times faster than a physical IO operation. As described in more detail herein, in various embodiments, the system updates the records in the memory of the target database for immediate access. These updates are based on the transaction information retrieved from the staging store310. The system may asynchronously update the records the hard disk of the target database.

When the user tries to read a record of a table, the user sends a command or simple statement (e.g., SELECT employee, name, salary from employee where employee ID=100) to the database server406, and can then view the record when the database server406returns the record. This request-response process occurs instantly, as the requested record is already prefetched and stored in the memory416. In various embodiments, once a record is stored in the memory416, the database server406may send the record to the user as a response to the user request for the record. The user can then see the record. In various embodiments, the cognitive target apply unit404may fire a SELECT statement based on a table key using the information from staging store310and will pre-fetch the records from hard disk408into the memory416bit earlier or in advance (before runtime) so that the cognitive target apply unit404can update those records in the memory416in a fast manner.

FIG. 5is an example flow diagram for replicating data in databases, according to some embodiments. As indicated herein, various embodiments improve data replication in databases by intelligently prefetching replicated data from a hard disk of a target database. This improves cognitive target apply unit404access to data in a database because accessing prefetched data stored in memory is many times faster than accessing data from a hard disk.

Referring to bothFIGS. 3, 4, and 5, a method begins at block502, where a system such as a database server associated with target datastore106accesses transaction information from the staging store310at the source database104. In some embodiments, access server110may facilitate and/or manage and/or perform various embodiments and described herein. In some embodiments, the transaction information includes data changes and database operations associated with one or more records stored at the source database. For example, in various embodiments, the database operations include one or more of selecting, inserting, updating, and deleting data associated with the one or more records stored at the source database.

At block504, the system determines one or more of the records410,412, and414to be prefetched from the hard disk408of the target database108based on the transaction information.

At block506, the system prefetches the one or more records410,412, and414to be prefetched from the hard disk408. In some embodiments, the prefetching of the one or more records to be prefetched further includes prefetching multiple records substantially simultaneously. In various embodiments, the prefetching of the one or more records to be prefetched is performed before runtime. As described herein, this enables the cognitive target apply unit404to access records immediately during runtime. In some embodiments, the prefetching of the one or more records to be prefetched is performed during a mirroring phase.

As indicated herein, the staging store holds all information needed to replicate records at the target database108, including the order of transactions. This information provides a full history of source transactions. Prefetching may be done using the keys of the tables, which the system also stores in metadata. In some embodiments, the prefetching logic is on the target side and the staging store repository assists for each and every record that needs to be prefetched.

At block508, the system stores the one or more prefetched records410,412, and414in the memory416of the target database108.

At block510, the system updates one or more records (e.g., records410,412, and414, etc.) in the memory416of the target database108based on the transaction information. In some embodiments, when updating the one or more records stored in the target database, the system determines a sequence of database operations and data changes associated with each record that has been updated in the source database. The system also updates the one or more records stored in the target database in a same sequence that corresponding records stored in the source database were updated. In other words, records are already brought from the hard disk408into the memory416, and the cognitive target apply unit404may fire the update/delete and updates the records. The database process may asynchronously writes to data files on the hard disk408.

After the prefetched records410,412, and414in the memory416are updated those prefetched records identical to the corresponding records stored at the source database104. As indicated herein, the target apply may then access and process records instantly, which increases throughput and efficiency for users.

Although the steps, operations, or computations may be presented in a specific order, the order may be changed in particular embodiments. Other orderings of the steps are possible, depending on the particular embodiment. In some particular embodiments, multiple steps shown as sequential in this specification may be performed at the same time. Also, some embodiments may not have all of the steps shown and/or may have other steps instead of, or in addition to, those shown herein.

The following describes additional embodiments that may be applied and/or combined with other embodiments described herein. In some embodiments, the replication process may occur in phases. For example, in an initial synchronization phase, initial data is synchronized from source database104to the target database108when the source database104is interacting with users, and inserts/updates/deletes are performed in parallel when the refresh is running. During the initial synchronization phase, the transaction logs are generated. The initial synchronization phase may also be referred to as a “refresh while active” phase.

A mirroring phase, or continuous mirroring phase, is a second phase of replication that occurs after the initial synchronization phase. The transaction logs are scraped, and incremental changes are sent to the target database on a continuous basis. Embodiments described herein are applied in the mirroring phase.

In some embodiments, the system performs the replication of changes to target tables in the target database. The system may also accumulate source table changes, and replicate those changes to the target table at a later time. If bidirectional replication is implemented, mirroring can occur to and from both the source and target tables.

In some embodiments, after a refresh, changes in the transaction log are read and sent to a target database on a continuous basis. As its name implies, continuous mirroring replicates changes to the target database on a continuous basis. During the mirroring phase, and after a replication source engine captures the change data from the database transaction logs, committed change data is placed into replication metadata in a “replication transaction repository.” The replication transaction repository stores the data and metadata both for each transaction in the same fashion as it occurred in the source database. After committed data is stored in the replication transaction repository, the committed data may be sent to a target replication engine where the target engine may apply the DMLs in the target database row-by-row and in the same sequence as it occurred on the source database. During a target apply, especially for the update and delete operations, the rows are read from the hard disk408and brought into the memory416and then DML statements (e.g., update, delete, etc.) get executed in the memory416.

FIG. 6is a block diagram of an example computer system600, which may be used for embodiments described herein. The computer system600is operationally coupled to one or more processing units such as processor606, a memory601, and a bus609that couples various system components, including the memory601to the processor606. The bus609represents one or more of any of several types of bus structure, including a memory bus or memory controller, a peripheral bus, an accelerated graphics port, and a processor or local bus using any of a variety of bus architectures. The memory601may include computer readable media in the form of volatile memory, such as random access memory (RAM)602or cache memory603, or storage604, which may include non-volatile storage media or other types of memory. The memory601may include at least one program product having a set of at least one program code605that are configured to carry out the functions of embodiment of the present invention when executed by the processor606. The computer system600may also communicate with a display610or one or more other external devices611via I/O interfaces607. The computer system600may communicate with one or more networks, such as communications network112, via network adapter608.