SQL PowerShell Demos for Spring of 2017

000_Downlading_SqlServer_module.ps1

Install-Module SqlServer

<# If you don't have admin #>
Install-Module SqlServer -Scope CurrentUser

<# Find the commands #>
Get-Command -Module SqlServer -CommandType Cmdlet |
Out-GridView

<# What if you need to install on a server behind a firewall #>
Save-Module SqlServer -Path c:\temp\

<# Just in case you run into an issue with the old SQLPS module #>
Get-Module -ListAvailable -Name SQL*

00_Backups.ps1

<# Where are my backups? #>
Invoke-Item (Get-Item SQLSERVER:\SQL\LOCALHOST\DEFAULT).BackupDirectory

<# Backup all my databases now please #>
Get-SqlDatabase -ServerInstance localhost |
Where { $_.Name -ne 'tempdb' } |
Backup-SqlDatabase -CompressionOption On;



<# Next, a message from Grant #>
Start-Process https://youtu.be/Ah0jabU9G8o?t=2m56s

<# Go to the data file directory #>
Invoke-Item (Get-Item SQLSERVER:\SQL\LOCALHOST\DEFAULT).DefaultFile

<# Grant said it, so we better do it. #>
#Install-Module dbatools
Test-DbaLastBackup -SqlServer localhost -Destination $new | Out-GridView

0A_Deploy SQL Server Performance Dashboard Reporting.ps1

<# http://sqlps.io/tigerreports
    This script is intented to help automate Steps 2-7 for downloading & installing the SQL Server Performance Dashboard Reports 
    https://blogs.msdn.microsoft.com/sql_server_team/sql-server-performance-dashboard-reports-unleashed-for-enterprise-monitoring/ #> 

<# If the ReportingServicesTools is not present, download the ReportingServicesTools module from GitHib #>
try {Import-Module ReportingServicesTools -ErrorAction Stop} catch {Install-module -Name ReportingServicesTools -Scope CurrentUser} finally {Import-Module ReportingServicesTools}

<# Setting our GitHub resources to variables #>
$ZipURL = "https://github.com/Microsoft/tigertoolbox/raw/master/SQL-performance-dashboard-reports/SQL%20Server%20Performance%20Dashboard%20Reporting%20Solution.zip"
$SQLURL = 'https://github.com/Microsoft/tigertoolbox/raw/master/SQL-performance-dashboard-reports/setup.sql'

<# Where to place the files when they are downloaded
   $ZipFile will go to the current users 'Downloads' folder.
   $ReportsBaseFolder is where the SSRS Solution will be unzipped to. 
       You could change this to somewhere else like "$($env:USERPROFILE)\Documents\Visual Studio 2015\Projects"
   $SQLFile is the Setup.SQL that must be run on each SQL Server before these reports can work. #>
$ZipFile = "$($env:USERPROFILE)\Downloads\SQLServerPerformanceDashboardReportingSolution.zip"
$ReportsBaseFolder = 'C:\SQL Server Performance Dashboard'
$SQLFile = "$($ReportsBaseFolder)\Setup.SQL"

<# SSRS Instance, this is where the reports will be rendered from.
    You probably need to change this to something like 'http://MyReportServer/ReportServer'.  If you have a named SSRS instance 'http://MyReportServer/ReportServer_SQL2016'  #>
$SSRSInstance = 'http://localhost/ReportServer_SQL2016'
$NewSSRSReportFolder = 'SQL Server Performance Dashboard'

<# Start up a web client and download the GitHub resources #>
$webclient = New-Object system.net.webclient
$webclient.DownloadFile($ZipURL,$ZipFile)

<# UnZip the Reporting Solution Zip file #>
Expand-Archive $ZipFile -DestinationPath $ReportsBaseFolder -Verbose -Force

<# Now that the reports are unzipped, download the SQL file to that same directory #>
$webclient.DownloadFile($SQLURL,$SQLFile)

Write-Host -ForegroundColor White -Separator "|" -BackgroundColor DarkBlue -Object "Finished Downloading & UnZipping Files"
<# Deploy the dashboard reports to the $NewSSRSReportFolder ('SQL Server Performance Dashboard') folder of an SSRS instance
    Note: We are creating the folder using New-RsFolder, you may need to skip this step if you’ve already run it once. #>
New-RsFolder -ReportServerUri $SSRSInstance -Path / -Name $NewSSRSReportFolder -Verbose

Write-RsFolderContent -ReportServerUri $SSRSInstance -Path "$($ReportsBaseFolder)\SQL Server Performance Dashboard\" -Destination /$NewSSRSReportFolder -Verbose

Get-RsFolderContent -ReportServerUri $SSRSInstance -Path /$NewSSRSReportFolder | 
SELECT Name, Path, TypeName, Size |
Format-Table -AutoSize

<# Loop through Registered Servers & deploy the Setup.SQL file to each instance
    You can also use a Central Management Server to list your SQL servers by swapping 'Database Engine Server Group' for 'Central Management Server Group'  #>
#foreach ($RegisteredSQLs IN dir -recurse SQLSERVER:\SQLRegistration\'Database Engine Server Group'\Host\ | where {$_.Mode -ne 'd'} )
#{
#Invoke-Sqlcmd -ServerInstance $RegisteredSQLs.Name -Database msdb -InputFile $SQLFile
#}

<# Go to SSRS and make sure everythng works
    You URL should look something like http://localhost/Reports_SQL2016/report/SQL%20Server%20Performance%20Dashboard/performance_dashboard_main #>
Start-Process "$($SSRSInstance -replace 'Server', 's')/report/$($NewSSRSReportFolder)/performance_dashboard_main"

<# Reset Demo 
Remove-Item $ZipFile;
Remove-Item $ReportsBaseFolder -Recurse;
Remove-RsCatalogItem -ReportServerUri $SSRSInstance -Path "/$($NewSSRSReportFolder)"
#>

0B_SQLProvider.ps1

#Modules on this machine
Get-Module -ListAvailable

#Before
Remove-Module SqlServer;
Get-PSDrive

#Load the SqlServer Module
Import-Module SQLServer

#New Resource
Get-PSDrive

CD SQLSERVER:\
DIR;

<# What's inside SQLRegistration? #>
CD SQLSERVER:\SQLRegistration
DIR;

CD 'Database Engine Server Group'
DIR;

CD Host
DIR;

<# What's inside that "SQL" folder? #>
CD SQLSERVER:\SQL\
DIR;

<# What's inside that computer? #>
CD 'localhost'
DIR;

<# Let's grab one of those instances and have a look around. #>
GET-ITEM 'SQL2016'

<# We can take these results and send them straight into a variable. 
    When we do that, PowerShell takes care of checking to see if a variable by 
    that name exists already, and assigns a data type to it based on what is being piped in.
    Note 1: You can assign your own data type if you wish.  #>
$MySQL2016Instance = GET-ITEM 'SQL2016'
$MySQL2016Instance

$MySQL2016Instance.GetType()

<# Let's take a quick look at all of the databases #>
CD SQLSERVER:\SQL\localhost\SQL2016\Databases\
DIR;

<# We're going to take a quick detour real quick.  
    PowerShell completes the whole line before placing the values inside
    the variable.  This could affect the data type assigned. #>

$bob = Get-ITEM 'AirStats'
$bob.GetType()

$ralph = Get-ITEM 'AirStats' | SELECT Name, Size, DataSpaceUsage, IndexSpaceUsage, SpaceAvailable
$ralph.GetType()

<# Let's take a quick look at all of my tables #>
CD SQLSERVER:\SQL\localhost\SQL2016\databases\AirStats\tables\
DIR;

DIR SQLSERVER:\sql\localhost\SQL2016\databases\AirStats\tables\ |
Select Schema, Name, DataSpaceUsed, IndexSpaceUsed, RowCount, HasCompressedPartitions, HasClusteredColumnStoreIndex |
ft -AutoSize


<# Let's take a quick look at all of my tables #>
DIR SQLSERVER:\sql\localhost\SQL2016\databases\AirStats\tables\ |
Select Schema, Name, DataSpaceUsed, IndexSpaceUsed, RowCount, HasCompressedPartitions, HasClusteredColumnStoreIndex |
Out-GridView


<# Let's take a quick look at tables with indexes larger than data #>
DIR SQLSERVER:\sql\localhost\SQL2016\databases\AirStats\tables\ |
WHERE {$_.IndexSpaceUsed -gt $_.DataSpaceUsed} |
SELECT Schema, Name, DataSpaceUsed, IndexSpaceUsed, RowCount, HasCompressedPartitions, HasClusteredColumnStoreIndex |
Out-GridView

<# Traversing the SQL Server Provider is just like Object Explorer #>
cd SQLServer:\sql\localhost\default\Databases\AdventureWorks2014\Tables\


#What can we do whith that? #Never Type in a Demo!
#Now this doesn't have to me local.  We're basically navigating Object Explorer
CD SQLSERVER:\SQL\localhost\Default\DATABASES\ADVENTUREWORKS2014\TABLES
DIR

#Let's script this table out
$PTH = get-item Production.TransactionHistory
$PTH.Script()

#How did I know that I can script it out?
#Switch to Side-by-Side
get-item Production.TransactionHistory | get-member
get-item Production.TransactionHistory | get-member -MemberType Methods

#What is this object's type?
$PTH.GetType()

0C_SSAS_Demo.ps1

Get-Command -Module SQLas*

CD SQLSERVER:\SQLAS\LOCALHOST\SQL2016\Databases\AdventureWorksDW2014Multidimensional-EE\Dimensions\

dir;

<# Hopefully you don't need the below command any more #>
#dir | SELECT Name, State, LastProcessed, ProcessingMode, CurrentStorageMode | Out-GridView -PassThru;

dir | %{$_.Process()}

<# Process Dimensions #>
dir |
Out-GridView -PassThru | %{
Invoke-ProcessDimension -Name $_.Name -Database $_.ParentDatabase -ProcessType ProcessFull
}


Get-Command -Module SqlServer -Name Invoke-Process*

<# Process Cubes #>
Dir 'SQLSERVER:\SQLAS\LOCALHOST\SQL2016\Databases\AdventureWorksDW2014Multidimensional-EE\Cubes\Adventure Works\MeasureGroups'|


Dir 'SQLSERVER:\SQLAS\LOCALHOST\SQL2016\Databases\AdventureWorksDW2014Multidimensional-EE\Cubes\'|
Out-GridView -PassThru | %{
Invoke-ProcessCube -Name $_.Name -Database $_.ParentDatabase -ProcessType ProcessFull
}

<# Can Also work with Tabular Databases #>
Dir SQLSERVER:\SQLAS\LOCALHOST\TAB2016\Databases\HealthCheck\Cubes\Model\MeasureGroups\

Dir SQLSERVER:\SQLAS\LOCALHOST\TAB2016\Databases\'Adventure Works Internet Sales'\Tables\'Internet Sales'\Measures\ |
SELECT *

2_Compare_sp_configure.ps1

<# I have a New SQL 2016 instance
    I want to make it look just like my SQL 2014 instance #>

$serverDefault = Connect-DbaSqlServer -SqlServer localhost
$Server = Connect-DbaSqlServer -SqlServer localhost\SQL2016

<# SMO is Hard. You have to use dots and stuff.  Also, I can't read without a table!  | Format-Table -AutoSize  #>
$serverDefault.Configuration.Properties | Format-Table -AutoSize


$DefaultSQL = $serverDefault.Configuration.Properties | 
Select-Object DisplayName, RunValue

$SQL2016 = $Server.Configuration.Properties | 
Select-Object DisplayName, RunValue

<# Ok, let's just compare #>
Compare-Object -ReferenceObject $SQL2016 -DifferenceObject $DefaultSQL -Property DisplayName, RunValue | 
Out-GridView


<# Build it your own or use awesome code like this: #>
Invoke-Item https://dbatools.io/functions/copy-sqlspconfigure/





<# Just here to reset the demo #>
$DefaultSQL = $Null
$SQL2016 = $Null

3A_GetFailedAgentJobs.ps1

Get-SqlAgent -ServerInstance 'localhost' | 
Get-SqlAgentJob | Out-GridView
<# Get-SqlAgentJob > Show-Command #>
Get-SqlAgentJob -ServerInstance localhost\SQL2016 
Get-SqlAgentJob -ServerInstance localhost, localhost\SQL2016


<# Compare this to OED of SQL Agent #>
Get-SqlAgentJob -ServerInstance localhost |
Out-GridView

Get-SqlAgentJob -ServerInstance localhost |
SELECT * 

Get-SqlAgentJob -ServerInstance localhost |
Where { $_.IsEnabled -eq $true -and $_.LastRunOutcome -eq 'Failed' }

Get-SqlAgentJob -ServerInstance localhost |
Where { $_.DateCreated -gt (get-date).AddDays(-7) }

Get-SqlAgentJob -ServerInstance localhost |gm -MemberType Properties| measure
SELECT * 


$sqlserver = 'localhost\SQL2016'
Get-SqlAgent -ServerInstance localhost | 

<# Looking at your output, if you're new to PowerShell you'll want to know... #>
#Format-Table
Get-SqlAgentJobHistory -ServerInstance $sqlserver -Since MIDNIGHT | %{$Job = $_}
FT -AutoSize


Get-SqlAgentJob -ServerInstance localhost\SQL2016


Get-SqlAgentJobHistory -ServerInstance localhost, localhost\SQL2016 -Since Midnight -OutcomesType Failed

Get-SqlAgentJobHistory -ServerInstance localhost, localhost\SQL2016 -Since Midnight -OutcomesType Failed |
Out-GridView


Get-SqlAgentJobHistory -ServerInstance localhost, localhost\SQL2016 -StartRunDate 2017-01-25::20:00 -OutcomesType Failed


#

Get-SqlAgentJobHistory -ServerInstance localhost, localhost\SQL2016 -StartRunDate (get-date).AddDays(-4) -OutcomesType Failed

Get-SqlAgentJobHistory -ServerInstance localhost, localhost\SQL2016 -StartRunDate (get-date).AddHours(-12) -OutcomesType Failed

$JobDate = (get-date).AddHours(-12)
Get-SqlAgentJobHistory -ServerInstance localhost, localhost\SQL2016 -StartRunDate $JobDate -OutcomesType Failed


DIR SQLSERVER:\SQLRegistration
<#
    .DESCRIPTION            

#>
foreach ($ServerList IN dir -recurse SQLSERVER:\SQLRegistration\'Database Engine Server Group'\Host  )
{
Get-SqlAgentJobHistory -ServerInstance $ServerList.Name -StartRunDate (get-date).AddHours(-12) -OutcomesType Failed |
Format-Table -AutoSize
}

$datevar = Get-Date -Format FileDate
[datetime]::ParseExact($datevar,"20170125",$null)

$ServerList = DIR -Recurse 'SQLSERVER:\SQLRegistration\Database Engine Server Group\Host'| 
WHERE {$_.Mode -ne 'd'}
$ServerList
$ServerList.Name

Get-SqlAgentJobHistory -ServerInstance $ServerList.Name -StartRunDate -Since Midnight -OutcomesType Failed |
Out-GridView

Get-SqlAgentJobHistory -ServerInstance $ServerList.Name -StartRunDate (get-date).AddHours(-12) -OutcomesType Failed |
Out-GridView


$A = Get-Date
$A.

#| WHERE RunStatus -EQ 1 | ogv

$Job.RunStatus

#Select-Object
Get-SqlAgentJobHistory -ServerInstance $sqlserver -Since LastMonth |
SELECT *


<# Check the history of all my Registered SQL Servers #>
<#
    .DESCRIPTION            
#>
foreach ($RegisteredSQLs IN dir -recurse SQLSERVER:\SQLRegistration\'Database Engine Server Group'\Host |
                                where {$_.Mode -ne 'd'} | OGV -PassThru)
{
Get-SqlAgent -ServerInstance $RegisteredSQLs.Name | 
Get-SqlAgentJob | 
Where-Object { $_.IsEnabled -eq $true -and $_.LastRunOutcome -eq 'Failed' }
}

<# This code will do the same thing as above.  
    Which to use is up to you and your situation.
    If you wanted to do more than just display the results, 
        that could make you lean towards the loop.           #>
Get-SqlAgent -ServerInstance localhost, localhost\SQL2016 | 
Get-SqlAgentJob | 
Where-Object { $_.IsEnabled -eq $true -and $_.LastRunOutcome -eq 'Failed' }

3B_Get-SQLErrors & Write-SqlTableData.ps1

<# .DEMO Get-SQLErrors & Write-SqlTableData #>
Get-SqlErrorLog -ServerInstance localhost, localhost\SQL2016 -Since LastWeek |
Out-GridView -PassThru | <# Once this opens, type in something to filter on,
e.g. memory, backup, port...
after it filters do select all, and then hit OK #>
Write-SqlTableData -ServerInstance localhost\SQL2016 -DatabaseName tempdb -SchemaName dbo -TableName MemoryErrors -Force;

<# After creating the table, show that the rows also landed there. #>
Invoke-Sqlcmd -ServerInstance localhost\SQL2016 -Database tempdb -Query "
SELECT *
  FROM dbo.MemoryErrors" -OutVariable MemErrors | measure

$MemErrors | SELECT *

4_Add-SqlLogin from Pipeline.ps1

<# Data Professionals want to be able to do _THIS_.
    We want to be able to move logins from our old SQL instance to
    our new SQL 2016 instance. #>
$a = Get-SqlLogin -ServerInstance localhost          -LoginType WindowsUser <# SQL 2014 #>
$b = Get-SqlLogin -ServerInstance localhost\SQL2016  -LoginType WindowsUser <# SQL 2016 #>
<# This one works but requires a prompt to enter the password of the new user when copying a SQL Login #>

Compare-Object -ReferenceObject $b -DifferenceObject $a -PassThru |  <# Use Compare-Object for missing logins #>
Out-GridView -PassThru | 
foreach{                                                             <# Out-GridView so you can filter #>
        Add-SqlLogin -ServerInstance localhost\SQL2016 -LoginType $_.LoginType -LoginName $_.Name -DefaultDatabase $_.DefaultDatabase -Script
        }

<# Make sure to run the command below to prove the login now exists on the 2016 instance #>
Get-SqlLogin -ServerInstance localhost\SQL2016 -LoginType WindowsUser