In the fourth level of this series I outlined the goodies of scripting options some cool operations to do with them, and automating simple tasks Now let´s take a look into the new and awesome SQLServer module.
The SQLServer Module
Microsoft made a huge step forward when they deprecated the SQLPS module. Yes, it is deprecated and will not be updated anymore. To replace it, they created the SQLServer module.
The fact is we still have two PowerShell modules. SQLPS and SQLServer. SQLPS as I said is deprecated but is still included in the SQL Server installation (for backwards compatibility only). The SQLServer module contains updated versions of cmdlets of SQLPS and new cmdlets to support the new SQL Server features. The SQLServer module is included starting with version 16.x of SSMS and in all the newew versions of SSMS . The module is installed via PowerShell Galley. The PowerShell Gallery is the place to find PowerShell code that is shared by the community, Microsoft, and other companies.
Installation
To Install with administrator rights, run this:
Install-Module -Name SqlServer
For the non-Administrator, use this code:
Install-Module -Name SqlServer -Scope CurrentUser
When you have other versions installed , you just update the module like this:
Update-Module -Name SqlServer
Or install with –AllowClobber option, which will ensure you overwrite any conflicting methods.
Install-Module -Name SqlServer -AllowClobber
To view the versions of the module installed:
Get-Module SqlServer -ListAvailable
This returns the version and the commands available.
Now we can import the module for use in a PowerShell session.
import-module sqlserver
With this code, we check what are the cmdlets of the module.
get-command -Module sqlserver
You can see there we have 105 cmdlets.
What's Changed
What’s changed is now the SQLServer module incorporates all the sqlps cmdlets and will be maintained and updated in the future. For instance, if you liked to use the SQL Server provider as file system, you still can, as shown here:
You can use the server itself and navigate as though it were a filesystem as before:
Some Cool Operations Using the SQLServer Module
In this section we will cover:
- get-sqlagentjob to get all information from the Agent Jobs
- Get-Sqlbackuphistory to get the backup history
- Get-SqlInstance to return information about the SQL Server Instance
- Add-SqlLogin to add a new login
- Get-SqlLogin to get the SQL Servers Login
- Create and check a login with credentials
- Backup-SQLDatabase – to back up a Database
- Get-SQLErrorlog – to check the SQL Server Error Logs
- The differences between Read-Sqltabledata and Invoke-Sqlcmd
Let´s start with selecting all information from SQL Agent. This information is useful to get some properties from the Agent such as the Category of the Agent (the JobCategories property) , if the Agent has alerts or operators configured (alerts and operator property), the jobs that are part of the Agent (the Jobs array), and the service account running the agent (AgentDomainGroup Property). We can get information with this code:
get-sqlagent -ServerInstance DESKTOP-SQEVVO1
We can see the results below:
PS C:\WINDOWS\system32> get-sqlagent -ServerInstance DESKTOP-SQEVVO1
Parent : [DESKTOP-SQEVVO1]
Name : DESKTOP-SQEVVO1
JobCategories : {[Uncategorized (Local)], [Uncategorized (Multi-Server)], Data Collector, Database Engine Tuning Advisor...}
OperatorCategories : {[Uncategorized]}
AlertCategories : {[Uncategorized], Replication}
AlertSystem : [DESKTOP-SQEVVO1]
Alerts : {}
Operators : {}
TargetServers : {}
TargetServerGroups : {}
Jobs : {CommandLog Cleanup, DatabaseBackup - SYSTEM_DATABASES - FULL, DatabaseBackup - USER_DATABASES - DIFF, DatabaseBackup - USER_DATABASES - FULL...}
SharedSchedules : {CollectorSchedule_Every_10min, CollectorSchedule_Every_15min, CollectorSchedule_Every_30min, CollectorSchedule_Every_5min...}
ProxyAccounts : {}
SysAdminOnly :
AgentDomainGroup : NT SERVICE\SQLSERVERAGENT
AgentLogLevel : Errors, Warnings
AgentMailType : SqlAgentMail
AgentShutdownWaitTime : 15
DatabaseMailProfile :
ErrorLogFile : C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\SQLAGENT.OUT
HostLoginName :
IdleCpuDuration : 600
IdleCpuPercentage : 10
IsCpuPollingEnabled : False
JobServerType : Standalone
LocalHostAlias :
LoginTimeout : 30
MaximumHistoryRows : 1000
MaximumJobHistoryRows : 100
MsxAccountCredentialName :
MsxAccountName :
MsxServerName :
NetSendRecipient :
ReplaceAlertTokensEnabled : False
SaveInSentFolder : False
ServiceAccount : NT Service\SQLSERVERAGENT
ServiceStartMode : Manual
SqlAgentAutoStart : False
SqlAgentMailProfile :
SqlAgentRestart : True
SqlServerRestart : True
WriteOemErrorLog : False
ParentCollection :
Urn : Server[@Name='DESKTOP-SQEVVO1']/JobServer
Properties : {Name=AgentLogLevel/Type=Microsoft.SqlServer.Management.Smo.Agent.AgentLogLevels/Writable=True/Value=Errors, Warnings,
Name=AgentShutdownWaitTime/Type=System.Int32/Writable=True/Value=15, Name=ErrorLogFile/Type=System.String/Writable=True/Value=C:\Program Files\Microsoft
SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\SQLAGENT.OUT, Name=HostLoginName/Type=System.String/Writable=False/Value=...}
ServerVersion : 16.0.1105
DatabaseEngineType : Standalone
DatabaseEngineEdition : Enterprise
ExecutionManager : Microsoft.SqlServer.Management.Smo.ExecutionManager
UserData :
State :Let’s use the JobCategories property, as it is an array, I know this is an array because of the {} in the visualization. We pipe the results through Select-Object and then we need to use –expandproperty parameter to get all the items for this property. We can use this code:
get-sqlagent -ServerInstance DESKTOP-SQEVVO1 | Select-Object -ExpandProperty JobCategories
Now, we see the properties listed:
PS C:\WINDOWS\system32> get-sqlagent -ServerInstance DESKTOP-SQEVVO1 | Select-Object -ExpandProperty JobCategories Name ---- [Uncategorized (Local)] [Uncategorized (Multi-Server)] Data Collector Database Engine Tuning Advisor Database Maintenance Full-Text Jobs from MSX Log Shipping REPL-Alert Response REPL-Checkup REPL-Distribution REPL-Distribution Cleanup REPL-History Cleanup REPL-LogReader REPL-Merge REPL-QueueReader REPL-Snapshot REPL-Subscription Cleanup
We can do the same thing with the jobs property. We can see the code and results below:
get-sqlagent -ServerInstance DESKTOP-SQEVVO1 | Select-Object -ExpandProperty jobs
The results I see for this instance are:
We can also pick specific properties to examine. Just use the select-object cmdlet with the property(s) you want to retrieve separated by commas. Here we add name, agentdomain, and state:
get-sqlagent -ServerInstance DESKTOP-SQEVVO1 | Select-Object name, AgentDomainGroup, state
We see the results shown below:
Other information you may want to retrieve is the jobs that are part of the SQL Agent. For that we use the Get-SqlagentJob. This cmdlet gives to you a macro vison of the Agent jobs. If you want specific details you need to select properties as we will see below. Here is the code to get a list of jobs.
get-sqlagentjob -ServerInstance DESKTOP-SQEVVO1
This returns some basic information about the jobs.
If I want to get information of the jobs we can get the properties and methods that we may use:
get-sqlagentjob -ServerInstance DESKTOP-SQEVVO1 | Get-member
This returns the various properties and methods available from the results of get-sqlagentjob.
Let’s use some information from the cmdlet. We can get this information from the jobs: Name, Date Created, Last modified date, and if the job is enabled. We use this code to select data:
get-sqlagentjob -ServerInstance 'DESKTOP-SQEVVO1' | Select Datecreated, Datelastmodified, isenabled
The property that returns if the last run of the job failed or not is LastRunOutcome. We can just select this property to know the last status of the job:
get-sqlagentjob -ServerInstance 'DESKTOP-SQEVVO1' |select name,
If we want to return only the failed jobs, we need to add a condition in the code with Where-Object. Note the structure of the comparison inside the braces:
get-sqlagentjob -ServerInstance 'DESKTOP-SQEVVO1' |
Where-Object {$_.lastrunoutcome -eq 'Failed'} |
Select-Object name, lastrunoutcome
This returns only the one failed job from above.
We can also get the schedules for jobs with:
Get-SqlAgentJob -ServerInstance DESKTOP-SQEVVO1 | Get-SqlAgentJobSchedule
Or all the steps for jobs with this code:
Get-SqlAgent -ServerInstance DESKTOP-SQEVVO1 | Get-SqlAgentJob | Get-SqlAgentJobStep
That isn't all that useful, but what if we want to get the steps for one job? We can get those for the job job that starts with BackupDatabase in the name with this code:
Get-SqlAgent -ServerInstance DESKTOP-SQEVVO1 |
Get-SqlAgentJob |
where-object {$_.name -like "DatabaseBAckup*"} |
Get-SqlAgentJobStep
It´s very important get job history to make sure all your jobs are running successfully. For that we use the Get-SQLAgentJobHistory and it will show the history of the jobs with the server and message properties.
Get-SqlAgentJobHistory -ServerInstance DESKTOP-SQEVVO1
The next cmdlet will get the general information of SQL Server instance.
Get-SqlInstance -ServerInstance DESKTOP-SQEVVO1
You can see this returns the SQL Server version and host version.
We can also get all the databases by chaining another cmdlet onto the previous one.
Adding a Login
A common task is to add a new login to an instance. When you add a new SQL login, you need to provide a password. We can use this code to avoid that:
Add-SqlLogin -ServerInstance DESKTOP-SQEVVO1 -LoginName "MyNewLogin" -LoginType "SqlLogin" -Enable
Since we have not included the loginpscredntial parameter, a prompt appears for you to provide the password , as showing in the image below:
You can enter a password, which will be used for the login.
You can create a pscredential to suppress the prompt :
$secpasswd = ConvertTo-SecureString "test" -AsPlainText -Force
$mycreds = New-Object System.Management.Automation.PSCredential ("mylogin", $secpasswd)
Add-SqlLogin -ServerInstance DESKTOP-SQEVVO1-LoginName "mylogin" -LoginType "SqlLogin" -LoginPSCredential $mycreds -EnableWe can check the login with Get-SqlLogin to verify it was added.
Get-SqlLogin -ServerInstance DESKTOP-SQEVVO1
We can get more information about logins, such as checking if a login is disabled with this code:
Get-SqlLogin -ServerInstance DESKTOP-SQEVVO1 -Disabled
We might use this to verify that "sa" is disabled on our instances.
Reading the SQL Server Error Log
When comes troubleshooting SQL Server, one of the more important action is to read the SQL Server error log. To get the SQL Server Error log, run this :
Get-SqlErrorLog -ServerInstance DESKTOP-SQEVVO1
This returns the entries as a series of objects.
We can also filter the log in different ways. We can see yesterday's entries with:
Get-SqlErrorLog -ServerInstance DESKTOP-SQEVVO1 -Since Yesterday
We can get look at the log before or after a date with this type of code:
Get-SqlErrorLog -ServerInstance DESKTOP-SQEVVO1 -After (Get-Date).AddDays(-2) Get-SqlErrorLog -ServerInstance DESKTOP-SQEVVO1 -before (Get-Date).AddDays(-3)
Try these and see what is returned on your instance.
Querying a Table
We have a cmdlet to read data from a table in Read-SqlTableData. Here I use the code to read from a specific table:
Read-SqlTableData -ServerInstance DESKTOP-SQEVVO1 -DatabaseName adventureworks2022 -TableName AWBuildVersion -SchemaName dbo
The output is
To be honest IMHO it´s a waste of time since we already have invoke-sqlcmd. They could have used the time and effort to create new and functional cmdlets. I can get the same result if I use Invoke-SQLcmd.
Invoke-Sqlcmd -ServerInstance DESKTOP-SQEVVO1 -Database adventureworks2022 -Query "Select * from humanresources.department"
Automating a Simple Task
To automate a backup database we can use both SMO and the SQL Server cmdlets. You can see that using SQL Server cmdlets is easier because you don’t need to have a previously knowledge of SMO . Both solutions work fine and you can choose what is the best option for your environment.
Here is the code for backing up all user databases using SMO.
$ServerName = DESKTOP-SQEVVO1
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null #A
$Server = new-object ('Microsoft.SqlServer.Management.Smo.Server') $serverName #E
$server.databases |
where {$_.IsSystemObject -eq $false -and $_.IsAccessible -eq $true}|
foreach-object {
Backup-SqlDatabase -ServerInstance $servername -Database $_.name -BackupFile "c:\backup\$($_.name).bak"
}
The code is much simpler and easier to read with PoSh SQLServer module cmdlets.
Get-SqlDatabase -ServerInstance 'DESKTOP-SQEVVO1' |
ForEach-Object {
if ($_.name -ne 'tempdb') {
Backup-SqlDatabase -ServerInstance 'DESKTOP-SQEVVO1' -Database $_.name -BackupFile "c:\temp\databases\DESKTOP-SQEVVO1_$($_.name).bak"
}
}
To make this easier, I can create a text file called servers.txt with the name of your SQL Server servers. I my case I have a SQL Server default instance called DESKTOP-SQEVV01, but you can add more as in the below image.
Just remember that the cmdlet will use Windows Authentication with this code:
get-content c:\temp\servers.txt |
ForEach-Object {
$serverinstance = $_
Get-SqlDatabase -ServerInstance $_ |
ForEach-Object {
if ($_.name -ne 'tempdb') {
Backup-SqlDatabase -ServerInstance $serverinstance -Database $_.name -BackupFile "c:\temp\databases\$($serverinstance)_$($_.name).bak"
}
}
}
We can automate this with PowerShell Jobs. When we use PowerShell jobs, PoSh concurrently runs commands and scripts through jobs in another scope. It means you don’t see the return of these jobs unless you use the cmdlets: Get-Job, Receive-Job, or Wait-Job. Here is some code to use a PoSh job.
needget-content c:\temp\servers.txt |
ForEach-Object {
$serverinstance = $_
Get-SqlDatabase -ServerInstance $_ |
ForEach-Object {
if ($_.name -ne 'tempdb') {
$dbname = $_.name
$job = start-job -scriptblock { Backup-SqlDatabase -ServerInstance $using:serverinstance -Database $using:dbname -BackupFile "c:\temp\databases\$($using:serverinstance)_$($using:dbname).bak" }
$pso = new-object psobject -property @{Server=$serverinstance;DbName=$dbname;JobId=$job.Id}
Register-ObjectEvent -InputObject $job -EventName Statechanged -MessageData $pso
$counter++
}
}
}
Get-job | receive-job
The problem with PowerShell Jobs are they run I another scope, as I said before, so it's hard to control and get the output. To solve this problem we will use PowerShell Events. By registering the event in the job we can be notified when status of the job change to completed. The cmdlet responsible for this is Register-ObjectEvent.
$counter = 0
get-content c:\temp\servers.txt |
ForEach-Object {
$serverinstance = $_
Get-SqlDatabase -ServerInstance $_ |
ForEach-Object {
if ($_.name -ne 'tempdb') {
$dbname = $_.name
$job = start-job -scriptblock { Backup-SqlDatabase -ServerInstance $using:serverinstance -Database $using:dbname -BackupFile "c:\temp\databases\$($using:serverinstance)_$($using:dbname).bak" }
$pso = new-object psobject -property @{Server=$serverinstance;DbName=$dbname;JobId=$job.Id}
Register-ObjectEvent -InputObject $job -EventName Statechanged -MessageData $pso
$counter++
}
}
}
1..$counter |
foreach-object {
$eventsource = Wait-Event
switch ($eventsource.SourceEventArgs.JobStateInfo.State){
"Failed" {
Write-Output "Backup $($eventsource.MessageData.server) - $($eventsource.MessageData.dbname) failed"
receive-job $eventsource.MessageData.jobid
}
"Completed" {
Write-Output "Backup $($eventsource.MessageData.server) - $($eventsource.MessageData.dbname) Completed"
}
"Running" {
Write-Output "Backup $($eventsource.MessageData.server) - $($eventsource.MessageData.dbname) is running"
}
}
remove-event $eventsource.SourceIdentifier
}
Get-event | remove-event
Get-EventSubscriber | Unregister-Event
We get the results from our console:
Now let’s cause an error by
if ($_.name -ne 'tempdb')
to
if ($_.name –eq 'tempdb')
and let’s see what happens. We see this errors out below:
We can make this run faster by using Foreach parallel, available in PowerShell (Core) v7+ only.
get-content c:\temp\servers.txt |
ForEach-Object {
$serverinstance = $_
Get-SqlDatabase -ServerInstance $_ |
ForEach-Object -Parallel {
if ($_.name -ne 'tempdb') {
$dbname = $_.name
Backup-SqlDatabase -ServerInstance $serverinstance -Database $_.name -BackupFile "c:\temp\databases\$($serverinstance)_$($_.name).bak"
}
} -ThrottleLimit 5
}
Summing Up
In this article we covered the new sqlserver module and it's use through examples of the sqlserver module cmdlets. We saw how to use PowerShell Jobs and the Register-ObjectEvent cmdlet in real world as well.
