SQL DBA Blog

Tag: PowerShell Tips

How to Set Environment Variables in PowerShell
To get environment variables in PowerShell, we can use Get-ChildItem env: to list all the environment variables and $env: to return the value of a specific environment variable. This blog post should help explain this as well as how to set new values for Environment Variables.
An environment variable is a value that is stored on a computer and can be accessed and used by programs to alter their behaviour. For example, in the AWS CLI you can set an environment variable called “AWS_REGION” and assign it a value such as “eu-west-1”. Then, you could have a program that reads this environment variable and uses it to specify the AWS region when making API requests.
In PowerShell, you can access environment variables using a number of different cmdlets. This post covers cover two of the most common methods, Get-ChildItem and Get-Item.
This post will cover the following:
– List All Environment Variables
– Get Value of a Specific Environment Variable
– Set New Environment Variable
List All Environment Variables
To list all of the environment variables on your system, use the Get-ChildItem cmdlet with the Env: drive. This displays a list of all the environment variables and their current values.
You can also run gci which is an alias for Get-ChildItem as displayed in my screenshots below.
```
# List all environment variables
Get-ChildItem Env:
```
Get Value of a Specific Environment Variable
To return the value of an environment variable in PowerShell, run the following:
```
# Get the value of the aws_default_region environment variable
gci Env:\AWS_DEFAULT_REGION

# Do the same but alt (easier) syntax
$env:AWS_DEFAULT_REGION
```
Set New Environment Variable
To set a new value for the AWS_DEFAULT_REGION environment variable with PowerShell, use the following command:
```
# Set the AWS Default Region Environment Variable
$env:AWS_DEFAULT_REGION="eu-west-2"
```
December 19, 2022
How to Delete Files in PowerShell
This is a post on how to delete files in PowerShell, which will be useful when you need to delete files as part of a script in Windows.
This post covers the following:
# PowerShell: Delete a File
# PowerShell: Delete a Folder
# PowerShell: Delete Files in Subfolders Recursively
I have a similar post on How to Create Files & Folders in PowerShell if of interest, and more random tips can be found in the PowerShell Tips Tag.
PowerShell: Delete a File
Remove-Item is the cmdlet to remove a directory or file in PowerShell. We can delete files within the current directory, or add the path parameter.
I’m showing some syntax variations below. If you are passing a directory with spaces in the name then you will have to add quotes to the path.
```
# Delete file in current directory
Remove-Item -Name testFile.txt

# Delete file in specified directory
rm -Path C:\temp\demoFolder\testFile.txt

# Delete all files in specified directory with .tmp file extension
rm "C:\temp\*.tmp"
```
PowerShell: Delete a Folder
We do not need to change any parameters to delete a file, just run the same command as above. The consideration when deleting folders is the files within them.
The following demo script first deletes a folder within the current directory, and the second part checks if folders exist before deleting them.
```
# Delete a folder in current directory
rm .\demoFolder\

# Delete folder if exists, force delete all files in folder
# Does not delete files in sub-directories
$path = "c:\temp\demoFolder"
If((Test-Path $path) ){
    rm $path -Force -Recurse
}
```
PowerShell: Delete Files in Subfolders Recursively
For when you need to delete files in a folder and subfolders, the script below should help. Amend path and file type wildcard as needed.
```
# Delete all .txt files in folder & subfolders
$path = "c:\temp\demoFolder"
If((Test-Path $path) ){
    Get-ChildItem $path -Include *.txt -Recurse | rm
}
```
Last additional note on this, I haven’t used gci within any of the PowerShell scripts above in this post. It’s an Alias of Get-ChildItem.
September 11, 2022
Create a New Firewall Rule with PowerShell
Create a New Firewall Rule with PowerShell
This is a short post to share a PowerShell script that will create a new Windows Firewall Rule on a local computer.
PowerShell Create New Firewall Rule
We have to ensure that we run PowerShell as Administrator for this to work.
```
# New firewall rule (run powershell as administrator)
if (-not( Get-NetFirewallRule -DisplayName “Allow Inbound SQL (1433)” -ErrorAction SilentlyContinue)) { 
New-NetFirewallRule `
    -DisplayName “Allow Inbound SQL - 1433” `
    -Direction Inbound `
    -Protocol TCP `
    -LocalPort 1433 `
    -Action Allow }
```
The above is creating a new firewall rule so incoming SQL Server (1433) traffic is allowed to happen on this computer.
Other similar/useful posts I have on this include the following:
# How to Check Windows Firewall Status with PowerShell
# How to Enable Windows Firewall with PowerShell
# How to Disable Windows Firewall with PowerShell
September 5, 2022
How to Check PowerShell Version
This post is a guide on how to check your PowerShell version on a Windows computer. I’m using $PSVersionTable to get this info.
It’s important to keep all software as up-to-date as possible, we all know it. Being on the latest PowerShell version is especially required if you are utilising the latest features/cmdlets. For example, you might one day attempt to copy and run a script, and realise that it contains a command that is not recognised on a lower version of PowerShell.
To check your PowerShell version, it’s as simple as running PSVersionTable on its own.
```
-- Check PowerShell Version
$PSVersionTable
```
I’m running version 5.1.19041 of Windows PowerShell here.
At the time of writing, version 5.1 is the latest Windows PowerShell version, but not the latest PowerShell version available to us. To get version 7, we can download and install it as per MS Docs: Installing PowerShell on Windows.
Before looking at rolling out upgrades across several Windows hosts, we should look at the current Supported PowerShell Versions for Windows compatibility table from Microsoft.
That’s it for now on this one. Cheers again.
August 31, 2022
How to Automate PowerShell Scripts with Task Scheduler
This post is useful if you need to run PowerShell Scripts automatically with a set schedule in Windows. In the demos below, we’re creating new Scheduled Tasks via GUI and with PowerShell.
Task Scheduler is a Windows application that we can use to create and schedule any task. We can set our Task Actions to start programs, send emails, and display messages, or we can run any script – likely a PowerShell script since we’re on Windows. These Actions can be triggered on any schedule you configure your Scheduled Task to use.
As a Systems Administrator, adding a Scheduled Task in Windows is something we might need to do regularly. As a SQL Database Administrator, we’d use the SQL Server Agent to schedule PowerShell scripts and other tasks. And Linux Sysadmins, they’ll use Cron.
This post covers the following :
# How to Create a Scheduled Task using PowerShell (CLI Option)
# How to Create a Scheduled Task for a PowerShell Script (GUI Option)
How to Create Scheduled Task using PowerShell
First, we need to create the PowerShell script that we want to be executed o a schedule. In the screenshot below, I’m creating an example .ps1 script in my c:\temp directory for this demo.
The script below updates the content of a text file, logging the current Average CPU of the computer with date/time. You could have anything in your PowerShell script, e.g. Restart-Service *ServiceName* or Restart-Computer.
Now, I’m creating a new Scheduled Task that will run the above PowerShell script on a daily Schedule. The following script uses the New-ScheduledTask and Register-ScheduledTask cmdlets.
```
$actions = (New-ScheduledTaskAction -Execute 'C:\temp\PowerShell_Scripts\avg_cpu_collector.ps1')
$principal = New-ScheduledTaskPrincipal -UserId 'Administrator' -RunLevel Highest
$trigger = New-ScheduledTaskTrigger -Daily -At '8:05 AM'
$settings = New-ScheduledTaskSettingsSet -WakeToRun
$task = New-ScheduledTask -Action $actions -Principal $principal -Trigger $trigger -Settings $settings
$taskPath = '\Admin\'
# create new scheduled task as per parameters above
Register-ScheduledTask 'DailyPSCollector' -InputObject $task -TaskPath $taskPath
```
How to Create a Scheduled Task for a PowerShell Script (GUI)
This is the GUI way of creating Scheduled Tasks in Windows. Open the Task Scheduler application to get started, and right-click in the empty area as shown to Create New Task.
Populate details in the General tab as shown in the screenshot below.
Go to the Triggers tab to configure the desired schedule for our new job.
Next, in the Actions Tab click to create a new task:
Action: Start a program
Program/script: powershell
Arguments: -File “C:\temp\PowerShell_Scripts\avg_cpu_collector.ps1”
Have a quick review of the Settings tab and click OK once done.
We can now review our new task in the Task Scheduler main window.
And that’s us sorted. The PowerShell script will run daily at 6 pm as we specified during Task creation.
Feel free to check out the Windows Admin and/or PowerShell Tips tags for more random informational blog posts.
August 28, 2022
SQL Server Default Port
The default port for the SQL Server Engine is 1433, which is a useful thing to know if you’re working as a Database Administrator (DBA) or Database Reliability Engineering (DBRE) type role.
Though-out my career I’ve seen SQL Server run on the default 1433 port 99% of the time. For the other 1 percent, it didn’t matter much to me what port it was using.
Occasionally as a DBA, you will need to arrange network line of sight (firewall rule changes) to be carried out by Network teams for MSSQL host-to-host connectivity. This is done via some request form a lot of the time, and we need to send source/destination addresses. Including IP addresses, we would also need to verify which port the SQL Server is using. Always best to double-check which MSSQL is running before submitting these types of requests, the demo below will help get this info.
We also need to consider the local Windows Firewall on each SQL Server host. The rule should be scoped to the individual IP connecting, or subnet (10.20.30.0\24, all 256 addresses).
SQL Server uses a lot of ports for various features & services, e.g. SQL Browser 1434 / SSIS 135. For anything beyond this basic SQL port info, MS Docs will provide you with what’s needed – there’s a lot, which I’m currently digesting at the time of writing.
Anyway, with network stuff out of the way, this post contains the following demos:
# How to Check Default SQL Server Port
# How to Add New Windows Firewall Rule for Default MSSQL Port (1433)
How to Check Default SQL Server Port
Open SQL Server Configuration Manager, which is accessible via the Start Menu on any SQL Server host.
Navigate as shown on the screenshot below, to SQL Server Network Configuration > Protocols for MSSQLSERVER > TCP/IP Properties > IP Address Tab –
You can amend ports here too, but read MS Docs and test first if doing on Production, of course.
How to Add New Windows Firewall Rule for Default MSSQL Port
This is an added note on how to add the SQL Server 1433 Port & SQL Server Browser 1434 Ports. We can use the GUI >
And we can do the same with PowerShell, using the New-NetFirewallRule cmdlet.
```
# Add SQL Server Local Firewall Rule
New-NetFirewallRule -DisplayName "SQLServer default instance" -Direction Inbound -LocalPort 1433 -Protocol TCP -Action Allow
# Add SQL Server Browser Local Firewall Rule
New-NetFirewallRule -DisplayName "SQLServer Browser service" -Direction Inbound -LocalPort 1434 -Protocol UDP -Action Allow
```
I hope this was a useful SQL Tip. I have another post that relates to Windows Ports- How to Find Which Process is Listening on a Port in Windows
August 26, 2022
PowerShell Restart Services on Multiple Hosts
In this post, we’ll walk through a script that restarts services on multiple remote hosts using PowerShell. A single PowerShell command will be run on several computers at the same time.
If you need to run a command on multiple remote machines, the PowerShell Invoke-Command cmdlet is a great option. We use this to send commands for execution on remote computers.
The command being passed in this demo is restarting the SQL Server Agent Service using the Restart-Service.
Restart Services Multiple Hosts with PowerShell
To use the script, simply populate the ComputerName array with your hostnames and then run the script. Here’s an example:
```
# Restart service on multiple hosts
$parameters = @{
  ComputerName = 
  	"Hostname1",
  	"Hostname2",
  	"Hostname3"
  ScriptBlock = { Restart-Service *SQLSERVERAGENT* }
}
Invoke-Command @parameters
```
The ScriptBlock argument can be amended to run a different PowerShell command, or you can add error checking if you’re building something more complex.
Remember that you should always be careful when running scripts that modify services on remote hosts. It’s a good idea to test the script on a single host before running it on multiple hosts to ensure that it works as expected.
As always, hope to follow this up with more of the same random blog posts. Feel free to check out my other PowerShell Tips.
August 24, 2022
PowerShell ForEach Loop Tutorial
Looping is a fundamental concept in PowerShell, and in programming in general. It’s needed for various situations where we need to work with one object at a time within an array/collection of objects.
Microsoft’s documentation on this, about Foreach : Microsoft Documentation, describes Foreach to be for ‘stepping through (iterating) a series of values in a collection of items‘ – always appreciate a good technical description.
This post contains some basic examples of the ForEach Loop in PowerShell:
# Basic ForEach Statement
# ForEach with Get-ChildItem
Basic ForEach Statement
We can hardcode anything into this array below, or populate it from somewhere else.
This is one of the most basic examples as shown in MS Docs, we’re iterating through each letter in the letterArray, writing to the terminal the value foreach loop.
```
$letterArray = "a","b","c","d"
foreach ($letter in $letterArray)
{
  Write-Host $letter
}
```
ForEach with Get-ChildItem
The PowerShell script below performs a write-to Console for each file in the demoFolder Directory.
I’m running this twice, the second time navigating out of the demoFolder.
```
foreach ($file in Get-ChildItem)
{
    Write-Host $file
    Write-Host $file.length
    Write-Host $file.lastaccesstime
}
```
The above is also showing us the LastAccessTime Property for each file.
August 22, 2022
How to Check Windows Firewall Status with PowerShell
This post will help you to check your local Windows Firewall status using PowerShell (whether it’s off or on for each Profile).
PowerShell commands follow standards and use verb-noun pairs for cmdlets. The verb at the start of the command describes the action the cmdlet performs, and the noun part is the action being performed. Here’s a list of Common Verbs, another Microsoft Docs link. The place of truth as I call it.
In this demo, we’re running Get-NetFirewallProfile with Format-Table
We’re getting the Firewall Profile status, and also formatting it into a table after a pipe ‘|’ –
```
# get local firewall status powershell
Get-NetFirewallProfile | Format-Table Name, Enabled
```
The Windows Firewall on this machine is enabled, for all Profiles <- have a look at this MS Docs link to know more about Windows Firewall Profiles.
Internal follow-up links:
# How To Disable Windows Firewall With PowerShell
# How To Enable Windows Firewall With PowerShell
August 16, 2022
How to Enable Windows Firewall with PowerShell
This post is contains a demo on how to enable Windows Firewall with PowerShell in Windows Server.
This might be needed if you have discovered Windows Firewall is disabled on your or multiple computers, or you might want to re-enable it after disabling this for a test (see my other post on disabling instead of enabling).
Enabling Windows Firewall with PowerShell can be done by going through the following steps:
# Understand Windows Firewall Profiles
# Get-NetFirewallProfile PowerShell
# Set-NetFirewallProfile PowerShell
Understand Windows Firewall Profiles
Before running any command or script on your machine, it’s important for us to understand everything that it’s doing. In the command below we are disabling all 3 profiles:
# Domain – For when you are connected to a Domain Controller (computer connected to AD).
# Private – For your home or private networks.
# Public – For public WIFI locations such as coffee shops etc.
For more information on this, see this link – Microsoft Docs: Windows Firewall Profiles
Also remember, all of this can be viewed and changed via GUI to help with understandings >
Get-NetFirewallProfile PowerShell
PowerShell commands follow standards and use verb-noun pairs for cmdlets. The verb at the start of the command describes the action the cmdlet performs, and the noun part is the action being performed. Here’s a list of Common Verbs, another Microsoft Docs link. The place of truth as I call it.
In this demo, we’re running Get-NetFirewallProfile with Format-Table
We’re getting the Firewall Profile status, and also formatting it into a table after a pipe ‘|’ –
```
# get local firewall status
Get-NetFirewallProfile | Format-Table Name, Enabled
```
In the example above, the Windows Firewall is disabled, showing as ‘False‘.
Set-NetFirewallProfile PowerShell
We’re switching the cmdlet we ran above from Get to Set here (following on from verb-noun cmdlet descriptions).
To be able to run this; we have to open PowerShell or Windows Terminal as Administrator.
Set-NetFirewallProfile is being executed below, which enables all Profiles of the Local Windows Firewall –
```
# disable local firewall
Set-NetFirewallProfile -Profile Domain,Public,Private -Enabled True

# get local firewall status
Get-NetFirewallProfile | Format-Table Name, Enabled
```
I followed the change by another run of Get-NetFirewallProfile to verify the change.
August 15, 2022