Public/Invoke-SqlCmdWithCache.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
function Invoke-SqlCmdWithCache {
<#
    .Synopsis
        Wrapper for Invoke-SQLCmd cmdlt which has some error handling, server name resolution, and optional local caching.
    .PARAMETER query
        The sql query to execute
     .PARAMETER CacheResultsLocally
        A switch that when specified will locally cache data to speed up subsequent queries
    .PARAMETER cacheDir
        A directory that the xml files that store the cached data will be stored in. Default is C:\temp
        YOU NEED TO CLEAN THESE FILES UP YOUR SELF!!!
    .PARAMETER cacheDays
        A integer that specifies how old a file can be before the local cache is refreashed. Default is -1 (1 day old)
 
        Set this to a positive number to force a refreash of the local cache.
 
     .EXAMPLE
         
        Store a copy of the data locally to speed up any other queries until the cached data is 5 days old.
 
        The local cache will use the default $cacheDir: C:\temp\Friendly_Chainsaw\$sourceServer$sourceDatabase_$queryHash
        ie: (ServerDatabase_145868016216295781216920420294223571441041221777622495882505022372121155874110212)
         
        $sqlQuery = 'Select [DateTimeColumn], [varchar100Column], [VarcharMAXColumn] from mySchema.myTable'
        $dataAsDataTable = Query-SqlWithCache -ServerInstance $sourceServer -Database $sourceDatabase -Query $sqlQuery -cacheDays -5
          
    .INPUTS
       A sql command
    .OUTPUTS
       An array of powershell objects
    #>

  [CmdletBinding(SupportsShouldProcess = $true)]
  param([Parameter(Position = 0)][ValidateSet("Debug","Info","Warning","Error","Disable")] [string]$logLevel = "Warning",[string]$ServerInstance
    ,[string]$Database
    ,[Parameter(Position = 1,ValueFromPipeline)] [string]$query = $null
    ,[string]$cacheDir = "$env:Temp\Friendly_Chainsaw"
    ,[int]$cacheDays = -1
  )
  $currentLogLevel = Get-LogLevel
  if (!([string]::IsNullOrEmpty($logLevel))) {
    Set-LogLevel $logLevel
  }

  Write-Log "ServerName : $ServerInstance" Debug
  Write-Log "Database: $Database" Debug

  $queryStartTime = [System.Diagnostics.Stopwatch]::StartNew()
  $queryHash = Get-StringHash $query
  $fqPath = "$cacheDir$ServerInstance$($Database)_$queryHash.xml"
  if (!(Test-Path $fqPath)) {
    Write-Log "Data is not cached, loading cache. File path: $fqPath" Debug
    Write-Log "sql cmd: $query" Debug
    $results = Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $Database -Query $query -QueryTimeout 0 -ConnectionTimeout 0
    $results | Export-Clixml -Path $fqPath
  }
  elseif ($(Get-ChildItem $fqPath).LastWriteTime -le (Get-Date).AddDays($cacheDays)) {
    Write-Log "Refreashing local cache. File path: $fqPath" Debug
    Write-Log "sql cmd: $query" Debug
    Remove-Item $fqPath
    $results = Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $Database -Query $query -QueryTimeout 0 -ConnectionTimeout 0
    $results | Export-Clixml -Path $fqPath
  }
  else {
    Write-Log "Using local cache. File path: $fqPath" Debug
    $results = Import-Clixml $fqPath
  }
  $elapsedTime = $queryStartTime.ElapsedMilliseconds
  Write-Log "Query took: $elapsedTime miliseconds" Debug
  Set-LogLevel $currentLogLevel
  Write-Output $results

} Export-ModuleMember -Function Invoke-SqlCmdWithCache