Suggestions on getting SQL Server errors into SEQ

[Brian Locke]

Howdy all,

I'm polling you for your experiences in logging SQL errors from SQL Server 2012 and 2005 to our Seq server.

I know there's a number of ways to do this and wanted to see any of you *have* done it and with great success.

Here's the idea's I've come up with so far:

1. Log SQL errors to an error table & write C# service to poll and write to Seq. (much like the SQL river for Elasticsearch)

2. Log SQL errors to EventLog, use ngix or C# service to poll and write to Seq.

3. Write a C# object to use for SQL to log via Serilog. (available for Sql 2005?)

Thanks much!

Brian

[Ian Yates]

Oh, good idea :)

Reading your suggestions, it sounds like you're trying to weave into the try/catch handling in T-SQL, correct?  Probably using the CLR (option #3) is best IMHO simply because #1 could be tricky with transaction rollbacks.

I was excited reading this in my email because I read your first sentence and assumed (incorrectly) that you wanted the SQL Server log itself to find its way into Seq.  For me that'd be gold as a bunch of issues on our client sites are to do with SQL Server running out of space or being generally cranky (for good reasons) - too many use full recovery on databases but misconfigure transaction log backups, etc.  The logs show that sort of thing nicely.

Back on topic...

IIRC, the CLR in SQL Server is a fairly hostile environment as far as background threads are concerned, but perhaps using Serilog and a custom sink that doesn't batch things - so it doesn't need a background thread - could do the trick. 

Sorry I don't have an answer but I'll watch this thread with interest

[nblum...@nblumhardt.com]

Ian, I'm also wandering off-topic I realise, but are the SQL Server logs you're interested in from the Windows Event Log? If so, I've been thinking about building some kind of collector - it'd be good to know if yours is one and the same scenario.

Brian, I haven't attempted this myself but it'd be great to hear what direction you go with it!

Cheers,

Nick

[Ian Yates]

Hey Nick, I was going to say that's not quite the same, but a quick checked showed we're talking the same thing (which was news to me - I didn't realise SQL Server put out the same messages to the Windows event log).

I usually access the SQL event logs via SQL Management Studio - see attached image.  It's often faster for me than bringing up the Windows event viewer.  That's probably because the data is retrieved from [MSSQLRootDir]\MSSQL\Log\*.*. 

A generic tool for Seq that uses the Windows event log is probably smarter and more broadly applicable :)

[Brian Locke]

Howdy all,

I just realized that I misspelled NXLog as ngix in option 2. (http://nxlog.org/products/nxlog-community-edition)

I imagine there's a way to feed Seq via an nxlog configuration. I'll play around with it this week.

Thanks for the input!

Brian

[Ian Yates]

Hi Brian,

That's an interesting utility!  Keen to see what you might find.  At worst it might require a little broker between it and Seq, but I've only read through a few of the info pages so far so I could be missing something.