Blog

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.

Dealing with Time Zones in SQL

In my sample script, I have a “transaction table” where I insert a few events for each office, with the ‘original date’ in PST, and I show how to easily convert that to each office’s local time, or visa-versa.

I’ve ranted about times, datetimes, and the like before. As most programmers & IT pros do, I loathe time zones with a mild passion. As one of my favorite #SQLCommunity members quips:

daylight savings time was actually created by a government works project to ensure that programmers could forever write tedious conditional logic in their date-based queries [and code].

Bert Wagner

Thankfully, in SQL 2016, MS has somewhat heard our outcries and given us an easier way to convert things between time-zones in a somewhat sensible manner.

Resources:

  • The at time zone keyphrase
  • The system metadata table that drives it
  • A good blog post that explains essentially what I’m about to (because it’s great to have different perspectives and unique voices!)
  • Two StackOverflow answers (one, two) on the topic
  • Bert’s succinct and fun post about the same topic, with video!
give me utc or give me death
This is Patrick Henry. He famously said “Give me liberty or give me death!”. Now you know.

A Use-Case

In fact, I’m not even going to repeat what 2 (and many more, I’m sure) people have already said. Go check out their posts above! But since you’re here, this is how I use it for my reporting environment.

To start with, the transactional data is in PST/PDT — i.e. Pacific Time with DST fluctuation. Yes, it’s horrible. No, I don’t know what happens to events or jobs at 2am on the “Fall Back” date, or between 2am and 3am on the “Spring Forward” date. No, I can’t change it right now. Stop whining.

Now, I have offices in Paris France, Hong Kong, and Beijing China. These are 3 different “time zones”, but only 2 different offsets — China and Hong Kong are in the same bucket, namely, UTC +08:00. More on that later.

So I have my OfficeLocation lookup table:

Office  | TimeZone
--------|-------------
Paris | 'Central Europe Standard Time'
Beijing | 'China Standard Time'
HK | 'China Standard Time'

(Again, see below for why we can’t call HK’s zone “Hong Kong Time” like most websites/APIs would assume.)

Now, the cool thing about this is, we can pull those strings into a variable, or use them straight from the table, to convert our PST/PDT times to the appropriate zone.

Here’s a variable example:

DECLARE @TimeZoneStr sysname; --"sysname" is just nvarchar(128)
SELECT @TimeZoneStr = TimeZone
FROM OfficeLocation
WHERE Office = 'Paris'

DECLARE @MyTimeNow datetime = GETDATE();
DECLARE @TimeInParis datetime;
SELECT @TimeInParis = @MyTimeNow
AT TIME ZONE 'Pacific Standard Time' --Converts to datetimeoffset
AT TIME ZONE @TimeZoneStr --Shifts it to Paris time

PRINT ('The time now in Paris is ' + CONVERT(varchar(30), @TimeInParis, 121);

And here’s an example using the field straight from the table.

SELECT Office
    , [Time in my location] = GETDATE()
    , [Time in remote office location] = CONVERT(datetime, GETDATE()
        AT TIME ZONE 'Pacific Standard Time' 
        AT TIME ZONE ol.TimeZone)
FROM OfficeLocation ol

See the Gist for a full-fledged sample script. In it, I have a “transaction table” where I insert a few events for each office, with the ‘original date’ in PST, and I show how to easily convert that to each office’s local time, or visa-versa.

working with time is easy -jon skeet
Well YEAH, if you’re frickin’ JON SKEET! =P
But srsly, go watch his presentation and read his stuff.

Gotchas

Here’s the major catch. The information available to YOUR instance of SQL Server is pulled from that server’s Windows Registry hive. No, I’m not making this up. So if that box doesn’t know about, say, ‘Hong Kong Standard Time’, and you try to use that in your SQL statement.. you’re hosed.

And yes, that is a real example from my own experience.

This article shows the “Windows standard format” time zone list. As you can see, they merged some zones with others because.. they felt like it? But apparently Central Europe Standard Time, Central European Standard Time, and Romance Standard Time (all UTC +01:00) were completely necessary to keep separate. Go figure.

In my use-case above, then, I couldn’t actually store the string Hong Kong Time, because my SQL instances (hence my Windows Servers) don’t know what that is. Thankfully, at least for this decade, it doesn’t look like Hong Kong and China will diverge in terms of their geopolitical directions, and we’re safe to assume that HKT = CST (China Standard Time, not to be confused with US Central Time!).

In another example, the typical go-to site for timezone questions says Japan observes “Japan Standard Time”. Obviously enough. But Microsoft, in their infinite wisdom, decided to call that “Tokyo Standard Time”. Go figure again.

It also kinda makes you wonder.. how does this work on SQL on Linux? No such thing as “the registry” there. I’m sure there’s an internal OS data-store that houses time-zone info, of course. Heck, they might even be better at it than Windows. But it makes you think.

world time zone map
Disclaimer: this is probably already out-of-date. And it’s from Wikipedia.

So What?

If you’re not already running SQL 2016 or upward, this should give you yet another compelling reason to upgrade. Seriously.

And don’t do what I did and attempt to store a “business locations with time-zone offsets” table, that you have to remind yourself every 6 months to go update (manually), and will inevitably fail to do so, and will not support any sensible manner of long-term historical reporting.

More to the point, don’t try to implement dynamic time-zone logic and calendaring yourself, in general. Because trust me, you’re not gonna get it right. Use the built-in tools, use the community resources, and be smart.

That’s all for today! ❤

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

Intermission: Update Fatigue

Just try to be conscious of how inconvenient it is to be constantly asked for updates all the time.

Wow it’s been a while! My apologies dear reader. July and August came and went far too quickly. While I try to cobble together part 2 of my replication post, allow me a short interim rant.

Software updates are a fact of life

Sure, I get it. Everybody wants to keep their apps up-to-date and patched against all these vulnerabilities and exploits that the forces of evil come up with every day. Fine. Or the eager developers want to release new features that marketing (ugh, marketing) promised to stakeholders. Whatever.

oprah yelling "you get an update and you get an update"
EVERYBODY GETS AN UPDAAAAAAAAAATE!

Can we all admit that we’re getting just a little sick and tired of it? I mean seriously. Seems like every damn day something yells at you from your phone or your tablet or your laptop or your watch or your smart-TV or your talking refrigerator (well, hopefully not, but I’m sure it happens) wanting a new update.

And I work in the freakin industry, for god’s sake! I KNOW these updates are generally for the best and generally a good idea to install sooner than later. But it still makes me grumpy.

Yes, we’re all Agile and DevOps-y and Unicorn-y

And all those other silly buzzwords. That’s great. Really, I’m not suggesting we go backward. There’s no arguing that, as a general function of the evolution of the software development lifecycle and the push for better build-test-release-deploy-operate-feedback-repeat pipelines, overall software quality and user-experience has improved.

yeah science bitch
Because reasons.

Yet, sometimes, it’s super inconvenient. How many of us have bemoaned an unintentional Windows update that sucks up hours of our productivity time just because we didn’t know enough or pay enough attention to catch the “do this later” option? If it was even given!

Another example. iTunes had been begging me for weeks to update my phone’s OS, whenever I plugged it into the laptop just for charging (sure, I could not use a USB port and switch to a pure power source, but again, convenience!). So I finally let it, thinking “Oh this’ll only take a few minutes”. 15 minutes later, late to catch my vanpool ride from work… You get the picture. And why? Because Apple just HAD to give me all these new features.. that.. wait for it.. ONLY apply to iPhone X’s and above! (I have an 8+). Hmm. Something seems maybe not quite ideally efficient here.

Yeah yeah, platform consistency blah blah unified codebase blah blah. Spare me. They have the resources to make this a smarter, more bespoke process. But that’s not the point.

Even now, at this moment, Red Gate’s SQL Prompt (and I love this tool, don’t get me wrong) is asking me to update it from 9.5.14 to 9.5.15. Does it give me any features or fixes that I really care about? Doubtful. Does it bug me every time I start up SSMS? Yep. Can I dismiss it or say “remind me later” or “skip this version”? Of course! So at least they’ve given me that courtesy.

So what IS your point?

You ask me that a lot, don’t you?

All I ask is that developers, in general, be more conscious of how inconvenient it is to be asked to update their apps all the time. Architect things in such a way that back-end fixes and improvements are de-coupled from the UX/front-end. As much as possible. Obviously this isn’t always feasible, and sometimes you literally do need to fix the UX. Great! But with more careful, thoughtful design, this should be far less frequent.

yo dawg i heard you like windows updates
You can’t go wrong with the classics.

‘Should’, of course, being the operative word. We’re still human. We still design and create systems with human assumptions and human error. I get it. Believe me, my code is FAR from perfect. If I had to put out a fix to every stored-procedure I wrote as often as they were found, by a user-base of any more than just myself and my dozen developers, I’d go insane. (-er.) Fortunately, those don’t require people to download an update package and wait for it to install. 😉

Anyway. Hope you enjoyed this rant. Now go update your apps and tools because they’re important. And probably vulnerable to some new zero-day exploit that’s going to take over your system and steal your cookies and bitcoins. =P