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?
Best Answer
You should look in to partitioning.
http://technet.microsoft.com/en-us/library/dd578580%28SQL.100%29.aspx
The cool thing about partitioning is that you have just one table name (as opposed to the multiple table approach) so your insert statements remain static. It works with every application - it's completely transparent to queries. You don't have to worry about what happens if you end up with different indexes or statistics on each of the tables, either.
You create a partition function that decides how to break up the table into multiple tables behind the scene. The function can only take one input parameter/field, and in your case, it would be a date field. The function can break up the table by date, week, month, or year - in your case, you'd want date, 24-hour period.
Then build a SQL Server Agent job that uses T-SQL to swap out the last partition every day. The delete becomes a metadata operation, and it's blazing fast. Swap the partition, then drop the old one out.