TN MES104 MES Database Maintenance - Purge, Archive, and Restore
Description
How to perform data archival, purge, and restore (APR) of Wonderware MES data.
- Author: Peter Farrell
- Published: 6/30/2015
- Applies to: MES 4.0
Details
You can use the Database Maintenance module to archive, purge, and restore the data in the MES DB This module allows you to create, run, delete, modify, and view the APR jobs. The Database Maintenance module is grouped under the System Management group on Navigation Bar.
Within the MES DB, the table containing the transaction data grows rapidly and continuously. To keep the size of the MES DB within manageable limits, you have to archive and/or purge data at regular intervals of time. You can archive the data in tables and their associated tables, and thenrestore them. In certain cases, you might even purge the data from the MES DB.
You can create and manage APR jobs by opening the Database Maintenance module in the editor window. The Database Maintenance editor allows you to:
Create new jobs
Run jobs
View jobs
Modify jobs
Delete jobs
Access job logs
You can manually run the archive and purge jobs on the MES DB to archive and/or purge the data and run the restore jobs on the non-production database. You can also manually select from the APR jobs that are already defined for the MES DB for execution. When you select a job, all the configuration information for that specific job is displayed. You can review and modify the jobspecifications before executing it.
If you run an archive job or an archive purge job, a directory is created with the job name (if it does not exist) on the DB Maintenance Service (at the configured root path). Under the job directory, a time-stamped .zip file is created that contains a .csv file for each archived table. The .zip file also contains a manifest file. A log file with the same name is created alongside the .zip file.
You can remove an existing job from the Database Maintenance module. Before removing the job, you ensure that the job is not running.
If you run a purge job or a restore job, the following is created on the DB Maintenance Service (at the configured root path):
A directory is created with the job name, if it does not already exist
Under the job directory, a time-stamped log file is created
You can schedule the APR job using the Windows Task Scheduler on the APR server. This allows the APR jobs to run automatically at a pre-determined time or on a regular basis, such as daily, weekly, or monthly.
You must configure the scheduled job on the same computer as the APR server using the Windows Task Scheduler. The proxy must be configured to send requests to this service in order for the command line program to send its requests.
Archive jobs
You can create a new archive job to execute a defined archive operation. This job will retrieve the selected records from the MES DB and record them in an archive file. You can also configure the archive jobs to execute a purge operation on the archived records following the successful completion of the archive operation.
Create an archive job
a. On the Navigation Bar, click the Database Maintenance module.
b. Right-click the empty space in the editor window and select New to create an archive job. A new archive job is added to the Database Maintenance editor window.
c. In the Properties pane, specify a name in the Name box.
d. Add a brief description of the job in the Description box.
Note: In the Properties pane, the job type is set to Archive by default. You can change the job type by setting it to Archive Purge, Purge, or Restore in the Type box.
e. Click the Details option to view and select the tables from which the data needs to be archived.
Note: You can select the Work Order State Filter status by selecting one of these available options: Released, Started, Completed, and Closed.
f. Under Date Filter, select the Relative option.
Note: The Relative option is set to 30 days by default. This implies the data older than 30 days is to be archived. You can also set the Date Filter option to Absolute and specify a time period for which you would like to restore the data before archiving the rest of the data.
Purge jobs
You can create a purge job to execute a purge operation. This job will remove records from an active, production MES DB to free-up resources on the database server. The purge operation can be done independently or as a follow-up to an archive job.
Note: When you create a new job, the job type is set to Archive by default. To create a purge job, you will set the job type to Purge in the Type box, in the Properties pane.
Restore jobs
You can create a restore job to execute a restore operation. This job will insert records from an archive file into the MES Restore DB. The MES DB must already exist (probably by running the MES DB Configuration Utility) and will not be created as part of the restore operation.
Note: To create a restore job, you will set the job type to Restore in the Type box, in the Properties pane. Before creating a restore job, you will also ensure that an archive file is available from a previously executed archive job.