T-SQL Tuesday #130: Automate Your Stress Away

T-SQL Tuesday
T-SQL Tuesday

This month Elizabeth Noble (blog|twitter) hosts the #TSQL2SDAY event (see the invitation) and would like to know how did you make your life easier by automating thing.

Paper age

There was this place I just started and wanted to request a day off. I asked people around how to do this, what system do we use. The answer was quite shocking as I have been told we had a paper form that we needed to fill out by hand with the dates and number of days and all the personal details – every single time you need time off.

I have never thought I can experience that, so my internal digital soul strongly opposed to it. I have prepared a template in the Excel spreadsheet where one could simply put all the details once and save it. I have added bank and national holidays to the exclusion list, so no one ever had to calculate the number of days. Just give the start/end dates and export to PDF to mail and print out (the paper was still required, but at least there was no need to use pen and hand-writing (yuck!) ).

That proves that not only things from your domain can be improved. Also, it was one of my first improvements at the place, so one should not be intimidated to bring new ideas and solutions to make everyone’s life easier.

Next, Next, Next

I remember when I have been put in charge to prepare a new SQL Server installation guide. It was brand new SQL 2008! The goal was that any person would able to install the SQL Server (without SQL knowledge).

Creating a guide was fun, but it was missing something. Something… magical. I found it when I finally had to install multiple SQL Server instances and despite the guide being straightforward you would need to click a lot of Nexts and bunch of OKs. At different place, we had to build several new test servers as the existing ones broke down. Finally, a request to build couple sets of two-node AGs before the end of the week.

All those scenarios from different places, but the same problem – install SQL Server quickly (the cloud was out of the question), and my solution was the ConfigurationFile.ini. After spending some time on crafting all the parameters and understanding what is going on I had a fancy new ConfigurationFile for our needs Getting a new SQL Server installed was a matter of few minutes (the underlying infrastructure was already prepared).

As you can see, solution that worked in one place, can be potentially repeated in two, three and maybe even more places.

Reports sent to nowhere

The last scenario is not much of automation, but making life easier by simply asking here and there. One of the routine tasks was to get some data, save it as an XLS file with a maximum of 60k rows, and mail to the user. The report might even have 600k rows, which means ten XLS files.

I have got curious why we have this limit and what happens with the process once we send the data. The reason for the limitation is simply the old version of Excel. As for the process, the user was forwarding the email further, where someone was saving files to the network share and merging them into single file (sic!).

After a few chats, we decided to get the XLSX file (better limits and compressed already) out of the database (without the need of slicing it into smaller batches) and saved straight on the file share. Saving time and eliminating unnecessary work. Next phase, the SSIS 😉

Final word

These are examples from the real world, but not necessarily highly advanced solution. However, even these solutions save tons of time and frustration from doing the same work over and over.

When there is something to be improved, improve – smallest things can add up. Does not need to be ideal in your first iteration, it will look better eventually.

Thank you,

1 Comment on "T-SQL Tuesday #130: Automate Your Stress Away"

Leave a Reply