Patent Publication Number: US-2023140730-A1

Title: System to copy database client data

Description:
BACKGROUND 
     Traditional computing system architectures include one or more servers executing applications which access data stored in one or more database systems. Users interact with an application to view, create and update the data in accordance with functionality provided by the application. The data may conform to an application-specific schema and the servers and database systems may be located on-premise and/or in cloud-based datacenters. 
     It is often desired to replicate the data of one client (i.e., source client) in another client (i.e., target client). Replication, also referred to as copying herein, describes a process which results in the data of the target client being identical to the data of the source client. In one example, the source client may reside in a production system and the target client might reside in a development system. Copying the data of the production system to the development system may allow testing of updated software on “real” data in the development system without disturbing the production system. 
     Conventionally, copying the source data of a source client to a target client begins by deleting all data from the target client. Once the deletion is complete, all the source data is copied from the source client to the target client. The copying process is similar regardless of whether the source client and the target client reside in the same or different database systems. In the latter case, the data is typically transferred from the database of the source client to the database of the target client over a Remote Function Call (RFC) connection. 
     The above-described copying significantly increases Central Processing Unit (CPU) consumption and memory load within the database system of the target client. These increases are particularly severe in the case of database systems which receive data into write-optimized data structures and subsequently merge the received data with read-optimized data structures. 
     Systems are desired to improve the efficiency with which source data of a source client is copied to target data of a target client. 
    
    
     
       BRIEF DESCRIPTION OF THE DRAWINGS 
       FIGS. lA through  1 D illustrate copying of source data of a source client to target data of a target client according to some embodiments. 
         FIG.  2    is a block diagram of a database system according to some embodiments. 
         FIG.  3    is a flow diagram of a process to copy source data of a source client to target data of a target client according to some embodiments. 
         FIG.  4    is a user interface of a local client copy tool according to some embodiments. 
         FIG.  5    is a flow diagram of a process to selectively use one of two algorithms to copy source data of a source client to target data of a target client according to some embodiments. 
         FIG.  6    is a tabular representation of a portion of a copy execution log according to some embodiments. 
         FIG.  7    is a block diagram of two database systems according to some embodiments. 
         FIG.  8    is a flow diagram of a process to copy source data of a source client to target data of a remote target client according to some embodiments. 
         FIG.  9    is a block diagram of a cloud-based database system according to some embodiments. 
     
    
    
     DETAILED DESCRIPTION 
     The following description is provided to enable any person in the art to make and use the described embodiments and sets forth the best mode contemplated for carrying out some embodiments. Various modifications, however, will be readily apparent to those in the art. 
     Some embodiments address the foregoing by providing a copying process which does not require deleting of all data from a target client followed by copying of all source data to the target client. According to some embodiments, only those records of the target data of the target client which do not exist in the source data are deleted, as opposed to deletion of all the records of the target data of the target client. Next, the target data is updated to insert records of the source data which do not exist in the target data and records of the target data which are different from corresponding records in the source data are updated. The foregoing process may significantly decrease the execution time and the memory footprint of the copy process. 
     Embodiments may copy source data managed by a database system to target data managed by the same database system. Such a database system may execute Structured Query Language (SQL) operators (e.g., NOT EXISTS or EXCEPT) for comparing the managed data during the copying process. These operators are not available to compare source data and target data which are managed by different database systems. Accordingly, in a case that the source data of the source client and the target data of the target client are managed by different database systems, embodiments may retrieve the source data into the database system of the target client and perform the above-mentioned comparisons, insertions and deletions. 
     It may be preferable in some scenarios to perform the conventional source-to-target copy process described in the Background. For example, the conventional process may be faster and/or consume fewer resources if the source data of the source client differs significantly from the target data of the target client. The data may differ significantly if the target data is empty, or if the source client is a first organization and the target client is a different unrelated organization some scenarios, for example. Some embodiments may therefore operate to selectively perform the conventional copy process or the process described below based on current conditions. 
       FIGS.  1 A through  1 D  illustrate a copy process according to some embodiments. For simplicity,  FIGS.  1 A through  1 D  assume that table  110  is the sole database table of source data of a source client, and table  120  is the sole database table of target data of a target client. Source data according to some embodiments may include any number of database tables and target data according to some embodiments may include any number of database tables. The number of database tables in the source data and the target data need not be equal, and zero or more of the database tables of the source data may share the same schema and semantics of a corresponding database table of the target data. 
     Tables  110  and  120  may be respectively stored in any type of database systems that are or become known. As will be described below, table  110  and table  120  may reside in a same or different database system. According to some embodiments, table  110  and table  120  may comprise records of a same database table stored in a single database system, with the records of table  110  being associated with a client ID (not shown) of the source client and the records of table  120  being associated with a client ID (not shown) of the target client. 
     Each record of each of tables  110  and  120  is associated with a key stored in key column Key. As is known in the art, within a given database table, each key is a unique identifier of the associated record of the table. As is also known in the art, a key may span one or more database columns. 
     It will be assumed that a process is initiated to copy source table  110  to target data, which currently includes table  120 . As noted above, “copying” in this context refers to a process which results in the target data being identical to the source data. As will be described below, the process may be initiated by a database administrator. 
     As shown in  FIG.  1 B , the process initially includes deletion of records of table  120  of which identical records do not exist in source table  110 . Specifically, it is determined that the record of table  120  identified by key=4 does not identically exist in table  110  because table  110  does not include a record having key=4. It is also determined that the record of table  120  identified by key=2 does not identically exist in table  110  because, although table  110  includes a record having key=2, the value of non-key field Col 2  of the record in table  110  (i.e., D) is different from the value of non-key field Col 2  of the corresponding record in table  120  (i.e., F). Accordingly, the records of table  120  identified by keys=2 and 4 are deleted therefrom, leaving the record associated with key=1. 
     Next, records of table  110  are identified which do not exist in table  120 . With respect to the former inquiry, the records of table  110  having keys=2, 3 and 5 are identified as not existing in table  120 . As illustrated in  FIG.  1 C , table  120  is then updated based on the thusly-identified records  130 . More specifically, and as shown in  FIG.  1 D , the identified records  130  which do not exist in table  120  are inserted in table  120 . As a result, target table  120  is identical to source table  110 . 
       FIG.  2    is a block diagram of database system  200  according to some embodiments. It will be assumed that database system  200  stores data associated with a source client and data associated with a target client. Database system  200  may implement some embodiments to copy data of the source client to the data of the target client as described herein. Such a copying operation may be referred to as a local client copy. 
     The illustrated elements of database system  200  may be implemented using any suitable combination of computing hardware and/or software that is or becomes known. In some embodiments, two or more elements of system  200  are implemented by a single computing device. One or more elements of system  200  may be implemented as a cloud service (e.g., Software-as-a-Service, Platform-as-a-Service). 
     Database system  200  includes node  210  including application server  215  and database  220 . Node  210  may support multi-tenancy to separately support multiple unrelated clients by providing multiple logical database systems whose logic and data are programmatically isolated from one another. A user of a first client who accesses node  210  is only provided with logic and data of the first client, while a user of a second client who accesses node  210  is only provided with logic and data of the second client. 
     Generally, application server  215  provides an execution environment and services for executing database applications, and database  220  is a query-responsive system for providing data to and storing data received from the applications. Some embodiments may provide one or more servers implementing application server  215  and a separate one or more servers implementing database  220 . 
     Server node  210  may receive a query from one of client UI applications  230  and  240  and return results thereto based on data stored within database  220  and on data processing provided by an application executing on application server  215 . For example, Web applications executing on application server  215  may receive Hypertext Transfer Protocol (HTTP) requests from client UI applications  240  as shown in  FIG.  2   . 
     Database  220  includes database management system (DBMS)  221  as is known in the art. DBMS  221  may receive and respond to database client requests and manage database transactions. DBMS  221  may also provide maintenance and administrative services including but not limited to garbage collection, backup and restore, transaction roll-back, configuration, storage optimization, a statistics service, etc. According to some embodiments, DBMS  221  includes processor-executable program code of a client copy tool which is executable to cause database  220  to perform a copying process as described herein. Such services and tools may be invoked and database  220  otherwise managed by an administrator via administrator UI  250 . 
     Database  220  includes various database components such as query processor  221 , which may comprise any suitable query processor that is or becomes known. Generally, query processor  221  receives a query (e.g., an SQL or Multi-Dimensional eXpression (MDX) statement) from a client, determines a query execution plan to execute the query against the data of storage device  224 , executes the plan to produce a result set, and provides the result set to the client. Query processor  221  may also process SQL statements issued by various database management tools such as a client copy tool based on data stored in storage device  224 . 
     Storage device  224  may comprise a persistent storage device such as but not limited to one or more hard disks, one or more Non-Volatile Random Access Memory devices, one or more Flash devices, etc. The data of storage device  224  may comprise one or more of conventional tabular data, row-stored data, column-stored data, and object-based data. Moreover, the data may be indexed and/or selectively replicated in an index to allow fast searching and retrieval thereof. 
     Metadata  225  includes data describing a database schema to which database tables  226  conform. Metadata  225  may therefore describe columns and properties of tables  226 , the properties of each column of each table  226 , the interrelations between the columns, and any other suitable information. Different ones of database tables  226  may be associated with different clients, as defined by metadata  225 , while a single one of database tables  226  may include data of one or more clients, logically segregated by corresponding client IDs. 
     Database  220  may implement an “in-memory” database, in which a full database is loaded from storage device  224  and stored in volatile (e.g., non-disk-based) memory (e.g., Random Access Memory) of database  220  for use during database operation. Embodiments are not limited to an in-memory implementation. For example, data may be stored during operation in Random Access Memory (e.g., cache memory for storing recently-used data) and in storage device  224 . 
       FIG.  3    comprises a flow diagram of process  300  to copy source data of a source client to target data of a target client according to some embodiments. Process  300  and all other processes mentioned herein may be embodied in processor-executable program code read from one or more of non-transitory computer-readable media, such as, for example, a hard disk drive, a volatile or non-volatile random access memory, a DVD-ROM, a Flash drive, and a magnetic tape, and then stored in a compressed, uncompiled and/or encrypted format. A processor may include any number of microprocessors, microprocessor cores, processing threads, or the like. In some embodiments, hard-wired circuitry may be used in place of, or in combination with, program code for implementation of processes according to some embodiments. Embodiments are therefore not limited to any specific combination of hardware and software. 
     Initially, at  5310 , an instruction is received to copy source data of a source client to target data of a target client. The instruction may be received from an administrator operating an administration UI of a DBMS. For example, a database administrator may access administrator UI  250  to submit the instruction to DBMS  221 . Embodiments are not limited thereto. 
       FIG.  4    illustrates user interface  400  which may be presented by administration UI  250  according to some embodiments. User interface  400  may be presented by a Web browser executed by a computing device operated by an administrator. The administrator may operate the Web browser to access a Uniform Resource Locator associated with DBMS  221  and may be presented with user interface  400  in response. 
     User interface  400  includes input area  410  to specify a source client and a target client. Each client may be specified by a client ID in some embodiments. If the source data of the source client and the target data of the target client are stored on and managed by the same database system, the database system may execute standard query language statements which reference both the source data and the target data. 
     Input area  420  allows the administrator to specify various optional constraints of the copy operation. For example, the Use Profile control of area  420  allows the administrator to specify certain types of source data to be coped to the target data. The Table Selection control of area  420  allows the specification of particular database tables to copy. The administrator may select Execute control  430  to transmit an instruction to copy source data of the specified source client to target data of the specified target client, based on the constraints (if any) indicated in area  420 . The instruction is received at S 310 . 
     After receipt of the instruction at S 310 , records of the target data which do not exist in the source data are deleted at S 320 . For example, any record of the target data which does not have an identical counterpart in the source data is deleted from the target data at S 320 . According to some embodiments, S 320  is performed by transmitting a request to a query processor such as query processor  222  to execute the statement:
     DELETE &lt;target_client&gt; WHERE NOT EXISTS(SELECT &lt;source_client&gt;)   

     Next, at S 330 , records of the source data which do not exist in the target data are inserted into the target data. S 330  may comprise inserting any record of the source data having a key which does not exist in a corresponding table of the target data into the target data. According to some embodiments, S 330  may be performed by requesting a query processor to execute the statement: 
                                            INSERT &lt;target_client&gt; FROM (            SELECT &lt;source_client&gt; EXCEPT            SELECT &lt;target_client&gt; )                        
After completion of process  300 , the records of the target data are identical to the records of the source data.
 
     In some scenarios, it may be desirable to perform a conventional copy operation instead of a copy operation as described with respect to  FIGS.  1 A- 1 D  and/or  FIG.  3   . Generally, it is desirable to perform whichever copy operation will result in the smallest overall resource consumption. 
       FIG.  5    comprises a flow diagram of process  500  to selectively perform either a conventional copy operation or a copy operation according to some embodiments. Initially, as described above, an instruction to copy source data of a source client to target data of a target client is received at S 510 . 
     Next, at S 520 , it is determined whether the source client of a last copy operation to the target client was the same source client as identified in the received instruction. S 520  may include review of a client copy execution log which identifies the chronological order of all previously-executed copy operations, as well as the source client and target client of each copy operation. If the source client of a last copy operation to the target client was the same source client identified in the received instruction, it is assumed that the amount of data to delete and insert at the target client using the process of S 320  and S 330  is significantly less than would be the case if the source data and the target data were substantially unrelated. Accordingly, in such a situation, flow proceeds from S 520  to execute S 530  and S 540  as described with respect to S 320  and S 330 . 
     If the source client of a last copy operation to the target client was not the source client identified in the received instruction, it may be assumed that the source data and the target data are substantially different, such that the process of S 320  and S 330  would consume more resources than the conventional copy operation described above. Accordingly, if the source client of a last copy operation to the target client was not the source client identified in the received instruction, flow proceeds from S 520  to S 550  to delete all the target data of the target client and copy all of the source data to the target data. 
       FIG.  6    is a tabular representation of a portion of client copy execution log  600 . Log  600  is presented to illustrate operation of process  500  according to some embodiments. Each record of log  600  is associated with a previously-executed copy operation to target client  200 , and the Execution field of log  600  indicates the chronological order in which the copy operations were executed. The Copied Data field indicates the type of copy operation used at each execution, with Full indicating the conventional copy operation, and Delta indicating a copy operation as described herein. 
     As shown, Execution 2 was performed as a Delta copy operation because the source client (i.e.,  100 ) of Execution 2 is the same as the source client of prior Execution 1. After Execution 2, an instruction was received to copy data of source client  300  to data of target client  200 . Since the source client was not the same as the source client of prior Execution 2, Execution 3 was a Full copy operation. 
     The next two executions (4 and 5) were associated with a same source client and target client as Execution 2 and therefore each consisted of Delta copy operations. Execution 6 was triggered by an instruction to copy data of source client  100  to data of target client  200 . Since the source client  100  was different than the source client of prior Execution 5, Execution 6 was a Full copy operation. Finally, Execution 7 was a copy operation of source client  300  to data of target client  200 . Since the source client (i.e.,  300 ) was different than the source client of prior Execution 5 (i.e.,  100 ), Execution 7 was also a Full copy operation. 
       FIG.  7    is a view of architecture  700  according to some embodiments. Architecture  700  includes database  710  storing data of a first client in tables  716  of storage  714 , and database  720  storing data of a second client in tables  726  of storage  724 . Either of databases  710  or  720  may be implemented on-premise or in a cloud service. Since the data of each client are located in different database systems, the data of one client cannot be copied to the other client using the copy operation described above with respect to  FIGS.  1 A- 1 D  and/or  FIG.  3   . Specifically, SQL comparison operations (e.g., NOT EXISTS, EXCEPT) used to implement the above-described copy operation cannot be used unless the source data and the target data are located within a single database system. 
     Database  710  includes remote client copy interface  717  and database  720  includes remote client copy interface  727 . Interfaces  717  and  727  may be used to transmit data from whichever of databases  710  or  720  stores source data of a copy operation to the other database (i.e., which stores target data of the copy operation) according to some embodiments as will be described below. 
     Process  800  of  FIG.  8    provides a copy operation in a case that the source data and the target data reside in different database systems according to some embodiments. Process  800  assumes that an instruction has been received to copy source data from a source client to target data of a target client. The instruction is received by the database system of the target client, and process  800  is executed by the database system of the target client. With respect to  FIG.  7   , an administrator may operate administrator UI  725  to submit an instruction to database system  720  to copy source data of a source client stored within tables  716  of database system  710  to target data of a target client stored within tables  726  of database system  720 . 
     The source data is requested from the source client database system at  5810  in response to the instruction. For example, database system  720  uses remote client copy interface  727  to request all of the source data from remote client copy interface  717  over a Remote Function Call (RFC) connection. The received instruction to perform the copy operation may specify details of the RFC connection in order to facilitate the request at S 810 . 
     In response to the request, the source client database system may select all of the source data and serialize the selected data into temporary storage. The serialized data is then transferred (e.g., over the RFC connection) to the database system of the target client. The source data is received thereby at S 820 . 
     At S 830 , a first record of the source data is selected in the database system of the target client. It is then determined at S 840  whether the source data record exists in the target data of the target client. The determination at S 840  may include determination of whether a key of the source data record is identical to a key of a record of the target data. If not, the source data record is added to an update table of the target database system at S 860 . 
     If a data record corresponding to the source data record exists in the target data, flow proceeds to S 850  to determine whether the non-key fields of the two records are identical. If not, the source data record is added to the update table at S 860 . If the non-key fields of the two records are identical, flow proceeds to S 870  and returns to S 830  if more source data records exist. Similarly, flow proceeds from S 860  to S 870  each time a source record is added to the update table. 
     Flow therefore cycles between S 840  and S 870  until all source data records have been processed. Once all source data records have been processed, the update table will include source data records which correspond to a target data record but which are not identical to the corresponding target data record. The update table will also include source data records which do not correspond to any target data record. 
     Next, at S 880 , all data records of the target client which do not exist in the source data records are identified (e.g., via their keys) and deleted. S 880  may occur prior to or during the above-described population of the update table. The data records of the target client are then updated at S 890  based on the records of the update table. For example, each record of the update table which shares a key with a record of the target data is used to update the record of the target data, and each record of the update table which does not share a key with a record of the target data is inserted into the target data. As a result, the target data of the target client and the source data of the source client are identical. 
     Some embodiments of process  800  may include logic to ensure that the size of the update table does not adversely affect memory usage and/or processing efficiency. For example, S 860  may include a determination of whether a size of the update table has exceeded a pre-defined and/or dynamic threshold. If the size exceeds the threshold, the data records of the target client may be updated as described with respect to S 890  based on the records of the current update table, after which the current update table is deleted and flow proceeds from S 860  to S 870 . 
       FIG.  9    is a block diagram of landscape  900  according to some embodiments. Landscape  900  includes administrator system  910 , cloud-based database  920  and cloud-based database  930 . Databases  920  and  930  may be implemented within one or more public clouds providing self-service and immediate provisioning, autoscaling, security, compliance and identity management features. 
     Either or both of database systems  920  and  930  may execute program code to execute one or more of the processes described herein. In one example, a Web browser executing on administrator system  910  may be operated to instruct database system  920  to initiate a copy of source data of database system  930  to target data of database system  920  as described with respect to process  800 . 
     The foregoing diagrams represent logical architectures for describing processes according to some embodiments, and actual implementations may include more or different components arranged in other manners. Other topologies may be used in conjunction with other embodiments. Moreover, each component or device described herein may be implemented by any number of devices in communication via any number of other public and/or private networks. Two or more of such computing devices may be located remote from one another and may communicate with one another via any known manner of network(s) and/or a dedicated connection. Each component or device may comprise any number of hardware and/or software elements suitable to provide the functions described herein as well as any other functions. For example, any computing device used in an implementation some embodiments may include a processor to execute program code such that the computing device operates as described herein. 
     Embodiments described herein are solely for the purpose of illustration. Those in the art will recognize other embodiments may be practiced with modifications and alterations to that described above.