Hello!

I’ve been writing a PowerShell Module that will handle deploying SQL Server Agent Jobs by using the Agent namespace in SMO. But outside of work I’ve been very much into playing around with Linux: I’m slowly making my way through Linux From Scratch; I’ve got Pi-hole setup on a Raspberry Pi; and my laptop dual boots into Windows and Ubuntu. And the last time I booted into Windows was May.

And so seeing as SQL Server Agent, PowerShell and SMO are all available on Ubuntu it made sense for me to test out my module to see if it will working across SQL Server for Linux as well as SQL Server.

I’ve got .NET Core installed, as well as Visual Studio Code with the PowerShell plugin already, but I needed SMO. A quick Google lead me to the SQLToolService project on GitHub: the blub on their wiki states that it is a  “.Net Core-based application supporting SQL Connection, Query Execution, Intellisense and other actions. It’s intended for use together with a front-end app such as the MSSQL extension for Visual Studio Code. ”

Sounds just like what I needed. So I installed it and tried to make a connection to a SQL Server on Linux instance using SMO: the error was “new-object : Cannot find type [Microsoft.SqlServer.Management.Smo.Server]: verify that the assembly containing this

type is loaded.”

Oh! I thought it was all going to work so smoothly! But then again this is Linux, and not only is PowerShell Core still on beta, but SQL Tools Service is on alpha releases. So if I’m not expecting a few issues along the way then I clearly haven’t learned much from my 10 years in software development…

After 90 minutes of fruitless tinkering I happened to find a bug raised on the SQL Tools Service project, that mimicked exactly the same issue. Turns out it’s an issue with using beta 3 of PowerShell Core. So rolling back to PowerShell beta 2 will resolve the issue.

The easiest way to rollback is to download the .deb (assuming you’re on Debian/Ubuntu) and then install from there. It overwrites the beta 3 version, but that’s fine by me. The full instructions for other versions are here.

Then I needed to update Visual Studio Code: by changing the settings and explicitly stating which version of PowerShell I was using, and by re-loading the terminal I was now able to use SMO no issues.

So, to briefly sum up, to use SMO on Linux, you need to do the following:

• Install .NET Core 2.0
• Install PowerShell beta 2
• Install SQL Tool Service

You can use PowerShell from the Terminal, but I prefer something like an IDE so this is optional:

• Download Visual Studio Code
• Install PowerShell plugin
• Change settings file to point explicitly to PowerShell beta 2.

None of the above is particularly challenging. Here is a script I’ve written to test the connection.

View the code on Gist.

But what of being able to create Jobs etc? Well at this point it’s pretty clear that you can use SMO to create SQL Agent Jobs. Below is a script that expands upon the one above.

View the code on Gist.

The job won’t exist first time around, so re-run and it will spit out the name. There are a few features missing from SQL Agent on Linux:

• Subsystems: CmdExec, PowerShell, Replication Distributor, Snapshot, Merge, Queue Reader, SSIS, SSAS, SSRS
• Alerts
• DB Mail
• Log Shipping
• Log Reader Agent
• Change Data Capture

I am mightily impressed by the effort of getting Microsoft technologies working on Linux distros, and I’m pleased to have spent some time putting some things together on Linux to verify how they work.