Skip to content

T-SQL Tuesday #110 – Automating a complex SQL Database refresh

Reading Time: 3 minutes

Hello and welcome to my T-SQL Tuesday contribution for this month based on automating a complex SQL Database refresh. For those of you who want to know more about this month’s T-SQL Tuesday topic “Äutomate 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:

  1. What do you want to automate or what automation are you proud of completing?
  2. 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 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

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.

Main point

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.

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.

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 blog helps others and gives some an insight into how the industry is changing.

Published inT-SQL Tuesday

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *