I talk way too fast, I want audience participation, and I always make stupid typos.
Well folks, it’s been a minute. How’s your pandemic going? Hopefully safe and healthy.
This month’s blog party is brought to you by Lisa GB, and the topic is “What (else) have you learned by Presenting?” — either giving a talk at a big conference, a SQL Saturday or meetup, or (like me, because I’m still working my way up to it) just to your own little group of coworkers.
I don’t have that much presentation experience under my belt yet. After COVID started, I briefly tried my hand at Twitch streaming, only to walk away dejected and disillusioned because nobody watched. (Probably blame the content as much as the presenter, but whatever.) I did give a few “big room” talks at work last year, which went pretty well, and I got some great feedback, including helpful constructive criticism — talk slower, keep things on-topic and relevant to the work we’re doing, etc. All good stuff.
Lately, I’ve been doing brief “TSQL Tuesday” video-meetings with very small groups of colleagues (usually only 2-5 people show up any given week). We talk about whatever database-related points of interest have come up during our day-to-day grind. Sometimes it’s completely spur-of-the-moment. At least a few times, one of my stellar new BA’s (also QA/Tester), brought something to the table that even *I* wasn’t familiar with, like the cool IIF function in TSQL, or the EXCEPT UNION EXCEPT method for comparing homogeneous data-sets and the related concept of “bucket chemistry” with large buckets of data in preliminary analysis. (Sounds like a blog post that someone should write.)
Here are the things I’ve learned. Well, besides those mentioned above.
A. I really do talk way too fast when I’m presenting. Still. Trying to work on it. B. I enjoy audience interaction much more than monologue-ing. C. Never type in demos.
And finally, I’ll leave this as a teaser for a future post. While working on some demo material for “#TempTables – Use or Abuse?”, I got super confused about temp-table scope in context of stored-procs that abort or fail. So for a moment I understood why developers insisted on putting DROP TABLE #MyTempTable at the end of every one of their procs — it’s basically a safeguard, with no legitimate downside. Anyway, as I read into things and did a bit of research, I found this gem about how temp-tables are cached, and realized that I’d been committing a particular sin (select into #temp, followed by add columns for fetching more data) for a long time without realizing its risks.
Stay safe and healthy friends!
PS: there’s been a bunch of twitter on Twitter about PASS, people resigning left and right, and I’ve no idea what’s going on, but I’ll get the popcorn, cuz it’s sure to get entertaining. =D
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 is a sort of Trello/Project/virtual-whiteboard hybrid that comes bundled with OfficeMicrosoft 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.
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!
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.
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.
Here is my list of apps that I remove immediately with a fresh install.
Feedback Hub – because Microsoft does not actually care what you think (or I wouldn’t be writing this, would I?)
Mixed Reality Portal
Print 3D (notice a pattern yet?)
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.
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.
Weather – because that’s what your phone is for, silly. Do you really check the weather on your PC/laptop?
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).
Groove Music – it’s not a great music player, but it’ll do if you don’t know any better.
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.
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.
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.
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?
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).
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!
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 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.
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.)
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!
I guess there’s not much of a big important point or lesson here.
Is there ever?
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.
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.
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.
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.
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.
So what are you waiting for? Catch me on Twitch and watch me play, or godownload all the goodies and check it out yourself!
See you in the mines!
Favorite tagline of many hot-shot pilots and level-designers.
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.
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 */
--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).
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? =)
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 otherprojects, 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.
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.
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.
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??
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
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.
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 AlexYates, 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!
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.
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! ❤
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’tapologize. 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”?
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:
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.
Run that captured workload against anotherSQL server instance, and capture THE SAME performance metrics.
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!”
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!
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
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.
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! ❤