• RSS
  • Twitter
  • FaceBook

Welcome to ISAserver.org

Forums | Register | Login | My Profile | Inbox | RSS RSS icon | My Subscription | My Forums | Address Book | Member List | Search | FAQ | Ticket List | Log Out

SQL 2005 Logging DB

Users viewing this topic: none

Logged in as: Guest
  Printable Version
All Forums >> [ISA 2006 Firewall] >> Logging and Reporting >> SQL 2005 Logging DB Page: [1]
Login
Message << Older Topic   Newer Topic >>
SQL 2005 Logging DB - 13.Mar.2008 5:54:37 AM   
stephen3rd

 

Posts: 9
Joined: 13.Mar.2008
Status: offline
Folks,

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!!!)

Many Thanks

Post #: 1
RE: SQL 2005 Logging DB - 18.Mar.2008 1:48:57 PM   
stephen3rd

 

Posts: 9
Joined: 13.Mar.2008
Status: offline
Does nobody out there using SQL 2005 for logging?

If somebody does would you mind letting me know how you have setup the maintenance on your databases?

Many thanks

(in reply to stephen3rd)
Post #: 2
RE: SQL 2005 Logging DB - 18.Mar.2008 9:13:43 PM   
Jason Jones

 

Posts: 4663
Joined: 30.Jul.2002
From: United Kingdom
Status: offline
I used to recommend SQL logging, but with experience I have now found that using file logging is so much better for most customers.

Log data is a lot smaller this way and the performance boost by avoiding SQL is also beneficial.

Not answering your question really, but maybe something to consider is you have 458GB databases!

_____________________________

Jason Jones | Forefront MVP | Silversands Ltd
My Blogs: http://blog.msedge.org.uk/ and http://blog.msfirewall.org.uk/

(in reply to stephen3rd)
Post #: 3
RE: SQL 2005 Logging DB - 19.Mar.2008 5:11:12 AM   
stephen3rd

 

Posts: 9
Joined: 13.Mar.2008
Status: offline
Thanks Jason.

How does that impact the reporting side of ISA?

Are you still able to produce the stardard built in ISA reports?

Thanks again.

Stephen

(in reply to Jason Jones)
Post #: 4
RE: SQL 2005 Logging DB - 19.Mar.2008 9:03:23 AM   
Jason Jones

 

Posts: 4663
Joined: 30.Jul.2002
From: United Kingdom
Status: offline
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.

It sounds like purging the database of old data is more what you are looking at, if so have a look here: http://forums.isaserver.org/m_2002010245/mpage_1/key_/tm.htm#2002010246

You will probably need to consider both the webproxy and firewall logs in your example, but this should be easier to work out from the above.

Cheers

JJ

< Message edited by Jason Jones -- 19.Mar.2008 9:19:23 AM >


_____________________________

Jason Jones | Forefront MVP | Silversands Ltd
My Blogs: http://blog.msedge.org.uk/ and http://blog.msfirewall.org.uk/

(in reply to stephen3rd)
Post #: 5

Page:   [1] << Older Topic    Newer Topic >>
All Forums >> [ISA 2006 Firewall] >> Logging and Reporting >> SQL 2005 Logging DB Page: [1]
Jump to:

New Messages No New Messages
Hot Topic w/ New Messages Hot Topic w/o New Messages
Locked w/ New Messages Locked w/o New Messages
 Post New Thread
 Reply to Message
 Post New Poll
 Submit Vote
 Delete My Own Post
 Delete My Own Thread
 Rate Posts