Stop, Start, or Restart a SSAS Tabular service instance in PowerShell with Invoke-Command and $variables

Question

I'm working on some PowerShell commands / scripts to stop, or start, or restart a SSAS Tabular service instance using Invoke-Command and some $variables. The end goal is to eventually put this code into an Agent Job for easy service restarting, but that's beyond the scope of this post -- I need to get this working with the $variables first.

I'm using PowerShell 5.0 on my source (local) machine, and trying to stop / start / restart a remote SSAS 2012 Tabular server instance (11.0.6540.0).

First, I start my script with Set-ExecutionPolicy.

Set-ExecutionPolicy -Scope CurrentUser RemoteSigned -Force

Then, using the hard coded method just to get the syntax right and watching the SQL Server Configuration Manager (Config Mgr) while RDP'd into the server to verify the command is working, I use this PowerShell code:

Invoke-Command -ComputerName DevServer { stop-service 'MSOLAP$TABULAR' }

This works great. I verify in Config Mgr that the service stops.

I also verify that the 2 below PowerShell code scripts work:

Invoke-Command -ComputerName DevServer { start-service 'MSOLAP$TABULAR' }

and

Invoke-Command -ComputerName DevServer { restart-service 'MSOLAP$TABULAR' }

Excellent. Works great.

Now I start putting things into variables like so:

$tabularinstance = 'MSOLAP$TABULAR'

$server = "DevServer"
$stop = "stop-service $tabularinstance"
$start = "start-service $tabularinstance"
$restart = "restart-service $tabularinstance"

I then show the values of the variables to verify expected results:

# show values
$tabularinstance
$stop
$start
$restart
$server

And I get the following results returned in the PowerShell console:

PS H:\> # show values
$tabularinstance
$stop
$start
$restart
$server
MSOLAP$TABULAR
stop-service MSOLAP$TABULAR
start-service MSOLAP$TABULAR
restart-service MSOLAP$TABULAR
DevServer

OK, so things seem good. I then run the below PowerShell command, expecting it to work, but nothing happens on the server; Config Mgr doesn't show the service is stopped, it's still running:

Invoke-Command -ComputerName $server { $stop }

I then realized that the $tabularinstance variable doesn't have the wrapping apostrophes, so I tried this:

$tabularinstance = '''MSOLAP$TABULAR''' # triple apostrophes needed to get the ' escape character and apply the apostrophes to variable

and then re-set the $stop, $start, and $restart variables to use the updated $tabularinstance variables:

$stop = "stop-service $tabularinstance"
$start = "start-service $tabularinstance"
$restart = "restart-service $tabularinstance"

and re-show them again to verify they include the wrapping apostrophes:

PS H:\> # show values
$tabularinstance
$stop
$start
$restart
$server
'MSOLAP$TABULAR'
stop-service 'MSOLAP$TABULAR'
start-service 'MSOLAP$TABULAR'
restart-service 'MSOLAP$TABULAR'
DevServer

Yep, they do. I then try my InvokeCommand again:

Invoke-Command -ComputerName $server { $stop }

Negative. Still did not work. In ConfigMgr, the service is still running.

I even thought to try to assign the full command to variable to see how it all resolves:

$test = "Invoke-Command -ComputerName $server { $stop }"

And then check the results:

PS H:\> $test
Invoke-Command -ComputerName DevServer { stop-service 'MSOLAP$TABULAR' }

This looks right to me too. It looks like the exact same command I did when hard-coding everything instead of using $variables.

So why isn't my PowerShell working using the $variables method? What's missing?

Answer 1

The script block works a bit different when it comes to passing variables. You have to tell the command what the variable contains on the remote computer.

As well if you want to pass the full command as a variable you will need at the type of the variable as [scriptblock].

$stop = "stop-service 'MSOLAP$TABULAR'" 
Invoke-Command -ComputerName $server { $stop } 

This needs to be changed to something like this (changed variable names to suite my example):

$MyService = 'W32Time'
$get = [scriptblock]::create( "param(`$servicename) Stop-Service `$servicename") 
Invoke-Command -ComputerName sql2014-01 -ScriptBlock $get -ArgumentList $MyService

The other way:

$MyService = 'W32Time'
Invoke-Command -ComputerName SQL2014-01 -ScriptBlock {param($servicename); Stop-Service $servicename }  -ArgumentList $MyService