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.