Same-database archiving

Usage notes

• This feature is available for instances in any control mode.

• Each source table must have a primary or unique key.

  Note

  Your source tables should contain a timestamp field that indicates when the data was last modified. You can use this field as a filter condition for archiving.

• To run scheduled data archiving tasks, the instance must be in the Secure Collaboration or Stable Change control mode. If you only need to run a one-time archiving task, no specific control mode is required.

  Note

  If the instance is in Stable Change control mode, we recommend that you enable Secure Agent Access or change the control mode to Secure Collaboration before running a scheduled archiving task to prevent failures caused by an expired instance logon. For more information, see Enable Secure Agent Access and Change the control mode of an instance.

Billing

Archiving data within the same database is free because you do not need to purchase a destination database.

Archive data

1. Log in to DMS 5.0.

2. In the top navigation bar, choose Solution > Data Archiving.

   Note

   If you use the DMS console in simple mode, move the pointer over the icon in the upper-left corner of the DMS console and choose All Features > Solution > Data Archiving.

3. In the upper-right corner of the page, click Create Data Archiving Ticket.

4. On the Create Data Archiving Ticket page, configure the following parameters:

   Parameter	Required	Description
   Task Name	Yes	Enter a descriptive name to easily identify the task later.
   Archive Destination	Yes	Select Archive data in the same database.
   Source Database	Yes	Search for and select the database.
   Archive Configuration	Yes	Table Name: Select the table from which you want to archive data. Filter Condition (Optional): Enter a condition to filter the data to be archived. For example, gmt_modified<='${6_month_ago}'. Note If you need to use time variables, for example, to archive data older than six months, you must first configure them in the Variable Configuration section. Click Add (Optional) to add multiple source tables.
   Archived Table Mapping	No	You can view information about the archived table in the Archived Table Mapping section, such as the table name, columns, and sharding key.
   Variable Configuration	No	If you use custom variables in the Archive Configuration section, you must define them here. For example, if the time variable is 6_month_ago, the format is yyyy-MM-dd, and the offset is -6 months, the variable ${6_month_ago} resolves to 2021-02-11 if the current date is 2021-08-12. For more information about how to configure time variables, see Configure time variables.
   Post-archive Action	No	If you select Clean up archived data from source table (lock-free delete), the system automatically deletes the archived data from the source table after the task is complete. This operation uses a DELETE statement as part of a lock-free data change. However, this process creates a temporary backup table in the source database to store the archived data. Ensure that the source database has sufficient space to prevent availability issues from insufficient storage. To clean up the temporary backup table: After you confirm that the data archiving is complete and the data is correct, you can create a regular data change ticket to delete the temporary backup table from the source database. If you do not select Clean up archived data from source table (lock-free delete), you must manually delete the data from the source table and optimize storage after the data is successfully archived. Delete data from the source table: Create a regular data change ticket to clean up the archived data. For more information about how to create a regular data change ticket, see Regular Data Change. Optimize storage for the source table: Create a lock-free change ticket to optimize storage for the source table. For more information, see Use a lock-free change ticket to implement lock-free schema changes.
   Execution Mode	Yes	Select an execution mode. The following modes are supported: One-time: The archive task runs only once after the ticket is approved. Scheduled: After the ticket is approved, the archive task runs based on the configured schedule. For more information, see Scheduled Execution.

5. Click Submit Ticket.

6. After the ticket is approved, the system automatically runs the data archiving task.

   If the task fails, go to the execution section of the ticket and click Details in the Actions column to view the task logs and identify the cause of the failure. If the failure is caused by an intermittent issue, such as a network or database connection error, you can click Resume to retry the task.

7. A status of Task Completed indicates that the data has been successfully archived. You can then query the archived data.

Querying archived data

During the data archiving process, DMS automatically creates a table in the current database to store the archived data. The table name is prefixed with tmp_ and suffixed with the ticket ID.

For example, after data from the orders_test table is archived in the same database, a table named tmp_orders_test_121**** is automatically created.

1. After the data archiving task succeeds, on the Ticket Details page, go to the Basic Information section and click View next to Destination Database.

2. On the SQL Console page, find your table in the table list on the left, double-click the table name, and then click Execute to view the archived data.

   Note

   The system automatically creates a new table in the source database based on the source table's name. Therefore, the destination database name is the same as the source database name.

   Four columns are added to the archived table. These columns do not affect the original data columns.

   • Archive information (ticket ID and archive time)

   • Database name

   • Table name

   • Instance ID (the ID assigned to the instance when it was added to DMS, which corresponds to the actual instance ID)