T-SQL Tuesday #130 – Automate Your Stress Away

With Elizabeth Noble Invitation, this will be my first post for T-SQL Tuesday. I am very excited while writing this blog post. Thank you so much Elizabeth for sending the invite.

With the current COVID situations, I believe there would be no one on this planet without stress. I wish we had a fast forward stress relief button which can automatically remove the stress from our lives.

With new challenges we face every day comes with new opportunities. While we travel the journey making these challenges into the opportunities, we will have chances to take some smart decisions and actions making our lives easier. Automation is one of them.

As new technologies emerge, automation becoming the key. The tasks which take longer time before can be completed in just few minutes by using automation.

In my everyday job, there are many tasks that I have to do repeatedly across many servers. These tasks can be very simple but time consuming when you are dealing with some hundreds of servers. Automating these tasks can be very helpful. Below are some of the simple tasks which I automated and saved lot of time:

1. We have several databases that are being replicated between different SQL server versions. This includes SQL server 2008, all the way up to SQL server 2016 versions. There are some databases still on SQL 2000 compatibility level on SQL 2008 servers. Sometimes, replicating these databases to other versions of SQL servers is a challenging task. When ever we had to take a fresh snapshot and replicate, distribution job fails with errors related to default setting options(example, ANSI_PADDING set to OFF when the option should be ON) on the .sch files the snapshot generates (.sch file contains the script to create the table and the replication specific stored procedures on the subscriber). It is a very tedious task to go find all .sch files having this setting as OFF and manually change the setting to ON. So, I used a powershell script in the snapshot job added as a second step after the first snapshot creation step that changes the ANSI_PADDING setting from OFF to ON on all .sch files. When we had many databases having the same issue, automating this task saved me from so much stress.
2. Once, I had to change the owner of the SQL agent jobs to sysadmin account on around 300 servers at a time. This is a stressful task if I had to do it manually one by one server. This will take me at least few days to complete for all 300 servers. I used powershell script by using commands to get the server list from the text file so the same SQL query runs on each SQL server mentioned in the list. This took me few minutes of time to complete for 300 servers. Imagine how much time this process would have saved me.
3. There are other instances where I use powershell automation. If I had to give permissions for logins on multiple servers at a time, automating this task using powershell really helps.
4. I had to convert around 200 servers between SQL server editions with out losing data and the databases. I used powershell script to do this task. If I had to do this manually by uninstalling and installing different edition takes me so much time and effort.
5. In my work environment, there are many non-production servers. We do make sure all these databases are in simple recovery mode. Developers do create and drop databases on non-prod environments for their testing purposes with full recovery mode. I created a SQL agent job which will check the database recovery modes and send me a report of database names which are not in simple recovery mode. This job runs every day at the scheduled time.
6. I use automation for documentation purposes too. For example, gathering all SQL agent jobs, gathering login permissions etc; from multiple servers using SQL agent jobs.

These are some of the examples of how I use automation in my every day work life. I would like to know how automation is helping others in their day to day work life.