Schema agnostic migration of delineated data between relational databases

Patent metadata

Field	Value
Publication number	US-11232084-B2
Application number	US-202016914060-A
Country	US
Kind code	B2
Filing date	Jun 26, 2020
Priority date	Jun 26, 2020
Publication date	Jan 25, 2022
Grant date	Jan 25, 2022

Abstract

Initially, a database schema is parsed and a table tree structure is created delineating the relationships between data that are identified in the schema. In addition to accommodating relationships between main tables of data, the table tree structure also accommodates possible side tables of data, and possible circular references between tables, should such be encountered when parsing the schema. Subsequently, a migration mechanism consumes the generated table tree structure and iteratively migrates data in accordance therewith. Individual layers of the table tree structure are migrated consecutively with referenced layers being migrated prior to referencing layers. Circular links are accommodated through temporary null values, and side tables are accommodated during migration of the referencing main table. The iterative process provides completeness and fault tolerance/failure recovery.

First claim

I claim:

1. One or more computer-readable storage media comprising computer-executable instructions, which, when executed by one or more processing units, cause one or more computing devices, in aggregate, to:
generate a table tree structure from a relational database schema of a source relational database, the table tree structure comprising:
a first node corresponding to a first table in the source relational database, the first node being at a first level of the table tree structure, the first table comprising a first record having an identifier specified in a primary key column of the first table;
a second node corresponding to a second table in the source relational database, the second node being at a second level of the table tree structure that is one level further from a root node than the first level, wherein a first record of the second table references the first record of the first table by specifying, in a foreign key column of the second table, the identifier of the first record of the first table that is specified in the primary key column of the first table; and
a first link commencing at the second node and terminating at the first node; and

migrate data from the source relational database to a destination relational database by iteratively traversing the table tree structure, the iterative traversal of the table tree structure comprising a first traversal of the table tree structure that comprises:
reading a migration table to determine whether the first record of the second table has already been migrated, the migration table correlating old identifiers of records in the source relational database to new identifiers of corresponding migrated records in the destination relational database;
reading the migration table to determine whether the first record of the first table has already been migrated; and
migrating the first record of the second table from the source relational database to the destination relational database in response to determining, from the reading of the migration table, that the first record of the first table has been migrated but the first record of the second table has not been migrated.

2. The computer-readable storage media of claim 1 , wherein the first traversal of the table tree structure further comprises:
inserting a first record into the migration table, the first record comprising an old identifier of the first record of the second table in the source relational database and a corresponding new identifier of a migrated copy of the first record of the second table in the destination relational database.

3. The computer-readable storage media of claim 1 , wherein a copy of the migration table exists in both the source relational database and in the destination relational database.

4. The computer-readable storage media of claim 1 , migrating the first record of the second table from the source relational database to the destination relational database comprises changing the identifier of the first record of the first table that is specified in the foreign key column of the second table to a corresponding new identifier obtained from the migration table.

5. The computer-readable storage media of claim 4 , wherein the determining that the first record of the first table has been migrated comprises finding a first record in the migration table corresponding to the first record of the first table, the first record in the migration table comprising:
the identifier of the first record of the first table that is specified in the primary key column of the first table in the source relational database; and
the corresponding new identifier, which is an identifier of the migrated first record in a corresponding first table in the destination relational database.

6. The computer-readable storage media of claim 1 , wherein the first traversal of the table tree structure further comprises:
migrating records of tables corresponding to nodes at the first level of the table tree structure before migrating records of tables corresponding to nodes at the second level of the table tree structure.

7. The computer-readable storage media of claim 1 , wherein the table tree structure further comprises:
a third node corresponding to a third table in the source relational database, the third node having no level assigned to it; and
a second link commencing at the second node and terminating at the third node;
wherein the first and second nodes are main nodes having links to higher levels of the table tree structure that, directly or indirectly, link to a root node; and
wherein further the third node is a side node having no links to higher levels of the table tree structure that, directly or indirectly, link to the root node.

8. The computer-readable storage media of claim 7 , wherein the first traversal of the table tree structure further comprises:
migrating records of the third table that are referenced by records of the second table prior to migrating the records of the second table.

9. The computer-readable storage media of claim 8 , wherein the first traversal of the table tree structure further comprises:
migrating records of a fourth table that are referenced by the records of the third table that are referenced by the records of the second table prior to migrating the records of the third table that are referenced by records of the second table;
wherein the table tree structure further comprises:
a fourth node corresponding to the fourth table;
a third link commencing at the third node and terminating at the fourth node;
wherein the fourth node is also a side node no links to higher levels of the table tree structure that, directly or indirectly, link to the root node.

10. The computer-readable storage media of claim 1 , wherein the first traversal of the table tree structure further comprises:
migrating the first record of the second table from the source relational database to the destination relational database in response to determining, from the reading of the migration table, that the first record of the first table has not been migrated and further determining that the first link is marked as a circular link in the table tree structure.

11. The computer-readable storage media of claim 10 , wherein the migrating the first record of the second table if the first record of the first table has not been migrated comprises replacing the identifier of the first record, in the foreign key column of the second table, with a null value.

12. The computer-readable storage media of claim 11 , wherein the iterative traversal of the table tree structure further comprises a final traversal that comprises:
replacing previously inserted null values with corresponding new identifiers of the migrated records in the destination relational database obtained from the migration table.

13. The computer-readable storage media of claim 1 , wherein the iterative traversal of the table tree structure comprises repeated traversals of the table tree structure while a quantity of records migrated during a prior traversal is greater than zero.

14. The computer-readable storage media of claim 13 , wherein the repeated traversals of the table tree structure end with a final traversal that is performed when a quantity of records migrated during a prior traversal is zero.

15. The computer-readable storage media of claim 1 , wherein the generating the table tree structure comprises:
creating a first node record corresponding to the first node;
identifying the second table by parsing the relational database schema to identify tables referencing the first table;
creating a second nod

Assignees

• Microsoft Technology Licensing Llc

Inventors

• Taranov Igor

Classifications

• G06F16/2282

  Tablespace storage structures; Management thereof · CPC title

• G06F16/2246

  Trees, e.g. B+trees · CPC title

• G06F16/214 Primary

  Database migration support · CPC title

• G06F16/211 Primary

  Schema design and management · CPC title