dbatools for systems engineers and accidental dbas

sys-summit-demo.ps1

break
<#
    01. Find instances
    02. Connect to instances
    03. Check backups
    04. Check disk space
    05. Perform backups
    06. Check for corruption
    07. Install maintenance scripts
    08. Export all settings for DR
    09. Look for failed jobs
    10. Check a few settings (memory & power plan)
    11. See what SQL components are installed
    12. Discover service accounts
    13. Update SQL Server
    14. Do a migration
    15. Langiappe

    Let's look around SQL Server Management Studio first
#>

# First, find unknown instances
# Probes tcp, udp, spns, wmi, all sorts of stuff
Find-DbaInstance -ComputerName macmini, sqlcs, sql2000 | Select-Object * | Out-GridView

# Next, try to connect!
Connect-DbaInstance -SqlInstance sql2000, sqlcs | Get-Member
$cred = Get-Credential sqladmin
Connect-DbaInstance -SqlInstance sqlcs -SqlCredential $cred

# sql logins vs windows + requires windows admin
Reset-DbaAdmin -SqlInstance sqlcs -SqlCredential $cred
Connect-DbaInstance -SqlInstance sqlcs -SqlCredential $cred

# Check your backups
Get-DbaLastBackup -SqlInstance sqlcs | Select-Object * | Out-GridView

# Backups missing? Check disk space to see if you can
Get-DbaDiskSpace -ComputerName sqlcs
Get-DbaDiskSpace -ComputerName sql01, sql02 | Out-GridView

# Cool, hurry and backup your whole instance real quick
Backup-DbaDatabase -SqlInstance sqlcs -Compress
Backup-DbaDatabase -SqlInstance sqlcs -Compress -Type Log
# Confirm the backups
Get-DbaLastBackup -SqlInstance sqlcs | Out-GridView

# Also check to see if your databases have been checked for corruption
Get-DbaLastGoodCheckDb -SqlInstance sqlcs | Out-GridView


# Install Ola's scripts
# Talk about importance of logs
# and scheduling and permissions
$params = @{
    SqlInstance     = "sqlcs"
    InstallJobs     = $true
    ReplaceExisting = $true
    BackupLocation  = "\\san\sql\backups"
}
Install-DbaMaintenanceSolution @params

# Run some backups
New-DbaDatabase -SqlInstance sqlcs
Get-DbaAgentJob -SqlInstance sqlcs | Out-GridView -PassThru | Start-DbaAgentJob -Wait
Invoke-Item -Path \\san\sql\backups

# if you're curious
Start-DbaAgentJob -SqlInstance sqlcs -Job "DatabaseBackup - USER_DATABASES - FULL"
Get-DbaRunningJob -SqlInstance sqlcs

# Go schedule then disable VSS
Get-Service -ComputerName sqlcs -DisplayName "SQL Server VSS Writer" | Set-Service -Status Stopped -StartupType Disabled

Test-DbaLastBackup -SqlInstance sqlcs | Out-GridView
# Wraps like 20
Export-DbaInstance -SqlInstance sqlcs -Path C:\temp\dr
Get-ChildItem -Path C:\temp\dr -Recurse -Filter *databa* | Invoke-Item

# agent -> dbatools.io/agent
Find-DbaAgentJob -SqlInstance sqlcs -Failed | Get-DbaAgentJobHistory

<#
To see why a job failed, you can also open up SSMS and expand the job step.
SSMS -> Connect to Server -> SQL Agent Job -> Right click -> View history
#>

# fix max memory and powerplan
Test-DbaMaxMemory -SqlInstance workstation -SqlCredential $cred | Set-DbaMaxMemory -WhatIf
Test-DbaPowerPlan -ComputerName sqlcs | Set-DbaPowerPlan -WhatIf

# Good for any system
Get-DbaOperatingSystem -ComputerName sqlcs | Out-GridView
Get-DbaComputerSystem -ComputerName sqlcs | Out-GridView

# Tip: Install only what you need
Get-DbaFeature -ComputerName sqlcs

# TIP ABOUT SQL SERVICE ACCOUNTS
Get-DbaService -ComputerName sqlcs | Out-GridView
Get-DbaService -ComputerName sqlcs | Select-Object * | Out-GridView
Get-DbaService -ComputerName sqlcs -Instance MSSQLSERVER -Type Agent | Update-DbaServiceAccount -Username 'Local system' -Verbose

# Scan the instances to check what version & Service Pack/Cumulative Update level we're at
Test-DbaBuild -SqlInstance sqlcs, sqlcluster -Latest

# Install-DbaInstance / Update-DbaInstance
Update-DbaInstance -ComputerName sql2017 -Path \\dc\share\patch -Credential base\ctrlb
Invoke-Item "$home\OneDrive\syseng\Patch several SQL Servers at once using Update-DbaInstance by Kirill Kravtsov.mp4"

# Perform the database and accompanying login migration
Copy-DbaDatabase -Source sqlcs -Destination sqlcluster -Database AdventureWorks -BackupRestore -SharedPath \\san\sql\migration -Force
Copy-DbaLogin -Source sqlcs -Destination sqlcluster -Login ad\sqldba -Force

# Langiappe

# docker - dbatools.io/docker & dbatools.io/docker-repo
# multiarch docker images based on sql server 2019
Start-Process https://dbatools.io/docker

# Show Reg Servers in SSMS
Get-DbaRegisteredServer
Get-DbaRegisteredServer -Name mssql1 | Connect-DbaInstance

# dbatools has its own tools -> options
Get-DbatoolsConfig | Out-Gridview

# Find detached databases, by example
1..5 | ForEach-Object {
    New-DbaDatabase -SqlInstance sqlcs | Detach-DbaDatabase
}
Find-DbaOrphanedFile -SqlInstance sqlcs | Out-GridView -PassThru |
    Select-Object -ExpandProperty RemoteFilename | Get-ChildItem -OutVariable del
$del | Remove-Item
$del | Get-ChildItem

# CSV galore!
Get-ChildItem $home\OneDrive\syseng\csv
Get-ChildItem $home\OneDrive\syseng\csv | Import-DbaCsv -SqlInstance sqlcs -Database tempdb -AutoCreateTable

Invoke-DbaQuery -SqlInstance sqlcs -Database tempdb -Query "Select top 10 * from [jmfh-year]"

# Process
Get-DbaProcess -SqlInstance sqlcs -Database tempdb |
    Out-GridView -PassThru |
    Stop-DbaProcess

# Network and Firewall
Get-DbaFirewallRule -SqlInstance sql01
New-DbaFirewallRule -SqlInstance sql01

# Got here? Show them TDE!

$cred = Get-Credential sqladmin

$params = @{
    SqlInstance             = "localhost"
    SqlCredential           = $cred
    MasterKeySecurePassword = $cred.Password
    BackupSecurePassword    = $cred.Password
    BackupPath              = "/tmp"
    AllUserDatabases        = $true
}
Start-DbaDbEncryption @params

Get-DbaFile -SqlInstance localhost -SqlCredential $cred -Path /tmp