We have been implementing Audit DB archiving strategy - as of BO 4.0 , SAP has provided option by which we can determine how many days we want to keep audit log. here , we would like to keep more than 5 years of audit log in schema , now 5 yrs is too long we know , so we've decided to build archiving approach where we would copy audit data(using SQL Script) into archive audit DB base on date/time. ( Our DB is SQL )
I referred main audit DB as "Active audit DB" and archived DB as "Archived Audit DB". (Active Audit DB is connected to BO CMS and Archived Audit DB is just like backup DB of Active Audit DB storing more rows).
As we further dig into system , we found there are 19 tables involves in Audit DB table , out of which only tables ADS_EVENT and ADS_EVENT_DETAIL having huge size and rest of 17 tables are of a smaller size.
My question - while applying deletion logic on archiving audit DB using SQL script , how we ensure that all data has been deleted from all tables. How those 19 tables are connected with each other so we can apply robust logic of deletion ( on Arching DB) to delete data ( for example , more than 3 years old).
Have any one every implemented Archiving for Audit DB.