Public/Invoke-KSP.ps1
|
<#
.SYNOPSIS Executes the collector process .DESCRIPTION Invoke-TheKraken is the main process which collects metadata information and stores it into the repository database .PARAMETER Environment Must match values set on the column environment on the sql_instances table. It executes the process against target sql servers that meet the environment criteria .PARAMETER Name Must match instance_name column values on the sql_instances table. It executes the process against specific target sql servers pass in this comma separated list. SQL Instance must exist in the sql_instances table .PARAMETER All If this Switch is used, the process is executed against all Active SQL Server Instances listed on the sql_instances table .PARAMETER Credential Credential [PSCredential] - If not specified it uses Trusted Authentication, else it will SQL Authentication .PARAMETER Throttle Number of concurrent running runspace jobs which are allowed at a time. .INPUTS Credential [PSCredential] .OUTPUTS None .EXAMPLE Executes the process just against DEV Target Servers Invoke-TheKraken -Environment "DEV" .EXAMPLE Executes the process just against All Target Servers Invoke-TheKraken -All .LINK https://github.com/dokier/Kraken #> function Invoke-KSP { [OutputType('void')] [CmdletBinding(DefaultParameterSetName = 'All')] param ( [Parameter(ParameterSetName = 'ByEnv')] [ValidateNotNullOrEmpty()] #[ValidateSet("DEV", "TST", "STG", "PRD")] [string[]]$Environment, [Parameter(ParameterSetName = 'BySQLInstance')] [ValidateNotNullOrEmpty()] [string[]]$SQLInstance, [Parameter(ParameterSetName = 'ByComputer')] [ValidateNotNullOrEmpty()] [string[]]$ComputerName, [Parameter(Mandatory = $false)] [string[]]$Database = $null, [Parameter(ParameterSetName = 'All')] [switch]$All, [Parameter(ValueFromPipeline)] [System.Management.Automation.Credential()] [PSCredential] $Credential = [System.Management.Automation.PSCredential]::Empty, [int] $Throttle = $env:NUMBER_OF_PROCESSORS ) begin { #$ErrorActionPreference = 'Stop' $defaultDB = "master" $connSettings = Get-ConnectionString $ModulePath = (Split-Path $PSScriptRoot) $script:PSConfigPath = (Get-Item $PSScriptRoot).Parent.FullName $json = Get-Content -Path $script:PSConfigPath\KSchemaPlugin.config.json -Raw | ConvertFrom-Json $Commands = $json.Commands $cmdNames = $Commands | Get-Member -MemberType NoteProperty | Select-Object -ExpandProperty Name $Private = @(Get-ChildItem -Path $PSScriptRoot\..\Private\*.ps1 -ErrorAction SilentlyContinue) $credSplat = @{} if ($Credential -ne [System.Management.Automation.PSCredential]::Empty) { $credSplat['Credential'] = $Credential } $databaseSplat = @{} if ($Database) { $databaseList = "'$($Database -join "','")'" $sqlParameters = "databaseList=$($databaseList)" $databaseSplat['variable'] = $sqlParameters } else { $Commands."Get-IndexInfo".query_name = "Get-IndexInfo2.sql" } } process { Write-Verbose "ParameterSetName $($PSCmdlet.ParameterSetName)" switch ($PSCmdlet.ParameterSetName) { 'ByEnv' { $SQLInstanceList = Get-SqlInstance -Environment $Environment @credSplat } 'BySQLInstance' { $SQLInstanceList = Get-SqlInstance -SQLInstance $SQLInstance @credSplat } 'ByComputer' { $SQLInstanceList = Get-SqlInstance -ComputerName $ComputerName @credSplat } 'All' { $SQLInstanceList = Get-SqlInstance -All @credSplat } } $Job = Get-Job -JobName "Schema-Plugin" @credSplat [void]$(Update-RunCount -JobName "Schema-Plugin" @credSplat) $RunCount = $Job.run_count + 1 #Removing old jobs in case the user aborted the process Ctrl C and re-executes on the same session Get-RSJob -Name "KSchema-Plugin" | Remove-RSJob #SCRIPT BLOCK to go parallel $jobList = $SQLInstanceList | Start-RSJob -Name { "KSchema-Plugin" } -Throttle $Throttle -ScriptBlock { #$_.instance_name $RunDate = Get-Date -Format "yyyy-MM-dd HH:mm:ss.fff" #Passing parent variables to scriptblock $LocalJob = $Using:Job $LocalInstance = $_.instance_name $LocalInstanceId = $_.id $LocalCommands = $Using:Commands $LocalcmdNames = $Using:cmdNames $LocaldefaultDB = $Using:defaultDB $LocalconnSettings = $Using:connSettings Write-Output $_.instance_name try { Invoke-Sqlcmd2 -ServerInstance $_.instance_name -Query "PRINT 'hello world'" @Using:credSplat -ErrorAction Stop $ConnStatus = $True Write-JobMessage -RunDate $RunDate -RunCount $Using:RunCount -SQLInstance $LocalInstance -JobId $LocalJob.id -StepName "Test-Connectivity" -Success $True @Using:credSplat } catch { $ConnStatus = $False Write-JobMessage -RunDate $RunDate -RunCount $Using:RunCount -SQLInstance $LocalInstance -JobId $LocalJob.id -StepName "Test-Connectivity" -Success $False -ExceptionMessage $_.Exception.Message @Using:credSplat Write-Output "Test-Connectivity FAILED. Exception: $($_.Exception.Message)" } if ($ConnStatus -eq $True) { foreach ($cmdName in $LocalcmdNames) { $QueryPath = "$Using:ModulePath\Private\SQLScripts\$($LocalCommands.$cmdName.query_name)" $RunDate = Get-Date Try { $DataSet = Invoke-WrappedSqlcmd -ServerInstance $LocalInstance -Database $LocaldefaultDB -InputFile $QueryPath @Using:credSplat @using:databaseSplat #-ErrorAction Continue if($Dataset) { $DataSet = ConvertFrom-DataRow -InputObject $DataSet -AsObject $DataSet | Add-Member -MemberType NoteProperty -Name "job_id" -Value $LocalJob.Id $DataSet | Add-Member -MemberType NoteProperty -Name "run_date" -Value $RunDate $DataSet | Add-Member -MemberType NoteProperty -Name "run_count" -Value $Using:RunCount $DataSet | Add-Member -MemberType NoteProperty -Name "instance_id" -Value $LocalInstanceId $DataSet | Add-Member -MemberType NoteProperty -Name "id" -Value "" } If ($Dataset){ Write-WrappedSqlTableData -ServerInstance $LocalconnSettings.server -DatabaseName $LocalconnSettings.database -SchemaName dbo -TableName $LocalCommands.$cmdName.dest_table -InputData $DataSet @Using:credSplat -Force -ErrorAction Stop } Write-JobMessage -RunDate $RunDate -RunCount $Using:RunCount -SQLInstance $LocalInstance -JobId $LocalJob.id -StepName $cmdName -Success $True @Using:credSplat } catch { Write-JobMessage -RunDate $RunDate -RunCount $Using:RunCount -SQLInstance $LocalInstance -JobId $LocalJob.id -StepName $cmdName -Success $False -ExceptionMessage $_.Exception.Message @Using:credSplat Write-Output "$($cmdName) FAILED. Exception: $($_.Exception.Message)" #Write-Output "$($_.InvocationInfo.ScriptLineNumber)" } } } } -FunctionFilesToImport $Private Write-Verbose "$($jobList)" Write-Verbose "`nThrottle Value: $($Throttle)" $runningJobs = (Get-RSJob -State Running).Count While ($runningJobs -ne 0) { $jobs = Get-RSJob $runningJobs = $Jobs.Where{ $PSItem.State -eq 'Running' }.Count $WaitingJobs = $Jobs.Where{ $PSItem.State -eq 'NotStarted' }.Count $CompletedJobs = $Jobs.Where{ $PSItem.State -eq 'Completed' }.Count $FailedJobs = $Jobs.Where{ $PSItem.State -eq 'Failed' }.Count Write-Verbose "$runningJobs Jobs Running - $WaitingJobs Jobs Waiting - $CompletedJobs Jobs Finished - $FailedJobs Jobs Failed" Start-Sleep -Seconds 5 } Write-Verbose "`nALL JOBS HAVE FINISHED" Write-Verbose "`nJOBS OUTPUT -" $jobReceive = Get-RSJob -Name "KSchema-Plugin" | Receive-RSJob Write-Verbose -Message "$($jobReceive)" Write-Verbose "`nREMOVING OLD JOBS" $jobRemove = Get-RSJob -Name "KSchema-Plugin" | Remove-RSJob Write-Verbose -Message "$($jobRemove)" Write-Verbose "`nFINISHED" Return $RunCount } # Process } |