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:

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',
                                                     @Owner='dbo',
                                                     @DatabaseName='EmpowerID_Archive'

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.