T-SQL Tuesday #128 – Learn From Others

Invitation from Kerry Tyler.

Pilots do something that a lot of non-pilots will find fairly weird if not outright horrifying: We read accident (“crash”) reports. Some of us spend a lot of time reading accident reports, actually. Officially “Accident Reports”, these are put out by the US National Transportation Safety Board (NTSB) after investigation into a crash or an “incident.” In addition to aviation-related reports, there are highway and railroad reports, and even hazardous materials incidents.

Reports come in two flavors, a “preliminary” report, and ultimately, a “final” report after the investigation has completed. The final reports includes such items as conclusions and the probable cause of the accident or incident. To make life easier, they also include a Recommendations section, which, well, includes recommendations for how to keep this type of accident from happening in the future. These tend to be regulatory in nature, as they are geared towards the FAA.

The search form for aviation reports is here–https://www.ntsb.gov/_layouts/ntsb.aviation/index.aspx–if you’re, uh, thinking you want to get into this sort of thing.

Why do pilots do this? The rationale is pretty simple: To learn from the mistakes of others. Or, to learn how a bad day was kept from becoming a worse day after something broke.

What Does This Have to Do With SQL Server?

Great question. Besides the fact that I think piloting airplanes and DBA-ing are the same job, just with different scenery,  I wish we had this kind of transparency in the IT world when things went wrong. When a corporation has a big security incident, we’re likely not to hear a lot of details publicly about what went wrong and what was done to mitigate similar attacks in the future. This kind of information could help everyone. This is one of the things that cloud providers do quite a bit better: When something breaks, we get good information on what happened, why, and what’s going to be done about it. Of course, this is done because public cloud providers basically have to–if things went down a lot and we never heard why, that provider probably wouldn’t have a lot of customers for very long.

This brings me to T-SQL Tuesday.

Tell me (us all, obviously) about something recently that broke or went wrong, and what it took to fix it. Of course, the intent here would be for this to be SQL Server-related, but it doesn’t have to be. We can all learn from something going wrong in infrastructure-land, or how there was a loophole in some business process that turned around and bit somebody’s arm. It doesn’t even have to be all that recent–maybe you’ve got a really good story about modem banks catching on fire and that’s how you found out the fire suppression system hadn’t been inspected in years. Just spitballin’ here. If you’ve got an incident whose resolution can help someone else avoid the same problem in the future or improve a policy as a preventative measure, let us hear about it.

TSQL Tuesday #127 Invite – Non SQL Tips and tricks

Invitation from Ken Fisher.

How about for this months TSQL Tuesday let’s do another month of tips and tricks. But just to be a bit different, nothing to do with SQL Server (in fact let’s say nothing related to a DBMS). No SSMS tricks, no T-SQL tips, something completely unrelated. For example did you know that, in Windows, if you grab a window and shake it then all of the other windows will be minimized? And then if you shake it again they all pop back up.

So give me a non SQL related tip or trick. OS (windows, mac), Cloud (Azure, AWS), etc. Something you think will be handy for the rest of us.

What is T-SQL Tuesday

T-SQL Tuesday is a blog party started by Adam Machanic (b/t) over a decade ago and now maintained by Steve Jones (blog|twitter) on tsqltuesday.com. The first Tuesday of each month a blogger hosts the party and suggests a topic. Then anyone who is interested blogs on that topic. It can be a lot of fun and quite a challenge to blog on a topic you didn’t pick. Not to mention the time limit. I didn’t mention the time limit? Oh, well, you have one week to write your blog and then it has to be posted on the second Tuesday of the month.

T-SQL Tuesday #126 – Folding@Home

Invitation and recap from Glenn Berry.

Introduction

This month’s T-SQL Tuesday is devoted to the Folding@Home (FAH) distributed computing project. This project was originally launched by Pande Lab at Stanford University on October 1, 2000. It is currently based at Washington University in St. Louis, led by Dr. Greg Bowman.

Folding@Home has been around for nearly twenty years, and they have a long record of generating valuable biomedical research. During the current COVID-19 pandemic, there has been a surge of donors and attention focused on the work that the FAH team has been doing. On March 8, 2020 I started a SQLFamily team for Folding@Home. Since then, 130 other people (so far) have joined in the effort. That leads to our current topic, T-SQL Tuesday #126 – Folding@Home.

T-SQL Tuesday #126 – Folding@Home

I had previously heard about Folding@Home many years ago, since a number of hardware related websites used to mention it (because they had competitive teams) fairly frequently. On March 7, I downloaded the FAH client software on a few of my machines and started folding. I started the team the next day. To my happy surprise, many other people in the SQL Server community started joining the team, donating their computer resources.

Since then, I have learned quite a bit about FAH. I have also done quite a bit of hardware modification, optimization and upgrades on my small fleet of lab machines. The point of the hardware work for me was to learn how to get more FAH production in the most efficient way possible. I have also tried to spread the word about the project and our team, and to share some of my experience.

Your mission, if you choose to accept it, is to write about what you have been doing as a response to COVID-19. Maybe you have joined the SQLFamily FAH team. Perhaps you have been printing “ear savers” using 3D-printers like Tim Radney. Perhaps you have been donating food or doing some other sort of volunteer work. Whatever you have been doing, now is your chance to talk about it and publicize it!

Personally, I would love to hear about your experience and any lesson’s learned with FAH. What kind of hardware are you using, and have you made any changes? Are you running FAH in the cloud or in some other interesting way? Have you done anything interesting from a technology perspective related to FAH?

T-SQL Tuesday #125 – Unit testing databases – we need to do this!!

Invitation from Hamish Watson.

It’s an awesome way of encouraging blog posts from the community and helping to share that knowledge out.

My topic is about unit testing databases – something that I don’t see enough of when I am working with clients. The good news is that over the years I’ve noticed that more people are speaking and writing about unit testing databases and folding that testing into DevOps processes like Continuous Integration & Continuous Delivery processes (CI/CD).

I hope that this topic drives some conversation both for it (because it protects your code and data…) and against it (it takes too long to write these pesky unit tests…!!).

We’re now delivering Bugs to Production faster than ever!!

This clickbait type heading actually describes what will happen if you embrace DevOps processes without doing any form of testing. Because DevOps is all about accelerating the delivery of software – we want to do more deployments and do them quicker…

..which is why testing is core to DevOps practises.  Specifically testing right throughout the deployment pipeline – that is starting at your laptop and finishing in Production (yes I advocate for testing in production but that’s a whole other blog post..).

What is Unit Testing?

Unit testing is a software testing method by which individual units of source code are tested to determine whether they are fit for use. A unit is the smallest possible testable software component.  A unit is small, so it is easier to design, execute, record, and analyse test results for than larger chunks of code are. Defects revealed by a unit test are easy to locate and relatively easy to repair.

This is opposed to finding the defect in Production – which is harder to triage and is now affecting users – whereas if you find that bug on your laptop via a unit test – it is way easier to remediate and only affects – you.

The ideal qualities of unit tests:

Decisive – the unit test has all info to determine success/failure

Valid – it produces a result that matches the intention of the code written

Complete – it contains all information it needs to run correctly within the test harness

Repeatable – always gives the same results if the test harness and code are same

Isolated – is not affected by other tests run before nor affects the tests run after it

Automated – requires only a start signal in order to run to completion

Benefits of Unit Testing

Below are the benefits of unit tests – this relates to application and databases alike.

Code Quality goes up:

Unit testing improves the quality of the code. It identifies every defect that may have come up before code is sent further for integration testing. Writing tests before actual coding makes you think harder about the problem. It exposes the edge cases and makes you write better code.

Find Issues early:

Issues are found at an early stage. Since unit testing is carried out by testing individual code before integration, issues can be found very early and can be resolved then and there without impacting the other pieces of the code.

Simplifies Integration

Unit testing allows us to refactor code or upgrade things at a later date and make sure everything still works correctly. Unit tests detect changes that may break things and help with maintaining and changing the code base.

The best part about unit testing is that it verifies the accuracy of the each unit of code. Afterward, when we integrate the code units together and run some form of integration testing during the build process we can then verify the individual units of code.

So what about databases?

So now that I have introduced unit testing – is it valuable for implementing with databases? Rather than write my own opinion – I’m going to hand it over to the community to answer this…

🙂

(BTW I think it is valuable – I speak on it regularly and I implement it with clients to safeguard their production databases…)

Lastly – we’re currently in a lockdown in New Zealand – because of the COVID-19 pandemic sweeping the world.

I want to say to all who are reading this:

Be Safe

Be Strong

and please:

Be Kind

If you’re struggling with things – please reach out to your support network (we care about you), I wrote some things that have been helping keep myself:

Take care – we’re in this together and you’re not alone
#sqlfamily

Yip.

T-SQL Tuesday #124 – Using Query Store or Not Let’s Blog

Invitation and summary from Tracy Boggiano.

Ever since Microsoft introduced Query Store I’ve been working with it, back to the CTPs in 2016.  I started presenting on it because it benefited my current company at the time.  I heard there are low adoptions rates and from a couple people implementations problems or just not having time to implement it.  After 3 years of presenting on it and writing a book about it I’m curious as to adoption rate of Query Store, but we won’t be writing about that.

For this T-SQL Tuesday, write about your experience adopting Query Store, maybe something unique you have seen, or a how your configure you databases, or any customization you done around it, or a story about how it saved the day.  Alternately, if you have not implemented yet blog about why if you are using 2016 and above, we know why if aren’t on 2016.  If you are unfortunate to be on below 2016 write about what in Query Store you are looking forward to the most once you are able to implement it.  Basically, anything related to Query Store is in for T-SQL Tuesday, hopefully everyone has read up on it and knows what it can do.

T-SQL Tuesday #123: Life hacks to make your day easier

Invitation from Jess Promfret.

So here we are, the first Tuesday of February. I personally always find February to be the month where my motivation is a little low. I live in the northern hemisphere so it can be a pretty dreary winter month where it still feels like there is a long way to spring (I will say this January I moved from Ohio back to England and the distinct lack of piles of snow is helping this cause somewhat). This makes my topic even more relevant as we need a little extra help to be productive and get through the month.

My topic is looking for your favourite ‘life hack’, something you use to make your day easier. This could be anything from a keyboard shortcut in SSMS that runs ‘sp_whoisactive’, to a technique you use to get and stay organised.  It doesn’t have to be directly related to a technology, just whatever you use to make your life easier.

Now, I’m personally a huge proponent of using keyboard shortcuts to get things done faster. In the last year or so I’ve started using Visual Studio Code as my editor of choice and the number of little ‘life hacks’ I’ve found has grown incredibly. I’m going to share a couple that I use often to get your ideas flowing.

Multiline Select – Ctrl + Alt+ Direction Key

This is something I love for formatting queries, among other things. I know you can use T-SQL to generate some queries from the metadata but if you have a list of tables you want to truncate, for example, you can easily accomplish this. Select the start of each line by using Ctrl + Alt + down direction key, add the TRUNCATE TABLE text and then press end to get to the end of each line, no matter the length, to add the semicolon.

The other use I have for this hack is to generate names and descriptions of Active Directory groups for tickets to have them created.  At my previous job we created read and admin groups for databases that users could then request access to. Multiline select made this really easy to generate the required information.

You can use multiline select at the beginning of the row. Start by selecting the first word and copying it (Ctrl+C), then you can type to format your group name. For example, I put SqlDb- before the database name and then -Read afterwards.  Pressing enter at the end of the group name will create a second line for all three groups where you can add the description. Notice I can now use paste (Ctrl+V) to add the database name that we copied from each line.

This ability to change multiple lines at once is really powerful and once you get the hang of what you can do with it you’ll find so many opportunities.

Change all occurrences – Ctrl + F2

A similar hack to my first, VS Code also lets you change multiple occurrences of characters. I say characters because you can select whole words, parts of words, or even punctuation. This is really handy, for example, for formatting a comma separated list on one row into a list with each value on a separate row.

Carrying on from my previous example, now that we have formatted the group names and description. I can select the word ‘Read’ and replace all with ‘Admin’. Just like that I have all I need to get the group request off to the help desk for creation.

Command Palette –  F1 or Ctrl+Shift+P

VS Code also has a really great Command Palette that offers a lot more for you to explore. A few of my favourites are:
– Sort Lines Ascending/Descending – Select some lines in VS Code and easily alphabetise them.
– Git: Undo Last Commit – Rescue that last commit back from your source control. Useful if you realised a second too late you committed to the wrong branch.
– File: Compare Active File With – This clearly highlights differences between two files.

Over to you

I hope my VS Code life hacks have got your ideas flowing, so now it’s over to you.

T-SQL Tuesday #122 – Imposter Syndrome

Invitation from Jon Shaulis

As we enter the new year, I’m sure many of us are setting goals, resolutions, or perhaps beginning new challenges. Change can often be terrifying, but that’s how we grow. With this in mind, the topic I’d like us to write about this month is “Imposter Syndrome”.

Imposter syndrome isn’t a topic I’ve seen addressed before via T-SQL Tuesday and this is an issue I’ve commonly seen in the IT industry.

Imposter Syndrome – The persistent inability to believe that one’s success is deserved or has been legitimately achieved as a result of one’s own efforts or skills.

https://www.lexico.com/definition/impostor_syndrome

I can assure you that if you have felt this way before, you are not alone. People in the community who I would consider experts have stated they felt (and sometimes still feel) imposter syndrome. These are people with more experience than years I’ve existed on this planet and they still feel this way. Coincidentally, this triggers my own imposter syndrome when I think about that.

T-SQL Tuesday Topic

I want to read your stories about when you’ve experienced, seen, or overcome imposter syndrome! Was there a job that you felt you were ill-prepared for? Did you make a mistake or did someone say something that made you question if you were a true data professional? Maybe there was a particular task you ran into that made you question your experience? Did you resolve your tasks and succeed in your job? How did you overcome that feeling of being an imposter and solve your challenges? Maybe you haven’t experienced it yourself but you saw someone who was feeling imposter syndrome, were you able to help them?

You can be technical or non-technical with this post, the goal is to share experiences to help those also experiencing imposter syndrome. Maybe you are still feeling it, sometimes walking through your challenges can help you brainstorm solutions.

T-SQL Tuesday #121: Gifts received for this year

Invitation and summary from Mala Mahadevan

It is December again. 2019 has gone by in a flash. I have the honor of hosting the last TSQL Tuesday blog party of the year. This monthly blog party started by SQL Guru Adam Machanic since 2009 has completed 121 months this year. I am the lucky host of event #121. If you are participating in this month’s party (kindly coordinated by my dear friend Steve Jones (b|t) – please be sure to read the housekeeping rules all the way below that are necessary for participation.

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? Some examples as below –

  • Getting to know someone in the community better,
  • Getting to speak at an event you always wanted to,
  • Attending a conference or training that you always wanted to attend,
  • Landing a job you never thought you would,
  • Published a book that you wrote,
  • Wrote for sqlservercentral/simpletalk/any of those cool websites
  • Got to play with a cool new technology that has you excited,
  • A feature of SQL Server that you always wanted and eventually showed up in 2019-I love lightweight query profiling to track query progress, for example.
  • A new cool feature that you never even thought possible is now there (I was just oooh-ing about how easy it is to script objects in Azure Data Studio, and how nice it is to have it store my query history for me).

T-SQL Tuesday #120 – What were you thinking?

Invitation and recap from Wayne Sheffield.

Ahh, November. The PASS Summit is kicking off tonight (with several great precons going on yesterday and today). Thanksgiving is right around the corner (for everyone in the United States). Right after Thanksgiving are the Black Friday and Cyber Monday sales. And since this is the first Tuesday of the month, it’s time for another T-SQL Tuesday. The brainchild of Adam Machanic (b|l|t), and designed to strengthen the SQL Server blogging community, T-SQL Tuesday gets a lot of bloggers posting about a specific theme, chosen by the host blogger (today, that’s me). And something that is really neat is that this month wraps up the 10th year of these T-SQL Tuesday posts. Wow!

Not too long ago, I ran across a situation where I was scratching my head, wondering why something had been implemented the way it had been (you can read about it here). And that gave me the idea for this T-SQL Tuesday topic.

In this month’s T-SQL Tuesday, I want to know about things that you’ve seen others do in SQL Server that have left you wondering “What were you thinking” (maybe even with a few #$%^& thrown in)? Tell us what you saw, why you thought it was so crazy, and what you did about it (if anything). And please… just tell us what you saw, not who you saw doing it.

T-SQL Tuesday #119 – Changing your mind

Invitation and write-up from Alex Yates.

Bringing people together

I’m excited about DevOps. I first heard the term as a sales person at an IT company. I recognised the gulf between the sales and tech silos at my company and I could observe conflicts with many of my customers between developers and DBAs. I had a lightbulb moment when I realised the potential – if you could just get all these different people and teams to work together effectively with a shared vision.

I’m also increasingly aware that we aren’t just conflicted in our work lives. I live in the UK and my society is increasingly polarised. I know the same is true in lots of other places. Our community tends to communicate through social media, most commonly on Twitter, where we create echo chambres for ourselves as we follow people who share our views and we consciously or unconsciously unfollow or block anyone who disagrees with us. Even if we try to avoid it, the algorithms tend to show us the content we like to read anyway.

It seems to me that at work, online, and in society at large we are becoming more stubborn and less open to exploring ideas that challenge us. It’s my belief that if we were all (myself included) more open, not just to talking, but to genuinely challenging our existing ideas, we would all benefit. I believe that’s true both in our professional and our personal lives.

The 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?

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 mands, it will help us to have better conversations in the future. I hope this will help us to work together more effectively at work – and maybe in other parts of our lives as well.