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,

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

<# DANGER - ***forced reboot(s) may occur*** #>
# $moduleName = "PSWindowsUpdate"
# if (Get-Module -ListAvailable -Name $moduleName) {
#     Write-Output "$moduleName module is already installed." | Out-String | Write-Host -ForegroundColor Yellow
#     Import-Module $moduleName
# }
# else {
#     Write-Output "Module $moduleName is not installed - installing..." | Out-String | Write-Host -ForegroundColor Yellow
#     $NuGetProvider = Get-PackageProvider -Name NuGet | Where-Object Version -GT 2.8.5.201; if ( -not $NuGetProvider) { Install-PackageProvider -Name NuGet -MinimumVersion 2.8.5.201 -Force -Confirm:$false }
#     <# Install moduleName #>Install-Module -Force $moduleName -Confirm:$false -Verbose

nanoDBA

<# copy DiskSpd to c:\DiskSpd after downloading it#>
cd $env:temp
Start-BitsTransfer -Destination .\DiskSpd.zip  -Source 'https://aka.ms/getdiskspd'
$Path = 'c:\DiskSpd'; If(-not (Test-Path $Path)){ mkdir $Path }
Expand-Archive -Path .\DiskSpd.zip -OutputPath C:\DiskSpd
cd \
<#
"This example command line will run a 30 second random I/O test using a 20GB test file located on the T: drive, with 
a 25% write and 75% read ratio, with an 8K block size. It will use eight worker threads, each with four outstanding
 I/Os and a write entropy value seed of 1GB. It will save the results of the test to a text file called 

nanoDBA

#requires -version 5.0
#Requires -RunAsAdministrator
<# DANGER REBOOTING!
   This is meant for a local machine in a scheduled task 
   and is not handling remoting or remote credentials
#>
[CmdletBinding()]
param (
    [Parameter()][int]$DelayMinutes = 15 #default to 15 minute delay if this parameter is not supplied
)

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

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

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

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

<# How to use TSS Tools with Microsoft Support - https://gist.github.com/nanoDBA/ac409275a6ef406f9134856f1245f72f #>
<# Pretty Timestamp prompt using dbatools module#>function Prompt{Write-Host "[" -NoNewline; Write-Host (Get-Date -Format "HH:mm:ss") -ForegroundColor Gray -NoNewline;try{$history = Get-History -ErrorAction Ignore;if ($history) {Write-Host "][" -NoNewline;if (([System.Management.Automation.PSTypeName]'Sqlcollaborative.Dbatools.Utility.DbaTimeSpanPretty').Type){Write-Host ([Sqlcollaborative.Dbatools.Utility.DbaTimeSpanPretty]($history[-1].EndExecutionTime - $history[-1].StartExecutionTime)) -ForegroundColor Gray -NoNewline} else{Write-Host ($history[-1].EndExecutionTime - $history[-1].StartExecutionTime) -ForegroundColor Gray -NoNewline;}}}catch { }Write-Host "] $($executionContext.SessionState.Path.CurrentLocation.ProviderPath)" -NoNewline;"> "}
cd $env:temp
$targetTssDir = 'c:\tss_tools'
if (!(Test-Path $targetTssDir)) {mkdir $targetTssDir };

Start-BitsTransfer -Destination .\tss_tools.zip  -