T-SQL Tuesday #130 – Recap: Automate Your Stress Away

T-SQL Tuesday Logo - A blue database with a calendar showing days of the week wrapped artfully around the database.

This month, I hosted #TSql2sDay for September 2020. If you’re unfamiliar with T-SQL Tuesday, it’s a blogging party. The idea is a host comes up with a topic and encourages other members of #SqlFamily to join in and write a blog on the topic. T-SQL Tuesday was stared by Adam Machanic and Steve Jones has carried on this fun tradition!

I’m really grateful for all the bloggers that took part this month. Especially since automation has been a topic discussed before. However, it’s hard for me to get too much of my day to day work automated. And I was really looking forward to these topics so that I could learn new tasks I could automate myself. With that said, let’s see all the wonderful ideas people contributed this month. And if you’re like me, you’re going to want to put some of this automation in place as soon as possible.

I’m right there with Greg Dodd regarding automation. Greg blogs about how automation can be a great way to minimize making mistakes. I also like how Greg points out that automation can mean we can all take a vacation without worrying that someone will call us with an emergency. Greg’s done some pretty interesting stuff including automating the creation of test servers using Chef. Greg keeps things realistic by stating that often automation is not a time saver at first, but over time you can really see the benefit.

Eitan Blumin is very familiar with automation, and Eitan has blogged about the topic extensively. As such, Eitan approached this month’s topic with a different perspective. Instead of discussing a project using automation, Eitan walks us through what steps we can use to start automating tasks around us.

This was Magda‘s first blog post ever (yay, Magda!), and I’m so glad that Magda joined in the fun to write about how we can all use automation. Magda might describe themselves as a non-technical worker, but I think Magda’s approach to automation is the same as the rest of us. Magda found that some manual tasks in Excel could be improved with automation. I’m also very glad that Magda reminds us all to celebrate the victories… and even a cup of tea with the time you’ve saved automating your work.

Kevin Chant lets us know what benefits he’s found with automation, which includes making our lives easier. Kevin shares information about the first steps you can make in creating your first Azure DevOps pipelines. I really like how Kevin reassures us all to not get stuck in perfectionism paralysis. Kevin lets us all know that sometimes its ok to have an idea and implement a proof of concept quickly.

We each have a preference when using SQL Server Management Studio (SSMS). Some of us prefer to write the code outright and others prefer the GUI. Rob Farley reminds of us one of the option available in SSMS. We can use the GUI to create a T-SQL script for you. You also have this option with Azure Data Studio (ADS) for many (but not all) actions.

When things go wrong, one of the more tedious tasks we must undertake is reviewing error logs. Aaron Bertrand shares his solution on how to aggregate various error logs using PowerShell. I think the idea of combining various error logs can help us all. There is even a copy of the current version of code available on the blog post as well as a link to the source control so you can keep up with any changes as Aaron improves the code over time.

Another common issue in our day-to-day life is keeping things as they are because they’ve always been that way. I know I am all too guilty of this at times. In this month’s post, Mikey Bronowski gives examples of manual tasks that need some rework. Mikey covers how to get rid of paperwork and standardize SQL Server installations. Mikey found an outdated process due to old technology and after some internal conversations, Mikey updated the process to use newer technology and saved time.

One of the common tasks we often must deal with is patching our SQL Servers. It is one of those tasks that is monotonous but also requires additional analysis. Volker Bachman shares how to use PowerShell to control when and what updates are being deployed to your servers. I love that Volker has also included the functionality to automatically send an email if the server is restarted. That can really save time troubleshooting a server restart the next business day.

Richard Swinbank found that sometimes that you may want to generate database objects based on tables already in the database. Using metadata and dynamic SQL, Richard shows us how we generate scripts that will create multiple database objects. I like how Richard took a problem dealing with multiple different systems and found a way to automate a way to create consistent and reliable code.

One of the areas I haven’t spent much time with in SQL Server is trace flags. However, I completely understand wanting to ensure that trace flags are applied consistently across SQL Server instances. Taiob Ali shows us how we can keep trace flags consistently applied. With startup procedures and configuration tables, we can decrease our stress and know that our SQL Servers are configured as expected.

Every environment is different and each have their own challenges. For Deepthi Goguri those challenges include managing hundreds of servers with multiple versions of SQL Server. Deepthi has found that PowerShell is the right tool for the job. With PowerShell, Deepthi has saved hours making job owners, SQL Server editions, and recovery models consistent. Thank you, Deepthi for writing your first blog post for #tsql2sdy.

Another first time T-SQL Tuesday blogger is Travis Page. Travis is also using PowerShell to help automate stress away. I’m not new to database source control, and I want all the things in source control. Travis is tackling one aspect of source control that can be quite tricky. Using dbatools, Travis has found a way to put SQL Server Agent jobs into source control.

I’ve recently started using Azure this year, and Drew Skwiers-Koballa‘s blog post addresses the issue of how to save money in Azure. One common pitfall can be creating resources and forgetting to clean them up. Drew shows us how to use PowerShell Workflow runbooks to automatically delete resources. I also like how he showed us not only how to deallocate resources but also introduced me to Azure Automation.

One of the things Database Administrators are tasked with is ensuring that the data for our company is protected. In this month’s T-SQL Tuesday, Gregg Barsley shows us how to use dbatools and PowerShell to generate an audit file of user permissions to SQL Server. This is one of those topics that I never knew I needed; now that I’ve seen it, I know I have to have it.

Among many other things, knowing that our SQL Server instances are properly configured and running peak efficiency can really help reduce stress. Glenn Berry shows various options for how we can automate our health checks. You have options to how to run Glenn’s diagnostic queries that include PowerShell and Jupyter Notebooks. As a bonus, Glenn also shared how you can setup SQL Server Agent Alerts for various errors.

Steve Jones mentions that he uses automation to do tedious work so that he can focus on problem solving, and Steve is busy solving various problems. He has automated projects ranging from collecting data for SQL Saturday events to deploying development databases. This kind of variety shows the power available with automation.

We database people love our backups. And aside from testing our backups many of us have found that developers often ask for fresh data from Production. Hugo Kornelis found himself in that exact situation. Using T-SQL, Hugo was able to create a script to backup a database from one server and restore it to a location and name of his choosing.

I’ve been looking forward to this month’s T-SQL Tuesday for quite a while because I was so eager to see what you are were automating. One thing I did not expect to see was Rob Sewell‘s post. Rob has a creative solution for how to easily get the details about SSIS failures. I’m not surprised that Rob is using PowerShell, but what is really cool is that Rob is sending the detailed messages to Teams!

I’m glad that Josh Handler could also join us a first time #tsql2sday blogger. As a data professional, there are some times where we can take our time performing a task and other times where we need to get things done quickly. Josh writes about one such tasks which involves installing SQL Server fast. By using the SQL Server configuration ini file and some additional PowerShell scripts, Josh has been streamline SQL Server installations.

Mike Donnelly shares his experiences creating a deployment pipeline for Redshift. Mike discusses some common pitfalls that we can all fall into. Sometimes we start out with what seems to be a simple process but as time goes on, we find that a little manual work can become overwhelming. Mike identified the challenges and came up with the solution of using Python to improve the deployment pipeline.

One of the more dreaded things that can happen as a data profession is getting reports that a system is slow, especially hours after the slowness occurred. John McCormack has a solution that can help you find what was running. John shows us how he automated recording query activity using sp_WhoIsActive and SQL Server Agent.

Many of us may use T-SQL are our most frequent programming language. However, Todd Kleinhans has found a new interest with Python as it pertains to Todd’s passion for UE4, Epic Games Unreal Engine. Knowing the power of Python, Todd looks into the usefulness of Python with SSIS for ETL and other projects.

When working with PCI data, one of the most frequent and important tasks involves finding where credit card data may be stored in the database. In this month’s blog, Jason Brimhall shows how to automate detecting credit card data in your database. With just some T-SQL and SQL Server Agent job, you will be well on your way.

Jess Pomfret discusses a frequent task that many database professionals perform to ensure the health of their database environments. Jess shows how she has automated daily health checks using dbachecks. With dbachecks 2.0, you are now able to easily save your results to a database. It looks like I’m going to need to get the newest version of dbachecks!

This was my first time hosting T-SQL Tuesday, and I want to thank you all for joining me. I hope you get a chance to check out all the blogs on this recap post and put some of these ideas into practice.

5 thoughts on “T-SQL Tuesday #130 – Recap: Automate Your Stress Away”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s