Setting 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 IDComponent Table NameArchiving Action IDRule CodeBack Interval In Days
1Workflow Instance1 (Delete)exists (select WorkflowInstanceDataid from WorkflowInstanceData d where d.WorkflowInstanceDataid = WorkflowInstance.WorkflowInstanceDataid and d.WorkflowData is null )30
2Workflow Instance Data1 (Delete)WorkflowData is null30
3Web Cache1 (Delete)NULL2
4Audit Log Operation2 (Archive)NULL180
5BPM Exception1 (Delete)NULL7
6BPM Alert1 (Delete)NULL4
7Login Session1 (Delete)CreatedOn < GETUTCDATE() -11
8SAML Transaction1 (Delete)NULL30
9Resource Entitlement Inbox2 (Archive)ProcessStatus > 130
10Account Password Reset Inbox2 (Archive)ProcessStatus > 130
11Account Object Attribute Inbox2 (Archive)ProcessStatus > 130
12Account Object Attribute Outbox2 (Archive)ProcessStatus > 130
13Person OrgRoleOrgZone ReEval Inbox2 (Archive)NULL30
14Execution Runtime Job History1 (Delete)NULL14
15Person Login History1 (Delete)NULL180
16Scheduled Workflow History1 (Delete)NULL14
17Workflow Internal Task History1 (Delete)
18Archiving Rule History1 (Delete)NULL30
21Access Request Catalog Item2 (Archive)NULL31

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.

To set up database archiving

  1. From any EmpowerID server with SQL Management tools installed, open Microsoft SQL Server Management Studio (SSMS).
  2. From SSMS, right-click Databases and select New Database... from the context menu.
  3. In the New Database window that opens, type EmpowerID_Archive in the Database name field and then click OK.
  4. From theEmpowerID Identity Warehouse, execute thedbo.Custom_ArchivingRule_CreateArchiveTablesstored 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 procedurein our environmentis shown below.

    Be sure to replace the value of the DatabaseServerName parameter with the name of the SQL server hosting the database in your environment.

    As a best practice, the archive database should be hosted on the same SQL server as the EmpowerID Identity Warehouse.

    If you name the database something other than EmpowerID_Archive, be sure to do the following:

    • Replace the value of the DatabaseName parameter as needed
    • Update the name in the ExecutionRunTimeJob table's ExtensionAttribute1
    • Perform an iisreset so that it picks up the new archive database

    EXEC dbo.Custom_ArchivingRule_CreateArchiveTables @DatabaseServerName=N'SQL2012',
  5. Optionally, edit the Back Interval In Days setting for each table as desired by doing the following:
    1. From the Navigation Sidebar 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. From 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.