I read with interest the article last week on using SQL 2005 as the logging server. The main reason for the interest is that this is how we have our ISA 2006 server setup and a couple of days before the article came out our DB ran out of disk space. As a temporary measure i am now logging to a file until i can start a new DB. A new DB may sound drastic but with the old one at 458Gb it is almost impossible to do anything with.....
Of those of you who have logging setup to SQL 2005 could you tell me what settings you have put in place to groom the database of historcal entries? (Say anything older than 90 days) and to prevent the DB getting out of hand? (Like mine is at the moment!!!)
From: United Kingdom
No, you need to rely upon third party products which use the file log data....not ideal I know. Some customers have written there own "viewers" which access data stored in the flat files.
If reporting is important, then yes, SQL is often a good choice, but it just seems to come with a high price...
As ever, never the same answer!
The other option is to sitck with SQL and go through your rule base and only log what you need, depending on how you are using ISA and what logs you care about, you can trim quite a bit of excess "noise" to try and reduce database growth. You may also only need one of the firewall or web proxy logs, but depends on your setup really.
In the past I have also used "cleanup" rules to get rid of a lot of rubbish traffic (netbios, broadcasts, dhcp etc) and then configured these rules with logging disabled. You then place the cleanup rules at the end of the rulebase, to prevent hitting the default deny which normally has logging enabled. Just an idea...
My advise would be to decide upon a retention period you are happy with as this will ultimately determine the maximum size of the DB. If this is still to big, you may need to archive the DB or consider logging data for a shorter period of time. It is pretty much impossible to guess 'X' days will equate into 'Y' MB of data without looking at your specific data usage patterns.