Hello and welcome to my T-SQL Tuesday contribution for this month, based on automating a complex Database refresh.
For those of you who want to know more about this month’s T-SQL Tuesday topic “Automate all the things” you can click here.
I’ve been a big believer of automation for a long time now. The main reasons for my love are that automation helps to make things go faster and that it reduces the chance of human error.
The assignment of this month T-SQL Tuesday is to discuss two separate things:
- What do you want to automate or what automation are you proud of completing?
- What is your go-to technology for automation?
1. What do you want to automate or what automation are you proud of completing?
For the first part I had to make a tough decision. It involved a choice between something I improved in the past or a very ambitious development pipeline I’m currently involved with.
Eventually I decided to discuss something I improved in the past.
Previously, I was asked to investigate improving the refresh of a test environment from production. It was a long process with many manual steps that took days.
Existing Database refresh process
It certainly was complex and included database schema comparisons, updates and encryption. In addition, it also had to manipulate data based on results of other DML statements. During my review of it I was told it was too complex to automate.
Challenge accepted to automate complex Database refresh
Therefore, I decided to take this on as a challenge. Whenever I got the chance I would look and try to see where the automation of the load could be improved.
After some tweaking and testing I managed to automate a manual process that took days into hours. I done this by collecting all the steps involved and then putting them into a long SQL Agent job. As a result it consisted mostly of a combination of T-SQL and Powershell.
Afterwards I improved the speed of the steps that took a long time. To check if any of the steps were redundant and could be removed, I talked to other people in the business.
To conclude this part, I’ll leave you with something to think about. I hope you bare the following in mind when you look to automate things in the future.
Automating something is certainly more than just using a scripting language. It’s also about other things like looking to see where you can improve the performance of a process and removing redundant steps.
2. What is your go-to technology for automation?
At this moment in time I certainly have multiple answers to this question.
Automating complex tasks using SQL Agent
Previously, I did a fair amount of automation by breaking down the steps that were required in a manual process and converting them to SQL Agent Job steps.
However, as time went by I started using Powershell more to do more complex tasks in these SQL Agent jobs. Times are changing, but there are still some people out there who work with SQL and would benefit from doing this.
Automating complex deployments using ARM Templates
Sometimes the technology I use for automation depends on the current situation. For instance, I learnt about automating Azure deployments using ARM templates in depth.
It is something I have used in the past for Azure deployments, and something I strongly suggest looking into. Especially now that infrastructure as code is being heavily pushed.
Currently
I’m currently involved with Powershell, Azure DevOps and other applications used by a client. In addition, I started working on automating deployments within Azure.
For instance, I have installed Azure DevOps Server 2019 RC1 because I am working with deployment pipelines. Subsequently, you can read my post about it here.
Certainly, I will post about any of the potential stuff with deployment pipelines I might be involved with this year. This post could be an eye opener for some as it probably includes a handful of other technologies.
DBATools
With confindence I can say that I’m not the only one that is using DBATools. Therefore, I’m sure others will be discussing DBATools. I definitely recommend using DBATools as it has many useful commands.
Final word
Anyway, that’s it from me.
I hope this post about automating a complex Database refresh helps others and gives some an insight into how the industry is changing.
Be First to Comment