Small Framework to load sql scripts simultaniously to several Servers

I use the following framework to run scripts calling sqlcmd simultaneously as PowerShell jobs during some MsBuild Task. My Buildagents are running Server 2003 and there are some linitations cf. StackOverflow.

But just calling sqlcmd works fine. In fact I'm dropping and recreating my test databases and running a lot of sql scripts completly within such background jobs. I didn't try it with the Oracle part using sqlplus which I run in the main script and after whose termination I wait for jobs to terminate in time or terminate them. 

$workdir = Split-Path $MyInvocation.MyCommand.Path            
$logdir = 'C:\temp\log'            
$server1 = 'my_sql2008'            
$server2 = 'my_sql2005'            
$datbase = 'PowerShellTest'            
            
$cmd1 = [scriptblock]::Create(". $workdir\script_containing_function_for_background_job.ps1
Some_function -server $server1 -database $datbase $logdir -asJob" )            
            
                
$cmd1 = [scriptblock]::Create(". $workdir\script_containing_function_for_background_job.ps1
Some_function -server $server2 -database $datbase $logdir -asJob" )            
            
            
"...Starting the background jobs"            
$SQLJobStartTime = Get-date            
$jnr1 = (Start-Job -ScriptBlock $cmd1).Id            
$jnr2 = (Start-Job -ScriptBlock $cmd2).Id            
# *****************************************            
            
' Doing something slow here (it happens to be with an Oracle Server'            
<#
....
#>            
            
function Get-WaitTime             
{            
    param(            
        [DateTime]$start,            
        $dauer            
    )            
    $rest = $dauer - ((Get-Date) - $start).Totalseconds             
    if ($rest -lt 0) {$rest = 0}            
    $rest            
}            
            
'...Waiting for job on  SQL Server 2008'            
Wait-job -id $jnr1 -timeout ( Get-WaitTime $SQLJobStartTime 960 )   # max 16 min            
if ((Get-job -id $jnr1).state -eq 'Running')            
{            
    Stop-job -id $jnr1            
}             
Receive-Job -id $jnr1            
$state1 = (Get-job -id $jnr1).State            
$dauer1 = ((get-date) - $SQLJobStartTime).Totalseconds            
            
"$(Get-date -f d)  $(Get-date -f t) Release $release($build) Einspielen auf SQl-Server 2008 $state1 $dauer1 secs"            
            
'...Waiting for job  SQL Server 2005'            
Wait-job -id $jnr2 -timeout ( Get-WaitTime $SQLJobStartTime 960 )   # max 16 min            
if ((Get-job -id $jnr2).state -eq 'Running')            
{            
    Stop-job -id $jnr2            
}             
Receive-Job -id $jnr2            
$state2 = (Get-job -id $jnr2).State            
$dauer2 = ((get-date) - $SQLJobStartTime).Totalseconds            
            
"$(Get-date -f d)  $(Get-date -f t) Release $release($build) Einspielen auf SQl-Server 2005 $state2 $dauer2 secs"            

Displaying the builds of a given build-definition since the latest succesfull one

When it comes to fixing broken builds, the first step is to determine when the last successful build run and which builds failed afterwards.

In practice I'm looking for the state of 20 build definitions each morning, but here I reduce the script to a single one.

# adapt the following 4 lines to your environment            
Add-Type -Path "C:\Program Files\Microsoft Visual Studio 10.0\Common7\IDE\ReferenceAssemblies\v2.0\Microsoft.TeamFoundation.Build.Client.dll"            
            
$tfsuri = 'your tfs server'            
$project = "your prpject"            
$buildDefinition = 'Main'            
            
function Get-BuildInfo            
{            
    param(            
        $buildDefinition,             
        $MaxBuildsPerDefinition = 5,            
        [switch]$DontBreakOnSuccess,            
        [switch]$Starttime            
        )            
            
    #Write-Host "Using MaxBuildsPerDefinition $MaxBuildsPerDefinition"            
    $tfs = [Microsoft.TeamFoundation.Client.TeamFoundationServerFactory]::GetServer($tfsuri)            
    $buildServerType = [Microsoft.TeamFoundation.Build.Client.IBuildServer]            
    $buildServer = $tfs.GetService($buildServerType);            
            
    $buildDetailSpec = $buildServer.CreateBuildDetailSpec($project, $buildDefinition);            
    if ($Starttime)            
    {            
        $buildDetailSpec.QueryOrder = [Microsoft.TeamFoundation.Build.Client.BuildQueryOrder]::StartTimeDescending            
    } else {            
        $buildDetailSpec.QueryOrder = [Microsoft.TeamFoundation.Build.Client.BuildQueryOrder]::FinishTimeDescending            
    }            
    $buildDetailSpec.MaxBuildsPerDefinition = $MaxBuildsPerDefinition            
            
            
    try            
    {            
     $buildQueryResult = $buildServer.QueryBuilds($buildDetailSpec)            
            
        foreach ($build in $buildQueryResult.builds)            
        {                
            # [Microsoft.TeamFoundation.Build.Client.IBuildDetail]$buildDetail = $buildQueryResult.builds[0]             
            $tfsBuildNumber = $build.BuildNumber            
            $shortBuildNumber = $tfsBuildNumber.Substring($tfsBuildNumber.LastIndexOf('_')+1)            
            
    #         #$shortBuildNumber            
    #         $buildQueryResult.failures.length            
    #         $buildQueryResult.builds.length            
    #         $buildQueryResult.builds[0].BuildNumber            
    #         #$buildQueryResult.builds[0].Status            
    #         #$buildQueryResult.builds[0].BuildDefinitionUri.AbsolutePath            
    #         $buildQueryResult.builds[0].StartTime            
    #         $buildQueryResult.builds[0].FinishTime            
              
              $buildAgent = '??'            
              $build.Information | % {            
                $_.nodes | % {            
                    try             
                    {            
                    $buildAgent = $_.children.nodes[2].fields['ReservedAgentName']            
                    }            
                    catch            
                    {            
                    }            
                }             
            }            
                
            New-Object PSObject -Property @{             
                Buildnumber = $build.BuildNumber;             
                Status = $build.Status;            
                CompilationStatus = $build.CompilationStatus;            
                Start = $build.StartTime;            
                End = $build.FinishTime;            
                DropLocation = $build.DropLocation;            
                SourceGetVersion = $build.SourceGetVersion;            
                BuildAgent = $buildAgent            
                }            
            if ($build.Status -eq 'Succeeded' -and ! $DontBreakOnSuccess) { break  }                
                            
        }            
    }            
    catch [Exception]            
    {            
     throw "TFS nicht erreichbar."            
    }            
            
}            
            
            
$a = @(Get-BuildInfo $buildDefinition)            
$a | % {            
    if ($_.Status -eq 'InProgress') { $end = Get-Date } else { $end = $_.End }            
    "{0,-32} {1,18}  {2:ddd} {3} - {4:T} {5,5:0} {6} {7,6} {8}" -f $_.Buildnumber, $_.Status, $_.Start, $_.Start, $End, ($End -$_.Start).Totalminutes, $_.SourceGetVersion, $_.buildAgent, $Droplocation            
}

The result tells me start time, end time, duration, included change set  and drop location.

In the case of failed builds I some further checks on the log files not included here to dispatch the problem as quick as possible to the developer  who caused the problem.