Category: T-SQL Tuesday

T-SQL Tuesday #121: Gifts Received This Year

Closing out 2019’s T-SQL Tuesday, Mala (blog | twitter) is asking us to reflect on the past twelve months:

T-SQL Tuesday Logo

This is a time for material gift giving, for many of us. It might also be a time to consider the many gifts we have received through the year, and perhaps use this opportunity to appreciate people or situations that we were blessed with. So my question would be – what are a few things would you consider as gifts, and why?

I’m publishing this a couple hours late and I’ll admit, I’ve seen a few other folks’ posts which have inspired a few thoughts of my own.

  • An amazing job opportunity came along at the end of the summer. I wasn’t looking to make a change, but amazing things happen when you least expect them. The jump was completed in mid-October and every day, I’m discovering more things to learn and do.
  • I know a few others have written this, but meeting more people in the SQL community and deepening the connections with people I already know has been wonderful. Y’all know who you are, and I am so grateful to know you 🙂
  • To celebrate a milestone anniversary, my wife & I enjoyed a short vacation. This was the first vacation we’ve had where it was just the two of us since our honeymoon. It was pretty nice.
  • I got a lot more comfortable with saying no. This applies to several contexts, but the most important one is saying no to myself. Over-committing is a habit I’m slowly breaking out of. I’ve learned that it’s OK to not force something into my life just because people expect me to do it. It’s OK to skip a weekly activity every now and then if you decide you just need a break. In a way, 2019 was about the things that I didn’t do as well as the things I did do.

T-SQL Tuesday #119 – Changing Your Mind

For T-SQL Tuesday this month, Alex Yates (blog | twitter) gives us this challenge:

I would like you to write about something in your IT career that you have changed your mind about. What was your original opinion? Why did you believe that? What do you believe now? Why did you change your mind?

T-SQL Tuesday Logo

You are welcome to discuss technical or non-technical topics. Feel free to go as deeply technical or as personal and human as you like. Brain-melting technical posts about the inner workings of the SQL engine or effective machine learning architectures in Azure are great. SQL 101 posts or perspectives on age old debates such as tabs and spaces or where to put your commas are great too. Human posts about effective teamwork or diversity or wellbeing in tech are also great.

I hope that if we think hard about the ways we have changed our minds in the past, and if we read about how and why other people have changed their minds, it will help us to have better conversations in the future. I hope this will help us to collaborate more effectively at work – and maybe in other parts of our lives as well.

The Only Constant is Change

I’ve changed my mind on a number of topics over the years. It’s only natural. We learn more, we get more information, circumstances change. But the first and biggest one that comes to mind for me is my position regarding Microsoft.

Say What?

“But Andy,” you say, “haven’t you spent your entire career working with Microsoft technology?” Yes, yes I have! But I didn’t always feel good about it.

My formative geek years were spent in the 1990s, living through the meteoric rise of Microsoft, their missteps around the early days of the WWW, and then their total dominance of the market. You might have read about it in the newspaper. I studied Computer Science, surrounded by UNIX and Linux systems day in and day out and steeped in the idealistic dogma of “Open Source is good, Microsoft is evil. Embrace, extend, and extinguish! EVIL!!!!!!”

Sure, I still used Windows. I needed it for the dial-up connection I had at home over the summer and to play games (it always comes back to games, doesn’t it?). At the time, it was Windows NT 4.0 Workstation because I had (thought I had) higher computing demands than I could entrust to “consumer” Windows. Except…Microsoft changed the video driver architecture and the driver for my video card was buggy in such a way that some JPEGs loaded in Netscape Navigator could crash the whole OS. Gee, how good can Microsoft be if I can’t even load a JPEG without crashing Windows?

As time went on, I found myself trying avoid Windows more and more. My final semester of college, I don’t think I used Windows for more than 15 minutes total. Linux all the way!

But I Need a Paycheck

In the middle of my final semester of college, I accepted a job that used Windows everywhere – desktop and server. Some of my friends shrugged. Others sneered. I wasn’t thrilled about feeding into the “Evil Microsoft Empire” but I needed to eat. As I wasn’t the best student, major companies like IBM and Red Hat weren’t exactly lining up to interview me on campus.

I begrudgingly used Windows at work. And I got pretty damn good at it. Within months, I was fixing major outstanding bugs in our ASP 2.0/VBScript apps and moved on to building new apps from scratch. But at home I was still running Linux all the time, holding on to that idealism. Until…it broke.

I had an distro upgrade go bad and decided I was over my Linux phase. Microsoft was still kind of “ick” but at the same time, I was done tinkering w/ my OS and just wanted something that was left in a 100% working state after the installation was finished.

OS/2 on the desktop was a flop so it was back to Windows. Windows 2000, then Windows XP fit the bill at home. And my employer was still all-Windows.

The next computer I bought for myself was a MacBook. I had been fascinated by macOS since seeing the first public preview of Mac OS X way back in early 2000. It was UNIX with a candy-coated shell! I installed Windows in a VM for the one or two pieces of software that I still needed that for, but I was going all-in on the Mac at home.

Did the Suits Take Over?

By then, Steve Ballmer’s version of Microsoft was in full effect. Vista finally happened, leading me to I question whether Microsoft could get anything right. Everything seemed to be built around “how can we make you a Windows customer, no matter how bad Windows may be?”

The Pendulum Swings

Microsoft’s OS group redeemed themselves with Windows 7. Hey, they can make a decent OS after all. Things started moving forward (let’s try to forget Windows 8 happened). Azure started spinning up, taking on Amazon.

More importantly, software releases became more frequent and more regular. Security improved, quality improved, and you could start seeing more influence from the engineering side of the company emerging. The business drivers started to take a backseat to delivering products to the people who influenced technical decisions. Build good products, and the business will follow.

The tipping point for me was when Satya Nadella took the reigns in 2014. Microsoft seemingly was returned to the engineers. Open Source was embraced and encouraged. Microsoft was even releasing things as Open Source without being asked about it! They moved their focus to “we want to host the platform you run your business on, regardless of the OS and toolkit – but here’s a whole bunch of awesome tools that you can build with if you want to.”

The Microsoft of today is not the company I grew up watching and fearing (that position is now jointly held by Google and Amazon). They’re building exciting, compelling products and driving the industry forward. It was a rough few years, but I have done a U-turn on Microsoft. While I’m dabbling in other environments because it’s the smart thing to do for my career, I’m still working primarily with Microsoft technologies and it’s a decision that’s served me well for over twenty years.

T-SQL Tuesday #113 -A Database for the Great Outdoors

This month’s T-SQL Tuesday comes from Todd Kleinhans (blog | twitter) who wants to know what we’re doing with databases outside of work.

T-SQL Tuesday Logo

I’m curious- outside of work and learning, what do you personally use databases for? Tracking books you have, recipes, collections, etc? While it can be said using databases for personal use could be either overkill or a hammer in search of nails on the other hand, it is exactly what they are for- storing data.

Continue reading “T-SQL Tuesday #113 -A Database for the Great Outdoors”

T-SQL Tuesday #112 – The Cookie Jar

This month’s T-SQL Tuesday comes from Shane O’Neill (blog | twitter). He starts us off with this:

Dipping into the Cookie Jar is about when the going gets tough and you don’t think you can handle anymore, then you think back about your accomplishments and take some sustenance from them. You dip back into that cookie jar and use whatever energy that provides to keep going.

So tell me about a time when you had an accomplishment that can keep you going.

I. Love. Cookies.

I feel like this could describe my entire career. I cannot tell you how many times I’ve found myself staring looking at a new project, thinking “how on earth am I going to pull this off? This is nuts!”

I do exactly what Shane describes above – I just didn’t know there was a name for it. I have to remind myself “hey, you’ve said this same thing before, and it worked out. Breathe, slow down, and you will figure it out. You always do.”

For the really gnarly problems in the past, I’ve hidden away for a few days to just hammer away at code. So, what do I do when I get to the next one? I remind myself that if I do the same thing, I’ll get through.

Here’s the thing

In this business, you should never be doing the same thing over and over. Those are the things you automate so you can move on to more interesting problems.

There should be something coming along new every week. If there isn’t, make something new happen. If all you’re doing is things you’ve done before, things that you already know exactly how to do, you’re going to stagnate. You’re not going to learn. You’re not going to grow.

For me, it’s all about breaking the problem down in two ways:

  • Find parts that you know you know how to do
  • Find ways to experiment with the parts that you don’t know how to do

The first one is your cookie jar. Break that project down and tackle the stuff you have seen before. Maybe you’ve even got a time-tested script or checklist to get you through it.

On to the stuff that’s new. This is the exciting part! You won’t get it right on the first try. Everyone falls the first time. That’s how we learn. That stuff that’s old hat to you in the paragraph above? There was a time you didn’t know how to do that, either. And now look at you – you’ve mastered those parts.

An Example

Right, Shane wanted real examples, didn’t he? Time for me to go back to the well that is my 8000-database migration. Let’s break that down:

  • Upgrading from SQL Server 2008R2 to a newer version? High-level, I’ve done that before. Had a sysadmin doing most of the heavy lifting, but I was right there with him for most of it. 🍪
  • Oh, but wait! I worked on an upgrade from 2000 to 2008R2 before that. We kept the whole workflow document and checklists around from that so we built on that. 🍪
  • I migrated from 2008R2 to 2008R2 before using dbatools. That went pretty well. 🍪
  • When I did that previous migration, I successfully copied things in groups, I didn’t have to do it all at once. Just like the big migration to 2016. 🍪
  • As an organization, we’d previously moved our 2008R2 setup to new server hardware, with minimal issues. We learned from the breakage we had and documented those lessons. 🍪
  • When I stood up the new test 2016 instance, I copied everything but the databases from the 2008R2 test instance and that worked out OK. 🍪

So, what was I left with here for the big migration? I’ve been through version upgrades. I’ve been through scripted migrations. I’ve copied everything except databases from 2008R2 to 2016. We’ve changed hardware before. So many cookies!

The only part that I was really worried about was attaching the databases themselves. As a result, I was able to test that out a couple dozen times to make sure it would work properly. And no, I didn’t get it right on the first attempt – it took probably a dozen attempts before I got the attach script working properly. But I knew it would be OK, because I’d worked on plenty of other projects where the first attempts were unsuccessful as well.

Keep at it

You didn’t give up in the past, and you found success. So keep plugging away and you’ll keep succeeding.

T-SQL Tuesday #108 – Learning Tech Beyond SQL Server

Malathi Mahadevan (b|t) is hosting this month’s T-SQL Tuesday, a monthly blog party for the SQL Server community.

Malathi has asked us to:

Pick one thing you want to learn that is not SQL Server. Write down ways and means to learn it and add it as another skill to your resume. If you are already learning it or know it – explain how you got there and how it has helped you. Your experience may help many others looking for guidance on this.

The timing for this works out well for me, as I’ll be working on my 2019 goals very soon and education is part of those. But right now, we’re kind of a homogenous shop, which makes stretching beyond SQL Server a bit more effort. I’m occasionally indecisive, so I’m going to throw out a few ideas of things to learn in 2019.

  1. Need to learn for work: Postgres on AWS. We have a pilot project starting up using this and eventually, my team will need to support it. So, we need to at least understand the basics – backup & restore, basic monitoring/troubleshooting.
  2. Want to learn on my own: A document database of some description. I’ll probably land on MongoDB due to convenience and cost (or lack thereof). Runner-up: DynamoDB.
  3. Stretch goal: I still don’t entirely get the concepts behind Docker or Kubernetes (although I’m close on Docker). I’ve tried to run SQL Server 2017 on my Mac via Docker, but Docker itself seems to make the machine unstable. Kubernetes is a complete mystery to me; I know a few people who are doing “stuff” with it, but haven’t talked with them about it yet.

But you may be asking “Andy, where’s Azure? CosmosDB is a document database on that platform. And PostGres is on Azure too, why not learn it there?” My shop is starting to do new work on AWS, so the opportunities are more available there and I can apply what I learn at work more readily. So it’ll cost me nothing out of pocket if I go with that or MongoDB. As for the learning plan:

  1. I’m going to have to do this one for work regardless, so step one will be setting aside time at the office to work through the AWS tutorials and documentation. Then set up a few test databases, throw some data in, and play around.
  2. I know we have a few MongoDB experts locally and a user group, so hopefully they can get me started in the right direction. I’ve got a Raspberry Pi 3B as well as a Pi Zero W that I can put to use here.
  3. Apparently one can run MongoDB on Docker with Kubernetes so maybe I can kill two birds with one stone? It could be an excuse to pick up a couple more Pis. Or maybe fire up some VMs on the rack mount server that’s been sitting in my basement for 5 years.

MongoDB & Docker/Kubernetes are completely new ground for me to cover, so just figuring out where and how to get started may be a challenge. By the time publishes, I may already be starting to read the MongoDB manual en route to PASS Summit – and I know I can talk to people about this stuff at Summit to get ideas on where to start.

Addendum

This post was written published early by accident. My reading of the MongoDB manual didn’t happen last Tuesday as I was not feeling well on my flight to Seattle.

T-SQL Tuesday #107 – Death March

This month’s T-SQL Tuesday is hosted by Jeff Mlakar and he asks us to write about a project that went horribly wrong. My story isn’t really worthy of the name “death march” but it was a pretty rough project.

The project started sometime in mid-2003. I was working as a web developer (Classic ASP) for an insurance company and they wanted to modernize the underwriting process with a web-based “workflow” application.

A platform was selected, the team was picked, and we set about customizing the devil out of it (it was more of a “framework” than turnkey application) and integrating with our existing systems. Of course, being an insurance company everything ultimately had to land in the mainframe.

The tech team was pretty large – upwards of a dozen and a half programmers across various disciplines. Four of us were kind of off on our own building the ASP front end and COM-based middleware – myself and Ned on the ASP side, Tony & Phil (all names changed) on the other side of the cube walls working on COM objects. The four of us worked really well together, with Phil & I each taking the lead in our respective disciplines. Our PM was great; we got along well and she knew the right questions to ask me to nudge me in the right direction.

As Phil & I dug into the platform API, we realized we were going to be implementing more features with our own code than expected as they weren’t actually built into the API yet.

We brought a few consultants from a large company you’ve certainly heard of to help us work out a UI design. After 5 weeks, we presented our design proposals. Every – I mean every – proposal put forth by the consultants and me was shot down. The response was “those are very nice, but we want what we described to you 2 months ago, so build that.” I was flabbergasted. I asked the consultants if they’d ever seen anything like that and they admitted that they hadn’t. We just spent five weeks and a bunch of money to produce nothing.

February was approaching and while Phil, Tony, Ned and myself were firing on all cylinders, others weren’t doing as well. We were supposed to launch in March, but it was becoming apparent that we’d miss that date. The release date slipped to mid-April.

The four of us spent the last half of February and all of March tightening up our code. We had some performance concerns but hadn’t figured out where they were coming from yet. Acceptance testing hadn’t started yet; the project was expected to be “nearly complete” before that’d start.

Acceptance testing started in late March. Ned & I knew our code inside and out, and I could suss out pretty easily if a reported issue was there or in the COM objects so I could throw those bugs over the wall to Tony & Phil as necessary.

I returned from lunch on the last Friday of March and Ned said to me “I’m leaving at the end of the day.” Um…OK, thanks Ned, I am too. Then he clarified. See, Ned was a contractor and he he was ending his contract at the end of the day – and starting a new gig in Miami on Monday! We were unable to bring in a replacement, so I was flying solo for the rest of the project.

I spent April fixing bugs as fast as I could, re-implementing some features because they sounded good on paper but once seen in action, they weren’t acceptable. Mid-April came around and the project release date was pushed back by another month.

I went to the PM and expressed my concerns about the release date changes. I reminded her that I was getting married Memorial Day weekend and I’d be unavailable for 2 weeks due to that and the honeymoon. She assured me that this was the last reschedule, and even if that turned out to not be true, I wouldn’t be asked to change my plans.

Early in May was the final blow for me. We had an all-project meeting with several people from the highest levels in the company in attendance. The missed deadlines, the incredible strain placed on not only the development team but their families, the importance of the project to the company, all brought up. People laid some very personal feelings out. The response from one of the top-level people was cold, harsh, uncaring, impersonal, tone-deaf, and completely disheartening.

The project went live 10 days before I left for my wedding and honeymoon.

Epilogue

No project implementation is perfect out of the gate. Right before we went live, Phil figured out what was causing our performance issues and over the next month or two, more or less re-implemented that portion of the vendor’s API himself. We pushed out an updated version as quickly as we could; users were complaining about performance from day one. We got a lackluster “yeah, I guess it’s better now” but no real acknowledgement of the improvement Phil had made so quickly. It was too late; the perception was that this system is slow and that wasn’t going to change, now matter how fast we made it.

The project was started in the summer or early Fall of 2003 and went live in the last third of May 2004. By the end of the first quarter of 2005, the company had decided to go in a different direction and the system was on track to be mothballed; no new business was being scanned into it, and once the last piece of business that was in it was processed, everything was shut down.

The system spent more time under development than it did in use.

T-SQL Tuesday #104 – Code You Would Hate To Live Without

This month’s T-SQL Tuesday is hosted by Bert Wagner and he asks us to write about code we’ve written that we would hate to live without.

First off, “hate” is a pretty strong word so let’s go with “code you couldn’t bear to live without”. The first bit of code I couldn’t live without is reviled in some circles. More often it’s misunderstood and lamented, or…well, I’ll just show it to you.

Yes, you read that right. It’s an SSMS Snippet that generates a cursor. I use cursors so often that I decided to create a snippet so I don’t have to rewrite them or copy/paste from a template file all the time.

Yes, really. Don’t @ me, come at me bro, whatever it is the kids are saying these days. I am dependent upon cursors every day and would be lost without them.

Wow Andy, you must be pretty bad at your job if you’re running cursors all the time! Don’t you know that’s terrible for performance? What’s up with that?

If we’ve met, either in-person or virtually on the SQL Community Slack, you probably know that I manage an instance hosting mumblemumble thousand databases. I don’t mean “a couple” thousand databases; we’re looking at Michael Swart’s 10 Percent Rule in the rearview mirror. I regularly have to look for a particular value across the same table in a few dozen/hundred/thousand databases, or pull a report across as many databases, or run the same data change for a big list of databases. Most often, I’ll be given a list of databases or be asked “run this for all the databases that meet these criteria.” And the only way to do that easily is via a cursor because I have to first collect the list of databases from another table. There are 3rd party tools I could use but doing the setup to run against an arbitrary list of databases is tedious, error-prone, and I haven’t quite worked out a way to improve it yet.

Processing a table or result set RBAR is a performance concern. But to crank through a long list of databases and execute the same query against each it’s the only way to go, as far as I know. sp_msforeachdb doesn’t cut it for my purposes because I don’t want to hit every database on my instance.

My second piece of code is more of a technique or design pattern. In a stored procedure or large script with dynamic SQL, I’ll often create two variables (they’re parameters, in the case of stored procedures – choose a sensible default!) called @Debug and @ImSure. They’re just bit types but I use them to control the output of debgging information and code execution.

By doing this, I don’t have to comment/uncomment sections of code all the time just to see what dynamic SQL I’m generating. I also have a failsafe which prevents changes from being executed until I’ve made sure that everything is solid.

Those are probably the two pieces of code that I can share which I couldn’t be without. Honorable mentions which I didn’t write but find indispensable:

  • QUOTENAME – pretty basic T-SQL function but with all that dynamic SQL I’m writing, I need it to keep my SQL clean and safe.
  • dbatools – I’ve written about this PowerShell module quite a bit here but suffice to say for doing bulk administrative tasks, collecting metadata about the environment for analysis, and moving databases or entire instances around, it’s a lifesaver.
  • Brent Ozar Unlimited’s First Responder Kit – I run sp_blitzcache & sp_blitzindex daily looking for places we can tweak our code or indexes to make things run better.
  • Adam Machanic’s sp_whoisactive – Gives me a great lightweight snapshot of what’s going on right now.

Thanks for joining me on this T-SQL Tuesday!

T-SQL Tuesday #99 – Dealer’s Choice / sqlibrium

This month’s T-SQL Tuesday from Aaron Bertrand gives us a choice:

And I’ve got one, maybe two posts in progress on the first topic. Alas,

Thanks to Eugene Meidinger (b|t) for nudging me in the direction of posting this. Ever since this month’s T-SQL Tuesday was announced, work and non-work has been a complete whirlwind and I have failed miserably at reaching sqlibrium.

I’ll expand on this in later posts and link back. Really.

T-SQL Tuesday #96: Folks Who Have Made a Difference

T-SQL Tuesday logoIt’s time for T-SQL Tuesday and this month’s edition is hosted by Ewald Cress (b|t). It’s non-technical this month because we’re all recovering from PASS Summit. Ewald asks us to:

give a shout-out to people (well-known or otherwise) who have made a meaningful contribution to your life in the world of data.

This post is both difficult and easy. Difficult because there are so many people in the community whom I’ve learned from. But easy because there are a

Allen White (b | t)

I met Allen in the winter of 2011. Allen was a consultant and my employer engaged with his firm for help with a SQL Server migration/upgrade and DBA services. Allen introduced me to PASS, the SQL Server community, and helped me discover a passion for the field. I had previously dabbled with SQL Server, and tried to pick up what I could on my own and from random internet searches, but I spent a lot of time fumbling around and learning bad habits. Allen got me pointed in the right direction and drew me into the #SQLFamily. He is one of the first people I look for every time I go to SQL Saturday Cleveland or PASS Summit.

Kendal Van Dyke (b | t)

A little over a year after I met Allen, Kendal joined his team and I started working with him. Kendal became our primary DBA and when we had difficulties with SQL Server (or, more accurately, our application was giving SQL Server agitation) Kendal was our first call. I worked on a number of issues with Kendal; he knows SQL Server inside and out, and I was deeply familiar with the application that was giving it trouble so we made a good combination and I learned a lot about troubleshooting/debugging and some creative ways to resolve/work around application issues that can’t be fixed at the application layer. Kendal even hosted my first-ever blog post! Every now and then, something pops up on Twitter and we reminisce about the good ol’ days.

Matt Slocum (b | t)

I met Matt when I started attending my local user group meetings (or maybe it was at PASS Summit 2012, after which I started going to the local meetings). He somehow talked me into helping him run the local SQL Saturday, then talked me into taking over running SQL Saturday. As I described in my post earlier this year about how I became a DBA, he gave me that final nudge to say “yes, I want to be a DBA now. It’s time to make it happen.”

Chris Sommer (b | t)

I met Chris through our local user group and it turns out we know a lot of the same people despite never having worked together; there aren’t a lot of SQL Server folks in town! Chris took a new job around the same time I changed jobs earlier this year so we’ve been out for quite a few lunchtime runs and walks where we discuss various technical (and non-technical) challenges we face at work.

Chrissy LeMaire, Rob Sewell, Constantine Kokkinos, Shawn Melton

Newcomers to this list. This group (and really, I can’t single any one of them out) has welcomed me into the dbatools team and been very patient with me as I’ve learned GitHub, learned how to work within the goals/parameters of dbatools, and let me fire off a lot of pull requests just to do things like spelling corrections. It’s a whole new piece of the SQL Server community they’ve helped me get exposure to, and at Summit this year I was shocked when I had people coming up to me saying “hi, I wanted to meet you” or “hey, you work on dbatools? I need some help…” My Impostor Syndrome has been working overtime here; it’s been a few months now and I still can’t believe they let me work on this stuff. I’ve been honing my own PowerShell skills, learning more about dbatools, and reinforcing what I thought I already knew by reading so much of their code as I work on my various fix-ups – and even making real code changes!

To everyone named here, and the whole SQL family, thank you. If you have ever attended or organized a SQL Server-related event – SQL Saturday, Summit, User Group meeting, webinar, or blog party – you have had an effect on me. My career would not be where it is today without you.