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

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

#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

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

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

# DANGER # Server Core? ---OPTIONAL--- Setting PowerShell as the Default Shell Manually
#If you've only got one server, a couple of servers or maybe your Server Core machines are workgroup members so you can't use Group Policy and if any of these are true, the manual method is for you. It's a simple PowerShell one-liner:
# source: https://richardjgreen.net/setting-powershell-default-shell-server-core/
Set-ItemProperty -Path 'HKLM:\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon' -Name Shell -Value 'PowerShell.exe -NoExit'

nanoDBA

# Yesterday was the winter solstice, so I am looking forward to more daylight!
# https://stackoverflow.com/questions/63236774/powershell-return-only-sunrise-sunset-time/63237047#63237047
# using API from https://sunrise-sunset.org/api

$lat = 35.787743 ; $long = -78.644257 # substitute desired latitude/longitude

# date range: 2 days ago until tomorrow and Loop de Loop using foreach
-2..1 | foreach {
    $Date = (Get-Date).AddDays($_) | Get-Date -Format "yyyy-MM-dd"
    $Daylight = (Invoke-RestMethod "https://api.sunrise-sunset.org/json?lat=$lat&lng=$long&formatted=0&date=$Date").results