PowerShell Cmdlets for SQL Diagnostic Manager

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.

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.

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.

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.

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.

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.

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.

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

• • Disables SQL Diagnostic Manager Application Security.

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 fred that 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.

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.

Remove-SQLdmUser

Removes an existing SQL Diagnostic Manager user

Remove-SQLdmUser -Path /AppSecurity/fred

• • Removes user fred from SQL Diagnostic Manager.

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


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

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

Set-SQLdmLogging