Set up Database Archiving

EmpowerID provides a Database Archiving Rule Processor job that you can use to move data from the EmpowerID Identity Warehouse to an archiving database – where it can be stored for long-term record keeping – as well as delete the data in a select number of history and log-related tables. When database archiving is set up and the job is enabled, the system processes the data in all eligible component tables based on the following information set on those components:

Archiving Action ID – This setting specifies whether the data in the Identity Warehouse is to be deleted or moved to the archiving database. An ID setting of 1 specifies delete, while an ID setting of 2 specifies archive.
Rule Code – This is optional SQL logic that must be evaluated to true before any archiving action can occur against the data in the corresponding table. If the Rule Code is set to NULL, archiving occurs based on the Archiving Action ID and the Back Interval In Days.
Back Interval In Days – This setting specifies the number of days the data must remain in the component table before any archiving action can occur.

As an example of how the above settings work, let's consider the Web Cache component table. This table has an Archiving Action ID of 1(delete), a Rule Code setting of NULL, and a Back Interval In Days setting of 2. Thus, when the Database Archiving Rule Processor job is enabled, the system will delete all data in the WebCache table of the EmpowerID Identity Warehouse when that data is older than two days. Since the Rule Code setting is NULL, no other conditions must be met.

In a default configuration, the tables eligible for archiving and archiving logic applied to those tables are as follows:

Archiving Rule ID	Component Table Name	Archiving Action ID	Rule Code	Back Interval In Days

Archiving Rule ID	Component Table Name	Archiving Action ID	Rule Code	Back Interval In Days
1	Workflow Instance	1 (Delete)	`exists (select WorkflowInstanceDataid from WorkflowInstanceData d where d.WorkflowInstanceDataid = WorkflowInstance.WorkflowInstanceDataid and d.WorkflowData is null )`	30
2	Workflow Instance Data	1 (Delete)	`WorkflowData is null`	30
3	Web Cache	1 (Delete)	`NULL`	2
4	Audit Log Operation	2 (Archive)	`NULL`	180
5	BPM Exception	1 (Delete)	`NULL`	7
6	BPM Alert	1 (Delete)	`NULL`	4
7	Login Session	1 (Delete)	CreatedOn < GETUTCDATE() -1	1
8	SAML Transaction	1 (Delete)	`NULL`	30
9	Resource Entitlement Inbox	2 (Archive)	ProcessStatus > 1	30
10	Account Password Reset Inbox	2 (Archive)	ProcessStatus > 1	30
11	Account Object Attribute Inbox	2 (Archive)	ProcessStatus > 1	30
12	Account Object Attribute Outbox	2 (Archive)	ProcessStatus > 1	30
13	Person OrgRoleOrgZone ReEval Inbox	2 (Archive)	`NULL`	30
14	Execution Runtime Job History	1 (Delete)	`NULL`	14
15	Person Login History	1 (Delete)	`NULL`	180
16	Scheduled Workflow History	1 (Delete)	`NULL`	14
17	Workflow Internal Task History	1 (Delete)		14
18	Archiving Rule History	1 (Delete)	`NULL`	30
21	Access Request Catalog Item	2 (Archive)	`NULL`	31

From the above table, we can see that the tables in the EmpowerID Identity Warehouse with data eligible for archiving or deletion are relatively small. When needed, it is possible to add more tables; however, as this requires an in-depth knowledge of the relationship between the data in the EmpowerID Identity Warehouse, you should contact EmpowerID professional services before doing so.

Set up database archiving

From any EmpowerID server with SQL Management tools installed, open Microsoft SQL Server Management Studio (SSMS).
From SSMS, right-click Databases and select New Database... from the context menu.
In the New Database window that opens, type EmpowerID_Archive in the Database name field and then click OK.
From the EmpowerID Identity Warehouse, execute the dbo.Custom_ArchivingRule_CreateArchiveTables stored procedure. This stored procedure generates the tables and other related information for each of the EmpowerID components with data that is either eligible for archiving or deletion. The default code for this procedure in our environment is shown below.
Optionally, edit the Back Interval In Days setting for each table as desired by doing the following:
1. On the navbar of the EmpowerID Web interface, expand Admin > Miscellaneous and click Archiving Rules.
2. On the Archiving Rule page, click the Component Table link for the component whose Back Interval In Days setting you want to change.
3. On the Archiving Rules Details view that appears, click the Edit link.
4. Enter the new integer value in the Back Interval (Days) field and then click Save.