Intro to PowerShell and dbatools for SQL Server DBAs (originally presented in August 2018 using the ISE)

Practical PowerShell for Busy DBAs.ps1

#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()
#>