T-SQL Tuesday #102: Giving Back

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 Saturday

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.

the-more-you-know
Yay learning!

Generosity

If there’s one thing I’ve learned from the many tributes 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.

Sorry, honey, be done in a sec!  =)

Anyway..

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.

it's not what you know, it's who you know
ORLY? Yes, RLY!
Advertisements

Where are all the Women?

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.  It’s also partly because my wife is out of the house, and wanted me to do some painting while she was away.  And well… paint has to dry.

man-watching-paint-dry
It’s so exciting!

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!

Early Colleagues

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.

Its-Not-All-Roses-and-Sunshine-Right-Now-But-Im-Working-On-It

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…

duh
Ya think?!?

DUH!!

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.

much to learn you still have
“But I’m trying…” “Don’t make me say the line again.”

To Be Continued…

This is getting quite long (~1400 words), so I’ll break here and save the rest for another post.  In which I’ll actually address the initial question, as well as my current experience related to female colleagues & a larger workplace.  Stay tuned!

PowerShell and BITS

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.

that is so four score and seven years ago
Don’t ask me why hes wearing horn-rims. I don’t know.

Preamble

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).

But Why?

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.

async all the things!
Because we can.

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.

  1. Get the list of files we want to copy, from “source”
  2. For each file:
    1. Make sure it doesn’t exist in the “destination”
    2. If not, start a BITS transfer job (saving said job to a variable for checking/finishing later)
    3. While said BITS job is pending, print a progress message and sleep for some seconds
    4. Finish (“complete”) said job and move on to the next file
  3. Conclude with a message about how much work we just did!
  4. Repeat steps 1-3 for another “set of files” (list) if desired

And without further ado, the code.

The Catch

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.

parallel vs serial ports on old computer
Obviously, serial won out in the end (specifically, his superstar protege, USB), but you gotta hand it to parallel, he had a good run.

Conclusion

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.