Use SQL Server to log applications. Pros and cons?

Question

I have a multi-user application that stores a centralized log file for activity. Right now, this magazine is going to text files to a tune of about 10 MB-50 MB / day. Text files are changed daily by the registrar, and we save the last 4 or 5 days. Older than this does not interest us.

They are rarely read: either when developing an application for error messages, diagnostic messages, or when the application is in the process of production to sort by problem with a user message or error.

(This is strictly an application log. Security logging is stored elsewhere.)

But when they read, it’s a pain in the ass. Grepping 10MB text files are not even interesting with Perl: fields (transaction ID, user ID, etc.) in the file are useful, but just text. Messages are recorded sequentially, for example, one at a time, so intermittent activity moves when trying to execute a specific transaction or user.

I am looking for thoughts on this topic. Has anyone done application level logging using an SQL database and liked it? Hated him?

Answer 1

I think that registering directly with the database is usually a bad idea, and I would avoid that.

The main reason is this: a good log will be most useful when you can use it to debug your application after opening it, as soon as the error has already occurred and you cannot reproduce it. To do this, you need to make sure that the journal itself is reliable. And to make any system reliable, its simplicity is a good start.

So, having a simple file log with several lines of code (open a file, add a line, close a file or save it, repeat ...) will usually be more reliable and useful in the future when you really need it to work.

Successful logging on the SQL server, on the other hand, will require that many more components work correctly, and there will be many more possible error situations where you cannot log the necessary information, simply because the log infrastructure itself will not work. And something worse: a failure in the logging procedure (for example, database corruption or deadlock) is likely to affect application performance, and then you will encounter a situation where the secondary component prevents the application of its primary function.

If you need to analyze the logs a lot and you are not comfortable using text tools such as grep, then store the logs in text files and periodically import them into the SQL database. If SQL fails, you won’t lose log information, and it won’t even affect the application’s ability to work. Then you can do all the data analysis in the database.

I think that these are the main reasons why I do not register the database, although I have done this in the past. Hope this helps.

Answer 2

We used the magazine database at my last job, and it was great.

We had stored procedures that would give an overview of the general state of the system for various indicators that I could download from a web page. We could also quickly splash out tracing for a given application for a certain period, and if I wanted to, it would be easy to get this as a text file if you are really just like grep-ing files.

So that the registration system itself does not become a problem, there is, of course, a general code structure that we used among various applications that processed writing to the log table. Part of this structure also included registration in the file, in case the problem is related to the database itself, and part of it is associated with the cyclical use of logs. Regarding space issues, the log database is in a different backup schedule, and it really is not a problem. Space (without support) is cheap.

I think this applies to most problems expressed elsewhere. All this is a matter of implementation. But if I stayed here, it would still be a "not much worse" case, and this was a bad reason to solve the problem of registering a database database. What I liked about this was that it allowed us to do some new things , which would be much harder to do with flat files.

Four major file improvements were discovered. Firstly, this is a review of the system, which I have already mentioned. Secondly, and most importantly, it is checking for the absence of any message application, where we usually expect to find them. These kinds of things are almost impossible to find in traditional journaling, unless you spend a lot of time every day looking at magazines with mind-blowing applications that just tell you everything in 99% of cases. It's amazing how to free up a show to show the missing journal entries. On most days, we didn't need to look at most log files at all ... something that would be dangerous and irresponsible without a database.

This leads to a third improvement. We created a single daily email address, and that was the only thing we needed to consider in the days when everything went fine. The included email showed errors and warnings. Skipped logs were overwritten as a warning by the same db job that sends the email, and the lack of email was a big deal. We could send a specific message to the log to our error tracker with one click directly from the daily email message (it was html-formatted, pulled the data from the web application).

The latest improvement was that if we wanted to keep a close eye on a particular application, say after making changes, we could subscribe to the RSS feed for that particular application until we are satisfied. This is more difficult to do from a text file.

Where am I now, we rely more on third-party tools and their logging capabilities, which means returning to a much more manual review. I really miss the DB, and I suppose to write a tool to read these magazines and rewrite them in the DB to bring these abilities back.

Again, we did this with text files as a reserve, and these are new features that really make the database worthwhile. If all you are going to do is write to the database and try to use it in the same way as the old text files, this adds unnecessary complexity and you can just use the old text files. This is an opportunity to create a system for new functions, which makes it useful.

Answer 3

Yes, we do it here, and I can’t bear it. One of the problems we have here is the problem with db (connection, corruption, etc.), all logging stops. My other big problem is that it's hard to see the problems. We also have problems with table logs that take up too much space and need to worry about truncating them when moving databases, because our logs are so large.

I think it is clumsy compared to log files. It’s hard for me to see the “big picture” when it is stored in the database. I admit that I am a journal person, I like to open a text file and view (regex) it instead of using sql to try to find something.

In the last place where I worked, we had log files of 100 megabytes in size. They are a little difficult to open, but if you have the right tool, it is not so bad. We also had a system for registering messages. You can quickly browse the file and determine which set of log entries belongs to that process.

Answer 4

I think that the problem you are working with in the log can be solved when registering in SQL, provided that you can separate the fields of interest to you in different columns. You cannot treat an SQL database as a text field, and expect it to be better, but it won’t.

Once you get everything you need to get into the columns you need, it’s much easier to keep track of sequential actions on something by deleting it by column. For example, if you had a "input" process, you register everything as usual when the text "input process" is placed in the "logtype" or "process" column. Then, when you have problems with the “input process”, the WHERE clause in this column isolates all input processes.

Answer 5

Previously, we used centralized SQL Server logging, and as mentioned above, the biggest problem was that a disconnected database connection would mean registration was interrupted. I actually finished adding the queue procedure to the protocol, which the DB would try first, and write to the physical file if it fails. You just need to add code to this routine, which in a successful log for db will check if local local records will be queued, and write them too.

I like to have everything in the database, unlike the physical log files, but only because I like to parse it with the reports that I wrote.

Answer 6

we do this in our organization in large volumes with SQL Server. In my discovery, writing to the database is better because of the ability to search and filter. Data performance from 10 to 50 MB and storing them only for 5 days does not affect your application. Tracking transactions and users will be very easy to compare with tracking it from a text file, since you can filter by transaction or user.

You note that files are rarely read. So, decide whether to invest time in development efforts to develop a journal structure? Calculate your time spent searching for logs from the log files during the year and the time it takes for the code and test. If the search time is 1 hour or more per day to search for logs, it is better to write the logs to the database. This can significantly reduce the time spent solving problems.

If you spend less than an hour, you can use some text search tools, such as "SRSearch", which is a great tool that I used, searches from multiple files in a folder and gives results in small snippts ("like google search result" ), where you click to open a file with an interested result. There are other text search tools. If the environment is windows, then you have Microsoft LogParser also a good tool available for free, where you can query your file as a database if the file is written in a specific format.

Answer 7

You can enter comma-delimited text or tabs, or include your logs for export to CSV format. When you need to read from a log, export the CSV file to a table on your SQL server, then you can query standard SQL queries. You can use SQL Integration Services to automate the process.

Answer 8

Here are some additional pros and cons and the reason I prefer log files instead of databases:

• Space is not so cheap when using VPS. Recovering space in real database systems is often a huge problem, and you may have to disable services when restoring space. If your magazines are so important that you have to keep them for many years (as we do), then this is a real problem. Remember that most databases do not recover space when deleting data, because it simply reuses space - it doesn’t help much if you really have not enough space.

• If you regularly visit the logs and you have to extract daily reports from the database using one huge table of logs and millions and millions of records, then you will affect the performance of your database services when querying data from the database.

• Log files can be created, and old logs are archived daily. Depending on the type of logs, massive amounts of space can be recovered by archiving the logs. We save about 6x of space when compressing our logs, and in most cases you are likely to save a lot more.

• Individual smaller files can be compressed and transferred without compromising the server. We used to have logs containing data from 100 GB data in a database. Moving such large databases between servers becomes a serious problem, especially because you must close the database server. I say service becomes a real pain the day you need to start moving large databases.

• Writing to log files as a whole is much faster than writing to the database. Do not underestimate the speed of the IO file of the operating system.

• Log files only suck if you structure your logs incorrectly. You may need to use additional tools, and you may even need to develop your own to help process them, but in the end it will be worth it.

Answer 9

I read all the answers and they are wonderful. But in the company, which I worked because of several restrictions and audits, it was necessary to enter the database. In any case, we had several ways to register, and the solution was to install a pipeline in which our programmers could connect to the pipeline and enter the database, file, console, or even redirect the log to the port that will be consumed by other applications. This pipeline does not interrupt the normal process and saves the log file at the same time as entering the database, which allows you to rarely lose a line. I suggest you study further log4net, which is great for you.

http://logging.apache.org/log4net/

Answer 10

I could see that it works well, provided that you have the ability to filter what needs to be registered, and when it should be registered. A log file (or a table, for example, it) is useless if you cannot find what you are looking for or contains unnecessary information.

Answer 11

Since your magazines are rarely read, I will write them in a file (better performance and reliability).

Then, if and only if you need to read them, I would import the log file into the database (best analysis).

Thus, you get the benefits of both methods.