Simplify and automate

It's only 2PM when I write this post, but the title already saved me a ton of time - in fact, enough to easily take a break to post this.

After reading this post, I want you all to have a simple takeaway: If you can simplify and automate some process, you should probably do it. In fact, there is a saying within sysadmins of Reddit - "If you did it more than once, you should automate it". It is very applicable in software maintenance, where we already have to write scripts to do our job - so why not write them a bit smarter, so they can do our job for us without taking any of our time?

What happened?

An error occurred in integration between our client and systems in the other company (let's call it company B). It wasn't a critical error - but some information wasn't received on their end, which resulted in further problems that could lead to potential money loss - so the worst situation possible in corporations.

Now, this kind of situation happens now and then, but usually on a much smaller scale. Once a day, maybe, a single row of data fails to be delivered properly, and has to be re-exported. I didn't automate the process of re-exporting yet, but here comes the first lesson.

Simplify.

A lot of my work is done on SQL Server databases, and a lot of it is executing identical scripts with different input data. The most important thing I learned is to always save these scripts somewhere on my computer in the purest form possible, so that if I ever need it again, I can just copy and paste some numbers as an input and send it to execution - without losing an hour of time to write the same script I wrote a week or three months ago.

My simplification of this problem is to keep the template for re-exporting data to company B. I have two scripts - one to check what data need to be exported, and the other for exporting. This way, all I need to do is run the first with a single number as an input, copy and paste data to the other script, and then run it. It's a few hundreds lines of SQL code every time, but it takes me approximately one minute or less to complete the task, which otherwise would take up to an hour. I plan on making them one script, but lack of time is preventing me so far from fulfilling this plan.

Tip: If you work with other people in the same field, and you use the same scripts, you can easily symlink a directory from a shared location to a templates' directory of SQL Server Management Studio. Following snipped is for Windows, but it can easily be reproduced on other OS'es.

mklink /d //networkShare/DirectoryName /SSMS/TemplateDirectory

This way you can easily add, edit or delete scripts, and your whole team always has the newest version.

Automate.

If you already have some fields where you always use the same scripts with different inputs, more often than not you can write just a bit of code, and the script can be fully automated. In my case, automation isn't full and complete yet, but it's already saving me a lot of time, which otherwise would have to be spent on boring and repetitive tasks.

My automation for this problem is simple: Once I re-exported the data to company B systems, I still have to occasionally check if everything has been properly processed. This is the most boring part - every day I had to run the same scripts about ten times to check each row of data, and if processing of any is finished, pass the info to the client that we're good.

All I've done was to write a piece of code to read rows of data from a TXT file and run the query for each of them. If processing of any file is complete, code emails me with the number of tasks in Jira and ID of data which was processed (as sometimes there is more than one ID in Jira task). After that, row is deleted from TXT file, and it's work there is done.

All that took me about one full day to write, which I've done over the weekend, and then another day to set up and test on my work computer. Simple automation, but it's nice to wake up and see that your work is done for you.

How it helped?

Today an error occurred on some 40+ rows of data. Thanks to my simplification, it didn't take much time to re-export them to company B, and it won't take any of my time in coming days to make sure that everything work's swimmingly on the other end. Win-win, isn't it?

Summary

• If you do it more than once, automate it.
• Save every script you run on your database - there is a big chance you will need it again.
• Use applications and functionalities that can make your life easier, like Template Library in SSMS.
• If you find yourself often running the same script with different input, there is a big chance you can automate it.

PS. This post was inspired by Andrzej Krzywda's and Arkademy's Blogconf and #5days5blogposts challenge. I recommend checking him out, and checking what Arkademy offers in terms of courses and sharing knowledge about programming - not only about code per se, but also about just making what you create better in every way.