IIS 访问日志到 SQL 规范化
我正在寻找将 IIS 6.0 访问日志（5 台服务器，每天超过 400MB）插入 SQL 数据库。让我害怕的是尺寸。您复制了很多信息（即网站名称、URL、引用者、浏览器），可以通过索引和查找表进行标准化。
我寻找自己的数据库而不是使用其他工具的原因是，有 5 台服务器，我需要针对每台、少数或全部服务器进行非常自定义的统计数据和报告。另外安装任何（特别是开源）软件都是一场屠杀（需要有 125% 的功能并且需要几个月的时间）。
我想知道最有效的方法是什么？有人看过有关它的例子或文章吗？
I am looking for insert IIS 6.0 access log ( 5 servers, and over 400MB daily ) to SQL database. What scares me is the size. There is a lot of information you are duplicating (i.e. site name, url, referrer, browser) and could be normalized by index and look-up table.
Reason why I am looking for own database instead using other tools is that is 5 servers and I need very custom statistics and reports on each, few or all. Also installing any (specially open source) software is massacre ( need have 125% functionality and take months ).
I wounder what would be the most efficient way to do it? Is someone saw examples or articles about it ?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果您坚持单独使用，我建议您购买一个像样的日志解析工具，看看 Log Parser
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=890cd06b-abf8-4c25-91b2-f8d975cf8c07&displaylang =en
帮助你做一些繁重的列表，要么进入 SQL，要么它可以直接得到你想要的结果。
Whilst I would suggest buying a decent log parsing tool if you insist on going it alone, take a look at Log Parser
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=890cd06b-abf8-4c25-91b2-f8d975cf8c07&displaylang=en
to help you do some of the heavy listing, either into SQL or maybe it can get the results you are after directly.
一方面，通过对服务器 IP 地址、用户代理和引用者等内容使用人工键，您将大大减少值的磁盘空间。您节省的部分空间将因索引而丢失，但每天节省的总体磁盘空间为 400 MB（乘以 5 个服务器）应该仍然是可观的。
当然，权衡是需要使用联接将这些信息重新组合在一起以进行报告。
我的挑剔是，用两列查找表的人工键替换一列的值不应该被称为“规范化”。您无需识别任何功能依赖性即可做到这一点。 （我不确定您是否打算这样做，但听起来确实如此。）
您查看的原始数据约为每月 12 场演出，对吧？您是否考虑过从数据仓库的角度来处理它？ （而不是 OLTP 的观点。）
On the one hand, you will reduce disk space for values a lot by using artificial keys for things like server IP address, user agent, and referrer. Some of that space you save will be lost to the index, but the overall disk savings for 400 MB per day, times 5 servers, should still be substantial.
The tradeoff, of course, is the need to use joins to bring that information back together for reporting.
My nitpick is that replacing one column's values with an artificial key to a two-column lookup table shouldn't be called "normalizing". You can do that without identifying any functional dependencies. (I'm not certain you're proposing to do that, but it sounds like it.)
You're looking at about 12 gigs a month in raw data, right? Did you consider approaching it from a data warehousing point of view? (Instead of an OLTP point of view.)