TSQL-Tuesday – Automate Your Stress Away

TSQL-Tuesday – Automate Your Stress Away

Elizabeth Noble is hosting this months TSQL-Tuesday, and she asks a great question: How do you automate stress away from your life?

I love automation, not because automation is cool in itself (although it is) but I love it because of all the side benefits it gives you.

It means I stuff up less often

Do you need to run a process every month? Do you need to generate a report every day? Have you ever forgotten? Have you ever done it wrong? If I need to produce something on a schedule, I want to automate that because then I can’t do it wrong.

It means I can take a holiday

Do you have a task that relies on what is in your head? Do you have a task that only you can do? While it’s nice to feel needed while you’re at work, it sucks when you don’t want to be there. While setting up Chef on Test servers didn’t do this for us, other automation projects have meant that I don’t need to be contacted to do a standard task. If I’m on holidays with my family, I don’t want a call saying “Everything is on fire, we need you!” I want an email when I get back saying “Everything was on fire, but we solved it.”

I’ve heard some people say they are afraid of automation because it’ll take their job away. If the only reason your boss pays you is because of that one task, then maybe you should be scared. But for most of us, if you automate something, your boss is not going to get rid of you, they’re going to give you more work.

Both of these things benefit me. But does automation help my employer?

One automation project was a few years back at the company I worked for at the time. We needed to build around 20 new Test servers, needing to install SQL Server, configure IIS, and a bunch of other services. We could have done this manually, but we decided to automate it using Chef.

Doing this work was nothing earth shattering, it wasn’t like anyone hadn’t automated building of servers using Chef before. And arguably, it wasn’t that much quicker than setting up each server manually. By the time we worked out how Chef worked, and wrote all the ruby scripts we wanted, it probably would have been almost as quick to just do the work. So was it worth doing? Absolutely.

The process of automating a task like setting up a server gives you a few key benefits:

It saves time in the future

I’m not going to lie, setting up Chef for the first time takes a lot of effort. I could imagine that it’d take more time than just doing the task. But what if you have to do the task twice, or five times or 100 times? We had some of those Test servers fail. What did we do? We asked the network team to just build a new OS for us. Because it only took a few minutes of actual work for our team to bootstrap it to Chef (the one bit of documentation we had to keep), we didn’t bother trying to solve problems for more than an hour. Just chuck it away and start again. Containers make this even easier, but a lot of companies aren’t there yet.

It reduces maintenance time

My favorite part of the Chef environment was that we could easily automate the installation of Windows and SQL patches. If WSUS could deploy it, we installed it. When the server team asked us to patch our servers, we just responded that it was already done. The servers only had to run from 9 to 5 on weekdays, so a couple of nights a week or over the weekend, we had them check in with WSUS and install the updates. How much time do you or your team spend on installing updates? Do you have to do it after hours? I don’t want to work after hours, so if I can automate that to run overnight, I’m going to do it.

It encourages better design

What stopped us from using Chef in production was that our Chef scripts could take servers offline, and if a server went offline then it would impact users. By having a mindset of wanting to automate away all the jobs, it encourages you to think about how to keep your system online while the automated job runs. Do I need to automate a failover? Am I running Availability Groups in SQL and so I can failover without downtime? Even the application code, is a user tied to a single server, or can they hit a load balancer and run with a node offline?

IT Teams should be working to keep their companies online 24/7 wherever possible, but when you throw in automation, it makes it simpler. If redundancy mean I need 5 servers instead of 1, I can set that up in almost no time by using automation. If I need to change a setting before updating a service, I can automate that it always happens.

It provides accurate documentation for building your environment

Have you ever tried to setup a new system, but can’t remember where the install files where? Have you ever thought you got all the bits in the right place, but when you use it you get an error? Have you ever seen the error and then thought “How did I fix that last time?”

You could argue that we should fix it with documentation, and you’d be right. We could have a document that lists out every step and then you follow the document. But how often is documentation actually right? How often does it get updated when you have a change? How often does it get fixed when someone adds a new step? How often does it get used to validate that it’s correct? If you have worked at places like I have, the answer is usually that the documentation is not good enough. It might get you 90% of the way there, but then you need to remember how to fix the last bits, or find the right person to talk to.

What if you had documentation that was always up-to-date? What if the documentation was not written by someone with screenshots that you had to follow, but rather written in a way that you just run a process and the computer does it for you?

This is what Chef does, if you put in the work. If you can make sure that the only way that new services are added to the server is via Chef, then Chef is living documentation. Want to know where we install from? Check Chef. Want to know what version of a product we use? Check Chef.

This was just one automation project that I have been involved in. I love automation of any task that you have to do more than a handful of times. Sure, it can cost to get it setup, but the benefits in the long term almost always outweigh the upfront cost.