PowerShell 3.0 Workflows and SQL Server. Don’t think wrong like I did.


My school on SQL Server was in performance and tuning. I started my career as SQL Server developer doing stored procedures.

My thought was always getting it right and run fast. When I moved into administration, one of my goals has always been my life easier by creating solutions to automating processes. I don’t need to say how much PowerShell helps me.

Even thinking about automation, besides having to create flexible and simple solutions, the “run fast” also counts a lot. I have a friend who uses a solution, that I did, in Powershell and SQL Server Agent, gathering Event Viewer Data from the last day, filtering errors , storing in a SQL Server Table , generating a Excel file  and sending by email

He does in 350 instances of SQL Server. There is no way to do this procedure without being as fast as possible too. In this solution I use the split-job and runspaces and this process takes on average 30 minutes.

When I heard the first time on processing workflows and I confess I did not expect to be faster than using runspaces, but thought, erroneously, that it might be faster than the serialized process . Obvious that for 350 instances is out of the question even in parallel processing, but maybe for some other process with fewer instances.

Then, today, I started my test. I do need to study a LOT workflows, I am newbie on that and it is an AWESOME feature, but I just needed to try my idea.

I have a txt with several instances and 30 .SQL files to gathering data from SQL Server. The process is for each server, run these .SQL files in a specific Database and export to a .csv files

This is my ServersWorkflow.txt :

image

Code 1 : running synchronously

$VerbosePreference ='Continue'

 

$Database = "SQLDMRepository"

$Server = "Win8-Vm"

$pathSQL = "c:\scripts"

 

measure-command {

 

    Get-Content -Path "C:\temp\ServersWorkFlow.txt" | % {

 

        $ServerName = $_ 

        $ServerNameToFile = $_ -replace '\\','_'

 

        Get-childitem $pathSQL -Filter "*.sql"  | % {

 

 

            $FileName = $_.basename

    

            Write-Verbose "Performing $($ServerName) File $($FileName)"

 

            Invoke-Sqlcmd2     -Database $Database `

                            -Query (Get-Content $_.fullname) `

                            -ServerInstance $ServerName |Export-Csv "C:\Scripts\output\$($ServerNameToFile)_$($FileName).csv" `

                                                                -NoTypeInformation -Force 

 

        }

 

    }

 

} 

 

$VerbosePreference ='SilentlyContinue'

image

image

59 Seconds and a significantly use of CPU

Code 2 – running workflow

 

workflow Invoke-ScriptsParallel {

 

    param (

            [string]$Database,

            [string]$ServerInstance,

            [string]$pathSQL

          )

            

 

 

 

 

    $server = Get-Content -Path "C:\temp\ServersWorkFlow.txt" 

 

    foreach -parallel ($ServerName in $server) {

 

         $ServerNameToFile = $_ -replace '\\','_'

 

        $TSQL =  Get-childitem -path $pathSQL -Filter "*.sql"  

 

        "Server $($ServerName)"

 

        foreach -parallel($InTSQL in $TSQL) {

 

            $FileName = $InTSQL.basename

            $FullName = $InTSQL.fullname

    

            #"Performing $($FileName)"

 

               "Script $($FileName)"

            inlinescript {

                   ipmo functions -DisableNameChecking -Force

                   

                Invoke-Sqlcmd2     -Database $using:Database -Query (Get-Content -Path $using:FullName) -ServerInstance $using:ServerInstance |  Export-Csv    -Path "C:\Scripts\output\$($using:ServerNameToFile)_$($FileName).csv"  -NoTypeInformation -Force 

 

            }

 

 

        }

    }

 

}

 

 

 

$Database = "SQLDMRepository"

$Server = "Win8-Vm"

$pathSQL = "c:\scripts"

 

measure-command {

 

Invoke-ScriptsParallel $Database $Server  $pathSQL 

 

}

 

 

 

image

image

1.56 Seconds and I can use my laptop as cooker

Code 3 – running asynchronous using runspaces

measure-command {

 

    Get-Content -Path "C:\temp\ServersWorkFlow.txt" | % {

 

        $ServerName = $_ 

        $ServerNameToFile = $_ -replace '\\','_'

 

        Get-childitem -path "c:\scripts" -Filter "*.sql"  | split-job { % {

                    $Filename = "$($_.basename)"

                    write-verbose "Performing $($Filename)"

                    Invoke-Sqlcmd2     -Database SQLDMRepository  -Query (Get-Content -Path $_.FullName) -ServerInstance $ServerName | Export-Csv    -Path "C:\Scripts\output\$($ServerNameToFile)_$($FileName).csv"   -NoTypeInformation -Force 

        } } -InitializeScript {    ipmo functions -DisableNameChecking -Force } -noprogress 

 

    }

}

 

 

 

image

 

image

32 Seconds and my CPU is ok.

Then, facing these values, I asked for a help to Sir Jeffery Hicks and Don Jones.

The answer is the main purpose to use workflows it is not to run fast . There is some overhead in the  engine to create the workflow XML and handle the workflow endpoint. Because I am using INLINE script, my invoke-sqlcmd2 is running locally , generating with more overhead.

According Sir Jeffery Hicks, “Generally I think we’ll use workflows for long running, unattended tasks that we’ll kick off”.

Before to think in solutions on SQL Server using workflows in this scenario (long running, unattended tasks ), I DO need to understand it better.

I can say that is an AWESOME feature and like anything else,need to be carefully studied and tested.

That is it guys . After all, it is all about solutions Alegre

The thread that Jeffery and Don reply to me you can see in HERE

#PowerShellLifeStyle

About Laerte Junior

Laerte Junior Laerte Junior is a SQL Server specialist and an active member of WW SQL Server and the Windows PowerShell community. He also is a huge Star Wars fan (yes, he has the Darth Vader´s Helmet with the voice changer). He has a passion for DC comics and living the simple life. "May The Force be with all of us"
This entry was posted in Powershell, SQL SERVER EM GERAL, Virtual Pass BR. Bookmark the permalink.

1 Response to PowerShell 3.0 Workflows and SQL Server. Don’t think wrong like I did.

  1. Pingback: PowerShell – Diversas « Alex Souza

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s