nanoDBA

## DANGER - this script is an example and will likely NUKE DELETE REMOVE DESTROY DATA
## If you run this on a prod system, it may be a résumé / CV generating event that could hurt businesses

###http://windowsitpro.com/windows-server-2012-r2/powershell-storage-space-creation-tips
#List all disks that can be pooled and output in table format (format-table)           
Get-PhysicalDisk -CanPool $True | ft FriendlyName,OperationalStatus,Size,MediaType           

get-disk 1,2
#### Initialize Disks to GPT
get-disk 1,2 | Initialize-Disk -PartitionStyle GPT

nanoDBA

--Do you really reallly want to run this?
DECLARE @highestcompatlevel TINYINT,
        @dbname NVARCHAR(128),
        @sql NVARCHAR(400);

SELECT @highestcompatlevel = MAX(compatibility_level)
FROM sys.databases;
SELECT @dbname = DB_NAME();

SELECT @sql = 'ALTER DATABASE [' + @dbname + '] set compatibility_level = ' + CAST(@highestcompatlevel AS NVARCHAR(3));

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

<# download and install SentryOne Plan Explorer: 2018-07-16 release #>$PlanEXP_filename = "$env:USERPROFILE\downloads\PlanExplorerInstaller.exe"; Start-BitsTransfer -Source 'https://downloads.sentryone.com/downloads/sqlsentryplanexplorer/x64/PlanExplorerInstaller.exe' -Destination $PlanEXP_filename; & $PlanEXP_filename /install /passive /norestart

nanoDBA

## Create AES key with random data and export to file
<#
Sometimes a script needs to use a password, but you want it stored securely.  Passing a credential by utilizing 2 things:

1.	A password file that contains the encrypted file
2.	A key file that contains a randomly generated AES key

allows for utilization of credentials within scripts by different users and servers.  
**The following method is only as secure as the locations of the files(password and key) themselves.**

nanoDBA

<# To compare two server configs ( sp_configure ) #>

# https://sqldbawithabeard.com/tag/sql-dba/
# https://dbatools.io/functions/get-dbaspconfigure/

$oldProps = Get-DbaSpConfigure -SqlInstance 'SERVER01' # | Sort-Object ConfigName
$newProps = Get-DbaSpConfigure -SqlInstance 'NEWSERVER-02'  #| Sort-Object ConfigName

$propCompare = foreach ($prop in $oldprops) {
    if (($prop2   = $newprops | Where-Object DisplayName -EQ $prop.DisplayName) -AND ($prop2.RunningValue -NE $prop.RunningValue)) {

nanoDBA

#Requires -Modules PoshRSJob
break; #Safety First - These bits of script are meant to match a user name
# and LOGOFF the RDP sessions of that user when passed a list of servers
# So... potentially very dangerous       

# source: https://4sysops.com/archives/how-to-force-a-user-logoff-remotely-with-powershell/
$results = Start-RSJob -Throttle 20 <#-ModulesToImport dbatools#> -InputObject $TargetServerstoKILL_RDP_SESSIONS -Verbose -ScriptBlock { $username = 'kill.this.username'; Write-Output "$($_)..."; $sessionId = 7777777; Write-Output "`$sessionId = $($sessionId)"; quser /server:"$($_)" | Where-Object { $_ -match $userName } ; $sessionId = ((quser /server:"$($_)" | Where-Object { $_ -match $userName }) -split ' +')[2] ; Write-Output "`$sessionId = $($sessionId)" ; IF ($sessionId -NE "SESSIONNAME" -AND $sessionId -NE $null -AND $_ -NE $($env:COMPUTERNAME)) { logoff $sessionId /server:"$($_)" } ELSE { Write-Output "No match found for $username" }  } | Wait-RSJob -Timeout 72 -ShowProgress -Verbose | Receive-RS

nanoDBA

<# Install Nuget for Allusers if it's not already there #>if (-not (Get-PackageProvider -Name NuGet -ListAvailable -ErrorAction:SilentlyContinue )) { Install-PackageProvider -Name NuGet -Force -Scope AllUsers }
# modified from https://gist.github.com/RobCannon/12f338f00ce906fb4d43ee472c6a1389

nanoDBA

--How far behind is Secondary in AlwaysOn replica ? ( LAG )
--http://blogs.extremeexperts.com/2013/11/04/sql-server-alwayson-how-far-behind-is-secondary/
--"There are a lot of times customers ask me how much time is my secondary behind my primary?
--Such a simple question can be answered easily using some good DMVs."

SELECT AGS.name                       AS AGGroupName, 
       AGL.dns_name			          AS Listener_dns_name, 
       AGL.port				          AS Listener_port, 
       AGL.ip_configuration_string_from_cluster    AS Cluster_IP_addresses, 
       AR.replica_server_name         AS InstanceName, 

nanoDBA

# Restoring Databases from Files in Parallel

# Let's restore 11, 21, 41 databases!
     $DbSuffixRange = 500..510
    # $DbSuffixRange = 500..520
    # $DbSuffixRange = 500..540

$RSJobparamHash = @{
    Throttle        = 8
    ModulesToImport = "dbatools"