Logging large volumes of activity in a production MVC / SQL application

Question

We are happy users of the ASP.NET MVC and SQL Server infrastructure currently using LINQ-to-SQL. It meets our needs with a consumer-oriented application with approximately 1.4 million users and 2+ million active users per month.

We have long needed to register all user actions (article views, search queries on our website, etc.), and we are trying to use the right architecture for this.

We would like the archiving system to be its own essence, and not part of the main SQL cluster, which stores production articles and a search engine. We would like it to be its own SQL cluster, starting from the first window.

To simplify the problem, let’s say, we just want to register the search conditions that these millions of users enter on our site for a month, and we want to do this with at least an intensive cycle.

My questions are: (1) Is there an asynchronous way to dump search queries to a remote box? Does LINQ support async for this?

(2) Would you recommend creating a cache of 1000 entries (userId, searchTerm, date) in the RAM cache and then flushing them at intervals to the database? I assume that this method will reduce open / close connections.

Or am I thinking about this completely wrong? We want to strike a balance between ease of implementation and reliability.

Answer 1

1) Of course, you can find a solution to achieve this. Linq is not the tool you need. 2) There should not be any major improvements, by doing this, "logging" will only work when performing a search. In the end, you get two calls instead of one, not a big deal.

Intended to use AOP

You can create a clean and separate logging level using Postsharp (there are other alternatives). Then you will decorate your actions with the required logging attribute only when you need to track what is being transferred to the action. The main advantages of this approach are:

• Logging logic is not inside your code (you do not need to change the method code), but it runs before / after your method.
• Clear Aspect selection from target method.
• You can easily enable / disable aspects

AOP is a common practice, especially when it comes to behavior that can be added to several methods such as logging, authentication, etc. And yes, this can be used asynchronously.

Answer 2

1) I suggest you create an HttpModule that will “catch” all search terms used by users. How and where you will unload this information (you said that you will use the SQL box), this is another thing that goes beyond the scope of the module, which should simply capture search topics. You can then create a component that contains the login to save this information using an Async call (or even a third part component such as Log4Net).

2) if you want to create a kind of batch insert by caching all the information you need and at some point dumping it to SQL, I would use MSMQ or any other technology that supports Reliability : I think you want to lose all this information in system failure, etc.