Category: SCOM

May 28 2010

Buy vs. Build

It’s the classic question faced by everyone in Information Services. I know how to do this and I could build software to do it, but I’m a lazy b_____d so I’d rather just pick up a piece of software that does it for me. I love working for large companies because I can usually get them to purchase stuff so that I can loll around stuffing my face all day instead of doing actual work. Unfortunately, not everyone can afford to pick up Microsoft’s Operations Manager or Idera’s Diagnostic Manager. But you still need to monitor your servers. With buy eliminated, that leaves build.

Which, is where this excellent blog post by Laerte Junior comes in. He lays out how to build a wrapper around calls to get Performan Counter information using PowerShell. It’s a pretty slick and worth a read. Becuase the thing is, when you need to build your monitoring products, you want to use a language that you know. Since everyone is learning Powershell (right?) this provides a good foundation for beginning your monitoring project.

Dec 23 2009

A Lack of Excitement

I usually use all the problems, crashes, and issues that I run into at work as grist for my mill, aka, material to blog about. But lately, we haven’t been crashing & burning much <knock wood, turn three times, throw salt over my left shoulder, spit>. But it was suggested that may be I should mention why that is.

The fact of the matter is that I’ve been spending a lot more time working on methods for monitoring our systems so that we avoid more of the stupid stuff, full disks, failed backups, long running agent jobs, etc.. I’ve blogged before about our use of Microsoft’s Operations Manager for monitoring our servers and how we’ve built custom rules and monitors to keep an eye on things. I’ve also mentioned how we use Idera’s Diagnostic Manager as a drill-down mechanism to keep on eye on SQL Server internals that OM just didn’t do as nifty a job on. What I haven’t mentioned is, that after attending the PASS Summit (and if you didn’t go, you sure missed out) and sitting through Buck Woody’s session on monitoring using Policy Based Management, “More Servers Less Control,” I’ve implemented PBM within our organization.

If you have yet to explore Policy Based Management, PBM, get on it. I know that others have blogged about their experiences with it, and there’s a reason that people are excited. Track down Buck Woody’s session over at the PASS site (it’s worth paying for it, trust me).  You too can get this stuff up and running in no time. That’s the good news. Now for the bad news, PBM only works with SQL Server 2008. But, I’ve got some good news about that too. There’s a great utility published over at CodePlex (and isn’t that a great place to browse on occasion) called the Enterprise Policy Management Framework, EPM (you can never have enough acronyms, especially with three letters). The EPM Framework allows you to run policies against 2000 and 2005 servers. Sweet!

All of this is work has been part of a concerted effort within our organization to get really and truly proactive, to prevent errors before they occur. Between the modifications I’ve made to OM and the implementation of PBM through EPM and reports against the OM data through SSRS, SQL just isn’t having as many problems these days (I love talking through acronyms sometimes just to watch peoples toes curl). I’ve been busy as all get out, but it’s on pretty mundane, non-exciting stuff. Hence fewer blog posts.

Dec 01 2009

How do -You- use SQL Server

I’ve been tagged by a misplaced yankee, uh, New Englander, whatever. The question is, how do I/we use SQL Server where I work. That’s a tough one. It would make a much shorter, and easier, blog post to describe the things we don’t use it for. However, keeping with the spirit of these tags, I’ll try to lay out it.

For those that don’t know, I work for a rather large insurance company. This means that we have lots and lots of databases, but not much data. We also are cheap. That means we’ll run an app into the ground rather than spend the money & time to replace it. We have apps still running from the 70′s and 80′s propped up by ancient men with pocket protectors, spit, bailing wire and happy thoughts. This also means that we have apps running on SQL Server 7, 2000, 2005 and 2008. Give me a couple of weeks and I’m sure I can get an R2 app deployed. There is also a few Oracle databases, our warehouse and Peoplesoft in particular. We even have a DB2 and, I think, one Sybase database somewhere.

I don’t want to go into lots of details about the type of data we store, lest I get in trouble, but basically, think insurance and you’ll get a pretty good idea of a lot of it. Add in the fact that my company prides itself on engineering to avoid risk and you’ll know that we gather quite a bit of information about the things that we insure. There are lots and lots of very small databases. Our largest db’s are just breaking 100gb, but must are in the 20-60gb range. We have a ton of OLTP systems gathering all the different data. These have been developed in just about every possible way. We even have a couple of systems using nHibernate under development. We move, mostly, pretty standard structured data. We have a few processes that are using XML, mostly from third party sources, to import data, so we’ve learned how to shred that stuff into the database. Spatial data, insurance remember, is the big new thing on the block. We’re seeing lots more implementations taking advantage of this. We don’t see much in the way of unstructured data, but some of the reports from the engineers falls into this realm. We also get quite a few photo’s from them that want us to store. We’re working on using FileStream to keep those in sync with the db rather than storing them within the database itself.

Currently, and I hate this, the overwhelming majority of our OLTP data is collected in SQL Server. All our datamarts used for reporting are in SQL Server. BUT, in the middle sits our Oracle warehouse. So we have to convert our data from SQL Server into Oracle and then back into SQL Server. It’s fun. Swapping data types over & over, shrinking column names only to re-expand them into something a human being can read… It’s lots of fun.

We use SSIS for almost all our ETL processes, although we have a few DTS packages still chugging away on some of the 2000 servers. We’re running a bit of replication, but nothing major. We have several fail-over clusters in production. We’re running virtual machines in lots of places. We’re expanding our Reporting Services implementation pretty rapidly. After attending Buck Woody’s sessions at PASS this year we’re getting Central Management Servers and Policy Based Management implemented.

Most of my time is spent working with development teams. We do most our deployment work using Visual Studio. I do database design, stored procedure writing and tuning, data access methods… you name it and I’m involved with it. The level of our involvement varies from team to team, but by & large, we get involved early in most development projects and are able to influence how databases are developed.

For monitoring we’re using Microsoft System Center Operations Manager (or whatever it’s called this week). We’ve modified it somewhat, adding special rules & monitors to keep an eye on the system better. We also use Idera’s SQL Diagnostic Manager to help keep a more detailed eye on the systems. I already mentioned that we use Visual Studio for most of our development & deployments. We also use several Red Gate products, Compare, Data Compare, Prompt, pretty much every day.

That’s pretty much it. We keep busy and keep the systems up, running & happy most of the time.

Because I think they’ll have interesting answers, I’m going to pass this on to Jeremiah Peschka and Tim Ford.

Oct 21 2009

Characters

No, I’m not talking about a Dickens novel. I’m talking about the number of characters in a string. I had a painful time recently because of the word “characters.” 

If you take a look at the dynamic management view sys.dm_exec_sql_text you can get the queries that have been run on your system that are still in the cache. It’s a great utility. Better still, you can get specific statements from the code that are actively running through sys.dm_exec_requests or ones that have run through sys.dm_exec_query_stats. To do this is very simple. Each of these DMV’s has a pair of columns, statement_start_offset and statement_end_offset. These columns, and I’m quoting directly from books online measure the “number of character” offset from the beginning of the SQL string and from the end of the SQL string. Using these values you can retrieve an individual statement out of a stored procedure that has multiple statements.

But… Here’s where things get tricky. Try this on your machine:

SELECT SUBSTRING(dest.text, (der.statement_start_offset ) + 1,
(der.statement_end_offset - der.statement_start_offset) + 1)
,LEN(dest.text) AS CharLength,
der.statement_start_offset,
der.statement_end_offset
FROM sys.dm_exec_query_stats AS der
CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) AS dest
WHERE der.statement_end_offset > -1

You might get an error or you might get a bunch of really odd looking statements in the first column, starting part way into TSQL and cutting off after they’re done or before they’re over. It’ll look odd. But what’s the deal? The SUBSTRING function should work. Logically it’s configured correctly. Here’s the problem.

The [text] column in sys.dm_exec_sql_text is of the datatype NVARCHAR(MAX). Unicode. If you look at the length of the text, it’ll tell you exactly how many characters you see in the string that called to your server. But, the statement_start_offset and statement_end_offset are measuring something different. They’re not measuring characters, they’re measuring unicode characters. Try this query instead:

SELECT SUBSTRING(dest.text, (der.statement_start_offset / 2) + 1,
(der.statement_end_offset - der.statement_start_offset) / 2+ 1),
LEN(dest.text) AS CharLength,
DATALENGTH(dest.text) AS DLength,
DATALENGTH(dest.text) / 2 AS HalfDLength,
der.statement_start_offset,
der.statement_end_offset
FROM sys.dm_exec_query_stats AS der
CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) AS dest
WHERE der.statement_end_offset > -1

You can see that the character length is, whatever it’s supposed to be, but the DATALENGTH is twice that much. Unicode, as we all know, includes a byte to identify the character set. That’s included in the character count in statement_start_offset and statement_end_offset.  You need to take that into account when dealing with these “characters.”

Oct 13 2009

Operations Manager Feedback

If you’re involved with maintaining Microsoft Operations Manager, you probably have a few things you’d like to tell the developers, such as, couldn’t you get some sort of override manager/explorer built into the system? Well, they’ve built a new feedback mechanism through the updated Connect interface. Get on over there and make your voice heard.

Yeah, I wasn’t really tracking it either until I saw this post from the OM development team’s blog.

Mar 16 2009

Tom LaRock on Reports In Operations Manager

Tom has published a great article on reporting in Operations Manager over at Simple-Talk. In case you don’t know, Tom is one of the best when it comes to OM on SQL Server. He’s presented at PASS on these topics for the last two years and has written other articles as well.

Nov 25 2008

Sign Me Up!

I am joining the battle. It’s the Battle Against Lawless Database Design (BALD-D or baldy). Because, after all, enough is enough.

I encourage you too to join the battle. Cross your arms, join the battle cry! Enough is enough!

Oct 29 2008

Custom Monitor for SQL Agent in Operations Manager

Tom Larock has a new article on how to create a custom monitor for SQL Agent. It’s worth a read. He walks you through a series of concepts that are pretty important if you want to track individual Agent jobs in custom fashion.

Jun 18 2008

SQL Server Management Pack Resources

There aren’t any. That’s not entirely true. There is the SQL Server Management Pack Guide from MS. It gives you the basics. After that you have to fall back generic documentation and help for the most part. If you haven’t already, get a copy of System Center Operations Manager 2007 Unleashed. It doesn’t cover the SQL Server Management Pack in any detail, but it gives you a lot of what you need to understand the management pack in general. The next best source of information is the newsgroup microsoft.public.opsmgr.sql. From there, you have to go to System Center Forum. Watch for articles by Tom LaRock. He seems to know as much about it as anyone. He’s presenting at the PASS Summit this year, so you might want to attend. There are occasional articles and posts over at SQL Server Central. You might keep an eye on the MS Team Blog, but I don’t recall seeing much that was SQL Server Management Pack specific. That’s all I’ve got. If anyone has anything more, please post it in the comments. I’ll add updates as I get them. Specifically, I’d like to see some good resources on creating custom reports against Operations Manager data.

Jun 05 2008

Operations Manager 2007 SQL Collection Article

It took me a while to find all the little pieces to put together a custom rule that used data collected through TSQL. In fact, because it took so much work to pull all the little pieces together, I wrote an article describing how I did it. This isn’t a wonderful new invention. It’s just the publication of a bunch of research. If you’re an Operations Manager expert, this isn’t for you. If you’re like I am, trying to find your way through the forest, this little bit of map will hopefully provide some assistance. Available at SQL Server Central.