Man Shakes Fist Angrily at the Clouds

This feature us utterly useless to me. No, worse, it’s actually a hindrance to productivity.

I feel like I’ve been ranting and bemoaning “cloud-y” stuff a lot lately. And I don’t like that, because I understand it’s “the future” and “the best way to deliver software” and blah blah blah. But hear me out.

Some things just really need to stop. Or at least be rolled out in gentle, opt-in style phases where users can choose “Yes I’d like that feature” or “Nope, hard-pass”.

And as a million developers scream at me from the glare of their screens…

But seriously. Let me give you examples. And guess what? Because I primarily use Microsoft technologies at work, that’s what I’m going to put on blast. But they’re not alone, and they’re probably not the biggest offender. (Although their marketing department STILL baffles me.)

Planner

Planner is a sort of Trello/Project/virtual-whiteboard hybrid that comes bundled with Office Microsoft Office 365 (screw your new naming convention). It’s half decent. I mostly use it to prove to my bosses that “Yeah, I have a crap-ton of work in the backlog, so stop asking me if Thing X is done yet.”

The hierarchy of Planner goes like so: Plan, Board, Section, Task. Task is the “unit of work” that most Agile teams are familiar with. Many Tasks make up a Section (or lane, or column, or whatever); many Sections make up a Board, and a Board is part of a Plan (aka a Project).

Recently they pushed out an update that made it horrendously ugly for me. Why? Because they assumed that any time you put a LINK on a Task (a URL), it must have some cutesy picture or preview associated with it, and thus you should see that picture as a disproportionately large head-space on that task. But guess what all my links are? Ticketing system tickets. They don’t have a picture. Or best-case scenario, they’re all the exact same picture (of said ticketing system’s logo/favicon).

Thus, this feature us utterly useless to me. No, worse, it’s actually a hindrance to productivity. Because now my tasks take up more than twice the space, with zero added value.

#SMH

Teams

Teams is MS’s answer to Slack. And in a great many users’ opinion, a poor substitute. I’m not an extremist in that camp; I can see its uses and the things it brings to the table, and I can generally use it on a day-to-day basis without getting frustrated. The video-conferencing capability is quite good, no complaints there.

So how have they done me dirty lately? Well, there’s this whole “double-click a user/avater to open a new pop-out window to chat with that user”. WHY? Why is this necessary? I already have the app open. Can you not just TAKE ME to the Chat tab to the conversation with that user?

Another thing. This isn’t a “new update” thing; this is a long-standing “Oh my lord I can’t believe they DESIGNED it this way, WHAT were they THINKING?!?!” thing. Files. Sending & receiving files. I get an picture, like a screenshot, from a user (that’s NOT a OneDrive link, because that’s a whole ‘nother can-of-worms). I click to download it. It goes.. where?

OH RIGHT. It goes to my ‘Downloads’ folder. That dumpster-fire, where everything from anywhere goes into, and nobody keeps it organized, and nobody knows how to find anything unless they’ve gotten smart enough to sort by Date Modified descending. Right, that.

But wait, the file name is even better. “MicrosoftTeams-image.png”. Oops, did you get another one? “MicrosoftTeams-image (1).png”. Another? “MicrosoftTeams-image (2).png” TOTALLY OBVIOUS what those are! Who could possibly get confused by that?!?

One last gripe. The freakin’ notifications. Do I really need to be notified every damn time someone gives one of my chats a “thumbs-up”? The notifications don’t even go away immediately! You have to literally go to the Notifications tab and CLICK ON IT, even if you were previously on that exact conversation in which the thumbs-up happened!

#SMH squared

old man yells at cloud
I’m not even over 40 yet… but I identify with this.

Chrome

This last one is a bit controversial. See, in theory, every Chrome browser update is supposed to make the web more secure, by enforcing standards compliance and security practices and all that jazz. Great! Guess what? We live in the real world. People have favorites/bookmarks and desktop-shortcuts and the like. Companies run on-prem email servers hybrid-ized with cloud email providers (yes, lookin’ at you again, Office 365) and their end-users expect things to JUST WORK. Continously.

If they can’t get their email due to some strange new “certificate” error that shows up when the click their favorite Favorite, guess who they call? Nope, not Google (because what even is a phone? or a human?) — their own company helpdesk. And the hapless helpdesk tech says, “Gee, it works for me, what’s going on?” Then they spend an hour talking with their team trying to compensate for every different combination of user-level/environment/browser/network-configuration, only to end up creating a redirect in their cloud-based DNS overlay so that the user doesn’t have to think about certificates and mysterious “Oops! :(” errors anymore.

#SMH a third time

What’s the point?

I don’t know… not like I can do anything about the way these software giants conduct their development. Just… if you’re a developer and you read this, take a moment to consider your end users more. Please? Not everybody always wants all your new features. Yeah I get it, option-izing them makes things harder for you. Sorry. If you wanted an easy career you’re in the wrong field. Just consider it, at least.

Quickie: Remove Junk Apps in Windows 10

Now, if you’ll excuse me, I’m going to go shake my fist at the empty air and scream into the void.

AKA “Crap that I uninstall immediately after a fresh installation of Windows 10.” It’s frankly mind-boggling how ridiculous it is that Microsoft continues to include junk like this with what is supposed to be “their best OS ever”. Granted, past versions of Windows came with their own fair share of bloatware/junk, but some of that stuff was REALLY amazing and useful, like the DVD Creator in Windows 7.

The Offensive

Here is my list of apps that I remove immediately with a fresh install.

  1. 3D Viewer
  2. Candy Crush whatever-the-hell-flavor-of-the-year-it-is
  3. Farm Heroes kill-me-now-with-these-stupid-games
  4. Feedback Hub – because Microsoft does not actually care what you think (or I wouldn’t be writing this, would I?)
  5. Mixed Reality Portal
  6. Mobile Plans
  7. Paint 3D
  8. Print 3D (notice a pattern yet?)
  9. Skype – dear god please kill this already. Unless you still use it… then you’re the reason MS keeps this arcane thing around, and you should #stopit.
  10. Tips – this may be a personal preference, but I find the built-in tips annoying and useless, so unless you’re brand-new to Windows 10, I say remove it.
  11. Weather – because that’s what your phone is for, silly. Do you really check the weather on your PC/laptop?
  12. Xbox
  13. Xbox Live
xbox microsoft stop it
Srsly.

Honorable mention: the ‘Game Bar’ (possibly sometimes called ‘Xbox Game Bar’), is not on this list (i.e. I leave it installed), because what it actually is, is a very useful screen-capture tool for recording a video (and/or stills) of your desktop and what you’re doing on it. So I could have, for instance, recorded a real live video of me going into the Settings page and uninstalling all these apps, and you could watch said video after I uploaded it to YouTube, instead reading all this boring text! What a world, eh?

Hit the key-combo ‘Windows+G’ to see it in action on your own PC. Hit Escape to dismiss it after panicking “omg wth did I just do to my computer?!?”.

The Tolerable (but Unnecessary)

And here are a few optional ones — I typically remove these from a “business-purpose” or “low-end” machine, but for an average end-user, they’re OK(ish).

  1. Groove Music – it’s not a great music player, but it’ll do if you don’t know any better.
  2. Microsoft Solitaire Collection – well, if there’s one thing everybody has ever done since the dawn of the PC, it’s play solitaire. So sure, I get it, it’s hard to let go. Fine, keep it if you must. I just don’t see the point anymore.
  3. Movies & TV – again, if you have nothing, this is a half-decent media player (think of it like old Windows Media Player’s love-child), but if you’re seriously into video watching on the computer, you’ll probably replace it with something better.
  4. Office – unless you’re actually planning on buying into their ecosystem with Office 365. Which I don’t disagree with – it’s a fine product, if you’re ok with paying the monthly/annual fees.
  5. Spotify – I’m not sure if this is always here, or if it was just on the machine I was working with at the time. But again, great, if you’re a Spotify user. Otherwise, why?

How To

instructions to uninstall apps on windows 10
OMG instructions!

In case you’re not sure how to uninstall apps — what used to be called programs, in the ‘Add & Remove Programs’ page of the Control Panel in Windows-past — simply hit Start, Settings, and go to Apps. In the ‘Apps & Features’ screen, scroll down the list of installed apps, select one, and click the ‘Uninstall’ button. On some of them, this button will be grayed-out (disabled), which means good ol’ Microsoft doesn’t let you remove it. Which is ludicrous for something like the ‘People’ app, that does god-knows-what and frankly should never have existed.

And now, if you’ll excuse me, I’m going to go shake my fist at the empty air and scream into the void. =P

Disclaimer: this was written in May 2020, as of Windows 10 version 1909. The native/built-in app list could change at any time if MS releases a major feature update. Which will cause more screaming. Welcome to OSaaS (Operating Systems as-a-Service).

How I Work from Home

Instead of relying on my home internet bandwidth, I simply RDP into my workstation that’s still physically sitting in the office.

This is like a month late, but I didn’t have anything for the latest TSQL Tuesday on Unit Testing. Kudos to those of you who are doing it… it’s REALLY HARD. Harder than it should be anyway.

So as we all started working from home last month, it dawned on me that, apparently, the way I work from home is drastically different than most everybody around me. And it got me really curious, whether this is sort of generally applicable IT workers, or whether I’m really the odd-duck!

a basic VPN diagram
Courtesy of Wikipedia.

The Setup

Probably like many of you, my primary workstation at the office is a laptop with a docking station hooked up to dual monitors and a full size keyboard and mouse. It makes going to meetings easier, and in theory I’m able to take it to the occasional conference. (I say “in theory” because, as I’ll explain in a moment, it rarely happens.)

Now, apparently the ‘norm’, at least for non-IT staff, is to take their laptop home, maybe with a dock & an extra monitor or two, fire it up, connect to the VPN, and off you go. Some people need printers and/or scanners, which makes it more complicated. But for now let’s focus on the typical knowledge worker that just needs a PC. And of course, if their workstation at the office is not a laptop, IT is tasked to provide them with one. Which, during this Coronavirus outbreak, has caused its own subset of issues with supply & demand at the big-box tech retailers.

Contrast that to me, and hopefully maybe some of you. I have the good fortune (or not, depending on whether you’d asked my wife early on) of owning a plethora of computing devices, most of which are at least suitable for running VPN & RDP (Remote Desktop). All of the, say, resources which I need access to and that I manage and manipulate on a regular basis, are in a data center, a big building with tons of racks of servers and gear and super-high-speed connections. And the office, of course, has a much higher-speed/bandwidth connection TO that data center than anybody does from their home. Some of you may see where I’m going with this.

The Why

So instead of relying on my home internet bandwidth to keep up with all the bandwidth-intensive tasks that constitute my workday — including, as my fellow DBAs will attest, dozens upon dozens of queries against the SQL Servers in SSMS — I simply RDP into my workstation at the office. Yes, my work laptop, which is still sitting in its dock on my desk in an empty (mostly) building. It’s the closest thing to actually being there.

Here’s what that allows me to do. All my network resources are just as quick and responsive as they are at the office. My code repositories and my Visual Studio windows are instantly available. My queries return results in milliseconds because they’re only traveling from the data center to the office. The only thing traveling the longer and slower distance to my house is the image of the screen from the work laptop. Make sense?

This is the same principle that drives some of these new “cloud gaming” services like Google Stadia or NVidia GeForce Now. The only thing you as the player really need is the continuous moving picture of the game, not all the hard work behind-the-scenes that it takes the GPU to render it. (Of course, the thing they haven’t really solved for is the input latency and the turnaround time of the player’s action being replicated on the screen and leading to the next action in the chain.)

a picture of an old desktop computer on a dining table with several other computers nearby.
Disclaimer: not my actual dining room. But at one point in history, it was pretty close.

Okay, I get it, it’s not for everyone

I understand some workers need more than that to do their jobs. And some folks don’t have the handful of spare PCs that I do. So I get it. Not everybody can or should work this way. But I was quite shocked that, out of all the folks I work with, I was the only who thought this was THE way to work remotely. Or at least the best way.

Surely, most people with a.. what we might call “gray collar” jobs — we work at a desk all day but we aren’t Wall Street and we don’t wear suits, so.. your typical middle class occupations, yeah? — anyway. We all have a workstation at the office. And surely we have at least one computer at home, even if it’s another laptop. Right? But no, apparently, the “tablet revolution” was a thing, and people don’t have big ol’ desktops or even traditional laptops at home much anymore.

Not only that, even if you could afford to invest in one FOR this season of mass-remote-work, you’d have had a bitch of time getting ahold of one, because everybody and their mother decided now was the time to buy. And not without good reason. Most of that purchasing was actually done BY the small-medium enterprise business market, because their IT departments had to buy a bunch of new laptops for their non-IT colleagues to take home for work!

Anyway.

I guess there’s not much of a big important point or lesson here.

Is there ever?

audience

If anything, I would encourage people to TRY working from home this way, if they have the resources to do so. It significantly lessens the burden on your IT folks, as long as your home PC that you’re using to VPN is secure. That’s really the biggest variable in this equation, and in some cases, it’s why they actually prefer to issue you a company-imaged/company-secured device instead. But, thankfully, with a few network and firewall tweaks, they can lock down the VPN traffic so that it doesn’t allow your home device to talk to anything BUT your own workstation — or at least, any computer in the “workstations” group. So yes, there are some trade-offs. But again, especially if your employer has had trouble keeping up with the surge in demand for “loaner laptops”, maybe suggest you try this way and see if it can work for you.

Stay safe, stay healthy, and be kind.

25 Years Ago in Gaming History

But NONE of these could hold a candle to the mind-bending stomach-churning six-degrees-of-freedom true 3D shooter that was Descent.

Descent.

When I say that word, what do you think of? Perhaps the 2005 horror movie based in a cave? Good film. Not quite there.

descent-1-rebirth-mission-brief-start
Mission briefing screen

“Prepare for descent…”

How about now? If you just got goosebumps up your joystick arm and tickly tingles in you trigger fingers, you’re my kind ‘a people. That’s right, my fellow four-eighty-six pilots, I’m talking about the game. The game of the year; nay, of the decade. The one that changed shooters forever. The ever impressive.. the one-contained.. the often imitated but never duplicated oh shi–.. Descent!

Yes, this game will forever live on in my memory as the pinnacle of 3D gaming from my childhood. The mid-1900s saw some killer game releases. Top shooters included Doom, Quake, Goldeneye, Wolfenstein, Half-Life, Unreal. But NONE of these could hold a candle to the mind-bending stomach-churning six-degrees-of-freedom true 3D shooter that was Descent. Bonus, since it didn’t involve killing humans (and thus, no gore) — evil robots were the enemy here — it was perfectly acceptable to my parents for a young 9-year-old Nate’s innocent eyeballs. The sheer rush of adrenaline as the ‘bots tried to ambush as you barely escaped with evasive maneuvers and turned around to blast them to bits… Pure nostalgic gold.

“But what the blazes are you going on about”, you may ask. Excellent question. Please see YouTube. It’s not the absolute greatest representation of the true 6DoF potential, but you’ll get the idea. And if you get a little queasy, a little motion-sick, that’s perfectly normal. Anybody who’s never played one of these before is likely to need some.. perspective.

descent-1-rebirth-gameplay-screenshot
Level 1, Lunar Outpost

Open-Source Developers are Awesome

Here’s why I love developers. Gather a few of ’em together around something they’re passionate about, and watch magic happen. Descent is 25 years old this year. It was made for DOS and Windows 95, as well as a few consoles. Heck, I even have the original CDs from the “Definitive Edition” pack (re-released a few years later). But there’s no way in heck they would run on modern computers with modern operating systems.

Enter open source. Thankfully, the Descent 1 & 2 source code was released to the public at some point. That’s like Christmas Day to developers — anybody with any programming skill could now peek and tweak at the code, even rewrite it from scratch. Two separate projects — called “source ports” — spawned from that seed: DXX-Rebirth, and D2X-XL. The former is simpler and more true-to-form, retaining as much of the original gameplay look & feel as possible, while still enabling it to run on modern systems and adding a few nice conveniences for the 21st century player. The latter is more of a “let’s see how far we can take this” philosophy, in that the author has consistently added many changes and enhancements to the core game mechanics and graphics that, while some players find appealing, I personally take it as “noisy”. But due to its popularity, there are even a number of levels (aka ‘missions’) that will only work with this version. And don’t get me wrong, the work is impressive, by any developer’s standards.

D2X-XL gameplay in revamped Level 1 Lunar Outpost
D2X-XL gameplay in revamped Level 1 Lunar Outpost

A Legacy Lives On

As with most legendary hit games, a dedicated “mapping & modding” community sprouted up around it. To this day, DescentBB forums are active, and a few members are even still making levels. I even tried my hand at it a couple times. I remember almost begging my parents to buy the re-packaged game box because it included the “Descent Mission Builder” software that let you make your own levels. Hours upon hours spent manipulating cubes and flying through tunnels to test. But nothing compared to the fun of playing through the true masterpieces of level-design produced by the most prolific builders of the day — they pushed the game so far beyond what the creators originally imagined, yet likely dared dream of.

A waterfall effect in D2X-XL
A waterfall effect in Descent 2 custom level “Alhambra”

OMG I Must Play!

Then have I got a page for you! Includes download links and step-by-step instructions for Windows users. Bonus, it’ll soon include my own hand-picked custom-levels pack (so you don’t have to sort through the piles of crap that accumulated from half-baked “level contests” and “archive servers” over the decades).

A Challenger Appears

Overload, a spiritual successor to the Descent series, and involving the very founders of Parallax, was released in 2018 after a successful Kickstarter campaign. I haven’t played it yet, but from the footage and reviews I’ve seen, it’s right at home in this game-hall-of-fame.

Overload gameplay screenshot
Overload gameplay screenshot

So what are you waiting for? Catch me on Twitch and watch me play, or go download all the goodies and check it out yourself!

See you in the mines!

Favorite tagline of many hot-shot pilots and level-designers.

Dirty Deeds Done in SQL

Yes, I wrote a loop to find a gap in an identity-value sequence. Sue me. (Or don’t, that’d be cool too.)

I’ve done some things I’m not proud of. We all do, in IT, typically when we’re under-the-gun for a deadline or when the systems and frameworks in which we work have some sort of nuance or limitation that we just cannot get around, past, or over. And so we hack. We write code we’re not happy with. We even write code that we despise with every fiber of our well-intentioned being. But it has to be done. Because there’s no other choice.

With that somewhat depressing introduction, I give you my latest “dirty deed, done dirt cheap” (or for a reasonable salary, but still very reprehensible for anybody who values clean code and clean architecture). Yes, this is an actual stored-procedure signature that I wrote. Thank gods it’s not integrated into any software; it just happened to serve a niche purpose at the time. But still! I felt ICKY.

cursors are bad mkay
Did you recognize the album art from the cover pic? Just wondering… 😉
/*
Description: Search for an available ID value in Category starting 
at the low end (i.e. find a gap), because it's aesthetically pleasing 
to have Root level Categories use low ID values. Yes, really.
But mostly b/c they need to be the same on DEV & PROD environments.
*/
CREATE OR ALTER PROCEDURE dbo.[GetNextAvailableRootCategoryID]
  @StartAtID int = 10  /*
      '1' is not used but we don't feel like starting THAT low
      (that'd be like "starting over" or "starting from scratch")
      You never get a checkbook with the first check# being '1', right?
      Wait, what's a checkbook?
      Ask your parents. Or your grandparents. */
  , @MaxID int = 1000  /*
      Throw an error if we can't find any gaps before this point */
AS
BEGIN
--and you can imagine how that went...
--something about a loop, a couple IF/ELSE's, a RAISERROR or two,
--and a RETURN @val in a B-tree(eeee).
END;

I know, I know. You’re saying “wow that’s really not that bad!”. Or “wow, you REALLY couldn’t do something better about the constraints to make it less terrible?” Again, time, priorities, and energy. What’s 10 minutes in the big scheme of things, even if the code is silly and perpetuates the bad decisions that came before it? Should the overlaying systems actually depend on matching ID values? NO!!! Obviously not. But was that even on the software development roadmap for the next quarter? Also no.

So, what’ve you got? Show me your dirty deeds done in SQL. It’s okay, nobody’s judging! Except yourself, if you’ve any skin in the game. We’re all our own worst critics. But if we can’t laugh at ourselves sometimes, what the hell are we doing here? =)

T-SQL Tuesday #122: Impostors!

Be humble, but be confident in your own expertise. Realize that you will sometimes be the teacher, and sometimes the student.

Howdy folks! It’s been a while. My last post was in October of last decade (har har)! I’ve been busy with other projects, work, and life in general. 🙂

Now, this month’s invitation is hosted by John Shaulis. I’m always excited to see someone in the #SQLcommunity that I’ve never heard of before. Looks like he’s familiar with the same WordPress templates as I am. Heh. I like it.

Side-note, I’ve been planning to buy my own domain and remove the ads here for a better reading experience (and more professional feel), so hang in there… it’s happening soon!

And now without further ado.

Who, Me?

Everybody reading this knows what impostor syndrome is. I won’t bore you with that. Fact is, if you’re in tech, you either have it or you know someone who does. If neither, well you’re probably a narcissist surrounded by other narcissists and you might wanna look for another job. Heh heh.

oh, get a job? just get a job? why don't I strap on my job helmet, get into a job cannon, and fire off into job land, where jobs grow on jobbies!
I think this is from “It’s Always Sunny in Philadelphia” but I’ve never seen the show.

Yes, You!

I’ll share a personal and ongoing example of where I’m constantly feeling “not good enough at my job”. It’s about server migrations. You know, where you take a running production SQL Server instance, and you have to move it to new hardware/infrastructure. Maybe it’s a physical (bare metal) cluster to a virtualized environment. Maybe it’s just one VM to another VM on a newer platform. It typically involves taking a maintenance window and convincing business stakeholders that the “downtime” is worthwhile.

Now surely we’ve all heard of DBATools by now, right? (If not, go there right now and check ’em out!) They were BUILT to do migrations. Yet I’ve not been able to successfully use them as the whole and ONLY toolset for that purpose. Let me clarify: I’ve used pieces of the framework (such as the copy logins / users command, and others) to HELP me in the migration project, but never been able to simply fire off Start-DbaMigration and go get coffee and watch as things magically successfully fall into place.

dog in a room on fire, casually drinking coffee, saying "this is fine."
It’s working EXACTLY AS IT SHOULD!

Couldn’t Be!

Even when I’ve built a beautiful check-list, lined up all my scripts, scheduled things via Agent Jobs, double-checked names and permissions and networks and drive letters and shares. Still, something inevitably goes wrong. Maybe it’s just that a database refuses to go into READ_ONLY mode when I ask it to. Maybe it’s that a whole series of jobs get lost because they were in the wrong category. Maybe user permissions don’t come over on this database because I didn’t sacrifice a chicken on the night of the blood-moon while the wind blew north-east.

Surely, by this point in my 10+ years as a database professional, 4 years at this particular company and role, I would be able to do this with my eyes closed. With no hiccups or misfires. Right? RIGHT??

murphy's law: if anything can go wrong, it will go wrong.
Full plaque samples, which are quite entertaining, can be seen here & here, to name a few.

Then Who?

Wrong. Murphy’s Law is a real thing. More than that, things CHANGE. The environment is always evolving to fit the business needs. The technology is always just a little ahead of my own knowledge-base. And every SQL instance in this environment is, unfortunately, still, a ‘pet‘. Not a ‘cattle‘ (cow?). They’re each carefully and fearfully constructed to suit a specific set of application and business needs, each with their own nuances and barely-documented dependencies.

Plus, it’s not like we’re doing these migrations very often. Anything you don’t do on a constant basis, at least a few times per week, tends to sink back toward the bottom of the mental stack, and you forget the details and gotchas that were involved because your brain needs to keep more important and relevant things near the top, for what you’re working on “right now”. This is normal, at least in my mind.

DevOps stole the cookie from the cookie jar!

Just hire a DevOps Engineer to solve all your problems. Then everything will be all sunshine and rainbows and unicorns. All your servers will be cattle, replaceable little containers that are allowed to fail because there’s always another one waiting to spin-up and replace it. Not like your DATA is important or anything. It’s only the lifeblood of the entire company.

Not really sure where I was going with this. I love DevOps, don’t get me wrong, and it does have a part to play in making the traditional RDBMS platform more agile, but the devilish details and difficulties therein are ALWAYS overlooked/brushed-off in popular discourse, and it’s got me a little jaded.

I mostly wanted to finish the song I’d started with the title-blocks. =P

N.

But srsly. Instead of feeling guilty about your impostor syndrome, just own it. Be humble, but be confident in your own expertise. Realize that you will sometimes be the teacher, and sometimes the student. And hopefully more often the latter, because there’s exponentially more to learn every year we evolve on this crazy spinning rock we call Earth.

live as if you were to die tomorrow. learn as if you were to live forever. -Mahatma Gandhi
Good advice in general.

T-SQL Tuesday #119: Change of Mind

Get up, come on get down with the SPACES!

Because I’m super late (as usual), about to go to bed (because I’ll be commuting tomorrow), and lazy (aren’t we all?), this will be a quickie.

This month’s #tsqltuesday hosted by the amazing Alex Yates, in which he asks us to discuss something about which we’ve changed our minds over the course of our career (or some subset of time therein).

I’m really excited to read some of the submissions I’ve skimmed on the Twitter feed so far, such as Oracle vs. MS-SQL and the importance of diversity. After all, what else am I gonna do while I sit in the vanpool for 3 hours? (round-trip, thankfully, not one-way!)

Tabs vs. Spaces

Oh my! Them’s fightin’ words. Even back in the early days of this very blog, I wrote about my preference for tabs. But now.. *gasp*.. I’m down with the spaces!

blasphemy-300
Blasphemy of the highest order!

Why, you ask?

Well, partially because I’ve changed some of my overall T-SQL coding style preferences and methods of construction. When I learned the Alt-Shift select method (block selection, aka vertical selection) in SSMS, it definitely set me on a track away from tabs. Now I don’t go all cray-cray with vertically aligned sections/clauses/etc. too much, but I will say that in certain instances, it’s made the query much easier to read. And in such instances, spaces definitely trump tabs for ease-of-use with said vertical-alignment efforts.

If you’re not sure what I’m talking about (because, admittedly, it’s hard to write about and much easier to show visually), just search Youtube for an example of SSMS block-select tricks.

And this is within the last 4 years, so I still find old stored-procs that I’ve written that have the tabs, and I chuckle slightly to myself as I Ctrl-K-Y (that’s the Red Gate SQLPrompt shortcut to ‘format this code in my current style’) and make my modifications.

Miscellaneous Little Things

I’ve developed some other preferences, too, which contradict some of my old formative-years’ habits. For example, I used to write my TSQL in pure lowercase. I now prefer the ANSI-CAPS for language constructs and keywords, but if I ever need to write dynamic-SQL, it goes in lowercase.

caps-lock-not-always-necessary
I even re-used old images, instead of finding new ones. LAAAZZZYY!! :O)

Some of these habits come from Aaron Bertrand and other SQL-community big-name bloggers. Like preferring CONVERT over CAST, or changing from trailing-commas to leading-commas. (Although he may have flipped on that again, I can’t remember.) While others just kinda happened organically. Like, two tabs for the ON line under each JOIN — the join predicate — just felt silly after a while, so I reverted to one. I used to be stickler for forcibly quoting identifiers that collided with language keywords — like if you have a column named Date or Value, you best be puttin them square-brackets around those suckers ([Date], [Value]), but now.. honestly, I don’t care enough to bother. Unless you do something really heinous, like timestamp. =P

Anyway, that’s all I have for now. There are much more important things that I could, and should have, written about, but as I said, and as always, I’m already late to the party. ‘Til next time! ❤

T-SQL Tuesday 118: Fantasy Feature

It really shouldn’t be this difficult. But it is. And that’s why we get paid. Still, it’d be nice if load-testing were easier, wouldn’t it?

Aka “hey look at us Microsoft, we want stuff!!” Because they shut down Connect and its replacement (Azure Feedback aka rebranded UserVoice) is awful. Just plain terrible. In unrelated news, I’ve never been an MVP.. wonder why? 🙄😜

Anyhoo, this month’s invitation is brought to us by the lovely and talented Kevin Chant. He asks us to fantasize about SQL Server. No, not like that Erik, get your mind out of the gutter.

And I don’t apologize. At all.

This IS a complaint. But hopefully it’s also an idea for those who are better at building stuff than me to.. ya know.. build stuff.

And shout-out to my favorite fellow blogger Shane (@SOZDBA) who’s too polite for his own good. ❤

Load Testing is HARD.

Too hard. So hard that nobody does it. At least not productively, efficiently, or willingly. About the only times that I can personally point out an instance where I’ve actually buckled down and done something roughly comparable to a true load test (which I’ll talk about in a minute), was when I was forced to do so by my managers to prove that a hardware environment upgrade hadn’t gone awry, and that our servers truly were running at least as good as, if not better than, before.

But guess what? We never really proved anything conclusively. We had inklings, feelings, warm fuzzies.. okay maybe a DiskSpd output file or two.. which indicated that things were “mostly probably pretty good and kinda sorta better.”

Why? BECAUSE IT’S FREAKING HARD.

What is “True Load Testing”?

a graphic representing software load testing
Something like that… maybe?

Glad you asked. Simply put, it’s the ability to execute these 3 steps, easily and efficiently, with minimal configuration overhead and without needing to pour agonizingly over tomes of docs:

  1. Capture and store a SQL server workload — i.e. ALL the transactions run against an instance in a given time frame — AND performance metrics from said instance while said workload was running.
  2. Run that captured workload against another SQL server instance, and capture THE SAME performance metrics.
  3. Compare results from each set of gathered performance metrics, with a concise, easy to understand rating system that tells you which instance ran better and why.

Now, could we argue that the “capturing” of #1 adds some overhead to the instance? Sure! So I’m fine with NOT gathering the performance metrics during the same window as the workload-capture. Put it off to step 2, where we replay said workload against 1 or more instances and measure the performance on them. So we could replay the same workload on the original instance, and a new one, and we’d have our two sets of measurements to compare.

Got me? Good.

The Plumbing is There

I know. I know what you’re screaming at your monitor/screen right now. “But Nate, that’s exactly what Distributed Replay is for!!”

Bruh, have you even USED Distributed Replay?!? It’s way too complicated to set up, let alone manage and operate. Remember what I said about tomes of docs? Yeah. ANGTFT.

That’s the sad part. Microsoft has built up the plumbing and scaffolding for all of this over the past few decades. But we’ve yet to see that final layer, that chrome polish and finishing touch that makes the user go “Ahhh, now THAT was an educational and enjoyable experience!”

red easy button
Staples’ trademark be damned!

Obviously when it comes to performance metrics we’ve got a huge wealth of knowledge in the system DMVs. Great! Now let’s condense and simplify those into like 4 key ratings of your instance, for those of us who aren’t Paul Randal or Glenn Berry.

Oh, 3rd party monitoring products you say? Sure! Great! Love em. Do they do what I just said? Nope. Because “it depends.” Anybody else sick of hearing that?

It Really Shouldn’t Be This Difficult

But that’s why we get paid. Because it is. And no matter how many cloud services Azure & AWS try to shove down our throats, the reality is that enterprises will continue to rely on human engineers to prove (or disprove) that NewFancyServerX is better than OldCrappyServerA for running YourTerribleSqlWorkloadZ.

Because we can’t architect perfection. And we live in the real world where business decisions and financial constraints have an actual measurable impact on our technology stack choices and roadmaps. So I’m not saying it’s inexcusable that we don’t have this — this easy, measurable, understandable toolset for performance-load-testing — yet. I’m just saying it’s mildly annoying. And perhaps a little frustrating.

With that, I think I’ve written two angry rant-y posts in a row, so I do apologize to you, dear reader (but not, and never, to Microsoft). I’ll leave you with this cute picture of my dog being ridiculous, because it always makes me smile. Til next time!

husky dog laying on back in silly position
doggo being doggo

Replication “Just Trust Me”

For what seems like years, I’ve bemoaned the fact that SQL Transactional Replication doesn’t come with a “Just Trust Me” option. I’ll explain more about what I mean in a moment. The other thing I’ve complained about is that there’s no “Pause” button — which not entirely accurate, since obviously you could just stop the distribution and subscription agents. But specifically what I mean is, it’s not easy to ‘put it on hold so you can make some schema changes to one of the tables that’s being replicated’, and then easily “Resume” it after you’re done with said changes.

Well, I’m happy to say that now I have both of these tools/methodologies in my arsenal!

Quick level-set: If you’ve been living under a virtual rock, SQL replication is an old-hat “tried-and-true” method of producing readable copies of your data on other SQL servers, whether for reporting or DR. It’s not an HA technology per-se, although I suppose you could use it for that if you were feeling adventurous. It’s more for “I need a reasonably up-to-date copy of my data ‘over there’ so I can run reports / crappy user-formed / EF-generated queries against it without slowing down my production OLTP system.”

Yes, I did just take a pot-shot at Entity Framework. #DealWithIt

i don't always break replication but when i do it drives me to drink
But not Dos Equis. That stuff is terrible. =P

Why?

The word that comes to most DBA’s minds when they think of replication is ‘brittle’. And for good reason — when it breaks, it breaks hard, and you’re often left trying to pick up the pieces while wondering how much worse it could be if you just started over from scratch (i.e. dropped all the replications and re-created them). Which, honestly, sometimes is easier. But not if you have a large volume of data, and certainly not if that data is indexed and you don’t want your apps to experience a performance-crisis!

Now, because this post has been sitting in my ‘Drafts’ area for far too long, I’m going to break this up into 2 parts, so I can get something out the door. In part 1, I’ll briefly explain each of the key components of the process. In part 2, I’ll dive into a little more step-by-step detail.

Primary resources that went into this: docs, article1, article2, article3. And my very own dba.SEanswer where I apparently went through a similar process back in 2016 and subsequently forgot about it (mostly).

Key 1: Sync-Type

TL;DR: the “Just Trust Me” option is, when you create the subscription, sys.sp_addsubscription, specifying the @sync_type = 'none' parameter value. Huge thanks to @garethn in the SQL Community Slack.

Sidebar: if you haven’t yet joined the SQL Community Slack, WHAT ARE YOU WAITING FOR?!?!? DO IT, DO IT NOW!!!

Ahnold ‘teh Governator’

@sync_type = 'replication support only' may be applicable in some scenarios as well, but I’m not 100% clear on the difference / use-cases at the moment. More to come later, hopefully.

Key 2: Script Publication Procs

Protip: sys.sp_scriptpublicationcustomprocs @publication = 'PublicationName' generates the internal repl-procs that control the table creations/updates on the subscriber. You run this ‘script’ command on the publisher, then get the results (the script it generates), copy-paste to a new SQL file, and run on the subscriber.

This has come in handy on several recent occasions, wherein I had to either swap tables behind-the-scenes due to a PK change, or make a column & index change that involved truncation. Using the “stop, shuffle, start” method, which I’ll get into in part 2, I’m able to tell the subscriber “Hey, the definition of this table has changed, you need to grab these new repl-procs so you can handle it correctly!”

Key 3: Publication Properties

In order to tell our publication that “We’re gonna be making some changes, don’t panic!”, we want to turn OFF 2 properties (assuming they’re true, which they likely are by default) using sys.sp_changepublication @publication='MyPub'. The properties are 'allow_anonymous' and 'immediate_sync', and you simply append the arguments to the proc call like so: @property='allow_anonymous', @value='false' / @property='immediate_sync', @value='false'.

Later, after we’re all done with our under-the-hood changes, we’ll want to turn the back on, in reverse order: first enable 'immediate_sync', then 'allow_anonymous'. Cool? Don’t ask me why; DBAs much smarter than I have decreed it so.

OMG, remember Xena Warrior Princess? Holy wow that’s some nostalgia for ya.

Honorable Mention: Pull Subscriptions

In one instance, I was using a PULL subscription (as opposed to PUSH). I had to re-start the Distribution agent (on the subscriber) twice for it to work (to start actually synchronizing). It STILL shows as ‘Uninitialized Subscription’ in the repl-monitor, though. Kinda annoying.

Pull subscriptions can be nice because they shift the burden to the subscriber DB, so that your publisher (master, primary, whatever you wanna call it) doesn’t get too bogged-down. But as always, there are trade-offs. Check out this handy little comparison guide on the topic from a fellow DBA blogger.

That’s all for now; stay tuned for more as I go into detail about how I used these in what scenarios. Thanks for reading! ❤

Facepalms Per Hour

My current velocity is sometimes measured in FPH – facepalms per hour.

This is a rant. Fair warning.

I guess the new ‘Millenial’ colloquialism for “grumpy” or “sarcastic” is “salty“. So I’m feeling extra salty this week. For several reasons. One, it’s audit season. Two, I had to churn out about a dozen new reports in the span of 4 days because the manager who was supposed to be tracking that project dropped the ball and forgot they were due by the end of this month until… yeah, last Friday. Wheeeeee!

Thus, I decided, my current ‘velocity’ (a SCRUM/DevOps term for “how much work are you getting done”) shall be measured in FPH – Facepalms Per Hour. Currently I’m at 3. Earlier this week I was approaching the double-digits, when the lovely report consumers kept thinking of “just one more little thing” they forgot about until after I’d delivered the ‘final’ product.

‘Final’ actually being a meaningful adjective in this context approximately NEVER.

facepalm original picard
The original gangsta.

Change Logs

How best to describe this scenario while still maintaining separation of “real job” from “blog land”… Hrm. So let’s say we have a CRM, like most companies. This stores customers, among other things, in a database. And since it also stores sales transactions and financials, it’s heavily audited — it has a lot of change-tracking mechanisms.

Now, auditors come along and want a report of some specific type of change over time. I happily oblige. Then… PANIC! And not at the disco. “What are all these changes to these customers by these users who don’t have permission to make said changes?!?”

K, calm down sparky. Try not to sound the alarm; auditors are a sensitive bunch.

Turns out, those changes are, in a word, “fake”. You see, there’s this background “customer sync” process that keeps them up to date with another part of the CRM where the actual changes were made. But, because it’s written poorly, it thinks that ANY field change, even just the Name or Address (which a lot more CSR’s, customer service reps, have the permission to change, because, you know, that’s their job), constitutes a change to the ENTIRE customer record on the other end. So the change tracker logs a change to every single field on the receiving end of that sync process, even though nothing really changed on the source side except maybe one or two fields.

With me so far? Great. So now the question is, “Well, can we get a report that doesn’t show those ‘fake’ changes?” But wait, it has to be “system generated” and you’re not allowed to “filter” or “add special exceptions” to it, because it still needs to be audit-able.

So what you’re saying is, give me a report that shows me what I care about, but you’re not allowed to change the logic behind said report.

Riiiiiiight.

So I give them a new report. I don’t explain how the sausage is made, I just make it and serve it up. “But why is this different from the original report?”

double facepalm

Well, do you want the audit-able answer, or the real answer? The audit-able answer is, “We made a system change that allowed us to prevent the ‘fake’ changes from being logged incorrectly.”

The real answer is, “B*tch, I AM the system!” — meaning yes, I excluded those with some hacky logic, and you need to stop asking questions about it.

Anyway. Change Logs are super fun.

Reports

Speaking of reporting. I could really go on for pages about how terrible and broken this whole system of “request-based report development” is. But it’s frankly all we have right now. Until there’s sufficient business buy-in to the concept of agile data warehousing and collaborative cross-functional data modeling, shit just comes in one funnel and goes out another with a little sparkle spackled to it. And we call it a report.

Example, you say? Sure! Let’s say we run a special sale on certain types of widgets every quarter. We want to track how these ‘specials’ perform — do they increase our sales of those widgets? By what factor, compared to the other not-on-sale widgets? Can we trend this over several quarters?

Oh but wait. The data structures that govern widget pricing and time-span-based sale pricing, and the logic that relates customer orders to what pricing structure they used at the time of ordering, is awful, terrible, and changes every time there’s a new quarterly promotional sale.

So you’re saying you want a report that trends sales of widgets based on arbitrarily changing promotional pricing as compared to other widgets that may or may not be subject to ‘normal’ pricing during that same time period, all without a simple definitive data-point that says “This is a Quarterly Promo sale, and That is Not.”

elrond's facepalm
Mister Anderson… I mean, wait.

Let’s try to get at the root of the problem, shall we? The business doesn’t seem to understand that the way they implement promo-sales is detrimental to long-term/comparative reporting. The data model makes this harder, not easier. Can we perhaps put some heads together and come up with a compromise that both A) makes more business sense, and B) improves the data model to be a bit more intuitive?

In Closing…

What’s your FPH? What causes you to facepalm on a regular basis? Let me know in the comments!  :o)