RockSolid Monitoring for AWS SQL Server RDS

The AWS SQL Server RDS service is a restricted access implementation of the Microsoft SQL Server software where all customer users, including the master user, do not have full sysadmin rights to SQL Server.  The impact of this is that not all RockSolid functions can be used with AWS RDS, however the bulk of RockSolid functionality will work as expected.  It is the intent of this article to detail the limitations of using RockSolid with AWS RDS.

Monitoring Account

To use RockSolid with AWS RDS either a SQL Server authenicated login must be created or the RDS instance needs to be integrated with Active Directory.  Regardless of authentication methods, the following permissions must be granted to the account used to monitor the RDS instance by RockSolid.

grant ALTER ANY CONNECTION to rsmonitor
grant ALTER ANY LINKED SERVER to rsmonitor
grant ALTER ANY LOGIN to rsmonitor
grant ALTER SERVER STATE to rsmonitor
grant ALTER TRACE to rsmonitor
grant CONNECT SQL to rsmonitor
grant CREATE ANY DATABASE to rsmonitor
grant VIEW ANY DATABASE to rsmonitor
grant VIEW ANY DEFINITION to rsmonitor
grant VIEW SERVER STATE to rsmonitor
grant ALTER ANY SERVER ROLE to rsmonitor
grant ALTER ANY USER to rsmonitor

exec sp_addsrvrolemember 'rsmonitor','PROCESSADMIN'
exec sp_addsrvrolemember 'rsmonitor','SETUPADMIN'

use msdb
CREATE USER rsmonitor FROM LOGIN rsmonitor
ALTER ROLE SQLAgentUserRole ADD MEMBER rsmonitor

exec sp_addsrvrolemember 'rsmonitor','SQLAgentUserRole'
exec sp_addsrvrolemember 'rsmonitor','PROCESSADMIN'

grant select on dbo.sysjobs to rsmonitor
grant select on [dbo].[sysjobactivity] to rsmonitor
grant select on [dbo].[sysjobhistory] to rsmonitor

Limitations

The following section lists the limitations of using RockSolid with AWS RDS, including which functions correctly function and which functions are unavailable, and those with degraded capabilities.

 

Function Impact Monitoring Alerting Resolution
Monitoring Availability None Yes Yes N/A
Monitoring Database Creation None Yes Yes N/A
Monitoring Database Capacity *See Topic 1 Yes Yes Yes
Monitoring Backup History Cannot Re-Run Yes Yes No
Monitoring Instance Memory Usage None Yes Yes N/A
SQL Server Latch Stats None Yes Yes N/A
Version Information None Yes Yes No
SQL Server  Configuration None Yes Yes No
 Spinlock Stats None Yes  Yes N/A
SQL Agent Job History * See Topic 2 Yes Yes Yes
SQL Error Log Enum None Yes Yes Yes
SQL Server Error Log None Yes Yes N/A
SQL Server Cache Hit Ratio None Yes Yes N/A
SQL Server Connections None Yes Yes N/A
SQL Server Endpoints None Yes Yes N/A
SQL Server Group Login Members Cannot Run No No No
SQL Server Level Permissions Cannot Alter Yes Yes No
SQL Server Logins Cannot Alter Yes Yes No
SQL Server Long Running Queries None Yes Yes Yes
SQL Server Server Role Members Cannot Alter Yes Yes No
SQL Server Wait Stats None Yes Yes  N/A
TempDB query usage Cannot Run No No No
Security Trace None Yes Yes N/A
SQL Server Replication Not Supported Not Supported Not Supported Not Supported
Log Shipping Not Supported Not Supported Not Supported Not Supported
Availability Groups None Yes Yes N/A
Restore History None Yes Yes N/A
Database Configuration *See Topic 1 Yes Yes Yes
Server Level Privileges None Yes Yes Yes

 

* Topic 1

To monitor/maintain this function at the database level the RockSolid monitoring account is required to have a user in each database and be a member of the db_owner role for each database.

* Topic 2

A current limitation is the RockSolid agent can only monitor jobs that have been created with the same login as what is being used to monitor the RDS instance with.  It is recommend that all jobs be created under the monitoring account.

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.