000_Setup

<#
    Let's go shopping in the PowerShell Store
    Ok, it is not actually called the 'store'
    it is called the PowerShell Gallery
    
#>
Start-Process http://PowerShellGallery.com
<# Use the Search #>

<#
Also, to get to the SqlServer PowerShell module
#>
Start-Process http://sqlps.io/sqlserver
<#
If you don't have PowerShell 5.0 / Windows MF 5 installed (https://www.microsoft.com/en-us/download/details.aspx?id=54616)
you will need SSMS 16.5.3 in order to follow along
#>

1A_WarmUpDemos

#Standard Naming, no abbreviations
<#Stevenh Owens List#>
CD D:\AirStatsCSVs
<#01#>
Dir
<# Sometimes when you pipe a command to format-list you will automatically get more properties #>
Dir | Format-List
<#03#>
Dir | SELECT *
<#04#>
MKDIR -Name CSVs -Path C:\temp\AirStats
<#05#>
Dir | Move-Item -Destination C:\temp\AirStats\CSVs
<# What If, does it help with Dynamic names? #>
Dir | Move-Item -Destination C:\temp\AirStats\CSVs -Force -WhatIf
<#07#>
while ($true) {Move-Item c:\temp\xyz.txt -PassThru}
<#08#>
Get-Service -ComputerName  | Stop-Service
<#09#>
Get-Help
<#Aaron's Demo List#>
Get-Service -DisplayName SQL*
<#01#>
Get-Service -DisplayName SQL* | select name -Last 5
<#01#>
Get-Service -DisplayName SQL* |
WHERE {$_.Name -Match 'ReportServer' -and $_.CanStop} |
Sort -Property DisplayName
<#01#>
Get-Service -ComputerName a,b,c -Name SQLSERVERAGENT | Start-Service
<#02#>
<# PowerShell isn't supposed to have all these short names, 
    so where do we find out what they mean? #>
DIR Alias:\

DIR Alias:\%

DIR Alias:\% | fl

DIR Alias:\ | where {$_.ResolvedCommand -eq 'ForEach-Object'}
#Start with showing how to Get-Table
<#03#>
<#04#>
<#05#>
<#06#>
<#07#>
<#08#>
<#09#>
<#10#>
<#11#>
<#12#>
<#13#>
<#14#>
<#15#>
<#16#>
<#17#>
<#18#>
<#19#>
<#20#>

1B_Get-History

<#
Get-History is your friend!
#>
Get-History


function Get-History2
{
Get-History | 
SELECT Id, @{Label="TotalRunningTime";Expression={$_.EndExecutionTime - $_.StartExecutionTime}}, CommandLine, ExecutionStatus, StartExecutionTime, EndExecutionTime
}

<#
Start-Transcript is even better!
#>
Start-Transcript -Path c:\temp\PowerShellTranscripts\

  

2A_Backups

<# Where are my backups? #>
Invoke-Item (Get-Item SQLSERVER:\SQL\LOCALHOST\SQL2014).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

<# My Surface Book is so fast we probably can't see this happen #>
Invoke-Item (Get-Item SQLSERVER:\SQL\LOCALHOST\SQL2014).SQL2014File

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

2B_SQLProvider

#Before
Remove-Module SqlServer;
Get-PSDrive

#Snapins that are running
Import-Module SQLServer
#Snapins that you can load
Get-Module

#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 brab 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 all of my tables #>
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()

<##########################

Now Let's talk about loops for a little bit.

##########################>

<# Dir gives a lot of results.  #>
cd SQLServer:\sql\localhost\default\Databases\AdventureWorks2014\Tables\

dir
dir| measure

<# What if we wanted to do something to tables one at a time, like 
    maybe update their statistics.
    There are multiple approaches to looping but some of the most common are:
    A) foreach () {}
    B) foreach {}
    C) | %{}             #>
foreach($Table IN dir)
{
"$Table"
}

<# So what, you've printed out the names of my tables #>
<# That's no moon.  Er, That's no PRINT statement #>
$Table.GetType()

foreach($Table IN dir)
{
$Table.UpdateStatistics()
}

<# We can even see all the statistics if we want to. #>
foreach($Table IN dir)
{
$Table.Statistics
}

<# But that's not super helpful because have lost the context of 
    which statistic belongs to which table. #>

<# Ok, let's cycle through the whole database to look at those indexes #>
foreach($Table IN dir)
        {
        foreach($Index IN $Table.Indexes)
                {
                $Index | SELECT Name, Parent, FileGroup
                }
        }

<# We can take that same loop and make it even more useful #>
$Index | SELECT *

foreach($Table IN dir)
        {
        foreach($Index IN $Table.Indexes)
                {
                $Index | SELECT Name, Parent, FileGroup, SpaceUsed
                }
        }

<# Of course, it would be more helpful if we could just call a function to get that info. #>

function Get-SQLIndexInfo
{
foreach($Table IN dir)
        {
        foreach($Index IN $Table.Indexes)
                {
                $Index | SELECT Parent, Name, FileGroup, SpaceUsed
                }
        }
}

<##########################

Next up DATA_COMPRESSION!

##########################>

CD SQLSERVER:\sql\localhost\DEFAULT\Databases\AdventureWorks2014\Tables\;

DIR | 
SELECT Schema, Name, HasCompressedPartitions, dataspaceused |
WHERE {$_.HasCompressedPartitions -eq $True} |
OGV -PassThru | %{
                Invoke-SqlCmd -query "
                        ALTER TABLE $($_.Schema).$($_.Name) REBUILD PARTITION = ALL
                        WITH 
                        (DATA_COMPRESSION = None);
                "}
#Invoke-SqlCmd -query "SELECT COUNT(*)  FROM $($_.Schema).$($_.Name)"

<# Note!: This method does not trigger the SMO to update itself
        which is one reason to avoid this approach if you can.
        The other reason is SQL Injection.                          #>

DIR | 
SELECT Schema, Name, HasCompressedPartitions, dataspaceused |
WHERE {$_.HasCompressedPartitions -eq $True}


<# Of course, you could do this with SMO code too. #>
dir | 
Out-GridView -PassThru |
foreach {
        foreach ($partition in $psitem.PhysicalPartitions) 
            {
            $partition.DataCompression = "Row"
            }
        $_.Alter()
        }


#But what about this table's childrens?
cd Production.TransactionHistory
dir

<# Now we can go exploring! 
    What interests you?     #>

DIR SQLSERVER:\
Invoke-SqlCmd -query "select * from $(get-item .)"

3_Profile

<#
You have lots of profiles that you can configure.
    We will focus on two for today.
#>
psedit $profile

psedit $profile.CurrentUserAllHosts

<#

#>

<#
This is how I get auto-completion for my parameter values
#>

<# First we need an argument completer for -ServerInstance #>
Register-ArgumentCompleter -ParameterName ServerInstance -ScriptBlock {
    (ls -Recurse SQLSERVER:\SQLRegistration\'Database Engine Server Group'\ |
      ?{ $_.Mode -ne 'd'} | 
      Group-Object ServerName).Name | ForEach-Object {
    $CompletionText = $_
    New-Object System.Management.Automation.CompletionResult (
              $CompletionText,
              $_,
              'ParameterValue',
              "$_ (SQLInstance)"
                )
          }
};
<# When the Database Parameter is called -Name we do this #>
Register-ArgumentCompleter -ParameterName Name -ScriptBlock {

    param($commandName, $parameterName, $wordToComplete, $commandAst, $fakeBoundParameter)

    #$global:__LastFakeBoundParameter = $fakeBoundParameter
    
    if ($fakeBoundParameter.ContainsKey('ServerInstance')) {
        (ls "SQLSERVER:\SQL\$($fakeBoundParameter.ServerInstance)\Databases").Name | ForEach-Object {
            New-Object System.Management.Automation.CompletionResult (
                $_,
                $_,
                'ParameterValue',
                "$_ (Database)"
            )
        }
    }
};
<# When the Parameter is called -Database we do this #>
Register-ArgumentCompleter -ParameterName Database -ScriptBlock {
    param($commandName, $parameterName, $wordToComplete, $commandAst, $fakeBoundParameter)

    if ($fakeBoundParameter.ContainsKey('ServerInstance')) {
    (Get-SqlDatabase -ServerInstance $($fakeBoundParameter.ServerInstance)).Name | ForEach-Object {
    $CompletionText = $_
    New-Object System.Management.Automation.CompletionResult (
              $CompletionText,
              $_,
              'ParameterValue',
              "$_ (Database)"
                )
         }
    }
};

4_Get-SqlDatabaseBasicsDemo

<# When we run Get-SqlDatabase and point it at one of our SQL instances we get 
    output that is similar to the basics of Object Explorer Details (F7) in SSMS. #>
Get-SqlDatabase -ServerInstance Localhost\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.  #>

<# THESE NUANCES ARE CRITICAL! 
   SERIOUSLY.  
   YOU HAVE NO IDEA HOW MUCH TIME I HAVE LOST TO LITTLE THINGS LIKE THIS          #>
<# When you use the -Name parameter to specify a database this works as expected. #>
$ralph = Get-SqlDatabase -ServerInstance Localhost\SQL2016 -Name AirStats
$ralph.GetType()

<# When you don't specify -Name you get all databases on the instance.
   Since this is the Get-SqlDatabase cmdlet not the Get-SqlInstance cmdlet you 
   end up with a data type you weren't expecting.  #>
$bob = Get-SqlDatabase -ServerInstance Localhost\SQL2016
$bob.GetType()
<# Inside of $bob are all the databases on the instance. #>
$bob | measure
$bob | %{$_}

<# Up-Vote this Connect Item if you want a working Get-SqlInstance cmdlet.
    https://connect.microsoft.com/SQLServer/feedback/details/3005019       #>

<# Interestingly, even though $bob is an array, each item inside is a  #>
$bob | %{$_.GetType()}

<# THESE NUANCES ARE CRITICAL! #>
<# Ok, let's make this data more interesting. #>
$bob | SELECT Name, Size, DataSpaceUsage, IndexSpaceUsage, SpaceAvailable

<# How did I know that I could find the Size, DataSpaceUsage, IndexSpaceUsage, 
    & SpaceAvailable properties inside of $bob?
    1) We can actually get some of this knowlege from OED (F7) in SSMS
    2) In PowerShell the most direct method is to use the Get-Member cmdlet.
    3) Since we're SQL-people you may prefer to use SELECT *                  #>
$bob | Get-Member
$bob | SELECT *

<# In this case, using SELECT * wasn't very user friendly because it ran #>

<# It is also handy to know that you can easyily grab a single property alomst
    as if it were a subquery #>
(Get-SqlDatabase -ServerInstance Localhost\SQL2016).Name
$bob.GetType()

5_GetSQLcmdlets

Get-Command -Module SqlServer -CommandType Cmdlet | 
Sort -Property noun | 
SELECT * -First 1

Get-Command -Module ReportingServicesTools | sort -Property name | Out-GridView

Get-Help -Name Get-SqlDatabase -ShowWindow

<# You can also see a list of commands and instructions at
    https://github.com/Microsoft/ReportingServicesTools     
    Invoke-Expression (Invoke-WebRequest https://aka.ms/rstools) #>
<# Upload a single report #>

Get-Help -Full Write-RsCatalogItem

Write-RsCatalogItem -ReportServerUri 'http://desktop-52bpltn/ReportServer_SQL2016' -Path 'C:\SSIS Reporting Pack\Reports\dashboard.rdl' -Destination /SSISReports

<# Upload an entire folder #>
Get-Help -Full Write-RsFolderContent

Write-RsFolderContent -ReportServerUri 'http://desktop-52bpltn/ReportServer_SQL2016' -Path 'C:\SSIS Reporting Pack\Reports' -Destination /Sanjay

<# Still trying to figure this part out. #>
Get-Help -Full New-RsDataSource

Set-RsDataSource -ReportServerUri 'http://desktop-52bpltn/ReportServer_SQL2016' -Name SSISCatalog -DataSourceDefinition 'Data Source=DESKTOP-52BPLTN\\SQL2016;Initial Catalog=SSISDB'

Get-Help -Full Set-RsDataSource

Set-RsDataSource -ReportServerUri 'http://desktop-52bpltn/ReportServer_SQL2016' -Name SSISCatalog -DataSourceDefinition 'Data Source=DESKTOP-52BPLTN\\SQL2016;Initial Catalog=SSISDB'

6_SSAS_ServerProperties

(Get-Item sqlserver:\SQLAS\localhost\default\).BackupDirectory
(Get-Item sqlserver:\SQLAS\localhost\SQL2016\).ServerProperties | 
WHERE {$_.Value -ne $_.DefaultValue} | OGV


<# Demo code #>
Import-Module SqlServer

(Get-Item sqlserver:\SQLAS\localhost\Default\).ServerProperties | 
WHERE {$_.Value -ne $_.DefaultValue} | OGV

<# /Demo code #>



Get-Item sqlserver:\SQLAS\SurfaceBookSQL\SQL2016\ | GM


Get-Help Compare-Object


$o3 = (Get-Item SQLSERVER:\SQLAS\ServerA\Default\).ServerProperties | SELECT Name, Value
$o2 = (Get-Item SQLSERVER:\SQLAS\ServerB\Default\).ServerProperties | SELECT Name, Value
Compare-Object -ReferenceObject $o3 -DifferenceObject $o2 -IncludeEqual -Property Name

<# Will this allow you to compare settings between two SSAS instances? #>
$o3 = (Get-Item SQLSERVER:\SQLAS\ServerA\Default\).ServerProperties
$o2 = (Get-Item SQLSERVER:\SQLAS\ServerB\Default\).ServerProperties
Compare-Object -ReferenceObject $o3 -DifferenceObject $o2 -IncludeEqual -Property Name
<# SSAS Database Properties #>
(
Get-Item SQLSERVER:\SQLAS\LOCALHOST\SQL2016\Databases\AdventureWorksDW2014Multidimensional-EE\ | GM -MemberType Properties
)