Run in background

Hi,

I have a script that does a number of things. One of these is to restore a DB. The script gets executed during ARM Template deployment, it works perfectly but the deployment takes ages while waiting for the DB to restore and eventually times out.

So I thought I would place the DB restore in a Start-Job script block so that the DB would continue restoring in the background and the job would complete successfully. When running this is my session it works as expected, but during the deployment, it doesn’t work. I’m not really sure why, but I assume it’s because the script is executed under the system account and maybe doesn’t work this way?

So I’m after some advice about what can be done, maybe a better way of executing the script, maybe a way to initial the DB restore but maybe throw a custom output? Once the DB restore is executed the DB will run in the background regardless of the PowerShell session running.

Many thanks in advance :slight_smile:

 

 

Have you tried:

Invoke-Command localhost -scriptblock { your script here } -asjob

Thanks Emil. I have tried this but I get the same results as when running start-job

This is tricky :frowning:

Sounds like the user that the script is executing as doesn’t have rights to something, as you guessed. Have you tried using your account in the -Credential parameter on Start-Job to see if that makes a difference? Some code and some more details on how the script is executed may help, too.

Hi Charles,

Below is the part of the script/function that I need to execute
        $Scripts = Get-ChildItem "C:\WindowsAzure\Setup\AppSetup\SQLScripts"
        foreach ($Script in $Scripts.Name){
                 Invoke-Sqlcmd -ServerInstance localhost\DBINSTANCE -Username sa -Password **********  -Querytimeout 0  -InputFile "C:\WindowsAzure\Setup\AppSetup\SQLScripts\$script" -ErrorAction SilentlyContinue
          }
    $smo = 'Microsoft.SqlServer.Management.Smo.'
    $wmi = new-object ($smo + 'Wmi.ManagedComputer')
    $uri = "ManagedComputer[@Name='NODENAME']/ ServerInstance[@Name='INSTANCENAME']/ServerProtocol[@Name='Tcp']"
    $Tcp = $wmi.GetSmoObject($uri)
    $Tcp.IsEnabled = $true
    $Tcp.Alter()
    $wmi.GetSmoObject($uri + "/IPAddress[@Name='IPAll']").IPAddressProperties
    $wmi.GetSmoObject($uri + "/IPAddress[@Name='IPAll']").IPAddressProperties[1].Value="51232"
    $wmi.GetSmoObject($uri + "/IPAddress[@Name='IPAll']").IPAddressProperties
    $Tcp.Alter()
    $SQLService = Get-Service -Name 'MSSQL$INSTANCENAME' | Restart-Service</pre>

If I wrap this in a Start-Job scriptBlock or invoke-command -asJob it fails when being deployed into Azure. If I run the same script in a session they work every way. Its only when deploying via ARM that it fails to execute.

The ARM Template deployment justs download the script and executes it.

Thanks

 

Not sure why it’s showing all the red :frowning: Hopefully you can make it out

Have you been able to get the error? Why is the error action above?

It’s because one of the SQL scripts throws a validation error, so I want it to continue regardless. That won’t cause it would it? I don’t get any actual errors from deployment.

 

It must be a credential issue, so using credential should help rule that out. Another cause may be some variable not being defined in the scriptblock. You could try to Start-Job with -filename where the file name is your script that is failing.

Ive pass through the creds, but it still doesn’t work. I can’t work this out.

        $password = ConvertTo-SecureString "MyPassword" -AsPlainText -Force
        $Cred = New-Object System.Management.Automation.PSCredential ('MyUser', $password)

        Start-Job -Credential $Cred -ScriptBlock {

            $Scripts = Get-ChildItem "C:\WindowsAzure\DatabaseSetup\SQLScripts"
            foreach ($Script in $Scripts.Name){
                   Invoke-Sqlcmd -ServerInstance localhost\InstanceName -Username sa -Password ********** -Querytimeout 0  -InputFile "C:\WindowsAzure\DatabaseSetup\SQLScripts\$script" -ErrorAction SilentlyContinue
               }
       
    }

What does Receive-Job with -Keep give you?

Funny thing is the job isn’t even listed, so it looks like its not even being trigger :frowning:

I logged the attempt to start the job, and below is the output. So it looks like its trying to start the job but failing. I don’t seem to be able to find anymore info to why it’s failing. :frowning:

 

JobStateInfo : Failed
Finished : System.Threading.ManualResetEvent
InstanceId : 3eabbdfd-c13d-4047-82d7-ea7a9babbf5b
Id : 6
Name : Job6
ChildJobs : {Job7}
PSBeginTime : 10/10/2019 10:56:11 AM
PSEndTime : 10/10/2019 10:56:12 AM
PSJobTypeName : BackgroundJob
Output : {}
Error : {}
Progress : {}
Verbose : {}
Debug : {}
Warning : {}
Information : {}
State : Failed

What do you have here?

Get-Job -id 6 | Select -ExpandProperty ChildJobs | Select *