The following cmdlets help you automate the administration of your SQL Diagnostic Manager deployment.deployments in different environments:
Note |
---|
To use SQL Diagnostic Manager snapin, you have to enable the provider and register the SQLdm SQLDM drive. |
Anchor |
---|
| Local_Environments |
---|
| Local_Environments |
---|
|
PowerShell Cmdlets in local environmentsThe 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 |
---|
Cmdlet Name | Description | Examples |
---|
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-
|
SQLName SQLdmName Server\Instance1,Server\Instance2
|
- Escapes the array of instance names.
Escape-
|
SQLName Instance 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.
|
RevokeSQLdmPermissionRevokes 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)
SQLdmAlertTemplate | Associates a template to a monitored instance or a tag | |
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 databaseon the MYSQLDMSERVER SQL Server using SQL Authentication. Note that Powershell will prompt for the password
|
Revokes Windows user MyDomain\UserA permission to ServerA\Instance1 |
New-SQLdmMonitoredInstance
| Adds a |
SQL Server Instance that you want monitored by 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.
|
SetSQLdmMonitoredInstanceConfigures options on your SQL Server Instance | Set-SQLdmMonitoredInstance -Path Instance1,Instance2,Instance3 -Credential sa
Updates the credentials used for instances Instance1,Instance2, and Instance3 to use SQL Authentication and set a new login and password. Note that 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.
|
SetSQLdmMonitoredInstance SQLdmUser (Escape-SQLdmName -Name
|
ServerAInstance1WindowsAuthenticationWindowsUser
- Creates a new user called mydomain\fred that uses
|
Updates the credentials used for instance ServerA\Instance1 to use |
Remove-SQLdmMonitoredInstance
| Removes a |
SQL Server 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
|
retain - 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
|
Disables SQL - DisablesSQL Diagnostic Manager Application Security.
|
NewSQLdmUserSQLdmLogging | Sets the logging level of the |
Creates a new 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, provider | Warning |
---|
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.
|
NewSQLdmUser SQLdmMonitoredInstance (Escape-SQLdmName -Name
|
mydomainfredWindowsUserWindowsAuthentication
- Updates the credentials used for ServerA\Instance1 to use
|
Creates a new user called mydomain\fred that uses |
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.
|
Anchor |
---|
| Cluster_Environments |
---|
| Cluster_Environments |
---|
|
PowerShell Cmdlets in DMSO environments Cmdlet Name | Description | Examples |
---|
New-SQLdmMonitoredInstance | Adds a SQL Server Instance to monitor in DMSO | New-SQLdmMonitoredInstance -Path Instance1 -Cluster cluster1 -WindowsAuthentication
- Adds a new instance namedInstance1 to the SQL DMSO environment.
New-SQLdmMonitoredInstance -Path
|
Remove-SQLdmUser
| Removes an existing SQL Diagnostic Manager user | Remove-SQLdmUser -Path /AppSecurity/fred
- Removes user fred from SQL Diagnostic Manager.
Remove-SQLdmUser MyDomainAUser)- 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
| Sets the logging level of the SQL Diagnostic Manager provider | Warning |
---|
Set-SQLdmLogging is only used for debugging purposes and should only be used at the request of IDERA Technical Support.
|
|
SQL Diagnostic Manager identifies and resolves SQL Server performance problems before they happen. Learn more > >
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 Note |
---|
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.
|
...