What if you want to keep ACS data for more than 256 days ? - Part I

I have a good friend who has configured his OpsMgr settings (last year) to keep his collected events in ACS for 2 years because his company's policy requires them to save all security events data for 2 years. And so the story goes ...

Last week, he contacted me with regards to a couple of errors that he encountred in his ACS Collector. Apparently he kept getting the following error in his Event Viewer and eventually his ACS Collector service has stopped and could not be started.

Event Type: Error
Event Source: AdtServer
Event Category: None
Event ID: 4618
Date: 04/12/2009
Time: 2:00:34 AM
User: N/A
Computer: SCOMAPP1
Description:
Error occured on database connection:
Status: 0x04080000
ODBC Error: 106
ODBC State: 42000
Message: [Microsoft][ODBC SQL Server Driver][SQL Server]Too many table names in the query. The maximum allowable is 256.
Database: SqlWriter
Connection: Maintenance
Statement:

We managed to find a hotfix to resolve this issue in the following link:
http://support.microsoft.com/kb/954948

and have the patch installed on the ACS Collector.

But then again if you read the documentation for the hotfix properly, it states that the system will only retain data of the latest 256 partitions/days of events. My friend needs to be able to pull reports for data of 2 years.

In my next blog post, I will share with you how do we open and study the ACS database and eventually found a way albeit messy and cumbersome method to achive that. At the same time, I will also share with you a product out there which will be able to resolve this problem without you getting your hands dirty. Stay tuned ...