Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Intro to PowerShell and dbatools for SQL Server DBAs (originally presented in August 2018 using the ISE)
#requires -version 5.0
#requires runasadministrator
#region overview
<#
'Practical PowerShell for The Busy DBA' – this will be hands-on (lab + presentation)
- Basic overview of PowerShell and getting started (WMF 5.1 and other versions: prereqs, cmdlets,
transcription, console navigation, and getting quick help/examples)
- Installing Modules with PowerShell 5.0 and up
- Checking SQL Servers for space at volume and database level using dbatools module
- Leveraging CMS & text files for simple loops
Each participant’s progress can be transcripted and shared - hive mind!
# ***Start Your Engines! Launch Windows PowerShell ISE as an administrator***
#>
#endregion overview
break; #Prevent this demo file from being accidentally run if [F5] is pressed...
#region ISE navigation
<#
___________________________________________________
> Administrator: Windows PowerShell ISE - x |
___________________________________________________|
| Script Pane |
|================|
| Console |
[ALT] + V or Click View
[ALT] + V, D Show Command Add-On = displays commands on the right-hand side of the screen
[CTRL] + D Go to Console (also toggles size of console)
[CTRL] + I Go to Script Pane
[CTRL] + R Show / Hide Script Pane
#>
#Verify PowerShell Version
$PSVersionTable
<#PSVersion 5 or greater needed
WMF(Windows Management Framework)
Download location:
https://microsoft.com/powershell
From https://blogs.msdn.microsoft.com/powershell/2017/01/19/windows-management-framework-wmf-5-1-released/
“PowerShell 5.1 ships in Windows Server 2016, the Windows 10 Creator’s Edition, and the WMF 5.1 release.
WMF 5.1 updates previously-released versions of Windows (Server 2012R2, 2012, 2008R2, & equivalent client versions),
with PowerShell 5.1 and includes WMI, WinRM, and SIL updates.”
#Supported Versions
<#
[ALT] + V, D Show Command Add-On = displays commands on the right-hand side of the screen
Drop downs for available modules and their commands
#>
#>
#endregion ISE navigation
#Show me the scripts ! (from this session)
Invoke-Item \\ServerName\ShareName
#region profiles
<#
Variables in PowerShell begin with dollar signs
Some are built in:
Try typing:
$profile
#>
$profile
#"dot sourcing" runs a script in PowerShell
. $profile
#Create profile if it doesn't exist and set an execution policy limited to process scope
if (!(Test-Path $profile)) {
Write-Output "Creating Profile...";
if (!(Test-Path(split-path $profile))) {
mkdir (split-path $PROFILE)
}
"Set-ExecutionPolicy Bypass -Scope Process -Force" | Set-Content $profile;
. $profile
}
#endregion profiles
#region flight recorder transcripts
#Transcripts show your work and are proof that the work was completed
#Transcripts can also serve as breadcrumbs for your future self and others
Invoke-Item \\ServerName\ShareName\Transcripts
$TranscriptPath = "\\ServerName\ShareName\Transcripts\" + ([string](Get-Date -format "yyyy-MM-dd__HH-mm-ss") + "_" + `
([string]($env:COMPUTERNAME)) + "_" + ([string]($env:USERNAME)) + "__PowerShell_Session_Transcript.txt")
<# start transcripting(AKA PowerShellblackbox=ON), save it out to a shared directory #>
Start-Transcript $TranscriptPath
#endregion flight recorder transcripts
#region slide deck
#endregion slide deck
#region Get-Command
# Tab completion is your friend
Get-Command
Get-Command Get*
# Tab completion still wants to be your friend
# Verb-Noun - that's what's happenin'... (singular noun)
Get-Command -Verb Get
# how many was that?
(Get-Command -Verb Get).count
Get-Command *DBA*
Find-DbaCommand *upgrade*
Find-DbaCommand -Tag AG
#What about paramter names?
Get-Command -ParameterName DataFileCount
Get-Command -Module SqlServer
Get-Command -Module dbatools
# above commands splattered blood on your screen?
# No worries! We'll get to that in a second
<#
[ALT] + V, D Show Command Add-On = displays commands on the right-hand side of the screen
Drop downs for available modules and their commands
#>
#endregion Get-Command
#region Get-Help
#This will take a while to run
Update-Help
#Questions so far?
#@Break Time!
Get-Help Get-Command -ShowWindow # [CTRL] + F is a friend
Get-Help Get-Command -Examples # awesome shortcut - just show some usage examples
Get-Help Get-Help -ShowWindow
Get-Help about_Scripts -ShowWindow #Describes how to run and write scripts in Windows PowerShell.
Get-Help about_Comment_Based_Help -ShowWindow # how to write help topics
#endregion Get-Help
#region Finding, Installing, and Updating Modules
Get-Module SQLServer
Get-Module SQLServer -ListAvailable
Find-Module SQLServer
<# Example Output
PS C:\> Get-Module SQLServer -ListAvailable
Directory: C:\Program Files\WindowsPowerShell\Modules
ModuleType Version Name ExportedCommands
---------- ------- ---- ----------------
Manifest 21.0.17099 SqlServer {Add-SqlColumnEncryptionKeyValue, Complete-SqlColumnMasterKeyRotation, Get-SqlColumnEncryptionKey, Get-SqlColumnMasterKey...}
PS C:\> Find-Module SQLServer
Version Name Repository Description
------- ---- ---------- -----------
21.0.17178 SqlServer PSGallery This module allows SQL Server developers, administrators and business intelligence professionals to automate database devel...
PS C:\> Update-Module SQLServer
Update-Module : Module 'SqlServer' (installed at'C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.0.17099') cannot be updated because Administrator rights are required to change that
directory. Log on to the computer with an account that has Administrator rights, and then try again. You can also try running the Windows PowerShell session with elevated rights (Run as
Administrator).
At line:1 char:1
+ Update-Module SQLServer
+ ~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (SQLServer:String) [Write-Error], WriteErrorException
+ FullyQualifiedErrorId : AdminPrivilegesAreRequiredForUpdate,Update-Module
#Are you running the ISE or Console with elevated rights?
#>
Update-Module SQLServer
Install-Module SQLServer
Install-Module dbatools
#Install-Module -Force dbatools # -Force removes confirmation
#cmdlets, functions, and aliases can be manually loaded from a module:
Import-Module SQLServer
#or... the module will be loaded when a command is referenced
Get-DbaService -ComputerName SomeSqlServerName | Format-Table -AutoSize
Get-DbaService -ComputerName SomeSqlServerName | Sort-Object ComputerName, StartName `
| Format-Table ComputerName, ServiceType, ServiceName, StartName, State, Startmode, BinaryPath -AutoSize
#endregion Finding, Installing, and Updating Modules
#region Get-Member
Get-Command -Verb Get -Noun *service*
#pipeline time!
Get-DbaSqlService -ComputerName SomeSqlServerName
Get-DbaSqlService -ComputerName SomeSqlServerName | Get-Member
Get-DbaService -ComputerName SomeSqlServerName | Sort-Object ComputerName, StartName `
| Select ComputerName, ServiceType, ServiceName, StartName, State, Startmode, BinaryPath | Format-Table -AutoSize
#endregion Get-Member
#region Checking SQL Servers for space at volume and database level
Get-Command -Module dbatools *disk*
Get-DbaDiskSpace SomeSqlServerName
Get-DbaDiskSpace SomeSqlServerName | Format-Table -AutoSize
#PercentFree is a property
Get-DbaDiskSpace SomeSqlServerName | Get-Member
#Let's use the property in the pipeline....
Get-DbaDiskSpace SomeSqlServerName | Sort PercentFree -Descending | Format-Table -AutoSize
Get-DbaDiskSpace SomeSqlServerName | Where PercentFree -LT 20 | Format-Table -AutoSize
# CMS Server? SSMS
Get-DbaRegisteredServer -SqlServer SomeCentralMgmtSqlServerName -Group "Dev"
#assign to variable
$ServerInstances = Get-DbaRegisteredServer -SqlServer SomeCentralMgmtSqlServerName -Group "Dev"
$ServerInstances | Get-Member #What type is it? Which methods and properties are available?
$ServerInstances.count
$ServerInstances
$ServerInstances | Get-DbaDiskSpace | Where PercentFree -LT 20 | Out-GridView
#Checking free space in a database
$ServerInstances | Get-DbaDatabaseSpace | select -first 1
$DbSpace = Get-DbaDatabaseSpace -SqlInstance SomeCentralMgmtSqlServerName
$DbSpace | Out-GridView
#assign $filename variable
$filename = ("C:\HappyDBA\csv\" + [string](Get-Date -format "yyyy-MM-dd__HHmmss") + "__database_sizes__.csv" );
$DbSpace | Export-Csv -NoTypeInformation -Path $filename #write out to CSV
Invoke-Item $filename
$other_autogrowth_types = Get-DbaDatabaseSpace -SqlInstance SomeCentralMgmtSqlServerName | where AutoGrowType -NE 'MB'
$other_autogrowth_types | Out-GridView
$other_autogrowth_types | Get-Member
$other_autogrowth_types | select Database, AutoGrowType
#endregion Checking SQL Servers for space at volume and database level
#region Leveraging CMS & text files for simple loops
$txtfilename = "C:\HappyDBA\txt\2019-04-29_ServerList.txt"
#write to a file
$ServerInstances | Out-File $txtfilename
$txtfilename
Get-Content $txtfilename
Invoke-Item $txtfilename
#Create a here-string variable containing a query
$sp_whoisactive_query = @"
EXECUTE sp_whoisactive --@show_sleeping_spids=2,
@output_column_list = '[dd%][status][session_id][request_id][open_tran_count][percent_complete][blocking_session_id][wait_info][host_name][database_name][sql_text][program_name][login_name][start_time][wait_info][cpu%][%]',
@sort_order = '[program_name],[start_time] ASC',
--@sort_order = '[start_time] ASC',
--@get_plans=1,
@get_task_info = 2
"@
#run query against single instance
Invoke-SqlCMD -ServerInstance SomeCentralMgmtSqlServerName -Verbose -OutputSqlErrors $true -QueryTimeout 60 -Query $sp_whoisactive_query
#assign result set to a variable
$results = Invoke-SqlCMD -ServerInstance SomeCentralMgmtSqlServerName -Verbose -OutputSqlErrors $true -QueryTimeout 60 -Query $sp_whoisactive_query
$results | Out-GridView
#use textfile to create loop
$ServerInstances = Get-Content $txtfilename
#create empty array
$multiple_resultsets = @();
#loop through array
foreach ($instance in $ServerInstances) {
Write-Output "$instance ...";
$multiple_resultsets += Invoke-SqlCMD -ServerInstance $instance -Verbose `
-OutputSqlErrors $true -QueryTimeout 60 -Query $sp_whoisactive_query
}
#Check object props
$multiple_resultsets | Get-Member
#$multiple_resultsets | Out-GridView
#column names become properties of the returned object
$multiple_resultsets | where wait_info -Like "*ASYNC_IO_COMPLETION*" | select database_name, open_tran_count, program_name, host_name
#endregion Leveraging CMS & text files for simple loops
#region MaxMemory
#check memory allocated to the database engine
$ServerInstances | Test-DbaMaxMemory
#endregion MaxMemory
#region additional resources
<#
Slack channel
https://dbatools.io/slack/
Fantastic multiple ~1 hour chunks intro to PowerShell w/slide decks, self-assessments, and downloadable videos for free:
https://mva.microsoft.com/en-us/training-courses/getting-started-with-microsoft-powershell-8276
https://twitter.com/nanoDBA
Available for additional training or deeper technical dives into PowerShell - just ask!
https://github.com/PowerShell/PowerShell/blob/master/README.md
#>
#endregion additional resources
<#
https://blogs.technet.microsoft.com/heyscriptingguy/2015/11/12/use-regions-in-powershell-ise-2/
CTRL+M in ISE does the same thing
CTRL+K,CTRL+0 in VS Code
"I can quickly collapse or expand all regions by using the ToggleOutliningExpansion() method.
This method is available from the Editor object of the current file. The command is shown here:"
$psISE.CurrentFile.Editor.ToggleOutliningExpansion()
#>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.