Application Scenario

Example Scenario

The following scenario explains the steps for the Archival Strategy.

Scenario Description

Consider a scenario where all workflow records related to employee leave details are stored in the database named Employees Leave Report. This database contains large amount of data, which affects the performance. Hence, all the completed and aborted workflow related data, which is not related to current financial year (2015), must be deleted from the database. A record of the deleted data must also be maintained in the Archived database.

This ensures that the Employees Leave Report database will have all the workflow related data for the current financial year (2015), and also all the executing or sleeping workflow related data for the previous financial years.

The Archived database will have only completed and aborted workflow instance records related to the previous financial years.

Scenario Application Details

The following application and workflow names are considered to describe this scenario effectively.

• Application Name/Repository Name: EmployeeRepo

• Workflow Name: LeaveReportWF

Consider that the SWExecute table contains the following entries. In this example, only few fields of the SWExecute table are considered.

Scenario Solution

The following section will give you a brief idea of each step in the archival strategy:

1. Run the WSBPMArchivingScript.sql script on the Employees Leave Report database. This will create the required tables and procedures.

2. Execute the proc_SKInsertIntoSKDeleteTable procedure as follows on the Employees Leave Report database to fetch the records of the previous financial years. This procedure will insert the fetched records into the SKDeleteTable table.

   Exec proc_SKInsertIntoSKDeleteTable '01-01-2013', '12-31-2014', NULL, NULL, NULL

   The above query will insert records between 01-01-2013 and 12-31-2014 with InternalStatus ‘FN’ and ‘AB’ into the SKDeleteTable table.

   The records to be deleted are stored with ExecutionID, ProductionID, Application, Name, Data, LocalVariablesId, ExecutionDetailsID, and Isdeleted details in the SKDeleteTable table. Initially, the ‘Isdeleted’ field is ‘0’ for all the records to be deleted.

3. Execute the proc_SKSKDeleteTableInArchiveDB procedure as follows on the Employees Leave Report database to fetch the records of the previous financial years. This procedure will insert the fetched records into the SKDeleteTableForArchiveDB table.

   Exec proc_SKSKDeleteTableInArchiveDB

4. Take a full backup of the Employees Leave Report database and rename it as ‘Backup Employees Leave Report’.

   Backup Employees Leave Report Database

   ID	Production ID	Application	Name	InternalStatus	RequestedOn
   1	1	EmployeeRepo	LeaveReportWF	FN	7/9/2013 17:00
   2	1	EmployeeRepo	LeaveReportWF	AB	11/22/2014 11:16
   3	2	EmployeeRepo	LeaveReportWF	FN	8/8/2013 19:22
   4	2	EmployeeRepo	LeaveReportWF	AB	12/4/2013 9:15
   5	2	EmployeeRepo	LeaveReportWF	SL	5/29/2013 15:32
   6	2	EmployeeRepo	LeaveReportWF	SL	5/2/2014 6:09
   7	2	EmployeeRepo	LeaveReportWF	PA	6/1/2013 20:56
   8	2	EmployeeRepo	LeaveReportWF	SL	11/3/2014 14:45
   9	2	EmployeeRepo	LeaveReportWF	FE	4/20/2013 10:00
   10	2	EmployeeRepo	LeaveReportWF	FN	3/3/2015 16:35

5. Execute the proc_SKDeleteFromProduction procedure on the Employees Leave Report database to delete all the workflow records from the Repository database tables with InternalStatus ‘FN’ or ‘AB’. After the workflow records are deleted, the ’Isdeleted’ field in the SKDeleteTable table is updated to ‘1’ for all deleted records. Now the 'Employees Leave Report' database contains the following records:

   Employees Leave Report Database

   ID	Production ID	Application	Name	InternalStatus	RequestedOn
   5	2	EmployeeRepo	LeaveReportWF	SL	5/29/2013 15:32
   6	2	EmployeeRepo	LeaveReportWF	SL	5/2/2014 6:09
   7	2	EmployeeRepo	LeaveReportWF	PA	6/1/2013 20:56
   8	2	EmployeeRepo	LeaveReportWF	SL	11/3/2014 14:45
   9	2	EmployeeRepo	LeaveReportWF	FE	4/20/2013 10:00
   10	2	EmployeeRepo	LeaveReportWF	FN	3/3/2015 16:35

   Note: Even though the highlighted workflow instance record is in finished state, this row and the records related to it are not deleted because this record belongs to the current financial year (2015). As per the parameters passed in step 2, only the records from previous financial years need to be deleted.

6. Execute the proc_SKDeleteFromArchivedDB procedure in the Backup Employees Leave Report database to delete all the records except the records in the SKDeleteTableForArchiveDB. Workflow records with InternalStatus other than ‘FN’ or ‘AB’. The value of the ‘Isdeleted’ field of the SKDeleteTableForArchiveDB table will remain as ‘0’.

Backup Employees Leave Report Database

Environment

The environment used for the sample exercise is an Intel Xenon computer with a 2.66 GHz processor and 32 GB RAM using Windows Server 2008 R2 Load Balanced Server.

Conclusion

After performing the above steps, the Employees Leave Report database will have data only related to the current financial year (2015) along with all the running workflow instances of the previous financial year.

The 'Backup Employees Leave Report' Archived database will have only those completed and aborted workflow instance records related to the previous financial year.

This is the method to reduce the amount of data from the production database to ensure better performance.