Breaking changes between SQL Monitor 9 and SQL Monitor 10
Published 07 October 2020
In SQL Monitor 10, we've taken the opportunity to simplify and tidy up many aspects of the SQL Monitor PowerShell API. This has resulted in a number of breaking changes, which are described here.
Downloading and importing the PowerShell API module
In Version 9, the PowerShell API module consisted of a single file, named RedgateSQM.psm1, which you could download and import into a script as follows:
Import-Module .\RedgateSQM.ps1
In Version 10, the PowerShell API module consists of multiple files that are delivered as a zip archive, named RedgateSQM.zip. Once downloaded, you need to extract the archive, before you can use it as follows:
Import-Module .\RedgateSQM\RedgateSQM.psd1
Please note that the imported module file has a .psd1 file extension, rather than .psm1.
All cmdlets now have a SqlMonitor prefix
In Version 9, the PowerShell cmdlets had relatively short names. When writing a script, the short names often made it difficult to find the SQL Monitor cmdlets amongst all the other ones available.
In Version 10, all of the SQL Monitor cmdlets now come with a "SqlMonitor" prefix. For example, Get-AlertSettings has been renamed to Get-SqlMonitorAlertSettings. In the PowerShell console you can now type Get-SqlMonitor and then press Ctrl+Space to show an autocompletion list of all available SQL Monitor cmdlets.
Most common IDEs that have PowerShell support will provide a similar autocompletion capability.
Shortening the cmdlet names
If you find that the increased length of the new SQL Monitor cmdlet names affects the readability of your scripts, you can change the prefix to something shorter by importing the PowerShell API module with an alternative prefix string. For example:
Import-Module .\RedgateSQM\RedgateSQM.psd1 -Prefix 'Sqm'
Now every cmdlet will have a "Sqm" prefix instead of "SqlMonitor". For example, Get-SqmAlertSettings instead of Get-SqlMonitorAlertSettings.
Initializing the PowerShell API module
In Version 9, the PowerShell module would have to be initialized with the following call before using any of the other cmdlets:
Initialize -AuthToken 'generated-auth-token'
In Version 10, the Initialize cmdlet has been renamed to Connect-SqlMonitor, and now requires you to explicitly provide the address of the SQL Monitor website. For example:
Connect-SqlMonitor -ServerUrl 'http://mysqlmonitorwebsite.com' -AuthToken 'generated-auth-token'
Simplified retrieval of monitored objects
In Version 9, the following cmdlets were used to access monitored objects such as clusters, SQL Server instances, Azure SQL Servers:
Get-AvailabilityGroupsForClusterGet-AvailabilityGroupsForClustersGet-AzureSqlServersGet-BaseMonitorsGet-ClustersGet-DatabasesForAzureSqlServerGet-DatabasesForAzureSqlServersGet-DatabasesForInstanceGet-DatabasesForInstancesGet-DisksForMachineGet-DisksForMachinesGet-ElasticPoolsForAzureSqlServerGet-ElasticPoolsForAzureSqlServersGet-InstancesForClusterGet-InstancesForClustersGet-InstancesForMachineGet-InstancesForMachinesGet-JobsForInstanceGet-JobsForInstancesGet-MachinesForClusterGet-MachinesForClustersGet-StandaloneMachines
In Version 10, all of this functionality has been simplified into the following set of cmdlets:
Get-SqlMonitorAvailabilityGroupGet-SqlMonitorAzureSqlServerGet-SqlMonitorBaseMonitorGet-SqlMonitorClusterGet-SqlMonitorDatabaseGet-SqlMonitorDiskGet-SqlMonitorElasticPoolGet-SqlMonitorInstanceGet-SqlMonitorJobGet-SqlMonitorMachine
All of these new cmdlets support pipelining, return multiple objects by default, and have a -Name parameter to help filter their output in order to obtain specific objects.
Get-SqlMonitorBaseMonitor
Calling Get-SqlMonitorBaseMonitor will retrieve all available Base Monitors:
C:\Users\Redgate> Get-SqlMonitorBaseMonitor Name ---- sqlmonitor-bm1.internal.red-gate.com sqlmonitor-bm2.internal.red-gate.com sqlmonitor-azure.us-east.cloudapp.azure.com
You can specify the -Name parameter to retrieve a specific Base Monitor:
C:\Users\Redgate> Get-SqlMonitorBaseMonitor -Name 'sqlmonitor-bm1.internal.red-gate.com' Name ---- sqlmonitor-bm1.internal.Red-gate.com
Get-SqlMonitorCluster
Calling Get-SqlMonitorCluster will retrieve all available clusters across all Base Monitors:
C:\Users\Redgate> Get-SqlMonitorCluster BaseMonitor Name ----------- ---- sqlmonitor-bm1.internal.red-gate.com test-wfc1.internal.red-gate.com sqlmonitor-bm1.internal.red-gate.com test-wfc2.internal.red-gate.com sqlmonitor-bm2.internal.red-gate.com win2016cluster.internal.red-gate.com
If you want to limit the search to a specific Base Monitor, you can chain together calls like this:
C:\Users\Redgate> Get-SqlMonitorBaseMonitor -Name 'sqlmonitor-bm1.internal.red-gate.com' | Get-SqlMonitorCluster BaseMonitor Name ----------- ---- sqlmonitor-bm1.internal.red-gate.com test-wfc1.internal.red-gate.com sqlmonitor-bm1.internal.red-gate.com test-wfc2.internal.red-gate.com
And finally, if you want to retrieve a specific cluster, you can use the -Name parameter, too:
C:\Users\Redgate> Get-SqlMonitorCluster -Name 'test-wfc1.internal.red-gate.com' BaseMonitor Name ----------- ---- sqlmonitor-bm1.internal.red-gate.com test-wfc1.internal.red-gate.com
Get-SqlMonitorMachine
Calling Get-SqlMonitorMachine will retrieve all machines across all Base Monitors. This includes both standalone machines and machines that are cluster nodes in a Windows failover cluster.
C:\Users\Redgate> Get-SqlMonitorMachine
BaseMonitor Name
----------- ----
sqlmonitor-bm1.internal.red-gate.com test-sql2017.internal.red-gate.com
sqlmonitor-bm1.internal.red-gate.com test-sql2019.internal.red-gate.com
test-wfc1-node1.internal.red-gate.com
test-wfc1-node2.internal.red-gate.com
test-wfc2-node1.internal.red-gate.com
test-wfc2-node2.internal.red-gate.com
sqlmonitor-bm2.internal.red-gate.com test-dave.internal.red-gate.com
sqlmonitor-bm2.internal.red-gate.com test-simon.internal.red-gate.com
win2016cluster-node1.internal.red-gate.com
win2016cluster-node2.internal.red-gate.comThose entries that have a BaseMonitor property represent standalone machines, while those without represent cluster nodes. They actually have a Cluster property, but a quirk of PowerShell means that the displayed columns are based on the first item in the list,. You can view all of the properties using PowerShell's built-in Format-List cmdlet:
C:\Users\Redgate> Get-SqlMonitorMachine | Format-List BaseMonitor : sqlmonitor-bm1.internal.red-gate.com Name : test-sql2017.internal.red-gate.com BaseMonitor : sqlmonitor-bm1.internal.red-gate.com Name : test-sql2019.internal.red-gate.com Cluster : test-wfc1.internal.red-gate.com Name : test-wfc1-node1.internal.red-gate.com Cluster : test-wfc1.internal.red-gate.com Node : test-wfc1-node2.internal.red-gate.com Cluster : test-wfc2.internal.red-gate.com Name : test-wfc2-node1.internal.red-gate.com Cluster : test-wfc2.internal.red-gate.com Name : test-wfc2-node2.internal.red-gate.com BaseMonitor : sqlmonitor-bm2.internal.red-gate.com Name : test-dave.internal.red-gate.com BaseMonitor : sqlmonitor-bm2.internal.red-gate.com Name : test-simon.internal.red-gate.com Cluster : win2016cluster.internal.red-gate.com Name : win2016cluster-node1.internal.red-gate.com Cluster : win2016cluster.internal.red-gate.com Name : win2016cluster-node2.internal.red-gate.com
Again, you can retrieve a specific machine using the -Name parameter, and you can limit the search for machines by piping a base-monitor into this cmdlet.
Example: How do I retrieve only cluster node machines?
In Version 9, you could use Get-MachinesForCluster and Get-MachinesForClusters to retrieve only those machines that represent cluster nodes. In Version 10, you can do this in one of two ways:
Get cluster node machines, method 1
C:\Users\Redgate> Get-SqlMonitorMachine | where {$_.Cluster}Here the PowerShell where cmdlet filters all machines except those that have a Cluster property, which cluster nodes have but standalone machines do not have.
Get cluster node machines, method 2
C:\Users\Redgate> Get-SqlMonitorCluster | Get-SqlMonitorMachine
Here we first retrieve all clusters using Get-SqlMonitorCluster, and then pipe those clusters into Get-SqlMonitorMachine, and so only machines belonging to a cluster are retrieved (i.e. only cluster nodes).
Both methods will output the same cluster nodes, but the second method turns out to be a bit faster.
Example: How do I retrieve only standalone machines?
In Version 9, you could use Get-StandaloneMachines to retrieve only standalone machines, and ignore cluster nodes. In Version 10, this is most easily achieved using the where cmdlet to filter the output of Get-SqlMonitorMachine to include only machines that don't belong to a cluster:
C:\Users\Redgate> Get-SqlMonitorMachine | where {-not $_.Cluster}Get-SqlMonitorInstance
In Version 9, the following cmdlets were used to retrieve SQL Server instances:
Get-InstancesForClusterGet-InstancesForClustersGet-InstancesForMachineGet-InstancesForMachines
In Version 10, SQL Server instances are retrieved through a single cmdlet, Get-SqlMonitorInstance.
Calling Get-SqlMonitorInstance without any parameters will retrieve all SQL Server instances across all Base Monitors, clusters, cluster nodes and standalone machines. Since different kinds of instance can have different properties, it's useful to format the instances using Format-List.
C:\Users\Redgate> Get-SqlMonitorInstance | Format-List
There are three kinds of SQL Server instance retrieved by this cmdlet:
- A SQL Server instance installed on a standalone machine.
- An "Always On Failover Cluster Instance", which is a SQL Server instance associated with a Windows Failover cluster.
- A SQL Server instance installed on a cluster node, which is typically part of an Always On Failover Cluster Instance.
It can take a lot of time to retrieve all SQL Server instances throughout the entire monitored estate, and it is strongly recommended that you always pipe input to this cmdlet to limit the search for instances, even if you then subsequently use the -Name parameter to retrieve a specific instance.
Example: How do I retrieve only instances running on standalone machines?
It's best to first retrieve the standalone machines using Get-SqlMonitorMachine, and then pipe those into Get-SqlMonitorInstance:
C:\Users\Redgate> Get-SqlMonitorMachine | where {-not $_.Cluster} | Get-SqlMonitorInstanceExample: How do I retrieve only Always On Failover Cluster instances?
It's best to first retrieve the clusters using Get-SqlMonitorCluster, then retrieve instances belonging to the cluster using Get-SqlMonitorInstance, and then filter out SQL Server instances that live on specific cluster nodes:
C:\Users\Redgate> Get-SqlMonitorCluster | Get-SqlMonitorInstance | where {$_.Cluster}Example: How to I retrieve SQL Server instances running on cluster nodes?
It's best to first retrieve the clusters using Get-SqlMonitorCluster, then retrieve the cluster nodes using Get-SqlMonitorMachine, and finally retrieve the SQL Server instances running on those cluster nodes using Get-SqlMonitorInstance:
C:\Users\Redgate> Get-SqlMonitorCluster | Get-SqlMonitorMachine | Get-SqlMonitorInstance
Get-SqlMonitorAzureSqlServer
In Version 10, Get-AzureSqlServers has been renamed to Get-SqlMonitorAzureSqlServer. When no parameters are specified, it will return all SQL Azure Servers across all Base Monitors:
C:\Users\Redgate> Get-SqlMonitorAzureSqlServer BaseMonitor Name ---------- ---- sqlmonitor-azure.us-east.cloudapp.azure.com sqlmonitor-azuredb-test1.database.windows.net sqlmonitor-azure.us-east.cloudapp.azure.com sqlmonitor-azuredb-test2.database.windows.net
If you want to retrieve a specific Azure SQL Server, you can use the -Name parameter. You can also pipe Base Monitors to this cmdlet to limit the search. This can be useful to speed up the search when you know that monitoring of your Azure SQL Servers is restricted to a specific Base Monitor:
C:\Users\Redgate> Get-SqlMonitorBaseMonitor -Name 'sqlmonitor-azure.us-east.cloudapp.azure.com' | Get-SqlMonitorAzureSqlServer -Name 'sqlmonitor-azuredb-test1.database.windows.net' BaseMonitor Name ---------- ---- sqlmonitor-azure.us-east.cloudapp.azure.com sqlmonitor-azuredb-test1.database.windows.net
Get-SqlMonitorDatabase
In Version 9, the following cmdlets were used to retrieve databases:
Get-DatabasesForAzureSqlServerGet-DatabasesForAzureSqlServersGet-DatabasesForInstanceGet-DatabasesForInstances
In Version 10, all databases are retrieved using the Get-SqlMonitorDatabase cmdlet. When called without any parameters, the cmdlet will return all databases for all SQL Server instances and Azure SQL Server instances across all Base Monitors. This can be quite slow, and it would be more usual to restrict the search for databases to specific SQL Server or Azure SQL Server instances. You can do this by piping the output of Get-SqlMonitorInstance or Get-SqlMonitorAzureSqlServer cmdlets into Get-SqlMonitorDatabase. For example, you can retrieve all Azure SQL Server databases using the following:
C:\Users\Redgate> Get-SqlMonitorAzureSqlServer | Get-SqlMonitorDatabase AzureSqlServer Name -------------- ---- sqlmonitor-azuredb-test1.database.windows.net AdventureWorks sqlmonitor-azuredb-test1.database.windows.net sqlservercentral-test sqlmonitor-azuredb-test1.database.windows.net testdb sqlmonitor-azuredb-test2.database.windows.net Database1 sqlmonitor-azuredb-test2.database.windows.net Database2 sqlmonitor-azuredb-test2.database.windows.net RedGateMonitor
Here's how to retrieve all databases hosted on a specific SQL Server instance:
C:\Users\Redgate> Get-SqlMonitorMachine -Name 'test-simon.internal.red-gate.com' | Get-SqlMonitorInstance -Name 'sql2019' | Get-SqlMonitorDatabase Instance Name -------- ---- test-simon.internal.red-gate.com\sql2019 master test-simon.internal.red-gate.com\sql2019 model test-simon.internal.red-gate.com\sql2019 msdb test-simon.internal.red-gate.com\sql2019 tempdb test-simon.internal.red-gate.com\sql2019 RedGateMonitor
Get-SqlMonitorAvailabilityGroup
Get-AvailabilityGroupsForCluster and Get-AvailabilityGroupsForClusters have been replaced by Get-SqlMonitorAvailabilityGroup.
You can retrieve all availiability groups by calling this cmdlet without any parameters:
C:\Users\Redgate> Get-SqlMonitorAvailabilityGroup
You can narrow the search by piping a cluster to this cmdlet, or by specifying the -Name parameter. For example, here's how to find a specific availability group on a specific cluster:
C:\Users\Redgate> Get-SqlMonitorCluster -Name 'sqlmonitor-bm1.internal.red-gate.com' | Get-AvailabilityGroup -Name 'AG1'
Get-SqlMonitorElasticPool
Get-ElasticPoolsForAzureSqlServer and Get-ElasticPoolsForAzureSqlServers have been replaced by Get-SqlMonitorElasticPool.
You can retrieve all elastic pools for all Azure SQL Servers by calling this cmdlet without any parameters:
C:\Users\Redgate> Get-SqlMonitorElasticPool
You can narrow the search by piping an Azure SQL Server to this cmdlet, or by specifying the -Name parameter. For example, here's how to find a specific elastic pool on a specific Azure SQL Server:
C:\Users\Redgate> Get-SqlMonitorAzureSqlServer -Name 'sqlmonitor-azuredb-test1.database.windows.net' | Get-SqlMonitorElasticPool -Name 'sqlmonitor-ep'
Get-SqlMonitorDisk
Get-DisksForMachine and Get-DisksForMachines have been replaced by Get-SqlMonitorDisk.
You can retrieve all disks for all montored machines by calling this cmdlet without any parameters:
C:\Users\Redgate> Get-SqlMonitorDisk
This can potentially return many results, depending on how many servers you're monitoring. It's more usual to narrow the search by piping specific machines to this cmdlet. You can also specify the -Name parameter to find a specific disk. For example, here's how to find a specific disk on a specific machine:
C:\Users\Redgate> Get-SqlMonitorMachine -Name 'test-sql2017.internal.red-gate.com' | Get-SqlMonitorDisk -Name 'C:'
Get-SqlMonitorJob
Get-JobsForInstance and Get-JobsForInstances have been replaced by Get-SqlMonitorJob.
You can retrieve all jobs across all SQL Server instances by calling this cmdlet without any parameters:
C:\Users\Redgate> Get-SqlMonitorJob
This can potentially return very many results, and it's usual to narrow the search by piping specific SQL Server instances into this cmdlet, or by specifying the -Name parameter. For example, here's how to find a specific job on a specific SQL Server instance:
C:\Users\Redgate> Get-SqlMonitorMachine -Name 'test-simon.internal.red-gate.com' | Get-SqlMonitorInstance -Name 'sql2019' | Get-SqlMonitorJob -Name 'syspolicy_purge_history'
Adding and removing monitored objects
Add-SqlMonitorMonitoredEntity
Add-MonitoredEntity has been renamed to Add-SqlMonitorMonitoredObject. This is primarily to eliminate the term "monitored entity" and improve naming consistency in PowerShell API module.
Remove-SqlMonitorMonitoredObject
Remove-MonitoredEntity has been renamed to Remove-SqlMonitorMonitoredObject. This eliminates the term "monitored entity" and improves naming consistency in the PowerShell API module. However, the new cmdlet's parameters are different.
In Version 9, to remove a monitored object you needed to specify both its name and the Base Monitor that was being monitored by. For example:
$basemonitor = Get-BaseMonitors | where {$_.Name -eq 'sqlmonitor-bm1.internal.red-gate.com'}
Remove-MonitoredEntity -BaseMonitor $basemonitor -MonitoredEntity 'test-wfc1.internal.red-gate.com'In Version 10, the -BaseMonitor parameter has been removed and the -MonitoredObject parameter no longer accepts the name of the item to remove. It now requires an object retrieved using one of the Get-SqlMonitorCluster, Get-SqlMonitorMachine or Get-SqlMonitorAzureSqlServer cmdlets. This is probably easiest to understand by example, so here's how to remove a specific named custer:
C:\Users\Redgate> Get-SqlMonitorCluster -Name 'test-wfc1.internal.red-gate.com' | Remove-SqlMonitorMonitoredObject
The annotation cmdlets now require the use of instances
In Version 9, the cmdlets for working with annotations all had a -Target parameter whose type was object. The type has now changed to Instance, which matches the type of the results retuned by Get-SqlMonitorInstance.
The affected cmdlets are Add-SqlMonitorAnnotation, Get-SqlMonitorAnnotation, Remove-SqlMonitorAnnotation and Update-SqlMonitorAnnotation.
