• 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

Logging to SQL-1 Gig per day

Users viewing this topic: none

Logged in as: Guest
  Printable Version
All Forums >> [ISA Server 2004 Firewall] >> Logging and Reporting >> Logging to SQL-1 Gig per day Page: [1]
Login
Message << Older Topic   Newer Topic >>
Logging to SQL-1 Gig per day - 9.Nov.2005 4:33:00 PM   
bniemiec

 

Posts: 1
Joined: 9.Nov.2005
Status: offline
I've recently set our ISA server to log to a SQL database. This is working great, except the database size is growing about 1 gig every day! I've found many posts that suggest doing a backup, then run a truncate table command.

I'd like to be able to provide web usage reports for 1-2 months back. If I start archiving the database, this will make reporting a huge hassle. Any help, input or suggestions would be greatly appreciated!
Post #: 1
RE: Logging to SQL-1 Gig per day - 11.Nov.2005 5:54:34 AM   
MRIS

 

Posts: 58
Joined: 4.Aug.2005
Status: offline
make sure the SQL database recovery mode is set to simple. (is the majority of the growth in the LOG file or the DATA file of the SQL DB?)
Alternatively, consider logging to the included MSDE, which rolls the database file each day, which you may find more managable?

(in reply to bniemiec)
Post #: 2
RE: Logging to SQL-1 Gig per day - 11.Nov.2005 10:28:02 AM   
zetone

 

Posts: 12
Joined: 10.Nov.2005
From: Romania
Status: offline
I had the same problem. Thing is that you cannot filter what is inserted into the database from ISA server level.

Besides using simple recovery mode, another workaround is to filter what is inserted into the DB by using SQL triggers. You have to think about what kind of report you would like to have? There is no way you'll need all the data, even if you'll do reporting for a large enterprise. Base on that you can apply several triggers to the same table, or even a trigger which may include all conditions. Beware though, the more triggers set for INSERT on the same table, the higher SQL performance hit will be. So, play smart. Also consider your SQL machine's muscles...

Triggers are a good way of filtering for example noise traffic, such as ARP requests or other common infrastructure protocols.

Here is an example of a SQL trigger:

create trigger ISAFilter on [table name]
for insert
as
 if exists (select 1 from inserted where [columnname] <> <OK value>)
begin
rollback
end
  

_____________________________

Cheers,
Dragos

(in reply to MRIS)
Post #: 3
RE: Logging to SQL-1 Gig per day - 24.Nov.2005 5:56:47 PM   
wishfly

 

Posts: 122
Joined: 6.Mar.2005
Status: offline
you can try this software -- netfee for ISA server
http://www.netfeesoftware.com/NetFee/Index.htm

(in reply to zetone)
Post #: 4

Page:   [1] << Older Topic    Newer Topic >>
All Forums >> [ISA Server 2004 Firewall] >> Logging and Reporting >> Logging to SQL-1 Gig per day 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