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
- 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
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