Tuwednesday. Blursday. It’s all the same day now, isn’t it? Really this post is just about showing off some incredibly spot-on analogies by fellow #sqlfamily bloggers.
And of course you start with the creme-de-la-creme, Mr. Ozar. We’re having our dessert first, you see. Life’s short, live a little, etc. The one-liner: the database is a walk-in fridge in the back of a restaurant; the DBA is the refrigerator technician; the developer is a chef that works in the restaurant to which that walk-in fridge belongs.
Speaking of food, Deb Melkin describes how execution-plans (query plans) are like a routine for going out and running your weekly errands. And how small changes can sometimes lead to incredibly large differences in the execution of said plans.
Jeff Ianucci has another restaurant-related metaphor. Boy, us DBAs sure are a hungry bunch, eh? And thirsty! Shane O’Neill regales an imaginary youngster in his native dialect about how databases are like whiskey cabinets. It’s true, sometimes the job does drive you to drink. Especially when a developer leaves the door to the walk-in-fridge open.
So what do I tell people when they ask me “What.. what would you say, you DO here?” Well. Let’s say the company is like an animal, right? Any kind of mammal, let’s say. Leopard, Zebra, Elephant, Platypus. Whatever you fancy. The organism’s body functions primarily because of the blood in its circulatory system. Blood delivers oxygen, transmits nutrients, and moves throughout the entire body. So, too, does DATA. It delivers valuable information, transmits decision-making-fuel, and moves throughout the organization. Without data, the company cannot function. It cannot know how many customers are coming in or leaving, how successful a marketing campaign has been, whether a product is selling, or when the next anticipated failure of a WidgetMaker9000 machine might be so they can bring in a service-tech to inspect it.
All of these “measures” are things that the company heads care about. And none of them are knowable without the data. Sure, you can wave your hand around and produce some gut-feel “SWAGs” (sophisticated wild-ass guesses), and if you’re lucky, you’ll only be wrong half the time… maybe 1/3rd if you’ve got some experience behind you. But data has the power to actually tell you those answers. All you have to do is listen.
It’s here that the analogy breaks down a bit, because we assume that, while you as an organism have very little power over where and how your bloodstream is organized & flows, you as a data professional (and as a technology-invested company, on the whole) have PLENTY of that power over your data. You need to invest in the right infrastructure, the right people, the right technology, and the right models, for your business. And much like the searing pain of heartbreak, if you discover that so much of that data-circulatory-system is wrong that it’s killing your organism (organization), “rip it out, and start again.”
Oi. Now I sound like I’m comparing DBAs to gods. Don’t worry, I’ve had enough humble-pie lately to dash that illusion to smithereens, thank you. Now go enjoy some good food and whiskey. ❤
This month hosted by Mr. Major (scribnasium / @RileyMajor), whose past #tsql2sday post I may have missed but I love it all the same (BeyondCompare FTW!). It’s about giving back to your community — mostly technical, i.e. the #SQLFamily / MS Data Platform community, but it can be about any community you’re a part of.
For starters, that’s what we blog for, at least partly. I mostly blog about things I’ve learned or found interesting in my work, so that I don’t forget them later! Of course, I’m always happy to learn that it helps someone else out there with a similar problem. But there’s so much more to a community ecosystem than that.
SQL Saturdays are the perfect example of this. I’ve been to 3 so far – 2 at Orange County, and 1 at San Diego. I have to call out OC’s organizers, Ted Stathakis & .. the other guy. Crap, I forgot his name. He’s awesome though. Srsly. I talked with Ted a bit after a session at the last one and he truly is as nice as he is busy – 3 boys, all kinds of volunteer work, AND a full time job as the BI Director at Del Taco! Wow!
I mean, it’s no Taco Bell, but still, Del Taco! (Sorry, I had to..) =P
I really want to volunteer to help at one of these. I’m not sure what I’d do, but I know when the time comes, it’ll be worth it.
Lunch & Learns
To get my feet wet, I hosted a couple “lunch & learns” at my company with my Business Analyst and Development teams. We talked about some basics, ranted about formatting a bit, tried to say goodbye to old habits from the SQL 2005 days (hello, date type!), and even dived into a few juicy things like performance-testing with IO stats and why you should never use scalar or multi-statement functions. We also had a couple heart-to-hearts about DevOps and what it means in our environment. (Hint: we’ve got a LOOONG way to go.)
At some point I’m going to scrub my slides and post them on SlideShare or something. I just have to remove any company-specific info first. ;o)
As with most teaching efforts, it helped me learn (and re-learn) some things myself! I had to read some documentation to get wording exactly right, and I built a few playground DBs on my local machine to try out different concepts & examples. Plus, it forced me to justify WHY I held certain opinions or notions on things, and in doing so, realize my own mistakes of the past. Thus, I became a better DBA just by reinforcing some good practices and updating my own assumptions.
If there’s one thing I’ve learned from the manytributes to our late #SQLFamily member SQLSoldier, it’s the importance of being generous with your time. Whether that means answering to the #sqlhelp tag on Twitter, participating in the SQL Community Slack, answering StackOverflow / DBA.StackExchange questions, or just taking a few moments to help someone at your workplace with a tech problem — it all makes a difference. I need to be better about this, as far as my “online presence” goes. In-person, I’m always eager to help, but because I work remotely most of the time (yay!), it’s a bit of a moving-target — the in-office days get packed with meetings and critical face-time (not FaceTime), so the peer-to-peer stuff is often postponed or relegated to Slack.
However, there’s a flip-side to this. In being generous, we can’t forget to prioritize ourselves and our families. I’m always being asked “Why are you working so much!?” — well, I could try to explain the difference between ‘work work’ and ‘tech community involvement’ and ‘self-betterment / career planning’ but… yeah.
I encourage you, dear reader, to give back to your own communities, in whatever form that takes — tech/online, real-life, etc. I promise that it will benefit you in some new & unexpected way. Plus, whenever you make a real solid connection with someone, that’s worth its weight in gold.
This is partly inspired by the recent #StackOverflowPodcast episode in which Jon Skeet and the some of the Stack Overflow women talk about the Feminist movement and what it’s means to them.
So I’m not going to even “go there” in terms of the movement in general, where I stand, or anything really deep, because it’s a huge iceberg of a topic and I can’t do anywhere near justice to it. Even the discussion above was pretty basic & high-level, but they give lots of links in the show-notes to truly deep-dive into, if you feel like it.
One burning question, essentially, boils down to this:
Why is there still a striking disproportion of females in the Data Professionals career space (and, more broadly, the IT/Dev space in general)?
And like so many questions I ask here, I won’t actually have an answer for you (that would be too easy, wouldn’t it?). Instead, I’ll simply reflect on my own (very limited) experience in working with women in tech, and hopefully get your thoughts in the comments as well!
In a very small software shop, there were actually no women for a time. But shortly before I was hired, they brought on a husband & wife team – two developers who were excellent at what they did, and had, surprisingly, broken the stereotype of “spouses can never work together”. She was so easy to work with, and I think my experience here helped me at least start to understand some of the nuances around “women in tech” and what it all meant. Basically, she wanted to be treated just like “one of the guys” — which, I understand now, is, in itself, an anti-feminist phrase, but back then I wouldn’t have known — and it reflects the culture of the time, which is that this was a team of mostly male developers and we were still “finding our way” on the long trail of equality in the workplace.
So what this meant, in practical terms, was a couple things:
No bias for job-assignments of things like documentation, task management, or communication-centric tasks. While yes, she was actually quite good at these, and would later become the de-facto PM and Scrum-master for us, it was understood (and probably stated) that this was not “because she was female”, this was because she was the best at it. But again, is that specifically because she’s a woman? I don’t think so.
Addressing the group as “you guys” was perfectly acceptable.
Pay was equal – at least between the equivalent roles & seniority levels (e.g. her & her spouse). You don’t typically share or discuss salaries among peers, but we knew, and our bookkeeper ensured, that this was true. Because if it wasn’t, someone would’ve had some words about it.
Also, there were a few positive aspects of the culture that helped make it more equality-apparent, which I think were just byproducts of the quality of people hired. We didn’t do “dirty jokes” or have sexist (even unintentionally) discussions, nor did we engage in gossip or any kind of “just the guys” activities. We really just did the work and kept it professional, and any time we were outside the office together, it was almost always shop-talk. I think that’s the nature of a startup — you really don’t have time for anything else, any of the “fluff” or crud that spawns from idle hands & minds.
But it wasn’t all roses & sunshine.
A New Female Developer Candidate
After that dev moved on, we knew we had to replace her. And the company workload was pivoting a bit, so our candidate criteria weren’t the same as those of her position. But putting it that way makes it sound like we were either specifically looking for someone different, or that we had moved somebody else into her position and now had a completely different role to fill. Neither is the case, really; with a startup that’s organically growing and shifting, you don’t get the luxury of well-defined roles. You basically need what the business and the team needs at the time, and that becomes your reality, until it’s not, and your team pivots again to fill the new mold, learning & growing along the way.
So anyway, we were hiring for a somewhat nebulous developer position. And one of the candidates we saw was female. We did not end up hiring her — unfortunately, in my opinion. That’s not to say the candidate we did hire was bad; he was great too, that’s just not relevant here. After her interview, the discussions we had were interesting. And I think it would have been greatly beneficial if the previous dev (the woman I talked about above & who had left recently) could have been present to offer her insight into the hiring process; but she, understandably, was not available & already busy with her new stuff.
This new candidate had a good deal of “embedded systems programming” background, which was interesting because it was not at all what our software was about, but in hindsight, probably could have proved valuable in making our SDLC processes leaner & more efficient. She also had great general tech skills and was a quick learner. But ultimately the reasons not to hire came down to the dissimilarity of background vs our product, AND her personality as we perceived it — in a word, she was “nervous” and “not confident”.
This is a big failure, in terms of equality/feminism.
And as I said, this is all purely hindsight. None of us realized at the time what we actually meant. But that’s no excuse, just history. So let’s unpack that a bit. Or, in other words…
Of course she was nervous! She was A) in an interview, and B) surrounded by men. It’s not like we said anything or acted in a way that was actually misogynistic; we’d like to think we’d learned how to be open & equality-centric enough that anybody would feel welcome and able to talk about their experience and why they’re a good fit for the job & the company. We didn’t even have much of a “culture” to speak of — it’s not like we were a big enough team to even have cliques or established norms, we just kinda discussed our work, did the work, collaborated on the work, and went home at the end of the day to our families/friends. However, in the same breath, we DID have a “culture”, in the sense that we were a small tight-knit team (while in the office) with a set of personalities that, so far, worked very well together; and on a small team, personality-compatibility is important.
Anyway, here’s the crux. We didn’t even recognize that what we were saying was, underneath, an anti-equality statement:
She should have been more self-confident than the average male candidate, in an interview, in order to meet our expectations.
Now obviously, if you ask the hiring manager (aka owner/CEO/president/founder) of the company and the HR person, they’ll agree (rightfully so) that she was not hired due to the gap in technical experience & the fact that her skills did not fit with what we needed. And this is true, as I said before; we were doing web-based software in ASP.NET, with a SQL back-end, and none of those were at the top of her skill-set. So I’m not self-flagellating for us passing on her as a candidate. (Again, the person we did hire worked out just fine.)
I’m acknowledging, and apologizing for, the fact that we elevated her (completely understandable) personality/disposition to an artificially high importance in our discussion about the candidate.
That, I think, is what an equality-minded leader would try to make sure we avoid next time. If she had had very similar experience and skills to the next candidate, we should have certainly hired her. And if I were to retroactively predict the past-future (breaking all kinds of grammatical rules in the process), had she been hired, she’d have “come out of her shell” and gotten along swimmingly with the team & the work.
But again, this is all ancient history by now; it’s not like we can go back and change our decisions. We just need to be conscious of them and learn from them.
this is about using PowerShell and the BITS framework to copy very large files across servers…
Welcome back! This month’s topic is PowerShell — thanks to one of our prominently bearded community members. PowerShell is a fantastic tool in the IT professional’s toolbelt. I was first introduced to it somewhere in 2014 or 2015 by a colleague, and started making much heavier use of it when my career took me to a new & bigger environment.
Actually, funny side-story. I remember seeing one of the very early incarnations of PowerShell, or what would eventually evolve into it, in college. A graphics programming course, of all things, had a MS partner come in to show us this “cool new” Windows command-shell thing (different and separate from the DOS-style CMD, obviously), where he demonstrated fetching some data from the filesystem, feeding it into a CSV, and doing some kind of super-basic analysis on it to show in a “report” (which was really just another text file). This was 2005-2006, so I couldn’t say what it was specifically, though I seem to remember something about the word “Longhorn”. Although, reading up on some of the Wiki-history, it seems more likely that it was a Monad beta.
Anyway, back on topic. Today’s post is pretty simplistic in comparison to what most people may be writing about. But I’ve already blogged about doing hands-off SQL installation with PowerShell & CLI, and this was another thing kicking-around the back of my mind. So this is about using PowerShell and the BITS framework (*-BitsTransfer cmdlets) to copy very large files across servers. Specifically, database backups. Because let’s face it, they can be really large. And if you’re faced with fetching them off a PROD box, you want to minimize the impact on that box’s resources.
Now sure, there are other ways – xcopy or robocopy with the /J flag (un-buffered IO), or fancy GUI tools. And in an ideal world your backups would be written to a network share that’s not a local drive on the PROD SQL server, right? Right…
Oh, and one more thing. You need to enable BITS via the Windows Features console — search “features” in your Start menu and it should come up as Turn Windows features on or off (Control Panel) . On a server, it’s under the Server Role “Web Server (IIS)”, feature “Background Intelligent Transfer Service (BITS)”. Underneath there are 2 sub-feature choices, “IIS Server Extension” and “Compact Server”. Honestly I don’t know which is preferable, but I left it with the default selection, the first (former). It should go without saying, but don’t do this in production (unless you have the blessing of your SysAdmins).
Why BITS? Well, as per the Docs, it has the following 3 key features (emphasis mine):
Asynchronously transfer files in the foreground or background.
Preserve the responsiveness of other network applications.
Automatically resume file transfers after network disconnects and computer restarts.
Wow, nifty! So it doesn’t hog the network, and it’s resumable (resume-able?) in case of connectivity hiccups. Pretty sweet, no? Also, it can run asynchronously in the background, which means it won’t hog your storage bandwidth or compute resources.
Let’s See an Example
Most of the guts and inspiration for this came from this article over on “Windows OS Hub” (woshub, a somewhat unfortunate sounding acronym, but certainly not as bad as some!). The datePattern nonsense is just to make it “dynamic” in the sense that, if you have a backup scheme like me, with Sunday FULLs, daily DIFFs, and obviously TLogs in some every-X-minutes fashion, you’ll usually want the latest set of FULLs and DIFFs. But you could easily tweak this, make it more point-in-time aware or whatever, as needed.
So, here’s a bit of a talk-thru outline, and then I’ll just link the Gist.
Get the list of files we want to copy, from “source”
For each file:
Make sure it doesn’t exist in the “destination”
If not, start a BITS transfer job (saving said job to a variable for checking/finishing later)
While said BITS job is pending, print a progress message and sleep for some seconds
Finish (“complete”) said job and move on to the next file
Conclude with a message about how much work we just did!
Repeat steps 1-3 for another “set of files” (list) if desired
There are some downsides here. First, you cannot use BITS in a non-interactive mode, i.e. inside a Scheduled Task as a User that’s not logged-in. This is because it’s a “desktop”-oriented feature, not a “server” one. Second, I’ve never been able to get multiple transfers going at once — or at least, multiple PoSh scripts which use BITS transfers. This could very well be my fault, but it does seem like the BITS jobs are “serial” in nature, i.e. one must finish before the next one can start. Again, not the expert, just observing what I found during my experiments.
BITS transfer is an interesting method for copying extra-large files around your environment with low overhead. PowerShell makes it easily accessible and lets you wrap it up in loops and checks so you can effectively build a progress-indicative, predictable and reproducible method for copying a whole SQL server’s set of backups from one place to another.
What cool little things have you discovered using PowerShell? Let me know! Thanks for reading.