nanoDBA

<# download and install latest SSMS release #>
$SSMS_filename = "$env:USERPROFILE\downloads\SSMS-Setup-ENU-" + ([string](Get-Date -format "yyyy-MM-dd")) + ".exe" 
Start-BitsTransfer -Source 'https://aka.ms/ssmsfullsetup' -Destination $SSMS_filename
& $SSMS_filename /install /passive
<# Thanks to @sqltoolsguy and his team for creating the aka.ms links! 
https://twitter.com/sqltoolsguy/status/1011754064516804608 #>

nanoDBA

<#
    How to use TSS Tools with Microsoft Support
    https://gist.github.com/nanoDBA/ac409275a6ef406f9134856f1245f72f

    Modern approach using TSS.ps1 -SDP (replaces the old Get-psSDP.ps1 standalone method).
    Also covers SQL LogScout — now Microsoft CSS's preferred tool for SQL-specific diagnostics.

    Source: https://aka.ms/TssTools
    SQL LogScout: https://github.com/microsoft/SQL_LogScout
#>

nanoDBA

<# DANGER REBOOTING! #>
$msg = "Due to installs/updates of SQL Prompt, this computer must be restarted. You have 15 minutes to save your work"
$delay = 900 # seconds
shutdown /r /f /d P:4:1 /t`$($delay) /c "$msg" 2>$null
if ($LastExitCode -ne 0) {
    Write-Host "Cannot reboot $PC ($LastExitCode)" -ForegroundColor black -BackgroundColor red

}
else {
    #LogWrite "$env:username,$PC,Reboot Sent,$datetime" #fix this...

nanoDBA

if (! ( (Get-WindowsFeature -Name Windows-Defender-Features).Installed)) {
    Write-Output "Windows Defender is not installed" | Out-String | Write-Host -ForegroundColor Yellow
}
if ( (Get-WindowsFeature -Name Windows-Defender-Features).Installed) { 
    #Disable Windows Defender http://www.thomasmaurer.ch/2016/07/how-to-disable-and-configure-windows-defender-on-windows-server-2016-using-powershell/
    Set-MpPreference -DisableRealtimeMonitoring $true
    # uninstall Windows Defender https://technet.microsoft.com/en-us/windows-server-docs/security/windows-defender/windows-defender-overview-windows-server
    Get-WindowsFeature -Name Windows-Defender-Features
    Uninstall-WindowsFeature -Name Windows-Defender-Features -Confirm:$false
    Get-WindowsFeature -Name Windows-Defender-Features

nanoDBA

<# download and install Solarwinds SentryOne Plan Explorer #>
$filenamePlanExplorerInstall = "$env:USERPROFILE\downloads\" + ([string](Get-Date -format "yyyy-MM-dd")) + "_SolarWinds-PlanExplorer.exe"
Start-BitsTransfer -Source 'https://downloads.solarwinds.com/solarwinds/Release/FreeTool/SolarWinds-PlanExplorer.exe' -Destination $filenamePlanExplorerInstall
& $filenamePlanExplorerInstall /install /passive /norestart

nanoDBA

$computerName = "some.computer.name"
$portNumber = 3389
while ((Test-NetConnection -ComputerName $computerName -Port $portNumber).TcpTestSucceeded -EQ $false){
    Write-Host "Checking TCP port $portNumber on $computerName..."
    Start-Sleep -Seconds 5
    Get-Date -Format "yyyy-MM-ddTHH:mm:ss (K)"

    if ((Test-NetConnection -ComputerName $computerName -Port $portNumber).TcpTestSucceeded -EQ $true){
         Write-Host -ForegroundColor Magenta "Yay!!  TCP port $portNumber on $computerName is back online!"
         while($true){(New-Object System.Media.SoundPlayer $((Get-ChildItem -Path "$env:windir\Media\tada.wav").FullName)).Play(); Start-Sleep -Seconds 2}

nanoDBA

# ------------------------------------------------------------------------------
# File:        Test-DiskSpd.ps1
# Description: 🚀 Automated EC2 volume and DiskSpd benchmarking for Windows
# Purpose:     Retrieves detailed EC2 volume info and runs robust DiskSpd I/O
#              benchmarks on specified drives. Handles AWS, local, and hybrid
#              scenarios with safety checks, resumable downloads, and cleanup.
#              Built for DBAs and sysadmins who want to know their storage is
#              actually as fast as the cloud bill says! 😎
# Created:     2025-06-26
# Modified:    2025-06-26

nanoDBA

<# https://4sysops.com/archives/use-powershell-to-test-if-a-windows-server-is-pending-a-reboot/ #> $pendingRebootTests=@(@{Name='RebootPending';Test={Get-ItemProperty -Path 'HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Component Based Servicing' -Name 'RebootPending' -ErrorAction Ignore};TestType='ValueExists'},@{Name='RebootRequired';Test={Get-ItemProperty -Path 'HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update' -Name 'RebootRequired' -ErrorAction Ignore};TestType='ValueExists'},@{Name='PendingFileRenameOperations';Test={Get-ItemProperty -Path 'HKLM:\SYSTEM\CurrentControlSet\Control\Session Manager' -Name 'PendingFileRenameOperations' -ErrorAction Ignore};TestType='NonNullValue'}); $results=@();foreach($item in $pendingRebootTests){$result=Invoke-Command -ScriptBlock $item.Test;if($result){$results+=New-Object PSObject -Property @{Name=$item.Name;Status='Pending'}}};if($results){$results|Format-Table}else{Write-Output 'No Pending Reboot'}

<# DANGER - ***forced reboot(s) may occur

nanoDBA

#robocopy root of a volume multithreaded unbuffered
$description = 'SRV_Migration'
$source = '\\10.0.0.0\P$'
$target = 'P:'

$robocopyLogFilename = "$($target)\" + ( [string](Get-Date -format "yyyy-MM-dd_HH-mm-ss") ) + "_" + $($description) + "_(" + ($target).Substring(0,1) + ")_robocopy.log"

# exclude the Recycle Bin and sysvol folders, and exclude pagefile
robocopy $source $target * /E /W:10 /XD "System Volume Information" "`$RECYCLE.BIN" /LOG+:$robocopyLogFilename /NP /TEE /SEC /MT:128 /XF pagefile.sys /J
<#

nanoDBA

--AG-Resume.sql for SQL Agent job

--Check Sync State and Execute Resume
DROP TABLE IF EXISTS #agAynamicSqlResume

DECLARE @sqlCommand NVARCHAR(max)

SELECT ';ALTER DATABASE [' + Db_name(DRS.database_id) + '] SET HADR RESUME' AS [resume_sql],
       AGS.name AS AGGroupName,
       AGL.dns_name AS Listener_dns_name,