August 29, 2014 at 1:50 am
When writing stored procedures I favour the RAISERROR method (albeit I will follow whatever the local standard is or what the local DBAs demand - I work freelance so these are the guys that will live with what I do so I have learnt that it is better to attempt to influence towards improvement but be compliant).
When coding for .NET we use loggers (e.g. Log4Net, NLog, etc.) that can be configured to log anywhere. We can log to text files, SQL Server, Windows Event Log so usually integrate with the local monitoring technology employed as these usually can be configured to pick up logs/events from these repositories.
Instrumentation is the most overlooked piece of the puzzle in software development today, in my opinion.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
August 29, 2014 at 2:11 am
Application problems are not necessarily easier solved by logs from a software layer that sits at the lower end end of the architecture pyramid (the database being the foundation of the application and all that). The database generally does not provide all the context needed to pinpoint what is really happening. You need to know from what webpage code was invoked and what did the user submit in his form, and which user was this and what did that user do before, etc.
Even with proper logging and this rich context available, a person reading the error must interpret it and understand the processes that the application uses. Something technical might go wrong (a deadlock or log space is full), but without understanding the processes of the application it still would be mostly useless.
Logging should be done and handled by the entire tier of the software pyramid to give as much context to an error as possible. This is why there most certainly has to be something for it build into the top-most application layer. The consumers of this information are people that KNOW the application well, not just any server administrator. Those have to use their own technical application unspecific logs to work independently. Even better they should have monitoring software that provide alarms long before most technical issues become apparent to applications.
In practice we have expose errors in two different ways.
1. We let an application log to a application specific folder that stores an index and a human readable error page (html) that exposes as much relevant information as possible. Including CGI, form and session variables as well as the executed query and its parameters. It also adds code snippets based on the stack trace thus we can instantly see what code was actually being executed. We have a error viewer that reads the index and groups similar errors and at which times these occurred. You can zoom in onto these to get to the html page of an individual error.
2. Essentially the same as option 1, but this is server wide for application that do not have their own error handling.
We have made preparations for gathering all of these errors into a single place, with a single more advanced viewer and an alert system. We just haven't found the time to finish that and use the current logs and viewers to analyze problems as we become aware of them.
August 29, 2014 at 3:45 am
peter-757102 (8/29/2014)
...We have made preparations for gathering all of these errors into a single place, with a single more advanced viewer and an alert system. We just haven't found the time to finish that and use the current logs and viewers to analyze problems as we become aware of them.
All good Peter, however, I believe Steve's point is that it is almost pointless logging defects unless they are integrated into the monitoring systems so that, without relying on reports from users, the operational support team can be alerted to those defects otherwise the logs will remain unread.
Logging is great for debugging and diagnosing purposes but used in isolation it is a poor monitoring system (although it can be a good input into monitoring systems like Splunk, for example).
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
August 29, 2014 at 4:06 am
Gary Varga (8/29/2014)
peter-757102 (8/29/2014)
...We have made preparations for gathering all of these errors into a single place, with a single more advanced viewer and an alert system. We just haven't found the time to finish that and use the current logs and viewers to analyze problems as we become aware of them.All good Peter, however, I believe Steve's point is that it is almost pointless logging defects unless they are integrated into the monitoring systems so that, without relying on reports from users, the operational support team can be alerted to those defects otherwise the logs will remain unread.
Logging is great for debugging and diagnosing purposes but used in isolation it is a poor monitoring system (although it can be a good input into monitoring systems like Splunk, for example).
Almost pointless is a gross overstatement in my opinion, it certainly depends on the organisation. Certainly it is preferable that the required people are aware of an issue before calls from customers come in. Right now we have to check our log viewers manually as there are no automated alerts, unless coded in explicitly (we have those cases too).
As for Splunk, I did not know of this product, thus checked their site quickly and I cannot say I feel attracted to it for various reasons.
August 29, 2014 at 6:32 am
I also use logging to track performance issues. As an ETL guy that writes custom programs to pull data from various and eclectic sources, often times the log is the best indicator of query and procedure performance. Helps resolve the finger pointing when a source is overloaded or a connection string/credentials has been changed without prior warning.
Hooking the log results to a ALS challenge machine that bathes users that change file schemes without warning is completely optional...
August 29, 2014 at 6:47 am
I often used the RAISERROR as a developer, though only for logging. Designing data warehouse ETL I provide a feedback file that is sent to operations listing all the orders for which some non-standard or missing critical data exists (eg open orders with closed dates, missing zip). This allows someone to take action on an order about which they would otherwise know nothing. Some ETL developers prefer to let users find the dirt, but I don't understand this at all, and it seems the ETL equivalent to the point of this article.
August 29, 2014 at 8:01 am
In some of the software I've written I've made it write to the event log. And personally I really like doing that. I can put in detailed information as to what went wrong. However, at least in my practice I've created a new source in the event log, so that I can quickly find the error message when opening the event viewer. However, that requires administrative privileges to create that source in the event log. Some users I've dealt with don't like my having to get onto their machine and do that. So at least from my interaction with the users I've hit a mixed bag about this.
As an aside, I've love to know if there is a way of getting that new source into the user's event log without actually having to go to their machine and enter it? I suppose it might be possible with a group policy, but doesn't that require the Windows Server to be as new as or newer than the Windows clients? (Often our Windows Server lagged behind the clients by years.)
Rod
August 29, 2014 at 8:14 am
peter-757102 (8/29/2014)
Gary Varga (8/29/2014)
peter-757102 (8/29/2014)
...We have made preparations for gathering all of these errors into a single place, with a single more advanced viewer and an alert system. We just haven't found the time to finish that and use the current logs and viewers to analyze problems as we become aware of them.All good Peter, however, I believe Steve's point is that it is almost pointless logging defects unless they are integrated into the monitoring systems so that, without relying on reports from users, the operational support team can be alerted to those defects otherwise the logs will remain unread.
Logging is great for debugging and diagnosing purposes but used in isolation it is a poor monitoring system (although it can be a good input into monitoring systems like Splunk, for example).
Almost pointless is a gross overstatement in my opinion, it certainly depends on the organisation. Certainly it is preferable that the required people are aware of an issue before calls from customers come in. Right now we have to check our log viewers manually as there are no automated alerts, unless coded in explicitly (we have those cases too).
As for Splunk, I did not know of this product, thus checked their site quickly and I cannot say I feel attracted to it for various reasons.
So it is integrated into the monitoring system. It is just that your monitoring system is manual. As for the almost pointless, I was referring to places where they think that if it is logged then that is job done.
I fear that I may have offended. I certainly did not intend to do so.
BTW, I have to love of Splunk either but it was just the first product of that type that sprang to mind.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
August 29, 2014 at 8:39 am
Doctor Who 2 (8/29/2014)
In some of the software I've written I've made it write to the event log. And personally I really like doing that. I can put in detailed information as to what went wrong. However, at least in my practice I've created a new source in the event log, so that I can quickly find the error message when opening the event viewer. However, that requires administrative privileges to create that source in the event log. Some users I've dealt with don't like my having to get onto their machine and do that. So at least from my interaction with the users I've hit a mixed bag about this.As an aside, I've love to know if there is a way of getting that new source into the user's event log without actually having to go to their machine and enter it? I suppose it might be possible with a group policy, but doesn't that require the Windows Server to be as new as or newer than the Windows clients? (Often our Windows Server lagged behind the clients by years.)
Here is how to do it in PowerShell:
$logName = "Your Log Name"
$source = "Component Name"
$found = $false
# Check whether it already exists.
Get-EventLog -List | ForEach-Object { if ($_.Log -eq $logName) { $found = $true } }
# If not then create it.
if ($false -eq $found) { New-EventLog -LogName $logName -Source $source }
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
August 29, 2014 at 8:49 am
The point isn't that you log everything to a central location for all debugging, but that you integrate into a management system that's being watched. If you don't have one, you might want one, regardless of if you're a 3 server, 2 person company or 1,000 server/10,000 person enterprise. Having things viewable at once is handy. It helps debugging with problems that might exceed the scope of your one application/database/server.
The idea is also that if you can help someone solve a problem without calling the developer, you should. Not necessarily the first time, but if you log some error, say a duplicate customer, then the second or third time someone sees it, perhaps they can solve it without an escalation.
August 29, 2014 at 8:58 am
Steve, your last point is dead on:
"... It's a maturity point where we recognize that we're building interconnected systems, not individual bits of software that live like hermits. "
As to which is the best, that can become somewhat of a philosophical debate.
The more you are prepared, the less you need it.
August 29, 2014 at 10:44 am
The trouble with thinking you can get stuff into the error log with RAISERROR is quite simply that most SQL should be running without SA privilege and probably without ALTER TRACE permission. Error handling often rolls back transactions, which makes logging to a table difficult. Yes, I can leave stuff around in variables (including table variables) that survives the rollback and then log the stuff in a new transaction after the rollback, but that still won't be in the system error log anyway and must be into a table that the code currently running can write, so if there are several apps which don't all trust each other I'm going to have to cope with multiple separate error log tables, so maybe I end up with a trusted app which monitors those tables and moves data from them to a common destination, which is probably a nightmare since different apps require different data structures to describe their errors. Or I can provide an SP which runs with extra permissions, give principals with low privileges permission to call it, and have it write to a single table, with the same data structure nightmares which are also a reason why RAISERROR wouldn't be a good method even if low privileged code could use it to log - unless of course I'm going to serialise everything and pray the result fits into the 2047 byte limit imposed by RAISERROR (in my experience it often won't). But 95% of errors are of no interest or concern to ops staff, they are of concern only to support staff and are specific to an individual app so why should anyone want to common things up like this anyway? The other 5% are of concern to support staff, and get into the error log anyway without any SQL code having to do anything about it.
I've been very rigorous about error management generally, including proper logging; but logging every error, whatever it is, to a single central point so that ops people can see it always struck me as lunacy, so I've never done it .
In my world everything logs to wherever is convenient for it; and all those convenient places are monitored by jobs that run frequently, pull out new stuff and format it into nice email meesages and attachments and email them to the appropriate people, plus generate daily, weekly and monthly summaries and email them to the appropriate people. Where possible as well as logging the software that detects the error sends email (and what it logs indicates that it has done so, to avoid duplicates).
Tom
August 29, 2014 at 12:38 pm
Gary Varga (8/29/2014)
Doctor Who 2 (8/29/2014)
In some of the software I've written I've made it write to the event log. And personally I really like doing that. I can put in detailed information as to what went wrong. However, at least in my practice I've created a new source in the event log, so that I can quickly find the error message when opening the event viewer. However, that requires administrative privileges to create that source in the event log. Some users I've dealt with don't like my having to get onto their machine and do that. So at least from my interaction with the users I've hit a mixed bag about this.As an aside, I've love to know if there is a way of getting that new source into the user's event log without actually having to go to their machine and enter it? I suppose it might be possible with a group policy, but doesn't that require the Windows Server to be as new as or newer than the Windows clients? (Often our Windows Server lagged behind the clients by years.)
Here is how to do it in PowerShell:
$logName = "Your Log Name"$source = "Component Name"
$found = $false
# Check whether it already exists.
Get-EventLog -List | ForEach-Object { if ($_.Log -eq $logName) { $found = $true } }
# If not then create it.
if ($false -eq $found) { New-EventLog -LogName $logName -Source $source }
That's good to know, Gary, thanks!
Rod
August 29, 2014 at 1:31 pm
We do utilize logging, but mostly from the application area so that (as some have said) we can get more descriptive information about the error and its cause. And no, we don't have a single common area to log all errors - we just send emails for errors and rely on people to check them. Most times WE are the equivalent of the "operations center". 😀
Steph Brown
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply