For faster reporting and performance analysis, we want to insert our web server logs into Sql Server. This will allow us to see traffic patterns, issues, slowdowns in near real-time.
We have a daemon that listens for request/response events from our load balancer and bulk inserts into the database.
However, we get around 1 GB of logs per day and we only need to keep about a week around (at least in this raw form).
What is the best way to store this data and the best way to delete old entries?
We've talked about storing each day's data in its own table, e.g.
Log_2011_04_07 would have all the entries for that day, and then dropping the oldest table. A view could be created to span all the day tables for easy querying. Is the feasible?