Infrastructure Integration

Configuration

1. Configure the agent by editing /etc/nutanix/epoch-dd-agent/conf.d/sqlserver.yamlin the collectors.

Example:

    init_config:
      #
      # By default, we only capture *some* of the metrics available in the
      # `sys.dm_os_performance_counters` table. You can easily add additional
      # metrics by following the custom_metrics structure shown below.
      #
      # In order to connect to SQL Server either enable SQL Authentication and
      # specify a username or password below. If you do not specify a username
      # or password then we will connect using integrated authentication.
      #
      # custom_metrics:

        # This is a basic custom metric. There is not instance associated with
        # this counter.
        #
        # - name: sqlserver.clr.execution
        #   counter_name: CLR Execution

        # This counter has multiple instances associated with it and we're
        # choosing to only fetch the 'Cumulative execution time (ms) per second' instance.
        #
        # - name: sqlserver.exec.in_progress
        #   counter_name: OLEDB calls
        #   instance_name: Cumulative execution time (ms) per second

        # This counter has multiple instances associated with it and we want
        # every instance available. We'll use the special case ALL instance
        # which *requires* a value for "tag_by". In this case, we'll get metrics
        # tagged as "db:mydb1", "db:mydb2".
        #
        # - name: sqlserver.db.commit_table_entries
        #   counter_name: Log Flushes/sec
        #   instance_name: ALL
        #   tag_by: db

        # As well as capturing from the DMV you can also capture from a custom proc
        # Please note this feature will produce a number of custom metrics that might affect your billing
        # To use this feature, specify in your instance the procedure to execute : 
        # stored_procedure: ProcedureToExecute
        #
        # The proc should return this table
        # CREATE TABLE
        # (
        #   [metric] varchar(255) not null,
        #   [type] varchar(50) not null,
        #   [value] float not null,
        #   [tags] varchar(255)
        # )
        # 
        # You can also specify:
        # ignore_missing_database : if DB doesn't exist on the server then don't do the check. Default False
        # proc_only_if : run this SQL before each call to stored_procedure. Only if it returns 1 then call the proc
        # proc_only_if_database : the database to run the proc_only_if SQL in. Optional. 
        #                         Defaults to database attribute
        #
        # The proc_only_if guard condition is useful for HA scenarios where a database can move between servers.

    instances:
       # All '%' characters must be escaped as '%%'.

      - host: HOST,PORT
        # Optional, change the connection method from adodbapi (the default) to
        # odbc (valid connector names are 'odbc' and 'adodbapi')
        # odbc is only available on Windows
        # connector: odbc

        # Optional, if using odbc, use the named driver.  If none is specified,
        # the 'SQL Server' driver will be used
        # driver: SQL Server

        # Optional, if using odbc, configure a connection using a DSN
        # dsn: DSN_NAME
        username: my_username
        password: my_password

        # Optional, timeout in seconds for the connection and each command run
        # command_timeout: 30
        # database: my_database # Optional, defaults to "master"
        tags:
          - optional_tag

      # get metrics from custom proc in MyDB but only if the database is writeable (i.e. it's the master in an availability group)
      # - host: HOST,PORT
      #   database: MyDB
      #   username: my_username
      #   password: my_password
      #   stored_procedure: GetMetrics
      #   proc_only_if: SELECT CASE CONVERT(sysname,DatabasePropertyEx('MyDB','Updateability')) WHEN 'READ_WRITE' THEN 1 ELSE 0 END
      #   proc_only_if_database: master
      #   ignore_missing_database: True

1. Check and make sure that all yaml files are valid with following command:

   /etc/init.d/epoch-collectors configcheck
   

2. Restart the Agent using the following command:

   /etc/init.d/epoch-collectors restart
   

3. Execute the info command to verify that the integration check has passed:

   /etc/init.d/epoch-collectors info
   

Infrastructure Datasources

Datasource	Available Aggregations	Unit	Description
sqlserver.buffer.cache_hit_ratio	avg max min sum	fraction	The ratio of data pages found and read from the buffer cache over all data page requests.
sqlserver.buffer.page_life_expectancy	avg max min sum	second	Duration that a page resides in the buffer pool.
sqlserver.stats.batch_requests	avg max min sum	request/second	The number of batch requests per second.
sqlserver.stats.sql_compilations	avg max min sum	operation/second	The number of SQL compilations per second.
sqlserver.stats.sql_recompilations	avg max min sum	operation/second	The number of SQL re-compilations per second.
sqlserver.stats.connections	avg max min sum	connection	The number of user connections.
sqlserver.stats.lock_waits	avg max min sum	lock/second	The number of times per second that SQL Server is unable to retain a lock right away for a resource.
sqlserver.access.page_splits	avg max min sum	operation/second	The number of page splits per second.
sqlserver.stats.procs_blocked	avg max min sum	process	The number of processes blocked.
sqlserver.buffer.checkpoint_pages	avg max min sum	page/second	The number of pages flushed to disk per second by a checkpoint or other operation that require all dirty pages to be flushed.