Having discovered that SQL Agent PowerShell steps use PowerShell 2.0, I found you can use the latest version of PowerShell on your server by invoking PowerShell and passing it the path to a script file:
powershell.exe -file “\\servername\path\filename.ps1”
I thought it would make the job easier to port to a new server one day if, instead of \\servername, I used \\127.0.0.1 but PowerShell said that the current execution policy prevented running remote unsigned scripts. (Our execution policy was RemoteSigned.)
I don’t understand why it treats the names this way. According to my reading, you can spoof \\machinename simply by adding it to the hosts file and redirecting it elsewhere. But, although you can do that with 127.0.0.1, it doesn’t work so I would have though PowerShell would treat such a script as local.
I’m not sure what’s going on here but it’s one to remember.
Like many DBAs, I suspect, I just haven’t found much need for PowerShell so far. There were lots of articles about how you could backup a database, run a query, get a list of databases etc. I just wasn’t seeing anything I couldn’t already do just fine in T-SQL. But with many Microsoft server products, PowerShell is taking over as the main management tool with the GUI serving as just a thin skin over the PowerShell API. Could this be coming our way?
So I’ve been meaning to give it a try and I just had a request to set up a daily FTP upload of some data to a partner organisation. I don’t know the command line stuff for FTP so this looked like a good excuse to learn a bit of PowerShell. After a while I had a script that worked nicely until I put it into a PowerShell step in a SQL Agent job. Then it failed complaining about how I was specifying non-existent arguments for a cmdlet. WTF?
After a lot of digging, I discovered that SQL Agent shells your script out to PowerShell 2.0. This is SQL Server 2014 and PowerShell 2.0 was RTMd in 2009. We’re up to PowerShell 5.0 now.
I imagine there are sound technical reasons for this but it’s a bit disappointing after all the hype.