PASS Data Architecture Virtual Group demo

DataArchDemo.ps1

break # Prevent everything from running at once. 
$SqlServer = "SQL2016"

Import-Module dbatools 

# Stop the current command if it throws an error or exception.
$ErrorActionPreference = "Stop"

# Multiple assignment, woo!
# Give us more information about what is going on behind the scenes!
$DebugPreference = $VerbosePreference = "SilentlyContinue"

# If you dont have all the help you see in this demo, run the Update-Help command in an admin shell.
# if you are familiar with the *nix word | more only works in console, but great for paging help content or long strings. 
Get-Help about_redirection 
Get-Help about_*

# Native help
Get-Help *dba* 
Get-Help Test-DbaSqlPath 
Get-Help Test-DbaSqlPath -Examples
Get-Help Test-DbaSqlPath -Online

Find-DbaCommand *bios*

# PowerShell naming conventions are a bit particular, but let you live within your own namespace.
Get-Verb

# Get/Test - Good (though test-dbalastbackup can restore databases, but no harm should come from that.) 
# Anything else - potentially state altering in the dbatools project. 

# If you do data things, you want to learn select and where :) 
Get-Help Select-Object
Get-Help Where-Object
Get-Help Format-*

# Things I use on the daily.
# Less code = less maint, less things to go wrong, less me being a dangus. 

    Get-DbaLastBackup -SqlInstance $SqlServer
    
    Get-DbaLastBackup -SqlInstance $SqlServer | 
    Select-Object ComputerName, Database, LastFullBackup, LastLogBackup, LastDiffBackup | 
    Sort-Object -Property LastFullBackup |  
    Format-Table

    Test-DbaJobOwner -SqlInstance $SqlServer -Login sa | ft

    Test-DbaDatabaseOwner -SqlInstance $SqlServer -TargetLogin sa | ft
    # Set-DbaDatabaseOwner

    Test-DbaVirtualLogFile -SqlInstance $SqlServer | where {$_.Count -gt 50} | Out-GridView
    
    # Base command
    Get-DbaDatabase -SqlInstance $SqlServer
    
    # Select useful fields
    Get-DbaDatabase -SqlInstance $SqlServer -ExcludeAllSystemDb | select Name, SizeMB
    
    # Assign and store to a table.
    # Gather databases metadata 
    $Databases = Get-DbaDatabase -SqlInstance $SqlServer -ExcludeAllSystemDb | select Name, SizeMB
    
    # Convert that metadata to a datatable object, that which SQL Server loves.
    $DataTable = Out-DbaDataTable -InputObject $Databases 
    
    # Write this to a table, and even generate it at runtime if we want!
    Write-DbaDataTable -SqlInstance $SqlServer -Database tempdb -InputObject $DataTable -Table DataSize -AutoCreateTable # Only for the demo!

    # Query to verify it exists using some dbatools magic in the .Query command 
    (Connect-DbaSqlServer -SqlInstance $SqlServer).Databases['tempdb'].Query( " select * from DataSize " ) 


# Things that I use to check on a new server I inherited. I LOVE the test commands!

    # SQL Server Settings
    Test-DbaMaxDop "sql2014" | ft # Cool output and supports testing databases and server level stuff in different versions!
    Test-DbaMaxMemory $SqlServer # Gives a recommend memory setting!
    
    # Network Crap.
    Test-DbaSpn -ComputerName $SqlServer | ft # Ugh, I hate SPNs. 
    Test-DbaConnectionAuthScheme -SqlInstance $SqlServer # Checks if you are using Kerebos, screw NTLM!
    
    # Windows Server configuration garbo, I thought I got to focus on db things :) 
    Test-DbaDiskAlignment -ComputerName $SqlServer | ft # Gives a recommended set of best practice flags!
    Test-DbaDiskAllocation -ComputerName $SqlServer | ft # Checks if our disks are allocated properly!
    Test-DbaPowerPlan -ComputerName $SqlServer # So annoying to get at, so easy to check. 

    # Database stuff which might be wrong. 
    Test-DbaDatabaseCollation -SqlInstance $SqlServer # Checking if correlation is screwy.