Blog

SQL Server for the IT Helpdesk

Know which servers are running SQL Server, monitor the services, and have an escalation procedure for failures.

Today’s post will be much less philosophical and (hopefully) much more practical. This is a topic that I’ll actually be presenting at work to my fellow IT team members, who are mostly all Helpdesk and Infrastructure people, with no Dev or database background. My goal is to explain the basics of SQL Server from an IT infrastructure perspective, and I hope that others find it useful as well as my team! So let’s get to it. Fair warning: lots of abbreviations ahead! But fear not, I will define them – I don’t want to assume any prior knowledge, other than basic IT skills & Windows familiarity.

admin-disabled-contact-admin
This might be a bad sign…

I also decided to break this up into 2 posts, because it got pretty lengthy.  So, here goes part 1!

SQL Server is, obviously, Microsoft’s relational database management system, or RDBMS. “Relational” means it’s based on the concept of relationships between entities (or objects, if you will; though mostly we mean tables). In layman’s terms, the relations can be expressed as “is a..”, “has a..”, or “has many..”; for example, a Customer object “has a” Contact-Info object, which probably “has many” Phone#s, Emails, etc. SQL Server is a transactional, fully ACID compliant database system – Atomicity, Consistency, Isolation, Durability. I won’t bore you with the computer science theory stuff; you can read all about it on Wikipedia or your favorite reference site, so let’s move on.

rdbms-family
One of the family!

There are a handful of components that make up SQL Server, and I’ll list them in what I think is the “most to least important” order, or at least most to least commonly used. Infrastructure-wise, at its core, SQL Server is basically an “application” and persistent data storage mechanism. It’s a set of processes (services) that run on your server and operate on specific sets/types of files, which we’ll cover in a bit.

First and foremost of said core components is the database engine. This is the DBA’s bread & butter – it’s the service process, sqlservr.exe, which handles all the queries and scripts, manipulates the data files & transaction logs, ensures referential integrity and all that other ACID-y goodness. Like any good service, it is best run under a dedicated service account, especially in a Windows domain environment. Otherwise, it will run as a built in system account, like NT Service\MSSQLSERVER. As a service, it’s set to “Automatic” run-mode and it accepts a number of startup parameters which can help govern and tweak its behavior. Most of those details are outside the scope of this post, so go ask your friendly (or surly, depending on the day) DBA if you’re curious.

msdn-sql-core-components
It’s an older infographic, sir, but it checks out.

A bit of key terminology: each running database engine is what we call a SQL Server instance. Typically you dedicate one physical (or virtual) server to each instance; however, in rare cases (usually in the lower tier environments like dev/test), you will see multiple instances on one server – this is called instance stacking, and it’s not usually recommended by DBAs due to resource governance complications. You typically reference a SQL Server instance simply by the server name that it’s running on, because the “default instance”, while technically named MSSQLSERVER, does not require any special referencing. So if my SQL server box was named FooBar, I would connect to the SQL instance with the name FooBar. If you get into stacked instances, you have to start naming the other instances uniquely, and your connections now have to include them with a backslash, e.g. FooBar\SQL2, FooBar\SQL3, etc.

As I said, the engine manages the data files. We’re talking about a permanent data storage system, so that data has to “live” somewhere – namely, mdf and ldf files (and sometimes ndf too). The mdf (and ndf) files hold the “actual data“, i.e. the tables, rows, indexes, & other objects in each database. The ldf files represent the transaction logs, or “T-logs” for short, which, again, are a fundamental part of the RDBMS. They allow for and enforce those fancy ACID properties. All of these files are exclusively locked by the sqlservr.exe process, meaning, nothing else is allowed to touch or modify those files but SQL Server itself, while SQL Server is running – not your antivirus, not your backup software (SQL has its own backup methodology), not even Windows itself. The only way to interact with and manipulate those files, or rather, the data within those files, is through the engine.

cant-touch-this-permission-denied
You can look, but you can’t touch!

Key takeaway: know which servers are running SQL Server, monitor the sqlservr.exe service(s), and work with your DBA to ensure that your AV software excludes the locations of those data files, and that your backup strategy includes & works in conjunction with SQL-based backups.

In a close second, we have the SQL Server Agent, sometimes just “agent” for short. Think of this like Windows Task Scheduler on steroids, but specifically focused within the realm of SQL Server. Like the engine, it runs as a service process – sqlagent.exe– again, typically set to automatic startup and running under a dedicated service account. (There’s a small debate on whether it’s best to use the same service account as the engine, or a separate one; in the end it doesn’t really matter much, so it’s mostly at the discretion of your DBA team and/or the Active Directory admin.)

sql-agent-man
Shamelessly borrowed from the Database Whisperer

SQL Server Agent is basically free automation – we can use it to schedule all kinds of tedious operations and database maintenance tasks, anything from “run this query every day at 12pm because Mary in accounting runs her big report after lunch and it needs to have data from X, Y, & Z”, to running the hallowed “good care & feeding of indexes & stats” that any DBA will tell you is not only essential to decent database performance, but essential to life itself (or at least, your SQL server’s life). These sets of work are called Agent Jobs (or just “jobs”), which consist of one or more Job Steps. A job has one or more Schedules, which dictate when it runs – such as, again, daily 12pm, or “every Saturday & Sunday at 5pm”, or “every 15 minutes between 8am and 9pm”. Ideally, jobs should be configured to alert the DBA (or, in some cases, the broader IT team) when they fail, so that appropriate action can be taken.

Key takeaway: monitor the service sqlagent.exe and have an alert & escalation procedure in place for handling failed Agent Jobs.

database-automation
Automation is good, mmkay?

A quick sidebar about transaction logs.  SQL Server uses what’s called a “write-ahead log” methodology, which basically means that operations are literally written to the T-logs before they’re written to the actual data files (MDFs/NDFs).  As stated so eloquently in this blog:

[When] neglected, it can easily become a bottleneck to our SQL Server environment.

It’s the DBA’s job to ensure the T-logs are being properly backed up, maintained, and experiencing good throughput.  And I’ll have another blurb about performance at the end of part 2.  However, as an infrastructure person, it’s worth knowing two things: A) these aren’t your “traditional” logs, like error or event logs; these are fundamental core part of SQL Server.  And B) —

Key takeaway: The disks which house the transaction logs should be FAST.  Like, stupid-fast.  Srsly.  At least at sequential writes.

samsung-960pro-m2-ssd
Yes please, I’ll take several… just charge it to the CIO’s platinum card.

Edge Cases

“Oh, that’ll never happen!”

I have a confession. I enjoy edge cases. More than I probably should. I take an unhealthy amount of pleasure in finding those unexpected oddities and poking holes in the assumptions and “soft rules” that business users and even developers make about their processes, applications, and most importantly, their data.

But let’s back up a minute. What exactly is an edge case? Well, I’ll quote Wikipedia’s definition at you, then I’ll put my own spin on it.

An edge case is a problem or situation that occurs only at an extreme (maximum or minimum) operating parameter.

edge-cases-99-to-1
it’s in a pie chart, so it must be true…

In my mind, there are really two types of edge cases. Although the results and treatment are largely similar, so it’s not a terribly important distinction, but for conversation’s sake, here they are.

  1. Known: while we can (and usually do) identify these cases, they are typically thought of as ultra-rare and/or “too difficult to reproduce”; thus, the plan for handling them involves tedious one-off or ad-hoc procedures, which are often completely undocumented.
  2. Unknown: the saying goes, “You don’t know what you don’t know” – these cases don’t even cross our minds as a possibility, either due to ignorance or because they are [supposedly] contrary to our business rules or our [assumed/remembered] application logic.

Again, the end result is the same: panic, discord, technical debt, and wasted hours of remediation. So why do we do this to ourselves? Well, one common justification tends to be “Oh, that’ll never happen!”, and we sweep it under the rug. Then there’s the laziness, busy-ness / lack of time, pressure to deliver, gap in abilities or tool-sets, passing the buck, etc. We’re all guilty of at least two of these in any given week.

basic-technical-debt-legos

So let’s move on to the important part: What can we do about it? Largely, we can simply take the excuses & reasons and simply turn them on their heads. Take ownership, commit to learning, communicate with management, make time for planning and documentation, and once a path is laid out for remediation, actually do the work. It’s often not easy or pretty, but a little pain now beats a lot of pain later.

tech-debt-cute-line-graph

I know, easier said than done, right? :o)

Example time.

Let’s say our sales offices are closed on Sunday – this is our “operating assumption”. Therefore, no orders will be processed on Sundays – this is our “business rule”. So because of this, we’ve decided to do some ETL processing and produce a revenue report for the previous week. Now, we’re using some antiquated tooling, so our first batch of ETL, which takes the orders from the sales system and loads them into the bookkeeping system, runs from, say, 2am to about 5am. Then we have a second batch, which moves that bookkeeping data into the report-staging area, from 6am to about 7am. We need those hours of “buffer zones” because the ETL times are unpredictable. And finally, our reporting engine churns & burns at 8am. But along comes Overachieving Oliver, on a Sunday at 5:30am, and he’s processed a couple orders from the other day (so perhaps he’s really Underachieving Oliver, but he’s trying to make up for it, because he enjoys alliteration almost as much as I do).

lumberg-work-on-sunday

Woah nelly! What happened? Oliver’s sales didn’t make it into the report! Not only that, but they don’t even exist according to bookkeeping. But come Monday, if he tries to re-process those orders, he’s probably going to get an error because they’re already in the sales system. So now he’s gotta get IT involved – probably an analyst, a developer, and maybe even a DBA. Damn, that’s a lot of resources expended because an assumption and a rule were broken!

warning-assumptions-ahead

Here’s another one. An order consists of 1 or more order-lines, which each contain 1 or more of a given item (product). Let’s say we store these in our database in a table called OrderLines, and each line has a LineNumber. Now, we have an assumption that those LineNumbers are always sequential. It’s even a rule in our applications – maybe not all parts or all modules, but at least some, and enough to cause a fuss if there’s a gap in that sequence or if a line is moved around somehow without proper data dependency updates (which, by the way, are application-controlled, not database-controlled). Plus there some manager who depends on this old reporting metric that also breaks when those line numbers are out-of-whack. But this should never happen, right?

assumptions-blind-spots-blanchard

The operative word there being “should”. But apparently there was a bug in an “update order” routine that left a gap in the sequence. Or maybe the DBA was asked to delete something from an order post-mortem, and there’s no way within the app’s ecosystem to do it, so he had to write some queries to make it work. And guess what? Because the Dev team is super busy working on the hot new feature that everybody wants, it will be 2 weeks before they can circle back around to address that update-bug or add that utility function for line-deletion. So now the DBA’s writing a stored-proc to wrap in a scheduled job to “fix” those order-line sequences every night, to prevent that one app module from breaking and keep those management reports accurate. And, to quote my very first post ever, the DBA waxe[s] wroth.

picard-why-we-cant-have-nice-things
Picard knows best..

So, prevention? Well, I’d probably start by locking down the order entry system during that off-limits window. We should also wire-up those big 3 processes so that there’s no need for indeterminate buffer-zones and inconsistent timing. And yeah, it could be a big change needing lots of team buy-in and approvals, but it’s worth the investment. Right? Right!

Hope you enjoyed reading! Until next time…

Drafted with StackEdit, finished with WordPress

What’s in a Name?

a thing by any other name… is a different thing.

Let’s just start with a universal truth:

Names matter.

What do I mean by that? In technology, specifically. Well, let me explain.

The name you choose for a thing – whether it’s a server, a database, an application, a service, a class, a file, a method, a function, a procedure, ad nauseum – that name is immediately and irrevocably in love with Edward associated with that thing. Even if you miraculously get to change that name somewhere down the road, it will still be (at least for a while) “formerly known as” the original name. Legacy teams will continue to use that old name until they’ve convinced everybody the new name is worth the trouble of switching all dependency references & documentation. Managers will continue to use the old name even longer because they’re not close enough to the tech to be aware of, let alone understand, the full implications of said name change.

you-keep-using-that-name
Enigo Montoya prefers easy and descriptive names, like “Six-Fingered-Man”.

So that’s why names matter. They’re a unique and semi-permanent identifier of something; they encapsulate the essence of that thing in a single word or phrase. And they become embedded in the business jargon and technology team lore. Such lofty expectations of such a simple (hopefully), short (usually), and often under-appreciated element of our field.

It’s unbelievably easy to find bad examples of IT naming schemes, too. Just look:

I don’t know about you, but I like my names to be pronounceable. I don’t want to need dozens of syllables and two extra breaths just to say a couple server names in conversation. It’s not hat hard to create meaningful multi-part names that are still phonetic. We speak these things just as much as we type them, let’s make them speak-able!

Thus, once again,

Names matter. REALLY.

And so, dear reader, choose them wisely, with care and consideration. With lots and lots of context. Maybe even some conventions, or at least conventional wisdom. And consistency. Plan for the future, while being aware of the past. Don’t let legacy remnants stand in the way, but don’t let delusions of grandeur force you into a naming scheme that breeds confusion or resentment.

dilbert-namingconventions

Allow me to illustrate. We have a handful of SQL servers as part of our core IT infrastructure. Let’s call them FooDB, BarDB, and CharDB. Now, they each serve a fairly distinct and semi-isolated set of apps/websites. In other words, they each fill a role. (Of course, there are cross-dependencies and integrations, but nothing extreme.) Now, we want to migrate them to new hardware and plan for scale, assuming the business keeps growing. So what do we name the new servers? We know that SQL Server doesn’t really scale horizontally, at least not very easily & not without major application rewrites. But we don’t want to draw ourselves into a corner by assuming that we’ll “never” scale out. Ok, how about this: DC-FooDB-P1, DC-BarDB-P1, etc. The P stands for production, because, along with the fancy new hardware, we’ve got some additional infrastructure room to build a proper Dev & QA environment.

 

dev_test_prod_white
Seriously. You need tiered environments. That’s a whole ‘nother blog post.

So what have we planned for? Tiered environments, as well as a bit of scale-out room – 9 servers in each “role”, so to speak. What if we grew to the point where we needed 10 servers? Well, as a DBA, I’d argue that we should consider some scale-up at that point; but also, to seriously start looking at a broad infrastructure revamp, because that kind of growth should indicate some serious cash influx and a much higher demand on IT-Dev resources.

will-this-scale
Don’t ask me what happened to rules #1-5. I don’t know, I didn’t read the article.

Why should the breaking point be 10 and not 100? or 1000? Well look, it definitely depends on the technology we’re dealing with. SQL server is not Mongo, or Hadoop, or IIS, or Apache. Certain tech was made to scale-out; other tech is more suited for scale-up. And there’s nothing better or worse about either approach – both have their place, and both are important. Scale-out is arguably more important, but for traditional IT shops, it’s also more difficult.

This is where that big C-word comes in. Context. If we’re talking about a tech that scales-out, sure, leave room for 100 or 1000 of those cookie-cutter instances that you can spin-up and shuffle around like cattle. But if it’s a scale-up tech, don’t kid yourself by thinking you’re going to get to the triple-digits.

star-wars-episode-vi-han-solo
Delusions of grandeur… we all have them!

The point is, if you’re starting at 1, it’s not too difficult to scale-out to a few more “nodes” with simple tricks and tweaks; i.e. without drastically changing your IT-Dev practices and environment. But when you start talking multi-digits, of redundant nodes fulfilling the same role, you need to seriously PLAN for that kind of redundancy and scale. Your apps have to be aware of the node topology and be concurrency-safe, meaning “no assumptions allowed” about where they’re running or how many other instances are running simultaneously. And since we’re talking about database servers, that means replication, multi-node identity management, maybe sharding, data warehousing, availability groups, and other enterprise-level features. We’re talkin’ big $$$. Again, it stands to reason that if the business is seeing the kind of growth that leads in this direction, it can afford to invest in the IT-Dev improvements required to support it.

show-me-the-money

I’d love to know your thoughts! Leave me a comment or two. Until next time!

Header image: Keira loved her little pet penguin… and then later she destroyed it and literally ate its face off.

Drafted with StackEdit, finished with WordPress.

Is Coding Style still a thing?

Make it readable, minimize scrolling.. provide context, clarify complexity.

I can hear it now.. the moans and groans, the wailing and gnashing of teeth.. “Another post about coding style / formatting? REALLY!? Why can’t we all just get along read/write/paste code in our own environment and let our IDE/toolset worry about the format?” I hear you, really I do. But you’re missing the point. The single most important trait of good code is readability. Readability should be platform-independent, i.e. it should not vary (too greatly) depending on your viewing medium or environment.

The ratio of time spent reading (code) versus writing is well over 10 to 1 … (therefore) making it easy to read makes it easier to write.

-Robert C. “Uncle Bob” Martin, Clean Code

And yet, all around the web, I continue to find terribly formatted, nigh-unreadable code samples. Specifically, SQL scripts. That’s me; that’s my focus, so that’s what I pay the most attention to. But I have no doubt that the same applies to most other languages that Devs & IT folks find themselves scouring the tubes for on a daily basis. There is just an excessive amount of bad, inconsistent, language-inappropriate formatting out there. And honestly, it’s not that surprising — after all, there are millions of people writing said code — but it’s still a source of annoyance.

Clean code always looks like it was written by someone who cares.

-Michael Feathers, Clean Code

Don’t you care about your fellow programmers?

Maybe it stems from my early days working in a small shop, where we all had to read each other’s code all the time, and we didn’t have these fancy automated toolsets that take the human eyeballs out of so much of the workflow/SDLC. So we agreed upon a simple set of coding style/layout rules that we could all follow, mostly in-line with Visual Studio’s default settings. And even though we occasionally had little tiffs about whether it was smarter to use tabs or spaces for indentation (protip: it’s tabs!), we all agreed it was helpful for doing code reviews and indoctrinating onboarding new team members.

My plea to you, dear reader, is simple. Be conscious of what you write and what you put out there for public consumption. Be aware of the fact that not everybody will have the exact same environment and tooling that you use, and that your code sample needs to be clean & structured enough to be viewed easily in various editors of various sizes. Not only that, but be mindful of your personal quirks & preferences showing through your code.

That last part is really difficult for me. I’m a bit of an anal-retentive bastard a stickler for SQL formatting, particularly when it doesn’t match “my way”. So that’s not the point of this post – I don’t mean to, nor should I ever, try to impose my own personal style nuances on a wide audience. (Sure, on my direct reports, but that’s why we hire underlings, no? To force our doctrine upon them? Oh, my bad…)

 

All I’m saying is, try to match the most widely accepted & prevalent style guidelines for your language, especially if it’s largely enforced by the IDE/environment-of-choice or platform-of-choice for said language. And I’m not talking about syntax-highlighting, color-coded keywords, and that stuff – those are things given to us by the wonderful IDEs we use. Mostly, I mean the layout stuff – your whitespace, your line breaks, your width vs. height ratio, etc.

For example, Visual Studio. We can all agree that this is the de-facto standard for C# coding projects, yes? Probably for other MS-stack-centric languages as well, but this is just an example. And in its default settings, certain style guides are set: curly braces on their own lines, cascading smart-indents, 4-space tab width, etc. Could we not publish C# code samples to StackOverflow that have 7 or 8 spaced tabs, curly braces all over the place (some on the same line as the header code or the closing statement of the block; some 3 lines down and spaced 17 tabs over because “woops, I forgot a closing paren. in a line above, I fixed it but I was too lazy to go down and fix the indentation on the subsequent lines!”).  GRRR!

Always code as if the [person] who ends up maintaining your code will be a violent psychopath who knows where you live.

John Woods

Now, on to SQL. Specifically, TSQL (MS SQL). Okay, I will concede that the de-facto environment, SSMS, does NOT have what we’d call “great” default style/layout settings. It doesn’t even force you to use what little guidelines there are, because it’s not an IDE, so it doesn’t do smart auto-formatting (or what I’ve come to call “auto-format-fixing, in VS) for you. And sure, there are add-ins like RedGate SQL Prompt, SSMSBoost, Poor Man’s T-SQL Formatter, etc. But even they can’t agree on a nice happy compromised set of defaults. So what do we do? Maybe just apply some good old fashioned common sense. (Also, if you’re using such an add-in, tweak it to match your standards – play with those settings, don’t be shy!)

I can’t tell you how frustrating it is to have to horizontally-scroll to read the tail-end of a long SQL CASE expression, only to encounter a list of 10+ literal values in an IN () expression in the WHERE clause, that are broken-out to 1-per-line! Or even worse, a BETWEEN expression where the AND <end-value> is on a brand new line – REALLY?? You couldn’t break-up the convoluted CASE expression, but you had to line-break in the middle of a BETWEEN? Come on.

youre-killin-me-smalls
You’re killin’ me, Smalls!

Or how about the classic JOIN debate? Do we put the right-hand table on the same line as the left-hand table? Do we also put the ON predicates in the same line? Do we sub-indent everything off the first FROM table? Use liberal tabs/spaces so that all the join-predicates line up way over on the right side of the page? Again, common sense.

Make it readable, minimize scrolling, call-out (emphasize) important elements/lines (tables, predicates, functional blocks, control-flow elements, variables). It’s not revolutionary; just stop putting so much crap on one line that in a non-word-wrapped editor, you’re forced to H-scroll for miles. (FYI, I try to turn word-wrap on in most of my editors, then at least if I have some crazy-wide code, I can still read it all.)

SWYMMWYS. Say what you mean, mean what you say. (“Swim wiz”? “Swimmies”? I don’t know, I usually like to phoneticize my acronyms, but this one may prove troublesome.)

swimmies
swimmies!

Modern languages are expressive enough that you can generally make your structure match your intended purpose and workflow. Make it obvious. And if you can’t, comment. Every language worth its salt supports comments of some kind – comment, meaning, a piece of text that is not compiled/run, but rather just displayed to the editor/viewer of the code. Comments need to provide context, convey intent, and clarify complexity.

So what’s my point? Basically, let’s all try to make our code samples a bit easier to read and digest, by using industry-standard/accepted best-practices in terms of layout & style.

Drafted with StackEdit, finished with WordPress.


PS: Just to eat my own words, here’s an example of my preference to how a somewhat complex FROM clause would look, if I was king of the auto-formatter world:

SQL-sample-dark-code
Hipster dark theme FTW!

Send comments, thumb-ups, and hate-mail to my Contact page. 😛

Header image: our husky Keira at 2.5 months, being a sleepy-head!

Mongos, anybody?

You’ve heard of this thing called MongoDB…

Unless you’ve been living under/in the proverbial rock/hole-in-the-ground, you’ve heard of this thing called MongoDB. It’s one of the biggest flavors of NoSQL database systems out there – specifically, it’s a “document DB”, which means it stores semi-structured data in the form of JSON documents, grouped into collections, grouped into (of course) DBs.

Now, I’m a Windows guy. SQL Server & Windows Server are my comfort zones. I like GUIs, but I’m ok with a command line too. PowerShell has become my friend good acquaintance. But every once in a while, we have to step outside our comfort zones, no? So when I was told “hey, you’ve got to get a handle on managing these MongoDB instances that we’ve got running on servers so-and-so, as part of this ‘Imaging system’ application”… I thought “Hahaha…”, but what came out was “Sure thing!”  Something a bit like this:

hahahayeah

(courtesy of DBA Reactions)

So the first order of business was to decide on a MongoDB “GUI” – a Windows app that could at least connect-to and give me a visual overview of the running MongoDB instances (properly referred to as a mongod, stemming from the Linux term “daemon”, which on the Windows side is basically like a process or service). I tried both the “official” desktop app from the big org, MongoDB Compass, and a neat open-source tool called Robomongo.

And I actually like them both, for different reasons; most of which can probably be attributed to my lack of depth with the technology, but hey. Anyway, Compass is really nice in that it gives you this kind of statistical overview of the collections in your DBs, performing some basic aggregates on a 10% or 1k sample of the collection documents to give you a graphical 40-thousand-foot view of the data. But where it breaks down for me is that little “query” bar, which is just an empty pair of curly-braces. It’s only for “selecting” (finding, querying); no other operations to see here. So we can’t manipulate our data with it, but it’s definitely great for viewing!

 

Whereas with Robomongo, I can right-click on the DBs/collections and do very simple things like “show documents”, “show statistics”, etc. And it actually writes the equivalent mongo shell command for me to poke at; say, to inject more logic to the find to get something specific or to write a new command or two as I read thru the docs and learn things like aggregates, indexes, and whatnot. Being a shell, it allows us to write or update data as well as read it.

But alas, GUIs will only take you so far. This is a tech born & bred on the command-line, so to really dig into it, I needed to let go of the mouse. And the mongo shell was actually pretty straightforward! A couple visits to their docs pages & some visual verification by using the GUI tools to check my work, and things were starting to come together. And even for a complete Javascript noob like me, the command syntax was straightforward enough. Soon I was configuring a replset and mongodump/mongorestore-ing and re-syncing a replica after purging some old stale collections.

Even though it’s a CLI/Linux flavored technology, it works perfectly fine in Windows… Except for one thing.  So, you install it as a service, and you typically start & stop services using net start & net start and as long as your service’s CLI arguments are all correct, you should be good — in theory! Trouble is, the service tends not to stop gracefully. So I found that, instead, the following command was more useful: mongo admin --eval "shutdownServer()". This uses the actual mongo shell to send the native shutdown command to the mongod, instead of relying on the Windows services gymnastics to do it correctly.

It just goes to show, dear reader, that you’ve got to get your hands dirty and try out new technology before dismissing it as “not my job” or “somebody else’s problem”.

PS: Nope, that’s not Compass’s logo or anybody else’s; I made it meself, with good old Paint.NET!

Drafted with StackEdit, finished with WordPress.

DEV, the SQL

See what I did there?

I have a confession.  I geek out over building & shopping for computers.  There’s barely any money in it, otherwise I’d do it for a living.  But those jobs belong to robots and overseas underpaid factory minions, and that’s probably for the best (at least, the first part).

But yeah, I’m a geek.  I’ll pour over articles and reviews, pick out the components, fill and empty my Amazon or Newegg cart, go back and read more, pick some other components… I love it.  It’s ridiculous.

This happens to be in direct conflict with one of my other passions, frugality.  Yes, I’m cheap.  Thus, usually I end up geeking-out helping other people with computer builds or replacements or whatnot.

So when my boss decided one day, “Hey, let’s go down to Micro Center and see if we can put together a replacement for the SQL Server Development box”, aka ‘SQLDEV‘, I was more than happy to oblige.  Because, you see, he loves computer shopping almost as much as me.  And he also happens to have a fancy corporate credit card.  *cha-ching!*

The current server “feels slow” — it’s a Dell Poweredge R710 with 24GB RAM, 2×2 (cores x hyperthreading) 3.6 GHz CPU, and an attached storage array of 7.2k SAS SATA II (3 Gbps) drives.  Shipped mid-2011 — it’s not that bad in the grand scheme of things, but it’s definitely outlived its life as a 3-instance SQL server with terabytes of data.

Here’s what we picked up at Micro Center:

Base system:

  • PowerSpec G403 Desktop – $950
    • Core i7-6700, 4×2 (cores x HT) @ 4.0GHz
    • 16GB DDR4-3200 RAM
    • 512GB SATA 6Gb/s SSD (Sandisk SD8SB8U512G1122)
    • GB LAN, DVD drive, integrated graphics
  • the one big problem I have with this PC is that it only has 4 SATA ports; more on that in a minute..

Add-ons:

  • 32GB DDR4-3200 RAM (for a total 48GB) – $180
  • 3x 2TB Samsung EVO SATA 6Gb/s SSD – $630 x3 = $1890
  • 500GB Samsung EVO M.2 SSD – $180
    • (for TempDB – supposedly even faster than SATA)
  • 5TB Toshiba 7.2k SATA III HDD – $145
    • (for pulling down backups & shuffling files between Production & Dev boxes)

Sub-total: $3345
Total with tax: $3612.60


For those of you keeping score at home, that’s 6.5TB of dedicated SQL space, plus another half TB for boot/OS, plus another 5TB for “slow” storage.

Now, if we start poking around Amazon for used servers in the same class as the R710, we do find some pretty compelling — and much cheaper! — systems.  So did we just spend that money for no good reason?  Well, cosmetic arguments aside, I still say, emphatically, NO.  Here’s why.

  1. This is dev. We don’t need redundancy, HA or DR.  We need one thing: speed.  Well, really two things: space and speed.  And sure, 2TB SSDs aren’t cheap.  But have you ever looked at 3 or 4 TB SSDs?  Holy bejeezus.  What about “more is less” — why not six 1TB SSDs?  Okay; can you find a desktop class motherboard with enough SATA ports?  Sure most of the good ones have 6, but that’s not all we need — we still need space for the OS/boot drive and the backup mechanical drive.  Sure, we can drop in a PCIe-SATA card and get 2-4 more free ports that way.  In fact, I already have to do that because this mobo skimped on ports!  But either way, as it turns out, most of our DB filegroups are near or over 1TB.  And again, without RAID, I’m looking at possibly sharding out data files across 2 drives per SQL instance, which A) doesn’t mimic production (although that’s a pretty weak argument at this point), and B) sounds like more of a headache than I care to deal with over saving a couple hundred bucks.
  2. Peace & quiet.  Servers are loud, power-hogging, heat-generating beasts.  A desktop PC is none of those things.  It’s sitting under my desk right now and I don’t even notice it.  Plus, it’s really easy to set up, tear down, move somewhere else, and set up again.
  3. Did I mention speed?  This thing is blazing fast.  CrystalDiskMark pics to follow.  But again, there’s no redundancy here, no warranties or service agreements to cover these parts or this system in the event of a bad component or data-loss incident.  That’s why you don’t run production (or even QA/UAT) on these types of machines — because parts do break, and when they do, you need redundancy and HA/DR built-in to your important systems.  On Dev, though, we can rebuild it in a day or less.  No problem!

Benchmarks: Boot, Temp, Data

So that’s where my geek flag has been flying this week.  My sales pitch to the boss was based on a post from Glenn Berry (http://www.sqlskills.com/blogs/glenn/building-a-desktop-workstation-for-sql-server-development-and-testing/), so if anybody’s to blame for feeding into the crazy, it’s probably him.  I’m sure he won’t mind!

Like any other business, our IT infrastructure has been slowly getting consolidated, converged, virtualized, and even moved to the cloud in some cases.  So granted, this is a short-term solution.  And because of said consolidations, we definitely do have decommissioned servers that may make a good home for “real” Dev/Test/QA environments very soon.  They’ll be expertly planned, well-managed, viable branches of the infrastructure… when the IT team has time to dedicate to those efforts.  But sometimes, there’s nothing wrong with a little good-old-fashioned geekdom, and having a little side-project that makes you gush like a schoolgirl & keeps you honest.

PS: Yes, the elephant in the room: the cloud option. I get it. Once again, the boss wanted to do it a certain way, and in this case, the cloud was not on the short-list. Next time, buddies!

Header image: our husky Keira at 2 months. No, that’s not a turd next to her mouth, it’s a treat.

No Regrets

Continuing from my previous post…

I loved my first job, no doubt. But slowly over the years, I started to feel pigeon-holed. I was always on-deck for problems that happened in the system due to what I’ll call “bad data”. If you’ve been in the industry, you know what that means – something in your datastore doesn’t “follow the rules”, or “breaks something that normally doesn’t break”, or “is just a little off-kilter”. (Otherwise known as “edge cases”, which I’ll talk about in a later post!) And often, it’s because the application allowed that data, which breaks the rules, into the system – Devs hate to hear this. But just as often, it’s because the data-guy, the DB-dev or analyst, ingested or transformed the data in a way that breaks the rules – so there’s plenty of blame to go around. And we knew that, and we respected that. We didn’t pass the blame, we just buckled down and tried to get it fixed.

But, too often, I was the one called upon to fix it, because I was the closest to the data – and yes, I knew it intimately, no doubt. Because of that, I started to feel left behind the curve. The other devs were pushing new tech stacks, learning new things, while I kept pluggin’ away at good ol’ TSQL (with the occasional smattering of C#). And yes, of course that’s partially my fault; I could have self-asserted, pushed for more learning opportunities, etc. I don’t regret my first job. I learned a ton – basically everything I know about databases, technology, and IT. But it was time for a change.

And that’s how I ended up where I am now!

Written with StackEdit.

Who Am I?

…and why I love what I do.

I started out as a data analyst, first playing in MS Access, and quickly moving to MS SQL Server. I also became a developer in the ASP.NET stack, way back in the 2.0 days (2006-07). Yet I found myself drawn to the data more than the code, for some reason. Perhaps it was the beautiful simplicity of the relational model. Or the myriad of variations that something as simple as an name field could contain & how challenging it could be to massage that “dirty data” into something elegant and consistent for the code to easily manage and present to the user. Or it could have been the thrill of analyzing a workload that may have taken several minutes to run, and then, by applying the right combination of indexes, relations, and set-logic, to bring that workload down to milliseconds.

This job was great. First “real” job out of college; a teeny-tiny software shop, 4 devs & the boss, working with K-12 school districts, making their lives just a bit easier when dealing with the vast swath of standardized testing and government-imposed student performance metrics that ultimately decided how they taught & how they got funded. And we grew, slowly but surely – 5 devs, then 6, then 8. We got to wear so many hats! Developer, Data Analyst, Helpdesk, Tech Support; even Printer, Delivery Crew, Trainer. It was truly a well-rounded experience.

As the years went by, it felt like family. And sure, every family has the occasional episode of dysfunction. But we were in it together, building up the business with passion, commitment, and respect. And those of us that were there since the start, we felt like we knew it all. Because we did; in that little microcosm, we truly were the experts. And each of us had a forte – I was the “data guy”, he was the “self-taught developer”, she was the “tech-evangelist / UX dev”, and he was the “dev architect”. Our best hire came soon after – the “young enthusiastic developer”, a true professional. Then there was our den-mother, bringer of snacks, paycheck-writer and book-keeper extraordinaire. And as always, the father-figure – the big kahuna, the boss man, who was more than willing to get his hands dirty and help with anything we asked him to.

That was a wonderful experience. In the near-decade I spent with that company and those colleagues, many of whom I consider my friends, I learned so much about technology, programming, databases, and business. And we welcomed more and more people into the family – the graphics & print-media design expert, the bright & cheery project manager, the ambitious salesperson, the kind & caring tier 1 support staff, the new data analyst, the veteran DB-dev, the expert architect, the UI/UX dev, the student dev, and even the Russian programmer! (They’re amazing, BTW.) Each & every one of them added value, and taught me something new.

I do not regret a single day of it. It was awesome.

But, as the baby bird has to eventually leave the nest, I needed to venture out, to get outside my comfort zone and see what else was out there, to join a new & different team and environment, to see if I was truly as good as I thought I was. Well, I think we all know the answer to that! (Hint: it’s “nope”.)

Actually, it’s been great – I drank from the proverbial fire-hose, learned a lot in a short time about a completely new line of business & a familiar but larger & much more complex tech stack & environment. So in general, I’m actually enjoying not being “the go-to guy” for every little problem that happens with the database.

And you know what else I discovered? I am pretty darn good at what I do. That’s a good feeling!

But I’m still the DBA. (Technically, one of two, but the other DBA is the company’s old-time-guru-turned-part-time-consultant). So in a sense, being that “go-to guy” hasn’t gone away, it’s just gotten much more manageable – there’s a larger organization of people around me that can help in many different ways. Whether it’s the old-timers with that tribal knowledge of all the hidden rules and nuances of the business, or the SysAdmin who can whip out a PowerShell script in less time than it takes to say “automation”, or the domain expert that can pinpoint exactly why something is broken because they literally “wrote the book” on that problem-space. So it’s really cool.

Plus, it gives me the space and the time to focus on getting really good at DBA stuff – performance tuning, report automation, data integrity, change management, backup/recovery, data security, all that jazz. I’m looking forward to really earning that “Senior DBA” badge – because even though that’s my title, I still recognize that I have a lot to learn. The landscape is constantly evolving – 5 years ago, this whole “Cloud” business was virtually (yes, that is a pun) unheard of; now it’s all anybody talks about. I “grew up” in an on-prem (that’s short for on-premises) Windows + SQL Server + IIS world; but now I’ve got my fingers on AWS instances (both EC2 and RDS), MongoDBs, a SSAS data warehouse, even a sprinkling of MySQL and Azure. Not to mention dozens of SQL Servers spread over 3 offices plus a data-center, and a ridiculously slick converged infrastructure platform called a “VxBlock”. The technology world just keeps getting bigger, better, faster, smarter.

And honestly, that’s why I love what I do.

Written with StackEdit.

Welcome

Thank you for reading, and stay tuned for more!

Picture mid-2006. A fresh college graduate, BS in Computer Engineering. That’s half hardware, half software. Maybe more like 40-60. Not Comp Sci, though looking back, that might have been a little smarter. I learned a bunch about hardware and embedded systems that I will probably never use in my life. But hey, I can probably solder a capacitor to a circuit board without completely frying both it and my fingers!

So I had some education, but not much practical experience… none, really. But luckily enough, I found a job, in my home town, and started my journey! From data-analyst to programmer to database administrator and beyond, it was a wild ride. The technology space is a fascinating, painful, exciting, crushing, inspiring, challenging, awesome field to work in – and yes, those were oxymorons.

Anyway. Enough waxing dramatic for one post. Stay tuned for more about me! (Sure, it sounds self-centered, but isn’t that exactly what a blog is?)

But seriously. Thank you for reading and please do come back to visit. I promise I’ll attempt to entertain, inform, and intrigue. Or at least one of those.

Until next time!

Written with StackEdit.

Origins

And the DBA waxed wroth…

Genesis of a DBA Universe

In the beginning was the disk array, and all was empty and raw, and Windows Server moved over the face of the platters. And the DBA said: Let there be SQL Server. And there was SQL Server.

And the environment variables were set, and the disks were striped and mirrored, and the config was established, and behold spindle was rent asunder from spindle. And the DBA saw that all was in spec.

And it was day, and it was the evening of the first day.

And the DBA said: Let there be objects. And setup.exe brought forth myriad crawling things upon the face of the array. And instcat.sql brought forth all manner of tables and views that swim unseen beneath the waters. And procsyst.sql brought forth all the built-in procedures and all the operators of the air, that the users might be given wings and take fight over the data.

And it was day, and it was the evening of the second day.

And the DBA said: Let there be databases. And there were databases. And the system administrator looked upon the disk array and did see what the databases had wrought upon the disk arrays, and he did gnash his teeth and seek a new work upon the Internet with an engine of search.

And it was day, and it was the evening of the third day.

And the DBA created users. Male and female he created them. And he said unto the users: Thou mayest create tables and views as thou wilt. Yea, though mayest create even indexes upon the data. Only meddle not with the system database, for it is a holy place, and on the day wherein thou treadest upon it, on that day thy roles shall surely be revoked.

And the serpent crept among the users and whispered to them, saying: Thine roles shall not be revoked. Taste ye all of the system database, for ye shall know of b-trees and hints and ye shall be as DBAs. And the users heeded the serpent and did fill the system database with crap. And the instance did crash and the client did wax wroth at the DBA. And the DBA did gnash his teeth and partake of the fruit of the vine, for behold the users were permanent employees, and the DBA was but a contractor and could not revoke their roles.

And it was day, and it was the evening of the fourth day.

And the DBA did set default databases and default schemata, and did lock down all that was upon the face of the array with roles and encryptions and all manner of quotas, yea even from the transaction logs even unto the archived backup files.

And it was day, and it was the evening of the fifth day.

And the DBA created synonyms and links and did tune the server and apply patches upon the face of the server. And the DBA saw that is was good.

And it was day, and it was the evening of the sixth day.

And on the seventh day the DBA did rest from all the labors of the creation. But lo, his pager did ring and he ceased from resting, and did spend his sabbath on the phone with Microsoft support. And by the time the DBA got through to someone who knew whereof they spake, behold it was day, and it was morning of the eighth day.

And the DBA waxed wroth.