Archive Database Structure

V9.4-V9.8

Prerequisites

• Intermediate knowledge of Cora SeQuence, and Cora SeQuence database structure.
• Configure MSDTC between the operational database and the archive database.
  For details, see this article.

Overview

You can set up a Cora SeQuence archive database on a Microsoft SQL server, either on-premise or on Azure cloud service. 

The archive database structure is similar to the Cora SeQuence database, with some additional schema changes. Only the XXXClosed tables and workflow instances are archived from the Cora SeQuence database to the archive database, and the archived tables are stored under a new schema referred to as [arc].

While deploying the archive database, a script is run that creates 12 partitions, one for each month starting from the month of deployment, in the database. The data is stored in these partitions month-wise, making it easier for you to access the data.
To create additional partitions in advance, run the PartitionMaintinancePlan.sql script every month. Before running the script, make sure to wrap the script with the job specific to on-premise or Azure service.

Closed tables in Cora SeQuence database

• tblActionItemRemindersClosed
• tblActionItemsAttachmentsClosed
• tblActionItemsClosed
• tblActionItemsOrgsClosed
• tblAllocatedSolutionCasesClosed
• tblAllocatedSolutionTasksClosed
• tblAttachmentsClosed
• tblInstanceActivitesClosed
• tblInstanceActivitiesRollbackClosed
• tblInstanceWorkflowsClosed
• tblInstanceWorkflowsDataPropagationClosed
• tblInstanceWorkflowsRollbackClosed
• tblSolutionCasesClosed
• tblSolutionTasksClosed
• tblWorkflowInstanceAttachmentsClosed
• tblWorkflowInstanceConversationsAttachmentsClosed
• tblWorkflowInstanceConversationsClosed

All tables moved from the Cora SeQuence database to the archive database have same schema, with following additional fields:

• fldJobId: represents the unique identifier of the job that has transferred the data.
• fldArchivingDate: represents the date when data has been archived.
• fldProperties: holds the JSON parameters in which we extract all the scheme and data from the original table.
• fldSourceTableName: represents the source table name from which the data is collected in the fldProperties field, in case of UACT tables.
• fldMasterWfld: represents the source workflow name from which the data is collected in the fldProperties field, in case of UWF tables.

Apart from the above mentioned closed tables, the archive data also has additional UACT and UWF tables.

• UACT: holds data related to the forms and integration activities, that is captured during activity execution. In form-based UACTs, there can be a record per activity, per workflow, or per action items, depending on the key definition in the data model. For integration activities, Sequence persists the response and request of the call.
• UWF: holds variables for a workflow template. Values are set and updated during workflow execution.

In archive database, only one UACT table [arc].[UACT] holds all the data from all the UACT tables in the Cora SeQuence database. The fldProperties fields contains all the schema and related data from the UACT tables. An additional fldSourceTableName field holds the name of the source UACT table for the respective fldProperties field.
The  [arc].[UACTBlobs] table holds the large objects (Blobs) from your UACTs tables.

The same solution is applied for UWF tables. In archive database, one UWF table [arc].[UWF] holds data from all the UWF tables in the Cora SeQuence database. The fldProperties fields contains all the schema and related data from the UWF tables. An additional fldMasterWfld field holds the name of the source UWF table for the respective fldProperties field.

After deploying the dacpac file for the archive database, you see some tables with a prefix ‘tmpXXX’. These tables are for simulation mode, which are created if the simulation is set to true in the JSON configuration, while setting up the archiving job. The simulation archiving helps the user to verify the data before deletion from the operational database.

Simulation tables

• tmptblActionItemRemindersClosed
• tmptblActionItemsAttachmentsClosed
• tmptblActionItemsClosed
• tmptblActionItemsOrgsClosed
• tmptblAllocatedSolutionCasesClosed
• tmptblAllocatedSolutionTasksClosed
• tmptblArchivingLog
• tmptblAttachmentsClosed
• tmptblFileBlobs
• tmptblFiles
• tmptblInstanceActivitiesClosed
• tmptblInstanceActivitiesRollbackClosed
• tmptblInstanceWorkflowsClosed
• tmptblInstanceWorkflowsDataPropagationClosed
• tmptblInstanceWorkflowsRollbackClosed
• tmptblLog
• tmptblSFComments
• tmptblSFQuestionsAnswers
• tmptblSolutionCasesClosed
• tmptblSolutionTasksClosed
• tmptblWorkflowInstanceAttachmentsClosed
• tmptblWorkflowInstanceConversationsAttachmentsClosed
• tmptblWorkflowInstanceConversationsClosed
• tmpUACT
• tmpUACTBlobs
• tmpUACTRequestResponse
• tmpUWF

Some of the tables stored in the archive database are not closed tables, but store important information. 

• tblLog: holds activity data from different users.
• tblArchivingLog: holds archiving log data from different scenarios.
• tblSFComments
• tblSFQuestionsAnswers