SQL Monitor 10

Breaking changes between SQL Monitor 9 and SQL Monitor 10

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-AvailabilityGroupsForCluster
  • Get-AvailabilityGroupsForClusters
  • Get-AzureSqlServers
  • Get-BaseMonitors
  • Get-Clusters
  • Get-DatabasesForAzureSqlServer
  • Get-DatabasesForAzureSqlServers
  • Get-DatabasesForInstance
  • Get-DatabasesForInstances
  • Get-DisksForMachine
  • Get-DisksForMachines
  • Get-ElasticPoolsForAzureSqlServer
  • Get-ElasticPoolsForAzureSqlServers
  • Get-InstancesForCluster
  • Get-InstancesForClusters
  • Get-InstancesForMachine
  • Get-InstancesForMachines
  • Get-JobsForInstance
  • Get-JobsForInstances
  • Get-MachinesForCluster
  • Get-MachinesForClusters
  • Get-StandaloneMachines

In Version 10, all of this functionality has been simplified into the following set of cmdlets:

  • Get-SqlMonitorAvailabilityGroup
  • Get-SqlMonitorAzureSqlServer
  • Get-SqlMonitorBaseMonitor
  • Get-SqlMonitorCluster
  • Get-SqlMonitorDatabase
  • Get-SqlMonitorDisk
  • Get-SqlMonitorElasticPool
  • Get-SqlMonitorInstance
  • Get-SqlMonitorJob
  • Get-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.com

Those 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-InstancesForCluster
  • Get-InstancesForClusters
  • Get-InstancesForMachine
  • Get-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-SqlMonitorInstance

Example: 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-DatabasesForAzureSqlServer
  • Get-DatabasesForAzureSqlServers
  • Get-DatabasesForInstance
  • Get-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-SqlMonitorAnnotationGet-SqlMonitorAnnotation, Remove-SqlMonitorAnnotation and Update-SqlMonitorAnnotation.


Do you have any feedback on this documentation?

Let us know at sqlmonitorfeedback@red-gate.com


Didn't find what you were looking for?