How to configure IIS to post logs to sql server

.net-4.0analyticsiis-7.5loggingsql-server-2008

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?

Best Answer

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.

Related Solutions

Replaying SQL Server transaction logs on changed db schema

Transaction logs contain the binary deltas of modifications applied to a database since the last log truncation; the keyword here is "binary": they don't contain SQL queries or something similar, but are actually more akin to binary patches to be applied to a program.

For this reason, they can be replayed only on the exact physical database files they were originally linked to; replaying them on another database (even with the same schema) would be exactly like applying a patch to a different executable to the one it was written for: not possible at all.

You also can't replay them on the same database, if that gets modified; i.e., you can't restore the database from an old backup, bring it online, make any modification at all to it and then replay the logs against it; you actually lose any log-replaying capability as soon as you bring the database fully online (there's even a specific flag in SQL Server restore operations for that).

ApplicationPoolIdentity IIS 7.5 to SQL Server 2008 R2 not working

I had the same issue as well. As per this page, the ApplicationPoolIdentity is supposed to access network resources as the machine account (DOMAIN\COMPUTERNAME$), but it was using NT AUTHORITY\ANONYMOUS LOGON to access SQL server instead.

I was able to use this hotfix to get ApplicationPoolIdentity working as the doc says it should. This hotfix doesn't specifically describe a solution for accessing network resources as NT AUTHORITY\ANONYMOUS LOGON, but it's related to the computer password changing.

This stackoverflow thread helped me find this solution:

Other useful tips for debugging ASP.NET identity and SQL identity

Since SQL integrated auth is related to the thread identity, it's useful to be able to view your user identity, thread identity (which changes depending on impersonation), and the SQL server login. This ASPX snippet displays all three:

<%@ Import Namespace="System.Security.Principal" %>

<div>ASP.NET Request User identity: <%= User.Identity.Name %></div>
<div>Current thread/process WindowsIdentity: <%= WindowsIdentity.GetCurrent(false).Name %></div>
<asp:SqlDataSource ID="CurrentUserData" runat="server" ConnectionString="<%$ ConnectionStrings:ConnID %>"
           SelectCommand="select SYSTEM_USER" />
<asp:DetailsView DataSourceID="CurrentUserData" runat="server" />

Best Answer

Related Solutions

Replaying SQL Server transaction logs on changed db schema

ApplicationPoolIdentity IIS 7.5 to SQL Server 2008 R2 not working

Related Topic