Monitoring servers without sysadmin permissions

Only required for SQL Server versions:

  • SQL Server 2014 (12.x) and earlier
  • SQL Server 2016 (13.x) before SP2.
  • SQL Server 2017 (14.x) before CU9.

If you are unable to grant sysadmin permissions on monitored servers to the monitoring account, follow this guide to configure Redgate Monitor and prevent it from attempting to perform actions which require sysadmin permissions. You can find a list of required permissions.

Sysadmin is required by Redgate Monitor to run the DBCC DBINFO command to determine the last integrity check for a database (required for the Integrity check overdue alert) as well as enabling the deadlock trace flag to collect deadlock information (not required when using Extended Events for deadlock collection). To prevent Redgate Monitor from trying to perform these actions, you will need to disable collection for the Integrity check overdue alert as follows.  

This can be done globally as well as at the specific cluster, machine or SQL Server level in cases where you have some entities you are able to grant sysadmin for and not others.  You can specify clusterName, sqlServerName, machineName as attributes, to restrict the particular schedule.  The clusterName is for a standalone Windows Server name or the Windows Cluster level name if it's a cluster, machineName is for cluster nodes and SqlServerName is for the SQL instance name.  You should put the names as lower case as seen in Redgate Monitor after you add the entity on the Monitored servers page.  More restrictive schedules should go first, for example, when adding per SQL Server collection schedule, you want to put it first in list, and the schedule for the remaining entities afterwards

If you wish to still get Deadlock alerts, you must either enable Extended Events for deadlock monitoring or enable the SQL Server trace flag 1222 on your monitored servers

Disabling integrity check collection

Follow these steps on the machine where you have installed the Redgate Monitor Base Monitor service:

  1. Open the following folder: %programdata%\Red Gate\Redgate Monitor 
  2. Make a copy of the RedGate.Monitor.BaseMonitor.exe.settings.config file and store it.
  3. Open the original file and locate the <collectionSchedules> section.
  4. You can disable the sampler for all entities or target it at a specific entity or entities:

    1. To disable the sampler globally, add the following new schedule:

      collectionSchedule

      <schedule qualifiedChannelName="[Cluster].[SqlServer].[Database].[IntegrityCheck]">
              <disabledSchedule />
      </schedule>
    2. To disable the sampler for a specific entity for the YourInstance SQL instance on the YourServerOrCluster server add the following new schedule:

      collectionSchedule

      <schedule qualifiedChannelName="[Cluster].[SqlServer].[Database].[IntegrityCheck]" clusterName="YourServerOrCluster" sqlServerName="YourInstance"> 
              <disabledSchedule /> 
      </schedule>
  5. A default file with these additions will have a <collectionSchedules> section like the following (this is illustrative of both ways and the ordering expected, you do not need to do both in this case since they are both disabling the sampler):

    Full collectionSchedule

    <collectionSchedules>
    
    <!-- Four schedules are available:
           intervalSchedule
           ****************
             parameters
                  interval="hh:mm:ss"
    
             Collect every hh:mm:ss. The full accepted format is "d.hh:mm:ss.ff".
             [ref: http://msdn.microsoft.com/en-us/library/se73z7b9%28v=VS.90%29.aspx ]
    
    
           disabledSchedule
           ****************
             (no parameters)
    
             A special schedule that disables collection.
    
           dailySchedule
           *************
             parameters 
                  hour="hh"
                  minutes="mm"
    
             collect once a day, at the specified local time (24-hour clock) 
    
           weeklySchedule
           **************
             parameters
                  dayOfWeek="dddddd"
                  hour="hh"
                  minute="mm"
    
             Collect once a week, at the specified local time, on the specified day. Day names are taken from 
             the set (Monday .. Sunday) 
             [ref: http://msdn.microsoft.com/en-us/library/system.dayofweek.aspx ]
    
    
             If a schedule parameter is specified incorrectly, the Base Monitor service will fail to start. A full reason will
             be logged to the Red Gate section of the Event Log.
    
             The metric is specified by a qualifiedChannelName.
    
             NB. All schedule times are in the local time zone of the Monitoring Service not UTC 
    -->
    
    <!-- Examples
          <schedule qualifiedChannelName="[Cluster].[Machine].[Process]">
            <intervalSchedule interval="00:01:00"/>
          </schedule>
          <schedule qualifiedChannelName="[Cluster].[SqlServer].[Database]">
            <disabledSchedule />
          </schedule>
          <schedule qualifiedChannelName="[Cluster].[SqlServer].[Database].[Table]">
            <dailySchedule hour="1" minute="0" />
          </schedule>
    -->
          <schedule qualifiedChannelName="[Cluster].[SqlServer].[Database].[IntegrityCheck]" clusterName="YourServerOrCluster" sqlServerName="YourInstance"> 
            <disabledSchedule /> 
          </schedule>
          <schedule qualifiedChannelName="[Cluster].[SqlServer].[Database].[IntegrityCheck]">
            <disabledSchedule />
    	  </schedule>
          <schedule qualifiedChannelName="[Cluster].[SqlServer].[Database].[Table].[Index]">
            <weeklySchedule dayOfWeek="Sunday" hour="1" minute="00"/>
          </schedule>
          
        </collectionSchedules>
  6. Save and close the file.
  7. Restart the Redgate Monitor Base Monitor service.




Didn't find what you were looking for?