7

how do I configure IIS to post logs to sql server?

How want to save ANY log to my site. the site has a lot of views and the data can be big in a very small time. In addition, I want to show analytics of the use, in real time, and for all the time.

  • Is it make sense to save all the logs in sql-server?

  • What is the pros and cons taking this approach have?

  • what other solution can I have?

stacker
  • 831
  • 3
  • 10
  • 15

3 Answers3

5

Microsoft has courteously created an entire KB article called "How To Use SQL Server to Analyze Web Logs" which should answer your title question.

Yes it makes sense to store the logs in sql server if for no other reason than mitigating file corruption issues and also making backups of those files easier using SQL scheduler. The pros of using a SQL Server database are virtually self evident. Flat text files offer no data mining ability. Relational databases that can be queried via SQL are made for information storage and retrieval. That information can easily be culled for trends, statistics, correlations and etc. It's all a matter of developing the queries and reports.

What other solution? Possible using Splunk to gather information from the flat files, but that's not the same. That's not statistics in quite the same way.

Wesley
  • 32,320
  • 9
  • 80
  • 116
  • Is it make sense to store this information *forever*? I mean this table going to be big... – stacker May 16 '10 at 07:41
  • @stacker "Big" is relative. As long as you have the hard drive space, SQL Sever can handle it. Tables of tens f millions of rows are nothing for the database engine. Of course, performance while parsing or mining that daa is another story. You could implement table paritioning or once in a while split the data by month or year ino its own table. Personally, if I had the space I'd keep the data around for at least three years for trending purposes, bu I'm a bit of a data packrat. – Wesley May 16 '10 at 18:58
  • 2
    The link in the answer is not working anymore. – tRuEsAtM Apr 20 '18 at 17:39
3

Do not do it - it introduces another hugh system into something as simple as writing log files. Resetting the SQL Server interrupts the logging process.

What I Do is:

  • Logfiles onto a disc
  • Regular load scripts loading them into sql server. On an error, they just get reloaded after an hour with the next attempt ;)
TomTom
  • 50,857
  • 7
  • 52
  • 134
  • The log files go any way to sql-server... so why shouldn't they go there on the beginning? – stacker May 16 '10 at 07:40
  • Good point about restarting the SQL Server interrupting the logging process. What do you use for loading scripts? An SSIS package? – Wesley May 16 '10 at 19:01
  • Some people like me do not have a choice. We're using software that forces this configuration on us. – chaostheory Oct 06 '20 at 21:08
2

Also see MS LogParser for loading IIS log FILES into SQL Server. e.g. http://www.jaygeiger.com/index.php/2010/11/09/load-iis-log-files-into-sql/

BJG
  • 21
  • 1