T-SQL Tuesday #110 – Automate All the Things

Standard

Reading Time: 4 minutes

This month’s T-SQL Tuesday is about automating all the things.

I love automating processes, especially when it comes to managing SQL Server, and I think that most processes should be automated.

So technically there are two tasks for this month:

  • What is your go-to technology for automation?
  • What do you want to automate or what automation are you proud of completing?

For me, a process can be automated when:

  • It’s repetitive
  • Takes a long time to execute
  • And needs human interaction

I will go far to automate a process. I’d rather spend a week developing a process to get it automated, than to execute it twice and spend lots of time on it.

What is your go-to technology for automation?

In the last couple of years I’ve automated a whole bunch of processes using different technologies to accomplish that. My go-to technologies for automating are PowerShell, DevOps, tSQLt and dbatools.

You might say; “dbatools is PowerShell right?”, and you’re correct, but this module has grown so much that it has to be mentioned on it’s own.

PowerShell

I’ve been working with PowerShell since version 1. I fell in love with it especially because it filled a hole that we needed something else than a GUI to manage our infrastructure.

PowerShell is my hammer to automate about anything. It crosses multiple domains within the Windows universe, from managing your domains, your users, servers, services.

The PowerShell team has done a great job to make this platform as versatile as possible for anyone to use.

DevOps

This part is still kind of new for me and I find that this technology uses different technologies to accomplish the job.

I used Jenkins and Octopus Deploy within my CI/CD pipeline to automate releases. The thing is that within that pipeline I used several technologies to accomplish that.

Technologies like Groovy for the pipeline script, PowerShell to execute certain tasks, dbatools to execute some other.

I like Jenkins because it let’s me define exactly what I want to do and it doesn’t have a big learning curve.

Octopus Deploy hooks into the part where Jenkins is finished. Creating releases, deploying them to different servers makes it this CI/CD process complete.

tSQLt

This technology has saved my life. I never did a lot of development but I know how to develop a database. In the past I made changes, pushed the changes to a development server and crossed my fingers if I didn’t break anything.

With tSQLt I have the ability to create unit tests for SQL Server databases to test every little aspect of my database.

If a small change in the schema breaks a procedure I will know before I release it.

I will be presenting about this subject in the near future and help people along the way because I think this needs to be used a lot more.

dbatools

Last but not least, dbatools. This project changed my life in several ways.

First, it taught me how to develop in a lage open-source project. To have standards and how to handle commits. I did not know Git that well before I started contributing and now I’m one of the major contributors.

Second, it gave me the ability to help others. Having several scripts laying around, that could easily be transformed to proper functions/commands that could be used by other people. If I had that problem there is bound to be someone else that has the same and I could save this person a lot of headache.

Third, it made my automate a lot of processes for myself. Think of the backup testing command “Test-DbaLastBackup“. This command tests your backup by restoring it, doing a DBCC check and returns the result back to you.
You will know if you’re backup can be restored and if that data within the backup is not corrupted.

If you have not worked with dbatools yet, make sure you start with it today. It will change your life as a database administrator or developer.

Fourth, and this one comes with contributing to the project, is that I met a lot of incredible people from the community. I love the community how they share their knowledge and help other people out.

What do I want to automate

I’ve been working on automating our releases for database development.

This process has taken a considerable amount of time because I had no knowledge and experience in that area.

I have almost finished that process and I’m glad to say that this year I’ll have a CI/CD pipeline with Visual Studio, Git, Jenkins and Octopus Deploy.

My next project will be to implement data masking making it possible to ship databases to other servers and people without exposing production data.

What automation are you proud of completing

These are several projects that I’m proud of:

The pattern is getting obvious. Automate repetitive, pain-staking processes.

Change your mindset to embrace automation and you never want to go back.

I have less and less time to get things done.
My agenda is filled with meetings, project deadlines and we get more and more things on my plate.

Start automating your work today. You have to put in some energy in getting to automate your processes, but you’ll never look back.

 

Deterministic masking with dbatools

dictionary
Standard

Reading Time: 2 minutes

The dbatools module recently got a couple of new commands mask data in their databases.

One feature with the masking commands that was not yet put in was deterministic masking.

What is deterministic masking

Deterministic masking is the process of replacing a value in a column with the exact value across tables.

In example, a database has multiple tables with a column that has first names. With deterministic masking the first name that’s present will always be replaced with the same value.

Let’s assume the first name “Chris” will be replaced with “Jeff”. The value “Chris” will be replaced with “Jeff” in a column, regardless of the table or column name.

This is very important when you’re dealing with a database that is not normalized using relationships like reporting or business intelligence related tables.

How does it work

When creating the masking configuration file you have the option to set a column to be deterministic like this:

deterministic masking config

During the process of masking the data the command  Invoke-DbaDbDataMasking will create a dictionary with the values from the columns that are set to deterministic.

Every time it processes a row it will check the dictionary if the value is already present. If it is, the new value of the the particular column and row will be set to the value from the dictionary.

If it cannot find the value a new value will be generated and added to the dictionary for later use.

But won’t this make my database less secure

You may think that, when you always use the same value every time, that it will be less secure but in this case it isn’t.

This is because the masking command does not rely on any particular key to regenerate the value. Every value that needs to be replaced will get a random new value.

This value is then put in the dictionary and basically has no reference to the old value.

That’s fantastic! But what are the downsides?

With every feature and extra check comes the fact that extra processing is needed to determine the value for a particular row.

Extra processing means that extra time is needed to process the table.

You also need more memory for the process because the dictionary will hold all the unique values from all the columns that are set the to deterministic.

This can lead to a large amount of values when you’re dealing with terabytes of data.

I would advise to use this feature only for columns that really need to be deterministic and not use it lightly across all the columns.

This feature enhanced the command to create some really sophisticated masking strategies.

For more information about the command read the blog post by Chrissy LeMaire has written about the new feature.