SQLServerCentral Article

Great Power(Shell)...Great Responsibility


Whenever I'm spreading PowerShell love (here with you mainly in the form of dbatools), now and then I try to remind people that this is so powerful for the good, but also for the bad stuff. Normally I say "Now, the good work can scale...however, remember - errors will scale too".

What do you mean?

It's part of human nature that whenever we learn something new and we are excited about it, we start applying it in more and more situations. Even though we gain confidence, we (can/will) start making mistakes.

Hey, no big deal! Mistakes happen. They are part of the evolution of learning, and as technical people, we like to break things so we can check if we can fix it. Right? Putting your hands on the mud ultimately is the best way to practice, gain confidence and improve!

Albert Einstein once said, "A person who never made a mistake never tried anything new."

So build your lab to run tests, make yourself comfortable with PowerShell, and re-use in your day-to-day work.

Automation - Generally speaking

You only need to forget about a parameter or provide the wrong value to take down a whole infrastructure - Read the "Root Cause Analysis" about GitLab outage. Using the SQL Server world as an analogy can be similar to run a DELETE without the WHERE clause.

With PowerShell, you can and should leverage on the -WhatIf parameter to be sure that you will be running and affect what you expect.

Start "small" with the Get-Dba* commands

When I share what's possible with PowerShell, and more specifically, show some dbatools magic, the "WOW" factor appears and that makes people feel empowered to do stuff, to bring their ideas that until now were just on their head! That is awesome!

When I see/feel this enthusiasm, I normally put a break right away. Seems evil right? But it's for their own good. If you don't have experience or feel comfortable with something, study more, read more (read the help!), brainstorm more and then try more.

That said, start by using the "Get-Dba*" commands. These commands are harmless, and by that I mean, they don't make any changes. You are just getting/obtaining/acquiring/reading something from your system. As of the time of this writing, dbatools has more than 200 Get-Dba* commands.

Let's see a couple of examples:

Get a list of databases within one instance

Get-DbaDatabase -SqlInstance .

If we peak at one of the outputs, we can see which default properties are being returned. This is a good start, you run a command to obtain information about your databases on a specific instance.

Listing Logins

Let's see another example, do you want to check a list of logins you have on your instance?

This seems a little bit noisy. In this case, let's go a step further and get the list but excluding some logins like the ones which name start with "##" or "NT". Let's check if we have a parameter that we can use.

Remember that I told you to use the help more often? Here is a good reason.

Get-Help Get-DbaLogin -ShowWindow

With '-ShowWindow' switch (only on Windows PowerShell and on PowerShell 7. Not available on PowerShell core, v6), a window will appear and you can even leverage on a nice "Find" box. Start typing and it will highlight the results. Look! There is a '-ExcludeFilter' parameter that accepts wildcards patterns.

Let's leverage this parameter:

Get-DbaLogin -SqlInstance . -ExcludeFilter "##*", "NT*"

This will return something like:

Again no harm as we are just getting information!

The Next Level

After you leverage on the non-harmful commands to get familiarized with parameter names, types, objects, info returned, etc., you will start feeling much more comfortable giving the next step and start using other verbs that (might) imply changes like the Set-Dba*/Add-Dba*/Remove-Dba*/etc. commands.

Let's see an example of how things can go wrong very easily

Remove a login

We can use the 'Get-Command' to find commands that would remove a login.

Get-Command -Module dbatools -Name remove*login*

Again, using the help, we can check the 'Examples' provided.

Get-Help Remove-DbaLogin -Examples

Take a look at the 4th example, we can pipe the results of a 'Get-DbaLogin' to the 'Remove-DbaLogin'.

This is perfect as you can use the previous Get-DbaLogin example, use the '-IncludeFilter' to get only the logins that starts with 'Test' on their names and pass it to the 'Remove-DbaLogin'

Gaining Confidence

But how can we be sure we are just dropping the ones we really want? Because I'm pretty confident in the filter I have done, I'm using the '-Force' parameter to avoid the confirmation messages. However, I have also specified the '-WhatIf' risk mitigation parameter:

Get-DbaLogin -SqlInstance . -IncludeFilter Test* | Remove-DbaLogin -Force -WhatIf

This way we can confirm which logins will be dropped if we execute the same line without the '-WhatIf' parameter.

As an example, let's say that we just discovered that this will, of course, drop also the 'TestX'  login that after all, we decided that was not correct.

This way, I can go back and fix the previous 'Get-DbaLogin' to exclude the login we don't want to remove. Re-run the command with '-WhatIf' to double-check now everything is ok and we can remove the '-WhatIf' to perform the action we want.

"Old news, I already knew that," one said

This may not be new for you or me. But, no matter if you are an experienced professional that uses PowerShell all day, or a newcomer arriving in this life, it's important to remember and learn the basics. That's why sometimes I like to write/talk about the basics (like in this case).

Bottom line

This is just a way to share, using words, what I repeat all the time when I'm in person and this subject comes to the table. As I feel (somehow) responsible for the knowledge I share, I felt that I should write this and share with all of you.

Thanks for reading.


5 (3)

You rated this post out of 5. Change rating




5 (3)

You rated this post out of 5. Change rating