Connect to SQLAgent on Linux via new SqlServer PowerShell module.

This post is about two very new features of SQL Server:  SqlServer PowerShell module and SQLAgent for SQL Server on Linux.

I won't cover all details of these features, just will show how we can see SQLAgent on Linux via PowerShell script.

At first we need to install SqlServer module in PowerShell. The full installation descrition you can find here: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-manage-powershell

To use SqlServer module in PowerShell you have to install it.
To run PowerShell, press "Win+R" on your keyboard, type "powershell_ise" and press "Enter".

When PowerShell ISE opens run following commands:

Import-Module SqlServer
Get-Module -Name SqlServer

When you copy-paste these commands and press F5, as the result you have to get something like this:

If you did not get that result, you can't go any further and have to troubleshoot the PowerShell module installation.

If now you run following command, you can get the list of all available cmdlets within SqlServer PowerShell module:

Get-Command -Module SqlServer

From this point we will work directly with SQL Server.

In order to establish connection you have to run following script.

The most important are 2nd and third lines:

- In second line you have to provide your SQL Server Instance address, by replacing "<your_server_instance>" by something like "192.168.58.11" or "192.168.58.11\MSSQLSERVER,1433"

- When second line runs it will ask you for SQL Server credentials !!! So, you have to enter SQL user name and it's password.

# Prompt for credentials to login into SQL Server
$serverInstance = "<your_server_instance>"
$credential = Get-Credential

# Load the SMO assembly and create a Server object
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$server = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $serverInstance

# Set credentials
$server.ConnectionContext.LoginSecure=$false
$server.ConnectionContext.set_Login($credential.UserName)
$server.ConnectionContext.set_SecurePassword($credential.Password)

# Connect to the Server and get a few properties
$server.Information | Select-Object Edition, HostPlatform, HostDistribution | Format-List

As the first result of that script you get prompt window:

IMPORTANT: Passwords IS Case Sensitive!!!

As final result you have to get these three lines:

- Your edition

- Platform

- Distribution (as of today it is available only in Linux)

Now we can play around with SQL Server we've just conncted.

At first will try to see its Errorlog:

Get-SqlErrorLog -ServerInstance $serverInstance -Credential $credential -Since Yesterday | Out-GridView

As the result, you get a new grid window, in which you can filter events, would say, by word "severity"

Then, you can get the list of all your databases on the server:

Get-SqlDatabase -ServerInstance $serverInstance -Credential $credential | Out-GridView

If you have installed SQL Server on Linux version CTP 1.4 or higher, with installed SQLAgent, you can run set of following scripts:

SQL Agent general Info:

Get-SqlAgent -ServerInstance $serverInstance -Credential $credential `
| Select-Object AgentDomainGroup, AgentLogLevel, ErrorLogFile, `
    ServiceStartMode, SqlAgentAutoStart, Name

List of SQL Agent Jobs:

Get-SqlAgent -ServerInstance $serverInstance -Credential $credential `
| Get-SqlAgentJob | Out-GridView 

List of Steps within a SQL Agent job (Please note that you have to specify "-Name" parameter. In this case it is: "Test Linux Job")

Get-SqlAgent -ServerInstance $serverInstance -Credential $credential `
| Get-SqlAgentJob -Name "Test Linux Job" | Get-SqlAgentJobStep `
| Select-Object ID, Name, SubSystem, DatabaseName, Command, `
    OnSuccessAction, OnSuccessStep, OnFailAction, OnFailStep, `
    LastRunDate, LastRunDuration, LastRunOutcome `
| Out-GridView 

Schedule details for a SQL Agent job (Please note that you have to specify "-Name" parameter. In this case it is: "Test Linux Job")

Get-SqlAgent -ServerInstance $serverInstance -Credential $credential `
| Get-SqlAgentJob -Name "Test Linux Job" | Get-SqlAgentJobSchedule `
| Select-Object Name, IsEnabled, Parent, DateCreated, ActiveStartDate, `
    ActiveStartTimeOfDay, ActiveEndDate, ActiveEndTimeOfDay, `
    FrequencyInterval, FrequencyRecurrenceFactor, `
    FrequencyRelativeIntervals, FrequencySubDayInterval, `
    FrequencySubDayTypes, FrequencyTypes 

SQL Agent Job History (Please note that you have to specify "-JobName" parameter. In this case it is: "Test Linux Job")

Get-SqlAgentJobHistory -ServerInstance $serverInstance -Credential $credential `
-JobName "Test Linux Job"`
| Select-Object RunDate, RunDuration, StepID, StepName, RunStatus, SqlSeverity, Message `
| Sort-Object -Property RunDate, StepID | Out-GridView 

You can easily filter that list by word "error" to see any problematic items:

As mentioned in the beginning, that is far not full guide on SqlServer PowerShell module and on SQL Server Agent on Linux. Both areas are very new and not well documented yet.

For more information on SqlServer PowerShell module see: https://blogs.technet.microsoft.com/dataplatforminsider/2016/06/30/sql-powershell-july-2016-update/

For more information on SQL Server on Linux see: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-overview