Blog

T-SQL Tuesday #112: Cookies!!

..this analogy of “dipping into the cookie jar”. What events or accomplishments can I take sustenance from, draw strength from, during these times?

Advertisements

tsql2sday150x150

Hi folks. It’s been a minute. Frankly it’s been a rough 5 months. From losing my wife, to dealing with the holidays and her birthday, to moving houses again. On the career front, I’m faced with the challenge of virtualizing our core business-critical SQL instances with minimal downtime. And obviously, because of all that personal/life stuff, it’s been difficult to stay focused and productive.

So this #tsql2sday‘s topic is poignant, I suppose — this analogy of “dipping into the cookie jar”. What events or accomplishments can I take sustenance from, draw strength from, during these times?

As usual, we must thank our host, Shane O’Neill (b|t), and remind readers to check out tsqltuesday.com.

Encouragement

Back when I first took this current job, I was worried sick about doing the commute every day. One and a half to two hours in traffic each way. Even if I used toll-roads, it might save 10-15 minutes, but it was still super stressful. But my wife never stopped encouraging me, telling me it would pay off. She put up with the crazy hours, she checked on me periodically, she stayed on the phone to keep me awake sometimes. She reminded me often, when the time was right, to have the telecommute/remote-work conversation with management.

And of course, to nobody’s surprise, she was right. I now work from home 4 days a week, take a vanpool the 5th day, and am much happier and more productive (in general!), much less stressed, and a markedly better driver. More importantly, because of her unwavering support, I can still look back and draw renewed energy from those memories and from her still-present spirit that stays with me in my heart.

the wife is always right
GOTO 1

Accomplishment

One of the first big projects on my plate was upgrading & migrating the SQL 2005 instances to new hardware and SQL 2016. We didn’t use anything super fancy for the actual migration, just backup shipping, essentially. DbaTools came in very handy for copying over the logins/users without having to hash/unhash passwords. The main hiccups were around Agent Jobs and Replication. Jobs were turned off at the old server before they were enabled on the new, but due to lack of documentation and understanding of some, it became difficult to see which ones were critically needed “now” vs. which could wait a while, and which may have dependencies on other SQL instances (via linked-servers) that may or may not have moved.

And replication is just a PITA in general. Fortunately, I took this as a ‘growth opportunity’ to more fully document and understand the replication environment we were dealing with. So at the end of the project, we had a full list of replication pub-subs with their articles, a sense of how long they each took to re-initialize, and a decision-process to consult when adding-to or updating articles within them.

Continuous Learning

A similar upgrade-migration project came to fruition in 2017: the ERP system upgrade. This was a delicious combo meal of both the database instance upgrade (SQL 2008R2 to 2016) and the application & related services/middleware (Dynamics NAV 2009 to 2017). And as I blogged about, it came with a super sticky horrible side-effect that we’re still dealing with over a year later: a different collation than the rest of our DB environment.

Which reminds me, I need to do some follow-up posts to that. Briefly, the “best” band-aids so far have been thus:

  1. If only dealing with the ERP entities, without joins to other DBs, just leave collations alone. The presentation layer won’t care (whether that SSRS or a .NET app).
  2. If relating (joining) ERP entities to other DB entities, I’ll load the ERP data into temp-tables that use the other DB’s collation, then join to those temp-tables. The “conversion” is essentially ‘free on write’, meaning when we load the weird-collation data into a temp-table that’s using the normal-collation, there’s no penalty for it.

As I said, I’ll try to dive into this more in a future post. It’s been a life-saver for performance problems that cropped up as a result of the upgrade & the different collations.

But the point here is that, even though this project didn’t end up as wildly successful as we’d hoped, it’s still a success, because we learned some key lessons and were able to pivot effectively to address the problems in a reasonable way. And frankly, there was no going back anyway; it’s not like the business would have said “Oh, never mind, we’ll just stick with the old versions of everything” (even though some reticent managers would probably have enjoyed that!). So even when things seem bleak, there’s always a way around.

when the going gets tough, the tough take a coffee break
Wait a minute…

Conclusion

I’m still trying to figure out what this new chapter of my life looks like, without her. I’m still trying to be the very best DBA & BI-Dev I can, supporting the dozens of requests & projects that the business throws at us. Fortunately, with the incredible SQL Community, a wonderfully supportive family, and a fantastic team of colleagues, I remember how far I’ve come, on whose shoulders I stand, and how much promise the future holds.

Even though the one person I was meant to share it all with is gone; she still smiles down and encourages me in subtle ways.

…with love & light ❤

What, ANOTHER blog?

Head on over to natethewriter.home.blog (yes, it’s still Wordpress, because I’m cheap!)

Yes, dear readers, it’s high time I drew a clear line between the tech/professional content and the personal. Also, I realized that I need to write more, because it’s been helping me through the grieving process. And a lot of that writing is going to be very… non-technical.

Don’t get me wrong; I need to, and will, resume my database-related blogging too. At some point. I promise! But I’m trying to honor my wife’s memory and take to heart her words of encouragement to me, when she said “You know, people always told me I should be a writer. But you should try it too; you’re pretty good at it.” I feel like she’s passed along some of her soul and spirit to me. If nothing else, at least I’ll get to show the world a little glimpse into her imagination, as I gather the little bits of sparkle that she left with me in her journals and scrapbooks. And hopefully, honor that soul and spirit by exposing some of my own.

So, head on over to natethewriter.home.blog (because I wasn’t feeling terribly creative — I know, not a good start for a so-called “writer” — but I decided to stick with the pattern)… even though I have absolutely NO delusions that I’m in any sense a “real writer”, just an amateur with the desire to express thoughts and ideas in concrete form. And follow, subscribe, stalk, etc.! Hope to see you there in the comments. ❤

keira looking at camera
She’s so excited!

What Marvel Movies Do I Need to Watch?

Welcome to the first post of the new year. I’ll be keeping things a little on the lighter side for now. I’m still very into my work and learning lots of share-worthy things in the data world. But for now, movies!

I also want to take a moment to appreciate those who reached out to us after our devastating loss. Thank you for your thoughts and prayers. Please continue to remember our family as we struggle to find a sense of normalcy.

So, some of my elder moviegoers asked me the question that many people have been asking over the last year or two: “What Marvel movies do I really need to watch before Infinity War?”, or more recently, “before End Game?”. More generally, which ones are worthwhile viewing to a casual non-geek, to someone who doesn’t need to obsess over every little minutiae, someone who is not by nature a “comic book movie lover”. It’s a completely fair question, and honestly it needs more.. less nerdy answers.

Hence, this post!

Iron Man (2008)

Really, how could you not? RDJ at his finest, and the start of what we now call the MCU. One could argue that it’s actually not entirely critical to “Avengers”, as a whole, especially if you have a general idea of who and what Iron Man is. But come on.

Thor (2011) OR Thor: Dark World (2013)

Opinions vary on which is a solid film and which is a dud. Either one is sufficient to introduce the Asgardians, the handsome dude with the big hammer, and our favorite flip-flopping hero-villain-depending-on-the-day-of-the-week.

Avengers (2012)

Obviously. While it precedes Thor 2, chronology isn’t the most important thing in the early storyline. It’s more about understanding the characters and the way they work together. If you just did Thor 2, you might not understand why Loki is the bad guy at this point; whereas if you just do Thor 1, you’ll miss the part where Loki “turns good” (ish?) as a lead-in to a future story. Not terribly important; just enjoy the big battle with giant alien snake-ships.

Guardians of the Galaxy (2014)

While not critical to the Avengers storyline, per se, it’s an excellent movie, with some key character development that ultimately explains a pivotal point in Starlord’s (Pratt) behavior in the third Avengers film. And the music is fabulous!

Captain America: Winter Soldier (2014)

This is also not technically required viewing, but is widely regarded as one of the best films to this point in the timeline. It also sets up some important elements in the larger story arc. However, if, like me, you’re not a giddy schoolgirl superfan of Chris Evans and/or the Cap in general, you can skip this one in favor of Civil War.

Avengers: Age of Ultron (2015)

Not the greatest movie from a quality perspective, but quantity, you get. Plus major plot and character development. Paul Bettany is amazing as always, and newcomer Elizabeth Olsen does not disappoint.

Honorable Mention: Doctor Strange (2016)

Again, not critical to the overall arc, but it’s something different. Cumberbatch really made his mark as a cross between the sarcastic cynicism of Tony Stark and the higher moral calling of Steve Rogers.

Captain America: Civil War (2016)

Basically Avengers 2.5, this film gave us so much plot and character push that it’s a sin to ignore. And it’s a truly fantastic movie to boot. Tom Holland’s Spider-Man is a breath of fresh air, and he will definitely make you want to see his first standalone feature, even if it’s not super essential to the Avengers storyline as a whole. As I said before, I’m not a drooling Captain fan, but this is truly a full-fledged ensemble worth watching.

Thor: Ragnarok (2017)

If Civil War was business as usual for the Avengers, Ragnarok is a welcome departure. It nearly flies in the face of the serious and dramatic tones of its predecessors, and I absolutely LOVED it for that. Hemsworth and Hiddleston are back to their charming selves, Ruffalo comes off a Hulk-high and bumbles back into our hearts, and Jeff Goldblum ramps up the ridiculousness. But the star of this show is Cate Blanchette’s villianess, Hela.

you're in my seat

I’m Hela.

Hella what?

Hella fab in this badass latex bodysuit!

The most important thing about Ragnarok is that it directly leads into the beginning of Infinity War. So watch past the credits and get ready for some epicness.

Honorable Mention, prior to Infinity War: Spider-Man Homecoming (2017) and Black Panther (2018)

As I mentioned, Tom Holland managed to breath fresh life into the Spider-Man character, after Sony churned out way too many movies bearing his namesake. Here he’s re-established as a pop-culture-savvy teenager who just wants to do the right thing and finds this HUGE world of good-vs-evil that he may hesitate about but ultimately knows he needs to become a part of. Black Panther really needs no introduction; it was one of the top performing movies of 2018, and for good reason. Not just because it’s one of the first black super hero movies of the decade, and indeed of the Marvel universe, but because it firmly and distinctly fired on all major cylinders of modern comic cinema – action, character, heart, culture, morality, and legacy. Yet, neither of these movies is, I argue, absolutely critical to understanding the larger Avengers plot-line. You get enough of an introduction to both characters in the other movies.

Avengers: Infinity War (2018)

The big daddy of them all. So far, of course. End Game will likely rightfully claim that title once it hits, but for now, this is the climax, the apex of the Avengers arc, and the MCU in general. It’s got everybody. It’s long, at 2.5 hours; it’s epic, full of action, and (semi-spoiler-alert, but honestly if you haven’t at least heard the gist of it by now, you have nobody to blame but yourself) heartbreak. If you’ve enjoyed any of the preceding movies, you will want to see this. And if you plan on seeing the final one, you definitely need to see this one first.

Upcoming: Captain Marvel (2019)

We know that, chronologically, this one takes place in the past. So it’s not absolutely crucial to the Avengers storyline, on its own; but we know that the heroine herself DOES play a crucial role in End Game, so it’s probably worth the time investment. Plus who doesn’t love a strong female superhero flick? We haven’t seen enough of those from the MCU, to date. To be fair, the source material is fairly patriarchal too, but this is 2019, so it stands to reason that a studio of Disney’s size could do anything they damn well please, including giving the leading ladies more of their own features. (Fingers crossed that the Jean Grey and Black Widow spinoffs do well!)

Where does it all end?

Avengers: End Game (2019) is hyped to be the absolute biggest superhero cinematic event of the decade. Some of the characters will continue to have their sequels or even origin stories, while others may meet their permanent end, be it death or just old-fashioned retirement. One thing is certain: if you haven’t at least watched the first three Avengers movies, you are by no means prepared for what’s coming.

Dude, that’s still like.. NINE movies. Ain’t nobody got time for all that!

Fair enough. Let me cut the list in half… almost. If you had a gun to my head (RUDE!) and asked me which films were absolutely 100% guaranteed required viewing, I would tell you this: Avengers 1, Avengers 2, Civil War, Thor Ragnarok, and Infinity War. That’s roughly 10-ish hours of quality cinema, and includes just about every hero and villain that you need to care about going into this year.

So I excluded your favorites, big whoop, wanna fight about it?

Do you disagree? Did I leave out anything terribly important? Do you still think Steve Rogers is a dreamboat and should be idolized by all his fellow heroes for his unwavering moral righteousness? (Spoiler alert: WRONG.) Let me know in the comments!

Briefly, here’s my two-cents about why the rest are unnecessary. Starting with the Iron Man sequels: 2 is messy and doesn’t really move the hero forward, while 3 is a far better character study at the expense of being slightly too CGI-heavy. (Then again, they all are going that direction, so you might as well embrace it.) The Incredible Hulk was ret-conned into the MCU, and it’s not Ruffalo’s Hulk, so it really serves no great purpose other than to fill a slot on a timeline. With the first two Thor‘s, as I said, you could do away with either one and still understand that world in general. Then we have the one-off origin stories in Captain America and Ant Man, which, fine, they’re decent, but quite unnecessary to the bigger picture. And Guardians of the Galaxy 2, while a very good sequel, has nothing earth-shattering (see-what-I-did-there?) to add to the timeline. Finally, like I mentioned before, Panther and Homecoming are truly great movies; in fact the adorable surrogate-father-son relationship that Stark (Iron) and Parker (Spidey) establish in the latter makes one of the final moments in Infinity War exponentially more heartbreaking. But do you absolutely need to have seen them to get up-to-speed for Avengers? Not really.

A final note. About the TV shows. I don’t watch them. Never have. If that’s your game, great, go for it. But the movies know that they can’t rely on the shows to make any significant plot contributions, let alone to be seen by nearly as many, or the same, people. So in my book they’re still all fluff. Maybe that’ll change someday, but I doubt it.

Now, go do your homework and watch some movies! =)

In Loving Memory

Do not waste moments. Put forth light and love into this world. Emanate joy and kindness. And cherish each day, for our time here is never guaranteed.

may your wings be strong
-her favorite quote, from the movie Maleficent.

Dear readers. It is with a heavy heart that I write this. 2018 has seen some significant losses of meaningful lives, whether it be in #SQLFamily or elsewhere. Sadly, that toll increases again.

In October this year, my wife of 10 years passed away unexpectedly, at home. No chronically debilitating illnesses being fought off, no sudden acute onset of horrible afflictions, just here one day, gone the next. Words could not express the deep sorrow and profound shock we experienced that night, and in the days and weeks that followed.

I count myself incredibly blessed to have been loved by her. She loved so deeply and so fiercely, pouring out her generous heart to those who would take the time to truly know her. The day we said “I do!” was the happiest day of my life. She had a contagious laugh and a smile that could light up the room, even when she was hurting, sometimes more than anybody knew. It gives me some small comfort that she no longer feels any pain, but is surrounded instead by joy, light, and love.

She was a sensitive soul, leaving a little bit of herself in every life she touched. She was an empath, which means she would often know how you were feeling before you did. She felt the emotions of others, magnified in herself, and was an amazing listener, who could always be counted on for comfort and support.

The holidays were her favorite time of year, specifically Thanksgiving and Christmas. So today, on the heels of our day of thanks and the toes of the season of giving, I encourage you with two things.

First, be thankful for your loved ones, your family and friends, and most of all your children, if you are so blessed to have them. Life is so precious. Spend it with those you love, and do not waste any moments.

Second, give of yourself unto others, be it time, money, helping hands, a listening ear, or a friendly voice. Put forth light and love into this world, not darkness or hate. Emanate joy and kindness, not malice or bitterness.

And above all, cherish each day, for our time here is never guaranteed.

May you ever walk the beaches of paradise,
May you dance along the sunsets,
And sing until the end of eternity.
You are forever loved, and always in my heart.

Movie Wednesday #3 (on a Thursday)

I’m a sucker for a good revenge flick. Especially if the protagonist is a strong female lead.

It’s that time again kids! Today (which is now yesterday), I saw Peppermint at the local cheap-seats theater. Aside from being surprisingly uncomfortable compared to the plush recliners of the deluxe place, it was a nice bit of nostalgia. There was almost nobody there. Frankly, I’m not sure how they’re still in business; their operating costs must be absolutely minuscule. But hey, works for me, $4 movies! Let’s get to it, shall we?

Revenge is Fun to Watch

sweet sweet revenge raccoon
Revengecoon is on point.

Call me a sucker for a good revenge flick. Even more so when the protagonist is a badass woman. I don’t get into the really graphic “rape-revenge” stuff like I Spit On Your Grave and its ilk, but in general, if you’ve got a leading lady kickin’ ass and taking names to avenge some injustice done to her or her loved ones, I’m game.

And the user reviews agree with me, but the critics decidedly do not. That’s typical. Critics look at lots of deep facets of film-making, but the average audience just wants to be entertained. Are there a few moments when it’s difficult to believe the character, or the lines feel just a tad forced? Sure. Are there some bits where perhaps we stretch reality just a bit? Of course. But it’s a damn fun movie to watch, as Garner takes out one gangster or corrupt-cog-in-the-justice-machine after another.

Jennifer Garner’s Appeal

Speaking of our leading lady… I’ve never seen Alias, so you’ll forgive the lack of comparison. I did see Elektra, which, although a pretty bad movie overall, at least showed that she could convincingly play a tough action hero, even if that particular character was over-sexualized. Obviously that was 13 years ago, but her age plays well as the seasoned, slightly weather-worn mother, who can still whip herself into better shape than the cartel thugs half her age, dispense her vigilante justice, and look good while doing so.

It’s purposeful, and effective, that she does not show skin or become a sex-object at any point. That’s not what this is about, nor should it be. She’s all business, and that business is bloody, brutal, and filled with sharp objects and shotgun shells.

The Bad Guys (and their deaths)

peppermint movie still showing judge trapped
See this? This is explosive rope. It can cut through really big trees and sh*t.

One of the best parts of a revenge flick is seeing the imaginative or poetic-justice-esque ways in which the hero deals death to those that deserve it. (Yes, I’m using those terms “hero” and “deserve” loosely and in the context of the film itself, not engaging in a philosophical debate outside the world of the story.)

While some of these are lackluster, especially for an R rating, there are few that really shine. The judge, a sort of Kevin Spacey lookalike, gets it good with nails-in-the-hands and a courtroom-sized explosion. The three shooters are strung-up by their ankles on a ferris wheel, the process and lead-up to which, I feel, would have been even more interesting to watch than the end result. And there are at least a few fantastic head-shots that you’ll just want to see for yourself.

As I said, given the rating, I do think the film makers could have gone darker with some of the kills, but overall, we get what we came for. It feels very similar to Taken, for obvious reasons (same director), and that’s a good thing.

Trailers Lie

Sadly, and seemingly more often these days, the trailer showed some sequences and dialog that either didn’t make it into the movie, or misrepresented it slightly. The titular ‘Peppermint’ moment isn’t there, at least not with the same impact; and a couple of the more badass-sounding vengeance lines are diluted by too much context or a lack of ‘oomph’ in the background score.

But again, that’s Hollywood. The trailer’s job is to make you desperately want to see the movie, and they often succeed. So we can’t blame them too harshly. The film is gritty, well-made, and compares favorably with others in its genre.

Speaking of trailers, they played one for Glass, a really intriguing upcoming crossover-continuation of Unbreakable and Split, from everybody’s favorite writer-director name to purposefully mispronounce. If you haven’t seen either of those, do yourself a favor. (McAvoy is phenomenal in the latter.)

Conclusion

peppermint angel graffiti wall
I brightened this up a bit from the original. I tried to find a better one but no luck so far.

If you enjoy watching a tough female lead, seeing bad-guys get their comeuppance, and following a story through to the end, this movie is for you. Even if you’re not generally a J Garner fan; she really does well with this role, and there are no hints of her typical rom-com personality sneaking in. It’s a good solid ride with a satisfying conclusion — exactly what you want from a popcorn vengeance flick.

Til next time, readers!

5 Things I Learned at SQLSaturday

Go find a SQL Saturday near you, at sqlsaturday.com!

The weekend before last, I attended my 4th #SQLSaturday event; my 2nd in San Diego CA (the others were Orange County CA, which is equally fantastic, and a little closer to work, though about the same distance from home). If you haven’t heard of or been to one, check out the home page and find one coming to a city near you! They’re fabulous FREE training events for the MS data platform, including but certainly not limited to SQL Server. For example, you’ll almost always find Kevin Feasel talking about aRrr or Kafka or Hadoop.

Did I mention free?

So I thought I’d share a few things that I learned this time!

The LinkedIn app’s Killer Feature

Did you know? The LinkedIn app has a “find nearby” feature that uses magic your phone’s various radios to instantly connect you with a fellow user who has the app open near you. It’s awesome! Now you don’t even have to look up from your convention coffee and security-blanket (phone) to network — just fire up the app, go to the People tab, hit “Find Nearby”, and commence trolling. =P

No, that’s horrible; be a normal human and talk to people. The tech is just there to help make the post-conversation connection.

linked-in find-nearby button

Storage Myths Busted

This was an interesting and even slightly entertaining session presented by Max @ SQLHA. One analogy that really stood out to me was this:

SANs have become a bit like the printer industry — You don’t pay a lot for the enclosure, the device itself, i.e. the SAN box & software; but you pay through the nose for ‘refills’, i.e. the drives that your SAN vendor gods deem worthy of their enclosure.

It’s frighteningly accurate. Ask your storage admin what it costs to add a single drive (or pair of drives, if you’re using something with built-in redundancy) to your SAN. Then compare that cost with the same exact drive off the retail market. It’s highway robbery. And we’re letting them get away with it because we can’t evolve fast enough to take advantage of storage virtualization tech (S2D, SOFS, RDMA) that effectively makes servers with locally attached SSDs a superior architecture. (As long as they’re not using a horribly outdated interface like SAS!)

Data Protection and Privacy is Hard

But completely necessary. We all need to become security practitioners to some extent. Even if it just means raising and documenting our concerns to our bosses. The great state of California has even jumped on the bandwagon with its very own privacy act. Still in the early stages, yet on the heels of GDPR, it can only mean that there will be more to come.

A few concrete action items from this, which should be “fairly simple” (with a big ol’ asterisk that says “depending on your organization and your change-management process”).

what if i told you you don't need 'sa'
For anything. Ever.
  1. At least encrypt your database backups. (And make a plan to implement some kind of “full” encryption across the data estate, be it TDE or AE or app-driven encryption via your developer teams.)
  2. Stop using sa! Reset the password, and disable it. Yes, your Agent Jobs will still run just fine.
  3. Disable Named Pipes & Shared Memory protocols; just use TCP/IP. Disable the SQL Browser service.
  4. Cut off your SQL servers from the public Internet (yes, you should still patch them; just download the patches to a fileshare and install them using PowerShell!). And stop letting people run SSMS on the server; that’s what client machines are for!

Columnstore All The Things!

Seriously. If you’re not using them yet, read about them, play with them, and start using them. They’re magic.

Okay, that’s a bit dramatic. As with any technology and feature, you need to know the WHY. Understand what the best use-cases are and how that translates to your own environment.

columnstore all the tables
Easy there sparky…

Here are just a few of the tips I gleaned from the session on this:

  • They were designed for data warehouses, but…
  • They’re also great for “operational analytics” — where you want to do aggregate reporting on your ‘live’ data, but that performance usually kinda sucks (and you don’t want to lock up those tables anyway).
  • Best with SQL 2016 or higher; 2012’s “v1” implementation was horrible, and 2014’s “v2” was semi-usable but still had some major drawbacks
  • Best candidate tables are “very large” (millions of rows or more), and best candidate columns have “low cardinality”, meaning they’re not full of unique values — they should be “compressible”. A simple example would be a Customer’s “State of residence” — you probably have millions of customers, but only 50-ish “State”s, and your typical report is asking “Hey, how many Customers per State ‘do X'” — that’s a great candidate for a columnstore index.

Users Don’t Like Date-Pickers

I mean they’re still better than text-entry boxes, but we can do better. Talking about SSRS here — reporting services, i.e. “the poor-man’s Tableau”.

Picture a typical business user, middle-manager type, going to your SSRS report that you just built for him/her. The first thing it asks them to do is “pick a start-date and an end-date” to define the “reporting period”. But you should know by now that they almost always want to see the current Fiscal Quarter. So you default them to those dates that define the current quarter. Great!

Now they want to quickly compare to the previous quarter, or the same quarter of the previous Fiscal Year. Ruh-roh. Nobody wants to go messing with those lame date-pickers with the pop-up calendar thing.

Give them a clickable label instead, which says “Compare to last Quarter” or “Previous Fiscal Year”.

The click action should be to “drill through” to the same report, while changing the (now internal/hidden) date parameters to the appropriate dates. Presto! The user only had to click once to get exactly what they wanted. Much nicer experience.

I’ll try to have a future post going into detail on this. I’ve got tons of ideas swimming around in my head after FishHeadTed‘s excellent SSRS classes, and not nearly enough time in the day to flesh them out.

i see what you did there?
Get it? Swimming, fish?!?

Stay tuned, and go find a SQLSaturday near you!

Career Day: SQL DBA

Build a solid CS/IT foundation, build your soft-skills, and then learn the latest & greatest tech stack. Or the mature enterprise stuff. There’s plenty of room for both!

Our benevolent overlord prompted us (months ago; I know, I’m always late to the party) to write about what we do in our day-to-day jobs as SQL Server professionals, aka #SQLCareer. The actual idea is to do 4 posts in a row, for 4 working days (either adjacently or the same day each week for 4 weeks).

Sticklers, us? Nope, never.

Without any kind of future guarantees, here is my first.

Morning Mash-up

Emails, tickets, and alerts, oh my! Today was actually pretty quiet on the monitoring front — no major performance issues, no interruptions, and no job failures. That one that occasionally takes 8+ hours did okay last night. More often than not, I have to kill it if I find it’s been running into the work-day. That should be automated. /backlogged

Re-wrote some analyst’s data change script to be more “friendly” and set-based, instead relying on identity values — which is important when they can differ between Dev & Production environments. Deployed a few change requests, including that one.

virtualize all the things
Even SQL? Yes! Especially SQL. Just know the caveats.

On the side of less tedium, I fleshed-out more of the plan to virtualize our remaining bare-metal SQL servers. The usual ‘gotchas’ have to be discussed with the Server Admins — reserving compute resources, preventing the ‘noisy neighbor’ problem, and having enough storage to do the migration.

Yes, that means about 2x the storage as they’re currently using. Got a problem with that? No? Good. 😉

Finally, I worked on some code for querying the SSRS ReportServer database to obtain report metadata (including stored-proc usage, parameters, and user activity). The core concepts came from Ted Stathakis‘s SQLSaturday session on SSRS 2016. Hopefully, some of that code will be coming to GitHub soon.

Afternoon Delight

“Delight” being sarcastic. No really, I love my job, but sometimes there are just some things that make you go ‘grr arg’.

First up, developing and finalizing some “data cleanup effort” reports. These are things that show the business users “what’s wrong with the data” and give them some guidance on how to “fix it”. Now, because there is no easy “management GUI” for this data, and because actual changes need to go through change-control, it’s not going to be a cake-walk. But given the right inputs and some domain knowledge, they can make the decisions as to what those changes should be, and then hand them off to the BA’s (business analysts). Usually in Excel form.

Excel spreadsheet with woman screaming in front of it
What? It’s under 1,048,576 rows! Barely…

Next we have the ‘grr’ part. This is where I spent about 40 minutes trying to explain to the 3rd-party software vendor exactly where and how to integrate with our core data systems. Most of the info should not be ‘new’ to them, but since I never know which support tech will be assigned to the issue, it’s best to err on the side of verbosity. To make things more confusing, the folks on my end, who have been interfacing with the vendor thus far, aren’t intimately familiar with the underlying technology bits, so the conversation gets a little dicey before we all arrive on the same page.

Fortunately, since I had their attention, I was able to sneak in a suggestion to upgrade the back-end database for the software (which we self-host, but is essentially managed by them & their software) to something from this decade. MySQL 5.0 is old, guys, mmkay?

Wrapping Up

A developer needs a new table & some initial data-load to production; it’s passed QA. Great! They wrote the query a little weird, but nothing ol’ SQLPrompt can’t fix.

Commit outstanding stored-proc code & RDL files (SSRS reports) to source control. Even if I’m the only one working on them, I’d rather they be safe & track-able.

Ruh-roh. A developer set off some proc in a Dev db, it’s been running for over 30 minutes… and then they cancelled it! But wait, it’s stuck in rollback. Yes, kids, rolling-back a transaction can take even longer than the transaction itself, due to rollbacks being single-threaded.

Now, since the user’s hitting of the ‘Cancel’ button (aka “Stop” in SSMS) does not wholly and entirely cause a kill SPID command, I do that for them (since only sysadmin or processadmin can kill sessions). Then I run ye olde kill 64 with statusonly (64 happened to be the SPID) to check on its rollback status. Good news, everyone! It’s at… 0%.

bad news nobody
You heard me.

And it stays at 0%. For fifteen minutes.

I move on to other tasks of course, since there’s nothing more I can do (short of pulling the plug on the server, which is never a good idea (unless you’re testing your Disaster Recovery Hope plan!). We also create a ticket to fix this stored-proc so that it doesn’t “take forever”, even if it has to churn through millions of records.

Finally, the statusonly check starts moving. It’s about 45% rolled back, with about “300 seconds remaining”. This, as anybody who’s been a DBA will tell you, is in “Microsoft time”. You know those progress-bars you typically get in Windows while something is happening? They’ll start off fast, make it to around 90-some percent, tell you there’s about 1 minute remaining, then take at least five minutes to “finish that last little bit”? Yeah, that’s a “Microsoft minute”. Patent-pending.

But fortunately for us, it does finish in just about the time it promised, maybe a bit longer. Just in time for the Dev to test what they wanted to test before going home for the day. And just in time for me to have my evening not ruined by a runaway transaction.

PS: Anybody who’s ever worked with SQL, including yours truly, has done this (set off a long transaction and/or tried to rollback a long transaction) at least once in their career. If you ask a seasoned DBA or DB-Dev, and they say “No, I’ve never done that!”, they’re a dirty rotten liar.  =D

Are You Not Entertained?

If the life of a DBA sounds like fun to you, get in touch with your local IT recruiter and find out what the job market is like! You’ll hear all sorts of doom & gloom about how “the Cloud is taking over everything” and “the DBA is dead; long live DevSecDataOpsUnicorns!”.

No, I joke. Yes, some of the fundamental responsibilities of the DBA are shifting to the cloud vendors, to IaaS/PaaS services, but at the end of the day, being in IT is about learning tech and working with people. Build a solid CS/IT foundation, build your soft-skills, and then learn the latest & greatest tech stack. Or the mature enterprise stuff. There’s plenty of room for both!

tee-shirt that says "tough enough to be a dba, crazy enough to love it"
We end on a happy note.