June 2, 2020 at 2:38 pm
Hi guys, any one using DPA to monitor their SQL 2017 servers? I am planning to implement this on one SQL server to monitor performance, since this SQL Server always reports slowness from users. Mainly CPU going high.
Management gave me 1 license to use SolarWinds DPA, so I can monitor this server. If anyone is using this DPA, how is DPA on the SQL Server itself? will the DPA agents generate additional load on SQL Server that is being monitored? Any other considerations? thanks!
June 2, 2020 at 3:13 pm
We've been using DPA (former Ignite/Confio) for last 5-6 years and monitoring a dozen of SQL Server (2016 and 2017) DB instances.
It work well for us … after initial setup and basic understanding of DB performance related parameters everything works smoothly.
Let me know if you have any specific question/concern
June 2, 2020 at 3:29 pm
Thanks for the inputs, sure I'll post back for any specify questions
June 2, 2020 at 5:20 pm
Will there be any agent installed on the monitoring (target ) SQL Server ? if yes what is the name of the Agent? I just added one SQL Server to DPA, and trying to do some research on how much resources, if the Agent consumes.
Did not see any service with DPA name installed on the windows box.
Also I had to mention 2 SQL Ids to use DPA and both were granted sysadmin rights , one to temporarily register instance and another one a monitor user (which was created by DPA). Can these both be removed?
thanks
June 2, 2020 at 5:29 pm
No agent on SQL server (back-end) side only Ignite PI service on Windows app server side (web portal) .
The Ignite PI server (service) is not consuming much resources (CPU/memory)
You account that connect to SQL Server DB instance needs to have elevated permission (sysdba role), at least temporarily.
I can post you a script/instruction to revoke sysdba role and grant minimal needed permission to that account later.
June 2, 2020 at 5:39 pm
Sure, please share the script to revoke access to the account, that would be very useful for me, since our company wants to restrict access as much as possible
June 2, 2020 at 5:48 pm
Please note, you will need a DB to store DPA performance related historical data as well. Our DBAs use an Oracle DB instance for it but we tested SQL Server and it works correctly too.
June 2, 2020 at 5:53 pm
I see, but on the target SQL Server , that I just used for monitoring I do not see any new database created, where would this database be? DPA did not ask me this while I was registering a SQL Server
June 2, 2020 at 5:58 pm
must be there (somewhere), assuming you connected only to one SQL Server instance during the install process … check if it is named something like ignite or confio or dpa (perhaps hidden inside system DBs)?
June 2, 2020 at 6:08 pm
I do not see any new database, I was also referring to the steps for registering a SQL server instance from the guide by solar winds
Which says below ==>
Oracle Repository Tablespace
If your repository database is not Oracle, the wizard skips this step.
Choose the tablespace in the repository database to store DPA performance data for this monitored instance.
By default, the performance data is stored in the default tablespace of the repository user. However, data for monitored instances can be stored in separate tablespaces.
I think this is what you were talking about?
June 2, 2020 at 6:31 pm
I feel that this database the repository database must be on the web application server where DPA is installed
June 3, 2020 at 2:24 pm
Here is a SQL script to revoke sysdba role and grant minimal needed permission to DPA monitoring SQL login/account:
-- Run this script below on the both Primary and Secondary in case you monitor a high availability group (DB instance)
-- Your_DPA_SQL_Login account that is used for connecting DPA to a monitoring DB instance can be Windows (domain) or SQL Server login
USE [master]
GO
CREATE LOGIN [Your_DPA_SQL_Login] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
GRANT VIEW ANY DATABASE TO [Your_DPA_SQL_Login]
GRANT VIEW SERVER STATE TO [Your_DPA_SQL_Login]
GRANT VIEW ANY DEFINITION TO [Your_DPA_SQL_Login]
GO
USE [master]
go
GRANT SELECT ON sys.dm_exec_query_stats TO [Your_DPA_SQL_Login]
GRANT SELECT ON sys.dm_exec_text_query_plan TO [Your_DPA_SQL_Login]
GRANT SELECT ON sys.fn_get_sql TO [Your_DPA_SQL_Login]
GRANT EXECUTE ON sys.sp_OADestroy TO [Your_DPA_SQL_Login]
GRANT EXECUTE ON sys.sp_OAGetErrorInfo TO [Your_DPA_SQL_Login]
GRANT EXECUTE ON sys.sp_OACreate TO [Your_DPA_SQL_Login]
GRANT EXECUTE ON sys.sp_OAGetProperty TO [Your_DPA_SQL_Login]
GRANT EXECUTE ON sys.sp_OAMethod TO [Your_DPA_SQL_Login]
GRANT EXECUTE ON sys.sp_OAStop TO [Your_DPA_SQL_Login]
GRANT EXECUTE ON sys.sp_OASetProperty TO [Your_DPA_SQL_Login]
GRANT CREATE TABLE TO [Your_DPA_SQL_Login]
go
--The next section of code applies only to Ignite 8.1 and below for plan collection
USE [master]
EXEC sp_MSforeachdb 'USE [?] CREATE USER [Your_DPA_SQL_Login] FOR LOGIN [Your_DPA_SQL_Login] WITH DEFAULT_SCHEMA=db_datareader'
GO
go
use [msdb]
go
GRANT SELECT ON dbo.sysjobs TO [Your_DPA_SQL_Login]
GRANT SELECT ON dbo.sysjobhistory TO [Your_DPA_SQL_Login]
GO
-------------------------------------------------------------------------------------------------------------
-- Last check if the user can connect to each DB (hasdbaccess to be 1) on both Primary and Secondary
USE [master]
go
EXEC sp_MSforeachdb 'USE [?] SELECT name, hasdbaccess FROM sys.sysusers WHERE name = ''Your_DPA_SQL_Login''';
--If the hasdbaccess is 0 then grant CONNECT for each DB
USE [master]
go
EXEC sp_MSforeachdb 'USE [?] GRANT CONNECT TO [Your_DPA_SQL_Login]';
-- Finaly revoke sysadmin role from Your_DPA_SQL_Login and verify DPA works correctly
June 3, 2020 at 2:26 pm
Thanks a lot appreciate it
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply