Category Archives: PowerShell

Being Lazy, or is it Efficient?

Posted on by
Reply

My main client the other week decided to update an ETL I had created for them over a year ago and add another couple of imports to it. This basically meant creating some PowerShell code and SQL code and schema to import the data.

I had started to write stuff out by hand when I suddenly remembered a PowerShell Script I had previously written and later wrote about for Red-Gate’s Simpletalk.

I won’t rewrite the post here, but basically if I give it the name of a CSV file, it’ll attempt to create the table schema, an import stored procedure and some of the object information for PowerShell. I still have to make some edits usually but this saves me a lot of work.

And it’s a good thing because after creating and testing the two imports that they requested last week, on Friday they scrapped that approach and decided to combine the data into a single file which means I needed to ditch all that work and start from scratch.

I wrote all the necessary PowerShell, SQL, and did the testing in less than 30 minutes. It was fairly trivial and fairly efficient. Or perhaps I was just being lazy.

But the time it took me to write the original script to create the scripts has definitely saved me time and as a result saved my customer money. So in the long rung, it’s well worth it!

And that’s all for this week!

… Other Duties as Assigned #2 in a series

Posted on by
Reply

I wrote last year about how my job title seems to be DBA and other Duties as assigned. A little incident yesterday got me thinking about that again. This time though, it got me thinking more about the DBA side of that title.

I’ve mentioned before I’ve written a number of ETLs for various clients. Some are fairly complex and some are fairly simply PowerShell scripts. In most cases, at the end of the script I send an email with a success or failure status and some additional information if appropriate.

Over the last two weeks I had noticed that the email for one particular ETL was coming in later and later. I finally found somet time to investigate. I looked at the destination table and noticed that some of the tables had extremely large numbers of rows. Now, my first inclination was that the source data had increased (which would be a good thing, it meant the client was basically selling more widgets). But the increase seemed too dramatic and large. My next thought was perhaps the export itself was simply giving me more data.

So I decided to look more closely at the data and I noticed something interesting. Picking on of the tables at random, I simply did a 

select * from RandomTable order by GoodIndex

SSMS returned approximately 24,000 rows. But something stood out. There appeared to be duplicates rows. Lots of them. This raised a lot of suspicions.

I then ran the query on my UAT box which in this case actually loads a copy of the production data. I got back about 1,600 rows. VERY curious.

Back on production now I ran

select distinct * from RandomTable order by GoodIndex

This time I got back the same number as UAT. Extremely curious.

Then it dawned on me, about 2 weeks ago, we had made a change to where the script looked for the source data, a number of CSV files. UAT had not changed, but Prod had. The reason for this change was to be able to get the ETL PowerShell script to run on the proper production server (the original location had security issues reading from the original CSV file location.)

So my first thought was that the team that had updated their export had somehow left out the command to delete the old file and was simply appending. I was about to write a pointed email when I stopped myself.

I looked at my code and realized that when I had setup the script on the new production box, I had properly handled all the permissions except one: the step that truncated the destination table. Sure enough, my code was no longer truncating the table before I inserted new information. Fortunately I realized my mistake before sending that pointed email.

So was this a DBA issue or a “other duties as assigned” issue? I don’t know, but I’d say as a DBA I should have not created the problem in the first place, but thankfully, due to proper logging and emails I was able to catch it and solve it fairly quickly.

Moral of the story: Pay attention to details. Notice when things start to drift. They can be the sign of a larger issue.

And Fun Was Had By All

Posted on by
Reply

I want to give shout-outs to Rob Farley, Peter Shore, Deborah Melkin, and Rob Sewell for making the final Capital Area SQL Server User Group meeting of the year a rousing success. And this being T-SQL Tuesday, I’m going to try to very loosely tie this into the topic this month How much do you love meeting in person, where would you like for your next event to take place, and why Costa Rica? as invited by Xavier Morera.

tsqltuesday
T-SQL Tuesday

First, let me get his questions out of the way:

  1. Favorite Conference: The easy answer has been SQL Pass, but honestly, at this point, any where I get to see folks in person!
  2. Best Venue: Ignoring Pass at Seattle, I have to say Manchester UK was nice, simply because it was my first overseas SQL Saturday, or perhaps Virginia Beach SQL Saturday, because Monica Rathbun and her group provided a nice charcuterie board!
  3. Best Presenter: Oh, this is a tough one. I’m going to take a pass. But then cheat and answer below. Sort of.
  4. Next event and why it’ll be Costa Rica: I’m suspecting sort of a bias in this question, but to be honest, I’d love to go. I think 2022 will be a bit too busy for me to visit, but perhaps 2023 or 2024. Maybe I can work in some caving then too!

That all said, I want to get back to my shout-outs above and tie that into this T-SQL Tuesday.

As the coordinator for the Capital Area SQL Server User Group, one of my primary roles, in fact perhaps the most important, is finding speakers to present. I’ve tried over the past few years to have a good variety and to bring some variety. We haven’t really missed a meeting since the pandemic has started, but we have been virtual for well over a year now. This has presented both drawbacks and opportunities. The biggest drawback of course is the lack of actual in-person interaction and the feeling of connectedness that has brought. On a personal note it also means not only have I not gotten out of cooking dinner the night of meetings, but often, I’m juggling getting something together for dinner and getting the session started (though last night my wonderful wife did take care of dinner for me.)

On the flip side, being virtual has allowed me to invite speakers who might not otherwise be willing or able to travel in person to Albany NY and for attendees from across the country to show up. It has also given me the opportunity to experiment a bit more with formats.

Last year, instead of our traditional in-person holiday party format, we did a version of “Bluff the Listener” where I asked various presenters to tell their worst IT/SQL horror stories, but one was lying. It was a success and a lot of fun.

Not wanting to repeat that, this year I decided to ask the above 4 presenters to present lightning rounds. That’s not so bad, except I added a twist. They didn’t get to choose their topics, they were given them: 10 minutes before they were scheduled to present. (And yes, some may I stole this idea from Buck Woody, I’d like to say I was inspired).

I’ll admit I was very nervous about this idea. It seemed a bit gimmicky and it could have been a complete disaster with lesser speakers. Fortunately, all four brought their A-Game.

Rob Farley, presenting from the future, in I believe a public work space, managed to give one of the best talks on column-store indices I’ve seen. Given he had only 10 minutes of prep, I was impressed. His presentation included the use of Powerpoint in sort of a “green screen” mode so he could draw on his screen and we could see what he was drawing.

Peter Shore followed up talking about Tips in Advancing a Career in Data. Again, off-the-cuff with limited prep time, he did very well with this topic. I think in some ways this was almost harder than the more technical topics because you can’t fall back on a demo or graphics.

Deborah Melkin followed, talking about the Best new SQL Server features (2019, 2022, Azure). I had announced previously that the best speaker would be awarded a prize. By I think unanimous declaration, even before Rob Sewell finished out the night with his presentation, the other speakers decided Deborah was the winner. She included some demos in her presentation, which, given the lead time, really impressed folks.

Closing out the evening, Rob Sewell entertained us with a demo of SQL Injection. Not surprisingly, he made use of PowerShell and Notebooks.

As I said, it was an entertaining and educational evening. I purposely set expectations low and made sure folks understood that the entertainment value was as much, if not more important than actual educational value. But I was very pleased with how educational it turned out to be. It was a nice way to end the year and honestly, I think a decent way to get a break from the bad news that seems to have surrounded us lately.

I do have a theory though about why the educational part turned out as well as it did though. In general I’ve always enjoyed lightning talks and I honestly, think they’re among the hardest type of talk to give. Sometimes people promote them as a good introduction to speaking for novice speakers, but I’m not so sure. To give a successful lightning talk, one really has to strip a presentation to the bare essentials and really focus on just one or two key concepts. This can be difficult. But done well I think it really makes those concepts stick.

Now, combine that with topics only being given out 10 minutes in advance, I think that really forces a presenter to focus on key concepts even more. I wouldn’t give an inexperienced presenter a random topic, and even with an experienced presenter, I’d give them a chance to decline a topic if they feel it’s completely outside their wheelhouse. But otherwise, give them a chance to see what they can do. It might surprise you. Heck, it might surprise them.

So, to go back and answer a question from above: Best Presenter… at least last night Deborah Melkin, who if nothing else proved her Google-foo was impressive.

And I think if I can find volunteers, I will definitely try to do an in-person version of this at a future SQL Saturday or Data Saturday or other conference.

Thanks to all who participated and joined us. It was a blast. But honestly, next year, I hope to see you all in person at our holiday party!

Feeling Good but…

Posted on by
Reply

I think it would be fair to say that like everyone, I’m a bit sick of Covid (thankfully not sick from it.) I just got my booster on Friday and then I’m hearing about the Omicron variant.

I submitted talks for SQLBits in the UK for next year, hoping to present in person. And I’m hearing about numbers rising.

I’m planning a mini-vacation/cave rescue training trip to Hawai’i next year and making sure everything is refundable. Just in case.

So I’m feeling god but…

At the start of each year, I set some financial goals for myself. Some include what things I may pay off, save, or how I’ll spend it (now admittedly most of those are fixed, such as knowing I’ll tax property taxes, etc.) As a contractor I also set a couple of various goals for new work and how much I’ll hopefully earn in the coming year. I find these are important as they help keep me focused and moving forward.

The good news is, financially I’ve hit all my goals, and then some, this year. The downside, with that, and with Covid continually popping up its ugly head, I’ve lost some of my motivation for the rest of the year.

Fortunately, this has freed up some time for some projects around the house. Almost two years ago, with help from the kids, I started on a project to replace some leaking pipes and replace the resulting damaged drywall in the basement. I’m proud to say I’ve finally gotten around to taping and painting the drywall in the basement and patching around where I put in the new bathroom fan. Things get done, albeit slowly.

I’m also feeling good because a major project for one of my clients is mostly completed. But it also came very close to burning me out and I’ll admit I even considered walking away from the client over it. The strange part is that it wasn’t a particularly complicated project, though it did involve a combination of SQL, PowerShell, and using a product called Pentaho. Technically it was fairly straightforward. But, for awhile, the project management was absent and the then lead was actually another agency who, I think it’s safe to say didn’t clearly understand the full scope of the project. With the addition of the client adding their own PM and working with a different agency taking over a bunch of the work, things have gone much more smoothly. Now we’re simply dealing with small niggling details that got missed before.

What kept me from walking away (besides it being my largest client) was a sense of responsibility to the client. Without my efforts, I think the project would have easily been set back a month as they would have had to bring someone else up to speed on my efforts.

Now the upside is that because of the overtime required (and it’s still ongoing) I met my financial goals for the year (and hence now have time for the house projects). So that’s a good thing.

But it did highlight how frustrating being a single-person consulting agency can be at times. It’s made me re-evaluate my goals for 2022. I’ll be writing more about this in a future blog, but it has got me thinking more about getting back to working for an company as a full-time employee, ideally in a management position. Strangely one thing I’ve come to realize is I actually enjoy making decisions and I enjoy managing. I sort of miss it.

And perhaps after nearly 2 years of Covid (and nearly a decade of pure consulting), it’s time I get out of the house more and travel a bit and interact face to face with people.

We’ll see.

But that’s it for today. I’m feeling good but…

P.S. One thing I did finally accomplish is submitting my latest article to Redgate’s Simple-Talk.

… Other Duties as Assigned

Posted on by
Reply

I’ve mentioned once before that at one of my clients I describe my job as “DBA and other duties as assigned.”

This phrase has really been on my mind this week, especially during a phone call with another client yesterday. This second client is a local consulting company that has hired me a few times to back them with my skills in SQL Server and MS Access. This time around the work they’re looking for is definitely SQL Server related. It was refreshing.

But it reminded me of my last two weeks with two of my other clients. One is having an issue with their app (that they always call “the database”) that is most likely a design issue that I need to dig into. This is a perfect example of what I call “software archeology” where I at times have to shift through “pot shards” to determine what the original developer was thinking. At times it can be fun and interesting, at other times, frustrating. I’ll be shifting through more pot shards in the near future to get to the bottom of this problem.

For my largest client, I spent most of my hours with them last week trying to true up a file with some financial data in it. In this case it’s part of an ETL process where I receive data, compile it and send it to a vendor. The process uses a combination of PowerShell and Pentaho. So while they interact with the database, the work I was doing wasn’t in T-SQL or directly on the database server.

The numbers weren’t adding up. There was an undercurrent of “Greg, your numbers are wrong” or “You’re filtering on the wrong criteria.” I kept pointing out that “I simply add up the numbers you give me.” Eventually the problem was narrowed down to the fact that in the source system, which is the system of record, they had deleted rows. Arguably, one should never be deleting rows in such a system, but rather issuing a 2nd row (a credit if you want to reverse a debit, or a debit to reverse a credit) and this was typically what was done. But in this case the maintainers of the source of record decided to wholesale delete these rows. I explained that from day one, since deletions are never supposed to happen (and given the way the system works, extremely hard to detect) all I do is either insert new rows, or update existing rows. In any event, with one minor schema change, some updates to the rows in question and an updated PowerShell script, I was able to make the numbers come out to match with theirs. So, is that really DBA work? Not in the traditional sense. But it’s definitely other duties as assigned.

Now that’s not to say I didn’t do what some might consider actual DBA work. On Saturday morning I patched one of their servers. And at one point during the week, I deployed a script to production. So, out of 18 hours of work for the customer last week, I think I can say maybe 1-2 total was “dba work” or about 5%.

Now, I want to be clear. This is not a rant or a complaint. I’ll admit I tend to prefer to work directly with SQL Server, but I was reminded of a quick discussion I had with a fellow DBA over the weekend about how they probably needed to start to learn PowerShell for their job.

I’ve been arguing for years that the role of a DBA has changed, and will continue to change dramatically over the next few years. Once where we might spend days head down slinging T-SQL code, setting up backups and restores, tuning indices, etc. now much of that is automated or at least far easier to do. Which is a good thing. In years past, a DBA might be responsible for a dozen machines or so at the most. If it was more than that, we’d feel sorry for them. That’s no longer uniformly true. I know a DBA who is responsible for over 100 machines. They’re the soul DBA. But, through PowerShell and other modern tools, it’s generally not an overwhelming job.

However, like the online presentation from the Atlanta Azure Data User Group I attended last night on SQL Database Edge, there is a growing list of things DBAs need to learn. Steve Jones recently posted about whether DBAs need to learn Linux? The short take away is not necessarily, but it’s probably a good idea, but we definitely need to learn about containers.

I have heard for years, “Microsoft will automate everything and the DBA’s job will go away.” Not only is that not true in my experience, the exact opposite is. I think being a successful DBA is in some ways harder than it was a decade ago. There’s so much more to be aware of and to learn.

Off the top of my head, without any real priority I came up with the list below of technologies that a modern DBA might find useful to know. This is not to say I know them all, or that one has to be an expert in all of them. And I will note, this is far from an inclusive list. I also left out third-party tools which are so common place. But I think it illustrates just how broad the required skillset of a good DBA is these days.

  • T-SQL
  • PowerShell
  • Query Store
  • Linux – at least at the most basic level
  • Containers
  • SSIS
  • SSAS
  • SSRS
  • Storage – (at least how different types can impact performance and the advantages and disadvantages of each)
  • Azure
  • SQL Database Edge
  • git or some form of version control

In conclusion, I’ll say, I’m not going to make any predictions about where the Microsoft data platform will be a decade from now, but I can tell you that DBAs will still be needed but their skillset will be as different from today as today is from a decade ago.

And post conclusion, I’ll add I’ll continue to rely on #sqlfamily and all my fellow DBAs to help me out. And continue to help them.

Let me Email that!

Posted on by
Reply

This week’s post is a short one, but hopefully a practical and useful one. I had a recent need to send an email to about 3 dozen folks. Prior to this particular email, I had been updating them with general information and was simply using the BCC function of my email client to send out the email. But in this particular case I needed to email them login information specific to them. I didn’t relish the idea of crafting 3 dozen separate emails and making sure I got the correct information into each. So, I turned to the tool closest at hand: PowerShell and in about 3 minutes of writing, and 10 more minutes of debugging and tweaking, had something that worked great.

param([parameter(mandatory)] $course_id, [parameter(mandatory)] $course_description)

$course_info = invoke-sqlcmd -server "sql_server" -query "select ct.Course_Type_Name, c.Location, c.City,c.State from Courses c
inner join course_types ct on ct.Course_Type_ID=c.Course_Type_ID
where course_id=$course_id" 


$class_list=invoke-sqlcmd -server 'sql_server' -query "select p.persno, first_name,LAST_NAME, email1 from courses_taken ct
inner join people p on p.PERSNO=ct.PERSNO
and ct.Course_ID=$course_id"

foreach ($person in $class_list)
{
    $body = "Thank you for participating in the recent $($course_info.Course_type_name) $course_description course at $($course_info.location) in $($course_info.City), $($course_info.state). Your class information has been entered into the Generic Class database. 
If you wish to review your unofficial transcript, you may login in at https://www.example.com/Account/Login.aspx. 
If you have not created an account before you can create an account at https://www.example.com/Account/Register.aspx. 
Please be sure to use the following email:$($person.email1) (you can update it after creating your account) and your Generic Class ID: $($person.persno). Please note this is NOT your OTT ID. 
If you have issues or questions, please contact Joe Smith at mailto:jsmith@example.com. Thank you and we look forward to seeing you at future training!"

    Send-MailMessage -From "jsmith@example.com" -SmtpServer Mail_Server -Subject "Generic Class: Your Generic Class Login and unofficial transcript" -Body $body -to $person.email1
}

Every course in this particular database has an ID. And a course can have a specific course information. The first query gathers that information.

The second query then gets the personal information for each student who took that particular course and puts it into a class_list object.

I then simply iterate over the object and send an email with the personalized details to each student.

It literally took me more time to tweak the text and formatting of the body of the message than it did to write the rest of the script.

I suppose I could have figured out my email client’s mail merge feature, or done something in Word which I know also has such a feature but that would have taken far longer. I also could have spent more time making things more generic (perhaps passing in the body and having it automatically formatted, or sending as a nice HTML message with appropriate formatting. But sometimes, quick and dirty is the way to go. And now I have a script I can customize for future use.

Not bad for about 15 minutes worth of work.

Speaker’s Timeline Part II

Posted on by
2

This is a follow-up to my first part. But before I dig into it, I want to thank all the readers who check in on my post last week. I had the best week ever in blogging. And I’ll admit, while my ego was pleased with the numbers, I think what really warmed me heart was the number of my fellow #SQLFamily members who retweeted, shared, or gave positive comments about it. Thanks.

So, back to my speaking timeline. On November 11th I’m giving my presentation on PowerShell for DBA Beginners at 2:00 PM EST. I’d be thrilled if you joined me.

So, last time I wrote about this, I had ended with what my next steps would be.

October 20th around 10:00 PM EDT

Upload final version of slide deck. Yes, I could probably improve upon it (and looking back now, there’s 1-2 slides I’d probably like to fix, but oh well).

October 20th – 10:44 PM EDT

Confirmation email: slides were received. Excellent!

October 21st – Midday

One more run through. Basically nail it at right around 0:58. But now really worried, what happens if I finish early before the live Q&A? Will there be 2 minutes of dead air?

October 22nd-24th

Do nothing. I deserve a break. Right? Right?

October 25th – Late Afternoon

Record my presentation with Zoom. It’s acceptable, but I made a mistake or two. Worst case, if I run out of time, I can use this, but honestly, I want a redo. But, like a good dba, I basically have a contingency plan in place in case I don’t get time to do a redo.

October 26th – Morning

Decide to use OBS to record, in part so I can include a window of me talking. I think it’ll be a bit more personal and interactive than simply having slides and a demo with a faceless voice talking.

October 26th – Morning 30 minutes later

What was I thinking? Why go through all this trouble. This is more work than I want to deal with today.

October 26th – Morning 45 minutes later

Ok, this just might work! I’ve figured out how to get the overlay the way I want, but gave up on green-screening me against a background, but that’s ok because the thumbnail video is small enough my background is not distracting.

October 26th – Morning 1:00:08 later

This recording is nearly perfect. I think it ran over by about 8 seconds, but if they cut that, it won’t hurt anything. Honestly, I’d ilke one more try, but I can’t stand the thought of listening to my own voice one more time.

October 26th – Late afternoon

Wait until the kids are done with school Zooms and my wife has no more meetings to start the upload.

October 26th – an hour later

Hmm, seems stuck. Do I wait or start over?

October 27th – an hour and 5 minutes later

PASS Virtual Summit 2020 – ‘Upload Video’ Upload Confirmation email arrives.

Excellent!

October 27th – an hour and 6 minutes later

Tweet about it!

Since Then

Several of my #SQLFamily members admit, some publicly, some in private that they missed the deadlines or at least feel better that they’re running as late as me. I feel for them and I’m glad that my timeline and tweets made them feel better about their own timelines.

Up until I had finally submitted my video, I had put off watching any other presenters talk about PowerShell. But now that I’ve submitted my video, I’ve decided to relax that rule and watch at least one other presentation on an introduction to PowerShell and start to think, “why didn’t I bring that up? Hmm, he’s got a good point there. Hmm, I should have covered that.” I start to have doubts about whether my presentation will hit the mark. But fortunately, upon further reflection I realize the other presenter took a different tack than I did and mine has a focus he doesn’t. Someone watching both will actually get useful information from each of these. Now I’m feeling better. In fact, feeling great because I think this is the way it should be, multiple paths to the same end point that can broaden your horizons. And given the time limitations there’s only so much any presenter can cover in a limited amount of time.

That said, I realize that Rob Sewell is doing a full-day pre-con called Introduction to PowerShell. I’m curious what he’ll cover and both am jealous he has a full-day to do this and thankful I didn’t have to come up with a full-day’s worth of slides and scripts! That said, I know this will be a great one, so highly recommend you attend. I’ve seen Rob present at lest once before and it was great.

October 28th – 6:40 PM EDT

Get an email from Audrey at Pass Summit asking if I want to be part of a part of a live Q&A panel with Rob Sewell, Hamish Watson, Brandon Leach, and Ben Miller at 8:00 AM on the 11th. I have to think about this? There’s some big names on that panel and they want lil’ ol’ me?

October 28th – 6:41 PM EDT

Reply, “Hell yeah!”

October 29th – Over the course of the day

Folks at PASS realize the world is round and that we all live in different timezones and 8:00 AM may not be the best time for folks living Down-Under. Of course their first suggestion for a new time is even worse. Finally Hamish steps in, declares the entire world is in the Hamish Time Zone and that the original time is fine and he’ll let FutureHamish deal with the lack of sleep. Fair enough!

October 31st – Morning

My wife reminds me I’ll be out of the house at 8:00 AM on the 11th. I start to panic, but decide, “I can do it from the car with my cell phone.” So this is going to happen!

November 2nd – 2:00 PM

Tech check with Zoom and all to make sure things will work for next week. Learn a little more about how the recorded session will work. Still nervous for the “live from the car” presentation, but do the tech with the cell phone as my uplink and it works.

It’s getting real.

Today – November 3rd

It’s election day and just over a week from my presentations. I’m excited. I’ve made it clear to work I won’t be available at all on the 11th and not much on the other days. This is going to be a summit unlike any other. I’ going to have to remind myself to actually “attend” it.

And now, finish up a few things and go vote.

I’m voting today for my kids and my friends and my family, blood or chosen. I’ll be voting for the future and for hope.

Voting and Apple Pie – Two American Traditions!

A Speaker’s Timeline

Posted on by
7

This post will be short, for reasons that are hopefully obvious by the end.

Sometime in February

Hmm, I should put together some ideas to submit to present to SQL Summit in Houston (not Dallas as Mistress SQL pointed out to me) this year.

March 16th

An update, the call for speakers has been postponed. Darn.

March 23rd

Call for speakers is finally open!

March 30th

Submit 3 possible topics.

April 1st

Approach a fellow speaker about a possible joint session, but after discussion, decide not to go ahead with the idea.

June 3rd

Get an update, Summit will be virtual this year. Thankfully I didn’t book any tickets or hotel rooms in Dallas.

July 20th 6:49 PM EDT

Woohoo! I got the email! One of my submissions got selected to present!

July 20th 6:50 PM EDT

Crap, now I actually have to write the entire thing!

July 20th 6:51 PM EDT

Wait, and it’s going to be virtual too. That’s going to make it a bit more of a challenge to present. But I’m up to it!

Sometime in August

I really should get started. Hmm, here’s one of the scripts I want to present.

But honestly, I’m preparing to teach a bunch of cavers and medical students cave rescue, I need to concentrate on that first.

September 5th

I just biked over 100 miles. I’m certainly not working on my presentation THIS weekend.

Later in September

Ok, now I’m going to sit down and really work through this. Here’s a basic outline.

October 1st

Oh wait, it’s going to be virtual AND I have to prerecord it? How is that supposed to work? I had better read up at the speaker portal!

October 2nd

Huh, ok, that sorta makes sense, upload the slides, do a recording, but I still don’t get how it’ll work with a presentation like mine with lots of demos. Well I’ll figure it out.

October 6th around 11 PM EDT

Well the PowerPoint template deck they provided looks pretty slick. I should start prepping my slides.

October 6th, approximately 5 minutes later

There, got the first slide done. Of course it’s only my name and pronouns, etc. But it’s a start.

Oh and the 2nd slide is done, but that’s simply the default PASS slide talking about chapters, SQL Saturday etc, so technically I didn’t do anything there.

I’ll start working on the closing slides.

October 7th, sometime after midnight

Ok, about 5 slides done. I’ll like to myself and say I’ve made great progress!

October 9th, approximately 10:00 PM EDT

Ok, I’ll at least start writing out the scripts I need.

October 9th, 20 minutes later

What the bloody hell? Why is this script failing? I’ve got to present this. If I can’t get this script working how is anyone going to believe that I know PowerShell, let alone actually use it.

October 9th, 5 minutes later

Well, damn, that was an embarrassing mistake, just had the , in the wrong place

October 10th around 9:00 PM EDT

Hmm, to properly demo this, I really need to run against 3-4 SQL Servers and I really don’t want to spin up a bunch of VMS and I can’t use my development one, too much proprietary data there.

I know, NOW is a perfect time to start to learn to use Docker! Why not? And besides Cathrine Wilhemsen has a great post on it. I’ll simply follow that.

2 hours and 1 reboot later

Hey, would you look at that? I’ve actually got a docker container running SQL. This is awesome!

Another minute later

But why can’t I actually connect? What network is it on? Why did I decide docker was easier? Why did I even submit this proposal? What the heck am I doing here? What is the meaning of life?

5 more minutes

That’s it, I’m going to bed.

October 11th, late night

Oh, I get it it now, I didn’t setup a full separate network, it’s bridged and that’s why it’s showing 0.0.0.0. I just need to change the port and I’m good to go!

A minute later

This is pretty awesome. Not what I’d do for a production setup, but definitely works for my demos. Now if I were really smart, I’d also setup persistent storage and the like, but this is good enough. And honestly now, setup a loop, increment a variable and bam, I’ve got 4 instances of SQL running in docker, 2 are 2017 and 2 are 2019. This is really incredible. I’m proud of myself.

Oh and even better, I’m doing all this in a PowerShell script, so I can actually make it PART of my presentation!

October 12th 2:26 PM EDT

Send off an email to the Program folks at PASS asking about how the recording stuff works with demos. Eagerly awaiting a reply.

October 15th, another late night

Yes, there’s a theme here, much of my work is being done late at night. It seems to work for me. But dang that deadline is getting closer!

October 16th, late night, again

Watched some Schitt$ Creek with the family. “Why didn’t we start watching this sooner? It’s hilarious! But I need to work on my presentation some more.”

Get all the PowerShell scripts basically done. I’m happy with it, need to work on my speaking script some.

October 19th 3:00 PM EDT

Get off the phone with a fellow Cave Rescue expert. Just before I get off, I mention my upcoming virtual, prerecorded session I have to finish. He says, “Oh, you know I just did 2-3 of those for a rescue conference, exact same format. It worked out really well. I can send you some details and feedback.”

I find that reassuring.

Also recheck email, still no answer from the folks at PASS on my questions about demos, etc.

October 19th, guess what time

I’ve finished everything, even updated the slides and scripts a bit more. I’m a bit worried I’m going to run too long, but decide to do my first of several practice run throughs.

Do my first full run through. Stop and correct a few mistakes or rough edges here and there. I’m not too worried if I run over now since I know I’ve artificially added some time.

October 19th, 42 minutes later

I get done, look at the PowerPoint timer: 42 minutes. “CRAP! I need this to be 60 minutes!” I’m not too worried, I can add more, but I’m not sure where and I don’t want to simply add fluff for the sake of fluff. I need to give this some thought.

Later on October 19th

Talking to a friend of mine who among other things has a background in adult education. She doesn’t know SQL or PowerShell, but she’s a good sounding board and she’s going to sit through my next run-through, not so much for the technical details but to give feedback on the flow and perhaps suggestions on where I may be making too many assumptions on what my listeners will know.

October 20th Early Morning

It’s a Tuesday, time to blog. As always I face that question, what should I blog about?

“I know, I’ll blog about how I’m getting my presentation together and the deadline is fast approaching. I can’t be the only speaker that often finds themselves up against the deadline and panicking.”

Next 36 hours

Add a bit more content and run through it 2-3 more time and then… RECORD! (technically it looks like I have until the 26th to upload my recording, but I want to get done early).

Conclusion

The above may or may not be a wholly accurate timeline or description of the process I’ve gone through trying to get my presentation ready for Pass Virtual Summit. I may have elided a few details and over-hyped a few others, but in general it’s close to true and accurate. Despite my always best intentions, I find myself often working up close to the deadline for submissions. Since for Summit they want NEW presentations, I can’t simply dust-off one of my previous presentations and use that, so there’s definitely more work involved here.

And honestly up until I learned it was going to be prerecorded, I thought I’d have most of October to work on it. The deadline to get the slides and recordings submitted sort of threw my original timeline for working on it in the dumpster so I’m actually a bit further behind than I expected to be.

On the other hand, I really did learn to use Docker and I think that’s valuable and I am making that part of my presentation. And, when all is said and done, I think I’ll be happy with it. I think though like any good speaker, I’ll look back and think “well next time, I’ll have to improve this or that.” There’s always room for improvement. I’m not keen on giving it prerecorded. I value the instantaneous feedback I get from the audience. So that will be different. But I at least can elicit questions during the presentation and there’s a life Q&A afterwards. But, I’ll still be nervous.

I’m in awe of speakers who get their presentations all prepped and prepared months in advance, but I suspect there’s a number out there like me, that don’t operate that way. And I suspect there’s a few who are even more nervous than I thinking, “OMG, am I the only one in this spot?” Nope, you’re not. Or rather, “Please let me know I’m not the only one!”

See you all at Summit, at least virtually!

And in the meantime there’s another possible deadline coming up I need to think about…

A Summit To Remember

Posted on by
Reply

There’s been a lot of talk about the 2020 PASS Summit and how the impact of making it virtual this year. I’ve even previously written about it. I’ll be clear, I would prefer an in-person summit. But that said, I think having it virtual does provide for some fascinating and interesting possibilities and I look forward to seeing how they’re handled.  It will certainly be different being able to watch a session at a later time as a default option. And my understanding is that session schedules will no longer be constrained by the timezone the Summit is being held in.

That said, I also have to admit a certain bias here. I’ve wanted to speak at Summit for a couple of years now and have been turned down twice in the past two years. This year I was hoping again to speak, but alas, I procrastinated a bit too long and literally missed the original window to submit by a few hours.

Note I said original window. Because the Summit was moved to a virtual Summit the decision was made to re-open the call for speakers. This time I took advantage of that 2nd chance and submitted a bid.

And I’m so glad I did. Because if you didn’t have a reason to attend summit before, you do now! You get to hear me talk about PowerShell! So, I’ll admit to getting an unexpected benefit out of the move to a Virtual Summit.

I still recall one of my first attempts to use PowerShell at a client site, about 8 years ago. It did not go well. The security policy wouldn’t let me do what I wanted and the available knowledge on the Internet was sparse. Basically I wanted to loop through a list of servers and see if they had SQL Server installed. I eventually gave up on that project.

Since then though, I’ve been drawn to PowerShell and have come to love it. Now, when you hear a DBA talk about PowerShell, they will almost always mention dbatools. I want to go on record right now, I think it’s a GREAT addition, but I rarely use it. Not because there’s anything wrong with it, but mostly because my current usage is a bit different than what it provides. I do talk about it a bit here though.

For the talk I’ll be presenting, my plan is to start with a real simple PowerShell Script and slowly build on it until it’s a useful script for deploying SQL Scripts to multiple servers. For anyone who has read my articles at Red-Gate, much of this will be familiar territory, but I hope to cover in 75 minutes what I cover in 3-4 articles.

Checking this morning, I noticed that I’m among good company, and it’s humbling to see it, when it comes to speaking about PowerShell.

So, I hope you “come” and see me present on PowerShell at SQL Summit 2020. I’ll be in New York, where will you be?

Yesterday was “A Monday”

Posted on by
2

Yesterday was a Monday. I don’t just mean it was Monday, but it was in the Garfield comic sense of things A Monday.

As a consultant, I’ve come to expect certain patterns in my work load. For one client, I know approximately every 2 months, over 2 weekends I’m going to have to patch their SQL Servers. I know certain passwords will need to be updated quarterly or annually. And I know sometimes I’ll have A Monday.

Yesterday was one of those. I woke up, checked my email and noticed two jobs had not run. So I logged in and it appeared that the PowerShell script on each server had hung. I killed it and tried to rerun it, but got an error. This wasn’t entirely surprising. This script, in its first part downloads a file from a 3rd party vendor and last week for example, their SFTP server had been down. At first I expected this to be the problem again. But further testing showed I was getting inconsistent errors. Finally the script ran. But, what normally took about 20 minutes to download, took about 2 hours. We learned later the vendor had done an upgrade to their product over the weekend. This shouldn’t have impacted their SFTP server performance, but here we were. Today (Tuesday) the process took 20 minutes again and is back to normal. Chalk yesterday’s issue up to being A Monday.

Then I took a look at another job that had failed. This one is purely internal. Basically SFTP a file from a Linux server to a NAS for a backup. A quick check showed that the NAS share was inaccessible. Reporting this triggered an avalanche of emails back and forth. The most interesting line basically came down to “Yes, the internal IT team did a migration of the NAS, but the migration was supposed to be completely transparent to the users.” Famous last words in my book. Actually, honestly, what I decided was more disturbing was that the failure was on the new NAS device apparently due to a typo. To me, this means, most likely, all the old shares were recreated on the new device by hand, rather than using a script that read out the old shares and recreated them. In any event, the problem was solved, the job was rerun and the backup created on the now new NAS. Chalk that one up to being A Monday.

Then one of the developers for one of the platforms at this client emailed me and said, “Hey database FOO is in recovery mode, what happened?” This one, fortunately I knew exactly what the problem was. Unfortunately I knew it was my fault. We had decided to reconfigure that database to be a log-shipped copy of the main database and I had set it up over the weekend. I had simply forgotten to set it up to place itself in Stand-by/Read-only mode after it had applied the most recent logs. I’ll chalk that one up to it being A Monday.

All of the above was taken care of before 10:00 AM. The rest of the day was filled with a variety of other issues and items, including looking at a Hyper-V host machine with 16 physical CPUs with hyperthreading turned on hosting 4 VMs, 1 with 4 vCPUs allocated, and the other 3 with 8 each. They’re having performance issues. I’m still tackling that one. Looking at that happened on Monday, but it’s not A Monday issue, it’s been an ongoing issue for months.

So what was it about this particular Monday, or Mondays in general?

Well in this case, all 3 of my early AM issues had one thing in common: upgrades or changes made over the weekend. I’m not going to debate the value or wisdom of the timing here, but just note, that on the particular Monday, it wasn’t just one issue, but three. It was definitely A Monday. But I survived as did my customer.

Now back to my regularly scheduled workload.