The following cmdlets help you automate the administration of your SQL Diagnostic Manager deployments in different environments:

To use SQL Diagnostic Manager snapin, you have to enable the provider and register the SQLDM drive.

PowerShell Cmdlets in local environments

The examples provided below provide a small sample of the parameters that are available with each cmdlet. The get-help cmdlet can be used to obtain more information regarding each cmdlet including all of the available parameters.

For example, the following command can be used to get more information on the Set-SQLdmMonitoredInstance cmdlet:

get-help Set-SQLdmMonitoredInstance

or

get-help Set-SQLdmMonitoredInstance -detailed 

The list of cmdlets and their descriptions for the local environment can be found below:

Cmdlet Name

Description

Examples

Escape-SQLdmName

Allows you to specify server names containing special characters such as |\?/<>[]:*

Escape-SQLdmName Server\Instance

    • Escapes the instance name Server\Instance.

Escape-SQLdmName Server\Instance1,Server\Instance2

    • Escapes the array of instance names.

Escape-SQLdmName Server\Instance | Escape-SQLdmName -undo

    • Escapes the instance name Server\Instance and then undo the operation.

Set-SQLdmMonitoredInstance (Escape-SQLName Server\Instance1) - WindowsAuthentication

    • Escapes the instance name for a Windows user.

Get-SQLServers

Retrieves a list of SQL Servers on the network

Get-SQLServers

    • Returns a list of all known SQL Servers.

Get-SQLServers -localonly

    • Returns a list of SQL Servers running on the local machine.

Grant-SQLdmPermissions

Grants SQL Diagnostic Manager permissions on a server

Grant-SQLdmPermission -Path dm:\appsecurity\AUser\AServer -Permission View

    • Grants View permissions to instance 'AServer' to user 'AUser'.

Grant-SQLdmPermission -Path("dm:\appsecurity\" + (Escape-SQLdmName -Name MyDomain\AUser)) -Name AServer -Permission Modify

    • Grants Modify permissions to instance 'AServer' to Windows user 'MyDomain\AUser'.

Grant-SQLdmPermission -Path dm:\appsecurity\AUser -Tag Prod -Permission Modify

    • Grants a user permissions to instances tagged as Prod. 
New-SQLdmAlertTemplate
Associates a template to a monitored instance or a tag
New-SQLdmAlertTemplate

  • Enter the following parameters when using this command: TemplateName, DataSource, and RepositoryName or Tag. For example:

    New-SQLdmAlertTemplate
    PS dm:\> New-SQLdmAlertTemplate -DataSource DESKTOP-IF6DHB -RepositoryName SQLdmRepository -RepositoryInstances DESKTOP
-IF6DHB -TemplateName "Default Template"

New-SQLdmDrive

Creates a drive for connecting to the SQLdm Repository

New-SQLdmDrive dm MYSQLDMSERVER SQLdmRepository

    • Creates a new SQL Diagnostic Manager drive called 'dm' that is connected to the SQLdmRepository database on the MYSQLDMSERVER SQL Server using Windows Authentication.

New-SQLdmDrive dm MYSQLDMSERVER SQLdmRepository -Credential sa

    • Creates a new SQL Diagnostic Manager drive called 'dm' that is connected to the SQLdmRepository database on the MYSQLDMSERVER SQL Server using SQL Authentication. Note that Powershell will prompt for the password.

New-SQLdmMonitoredInstance

Adds a SQL Server Instance to monitor in SQL Diagnostic Manager

New-SQLdmMonitoredInstance -Path Instance1 -WindowsAuthentication -tags production,finance,critical

    • Adds a new instance named Instance1 to SQL Diagnostic Manager.

New-SQLdmMonitoredInstance -Path (Escape-SQLdmName -Name ServerA\Instance1) -Credential sa

    • Adds a new instance named ServerA\Instance1 to SQL Diagnostic Manager.

New-SQLdmUser

Creates a new SQL Diagnostic Manager user

New-SQLdmUser -Path fred -SQLLogin -Password xxxx -Comment "Fred is an admin"

    • Creates a new user called fredthat uses SQL Authentication . Note that if the password is not specified, PowerShell will prompt for the password.

New-SQLdmUser (Escape-SQLdmName -Name mydomain\fred) -WindowsUser

    • Creates a new user called mydomain\fred that uses Windows Authentication.

Remove-SQLdmMonitoredInstance

Removes a SQL Server Instance from SQL Diagnostic Manager monitoring

Remove-SQLdmMonitoredInstance -Path \Instances\ServerA

    • Removes the instance named ServerA from SQL Diagnostic Manager.

Remove-SQLdmMonitoredInstance (Escape-SQLdmName -Name ServerA\Instance1) -retaindata

    • Removes the instance named ServerA from SQL Diagnostic Manager but retains its data for reporting.

Remove-SQLdmUser

Removes an existing SQL Diagnostic Manager user

Remove-SQLdmUser -Path /AppSecurity/fred

    • Removes user fredfrom SQL Diagnostic Manager.

Remove-SQLdmUser (Escape-SQLdmName -Name MyDomain\AUser)

    • Removes Windows user MyDomain\AUser from SQL Diagnostic Manager.

Revoke-SQLdmPermission

Revokes SQL Diagnostic Manager permissions on a server

Revoke-SQLdmPermission -Path \AppSecurity\fred\ServerA

    • Revokes user fred's permissions to instance ServerA.

Revoke-Permission -Path /AppSecurity/fred -Name ServerA

    • Revokes user fred's permissions to instance ServerA.

Revoke-Permission -Path /AppSecurity/fred -Tag Prod

    • Revokes user fred's permissions to instances tagged as Prod.

Revoke-SQLdmPermission -Path ("dm:\AppSecurity\" + (Escape-SQLdmName -Name MyDomain\UserA)) -Name (Escape-SQLdmName -Name ServerA\Instance1)

    • Revokes Windows user MyDomain\UserA permission to ServerA\Instance1.

Set-SQLdmAppSecurity

Enables or disables Application Security for SQL Diagnostic Manager

Set-SQLdmAppSecurity \AppSecurity -Enabled

    • Enables SQL Diagnostic Manager Application Security.

Set-SQLdmAppSecurity \AppSecurity -Disabled

    • DisablesSQL Diagnostic Manager Application Security.
Set-SQLdmLogging
Sets the logging level of the SQL Diagnostic Manager provider

Use Set-SQLdmLogging for debugging purposes only. This cmdlet should be used at the request of IDERA Technical Support.

Set-SQLdmMonitoredInstance

Configures options on your SQL Server Instance

Set-SQLdmMonitoredInstance -Path Instance1,Instance2,Instance3 -Credential sa

    • Updates the credentials used for Instance1,Instance2, and Instance3 to use SQL Authentication and sets a new login and password. Note that PowerShell will prompt for the password.

Set-SQLdmMonitoredInstance (Escape-SQLdmName -Name ServerA\Instance1) -WindowsAuthentication

    • Updates the credentials used for ServerA\Instance1 to use Windows Authentication.

Set-SQLdmUser

Set the properties of an existing SQL Diagnostic Manager user

Set-SQLdmUser -path \AppSecurity\bozo -Disabled -Comment "Bozo is disabled."

    • Disables the Set SQL Diagnostic Manager user bozo.

Set-SQLdmUser fred -Enabled -Administrator

    • Enables SQL Diagnostic Manager user fred and gives him administrator rights.

Set-SQLdmUser (Escape-SQLdmName -Name mydomain\fred) -Disabled

    • Disables the SQL Diagnostic Manager user mydomain\fred.

 PowerShell Cmdlets in DMSO environments

Cmdlet Name

Description

Examples
New-SQLdmMonitoredInstanceAdds a SQL Server Instance to monitor in DMSO 

New-SQLdmMonitoredInstance -Path Instance1 -Cluster cluster1 -WindowsAuthentication

  • Adds a new instance named Instance1 to the SQL DMSO environment.

New-SQLdmMonitoredInstance -Path (Escape-SQLdmName -Name ServerA\Instance1) -Cluster cluster1 -Credential sa

  • Adds a new instance named ServerA\Instance1 to SQL DMSO environment.
Remove-SQLdmMonitoredInstance

Removes a SQL Server Instance from SQL DMSO environments monitoring

This cmdlet is the same for clusters and local environments.

Remove-SQLdmMonitoredInstance -Path \Instances\ServerA

    • Removes the instance named ServerA from SQL DMSO environment.

Remove-SQLdmMonitoredInstance (Escape-SQLdmName -Name ServerA\Instance1) -retaindata

    • Removes the instance named ServerA from SQL Diagnostic Manager but retains its data for reporting.
IDERA | Products | Purchase | Support | Community | Resources | About Us | Legal