Online data migration

• US 8,356,010 B2
• Filed: 08/11/2010
• Issued: 01/15/2013
• Est. Priority Date: 08/11/2010
• Status: Active Grant

• Current Assignee: SAP SE
• Original Assignee: SAP AG (SAP SE)
• Inventors: Driesen, Volker
• Primary Examiner(s): Yen, Syling
• Application Number:
  US12/854,789
• Publication Number:
  US 20120041933A1
• Time in Patent Office: 888 Days
• Field of Search: 707/610, 707/611, 707/613, 707/617, 707/618, 707/620, 707/624, 707/626, 707/634, 707/635, 707/636, 707/637, 707/639, 707/640, 707/661, 707/674, 707/999.204
• US Class Current: 707/624
• CPC Class Codes: G06F 16/214 Database migration support

Abstract

Data is recursively migrated during uptime from a first table to a second table in a first schema in a database while taking into account changes to the first table in a change table. The database has first and second application servers respectively connecting to the database using first and second schemas and running first and second versions of a software program. Downtime can be initiated to migrate the remaining entries in the change table to the second table, delete the first table, and rename the second table to have a same name as the first table prior to deletion so that during uptime the second application server connects to the second table and runs the second version of the software program. Related apparatus, systems, techniques and articles are also described.

Claims

1. A computer-implemented method comprising:

migrating data during uptime from a first table to a second table in a first schema in a database, a first application server connecting to the database using the first schema, a second application server connecting to the database using a second schema, the first application server running a first version of a software program, the second application server running a second version of the software program, the second schema having a first alias pointing to the first table and a second alias pointing to the second table;

adding entries during uptime to a change table in the first schema characterizing changes to the first table during the data migration, the second schema including a change alias pointing to the change table;

recursively updating the second table to include the entries referred to in the change table using the second application server, the recursively updating comprising;

reading data from the first alias corresponding to the entries referred to in the change table, and writing the data from the first alias to the second alias until a remaining number of the entries is below a pre-defined threshold; and

initiating downtime to migrate remaining entries in the change table to the second table, delete the first table, and rename the second table to have a same name as the first table prior to deletion so that during uptime the second application server connects to the second table and runs the second version of the software program.

2. A method as in claim 1, further comprising deleting the first alias, the second alias, and the change alias.

3. A method as in claim 1, wherein the first alias, the second alias, and/or the change alias comprise:

a database view, a database alias, or a database synonym.

4. A method as in claim 1, wherein the second table has at least one additional field as compared to the first table.

5. A method as in claim 1, wherein the changes to the first table comprise one or more of:

an update, and insert, or a delete.

6. A method as in claim 1, wherein a database trigger monitors changes to the first table and generates entries in the change table characterizing the monitored changes.

7. A method as in claim 6, wherein the database trigger is a stored procedure.

8. A method as in claim 6, wherein the entries in the change table are generated by the database trigger as part of a single transaction along with a corresponding change to the first table.

9. A method as in claim 1, wherein the aliases allow for reading, updating, deleting, and inserting of entries in the corresponding table.

10. A method as in claim 1, wherein the migration of data is initiated by a migration program forming part of application software being executed on the second application server.

11. A method as in claim 10, wherein the migration program determines whether entries in the change table require migration or have already been migrated.

12. A method as in claim 11, wherein the entries in the change table include first and second fields, the first field including either a first designation indicating that a database trigger updated the entry in the change table or a second designation indicating that the migration program updated the entry in the change table, the second field comprising a sequence number characterizing when the entry was last updated;

wherein;

the entry is considered to be new and it is migrated to the second table when there is only a single entry with the first designation;

the entry is considered to have already been migrated if there is only a single entry with the second designation; and

the entry is migrated to the second table if the sequence number for the entry with the first designation is higher than the sequence number for the entry with the second designation, or the entry is skipped if the sequence number for the entry with the second designation is higher than the sequence number for the entry with the first designation when there are entries with both the first designation and the second designation.

13. A method as in claim 12, further comprising removing the database trigger once the data has been completely migrated.

14. A method as in claim 1, wherein the first application server is different from the second application server.

15. A method as in claim 1, wherein the first application server and the second application server are the same.

16. A method as in claim 1, wherein there are at least two instances adding entries during uptime to the change table in the first schema.

17. A method as in claim 16, wherein each of the at least two instances adding entries during uptime to the change table in the first schema are executed using separate servers.

18. A computer-implemented method comprising:

migrating data during uptime from a first table to a second table in a first schema in a database, a first application server connecting to the database using the first schema, a second application server connecting to the database using a second schema, the first application server running a first version of a software program, the second application server running a second version of the software program, the second schema having a first alias pointing to the first table and a second alias pointing to the second table;

adding entries during uptime to a change table in the first schema characterizing changes to the first table during the data migration, the second schema including a change alias pointing to the change table;

recursively updating the second table to include the entries referred to in the change table using the second application server, the recursively updating comprising;

reading data from the first alias corresponding to the entries referred to in the change table, and writing the data from the first alias to the second alias until a remaining number of the entries is below a pre-defined threshold, each update comprising sequentially passing through entries in the change table; and

initiating downtime to migrate remaining entries in the change table to the second table, delete the first table, and rename the second table to have a same name as the first table prior to deletion so that during uptime the second application server connects to the second table and runs the second version of the software program.

19. A non-transitory computer program product storing instructions, which when executed by at least one data processor forming part of a least one computing system, result in operations comprising:

migrating data during uptime from a first table to a second table in a first schema in a database, a first application server connecting to the database using the first schema, a second application server connecting to the database using a second schema, the first application server running a first version of a software program, the second application server running a second version of the software program, the second schema having a first alias pointing to the first table and a second alias pointing to the second table;

adding entries during uptime to a change table in the first schema characterizing changes to the first table during the data migration, the second schema including a change alias pointing to the change table;

recursively updating the second table to include the entries referred to in the change table using the second application server, the recursively updating comprising;

reading data from the first alias corresponding to the entries referred to in the change table, and writing the data from the first alias to the second alias until a remaining number of the entries is below a pre-defined threshold; and

initiating downtime to migrate remaining entries in the change table to the second table, delete the first table, and rename the second table to have a same name as the first table prior to deletion so that during uptime the second application server connects to the second table and runs the second version of the software program.