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 archive database for long-term record keeping storage. This also allows you to delete the data from 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 archive 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 evaluate 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, 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 deletes all data from 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
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.

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. You can name the archiving database as desired. However, if you change the name to something other than EmpowerID_Archive, you need to change the value of the DatabaseName parameter in the next step to the name of the database.
  4. 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.
    5. 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:
    • 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',
                                                      @Owner='dbo',
                                                      @DatabaseName='EmpowerID_Archive'
  5. Next, log in to the EmpowerID Management Console as an administrative user.
  6. From the EmpowerID Management Console, navigate to Configuration Manager by clicking the application icon and selecting Configuration Manager from the context menu.
  7. From Configuration Manager, click the Servers and Roles node and then check the Database Archiving Rule Processor job on at least one EmpowerID server.

  8. Optionally, edit the Back Interval In Days setting for each table as desired by doing the following:
    1. Log in to the EmpowerID Web interface.
    2. From the Navigation Sidebar, navigate to the Archiving Rules find page by expanding Admin > Miscellaneous and clicking Archiving Rules.
    3. From the Archiving Rule find page, click the Component Table link for the component whose Back Interval In Days setting you want to change.

    4. From the Archiving Rules Details view that appears, click the Edit link. Edit links have the pencil icon.

    5. Type the new integer value in the Back Interval (Days) field and then click Save.