Installing and Configuring the Microsoft SQL Server Trigger Agent

Introduction

The Microsoft SQL Server Trigger Agent is used to monitor a SQL Server instance to detect when changes are made within a database.

It communicates with the TaskCentre Server and is used to trigger tasks to run as soon as an INSERT, UPDATE or DELETE occurs. Data can be captured and processed within a task to produce a required outcome.

The Microsoft SQL Server Trigger Agent can be installed on any machine running SQL Server, it is a distributed component meaning this can be the same machine as TaskCentre or a separate one. The agent is then registered the TaskCentre Server before it becomes operational.

NOTE: TaskCentre triggers can also be deployed on Oracle Database servers, using the Oracle Trigger Agent.


The TaskCentre server installation is always the first step in a new deployment, for further details please see our knowledge base article for installing and configuring the server: Installing and Configuring the Server

Planning an Installation

What do you need to do before installing the SMicrosoft QL Server Trigger Agent? This checklist will help to plan a successful installation.

CHECKLIST – Microsoft SQL Server Trigger Agent

  • The Microsoft SQL Server Trigger Agent is supported on the following versions of Microsoft SQL Server:
    • Microsoft SQL Server 2005
    • Microsoft SQL Server 2008
    • Microsoft SQL Server 2012 & 2012 R2
    • Microsoft SQL Server 2014
    • Microsoft SQL Server 2016
    • Microsoft SQL Server 2017
  • Be aware that specific permissions to extended stored procedures are required in order to use triggers and have a trigger fire a task; referenced in this article.
  • Obtain the server name or IP address of the TaskCentre server so the agent can be registered with the server.
  • Obtain the administrator password for the TaskCentre server in order to complete the registration.
  • Plan for a reboot of the server where you’re installing the agent (though it’s not normally required).
  • Check ports 4222 and 4223 are open on the TaskCentre server so that the Trigger Agent machine can see it. *only required if the agent is being installed on a remote machine.

Permissions & Rights to Stored Procedures

A SQL Server account with sufficient permissions to create triggers on a database must be available for use by the Microsoft SQL Server Trigger tool.

TaskCentre trigger functionality also depends on being able to run a set of extended stored procedures that are present in the master database of SQL Server.

This enables a trigger to run a task when changes are detected. For details about configuring these permissions see the following knowledge base article: Required permissions to create SQL server triggers

Installing the Agent

The Microsoft SQL Server Trigger Agent is selected as a feature to install from the TaskCentre installer.

The agent can be installed on any machine running SQL Server, therefore this can be the same machine as TaskCentre or one that exists at another location on your network.

If you require the agent to be installed on a separate machine then a standalone install of the Microsoft SQL Server Trigger Agent agent can be completed.

 

Configuring the Agent

The TaskCentre Configurator must be used to register the agent with the TaskCentre Server. This will allow the agent to notify the server when a SQL event occurs.

A prompt will appear after install and if accepted the Configurator will load. You can deselect this prompt if you want to carry out configuration at a later time.

You can open the Configurator from the Start -> Programs menu or for newer operating systems you can select the Configurator app from the start screen. Configuration of the agent will consist of three main tasks:

  • Checking the prerequisites
  • Registering the agent with a SQL Server instance
  • Registering the agent with the TaskCentre server
NOTE: A TaskCentre server can have any number of Microsoft SQL Server Trigger Agents registered, one for each deployment of the agent. The agent can be installed on any number of SQL Server machines.

Locating the Settings

When installing the agent for the first time the Configurator will take you straight to the Microsoft SQL Server Trigger Agent component.

Alternatively, it can be located under Event Agents -> Microsoft SQL Server Trigger Agent in the left-hand navigation pane. Clicking this folder will display which SQL instance the agent is registered with.

Checking Prerequisites

One of the functions of the Configurator is to check and report whether the required software and settings are in place for the agent to operate correctly.

The Configurator will check that a supported version of SQL Server is in use and that one of the SQL Browser services is installed and running on the default port 1434.

Selecting the Prerequisites node will display a list of requirements in the pane on the right.

  • A tick icon Tick indicates the prerequisite has been met
  • A cross icon Cross indicates that an aspect of the required environment is missing or not running and action is required before the feature will work properly
  • A warning Warning icon  indicates that a configuration issue exists but the feature may still be usable

If you have corrected a prerequisite issue, you can use the Refresh button to update the prerequisite report.

Selecting a SQL Server Instance

The Configuration node allows you to select a SQL Server instance to register the agent with. The agent will monitor this instance instance to detect changes such as inserts, updates or deletes.

Use the SQL Server Instance drop down list to select an available instance on the server to register the agent with then click Save to continue.

Registering the Agent

The agent now needs to be registered with the TaskCentre Server. Clicking Save has created a new node called Agent Instance which will open automatically. Clicking the node will display in the right-hand pane a summary of the agent configuration.

Prerequisites

As before, you begin by checking the Prerequisites to check if the required environment for the agent to operate in is available.

If you receive a red cross against SQL Server OLE Automation, the Configurator has detected that this feature is not enabled for the selected SQL instance. You can use the Modify button to allow the Configurator to enable the feature for you.

Clicking Next will proceed.

Registration Settings

The Registration Settings node allows you to register the agent with the TaskCentre Server. This enables the agent to notify the server when SQL events have been identified, this in turn will trigger a task to run.

By default, the Server field displays the current machine name and the Agent Name as ‘Microsoft SQL Server Trigger’. You must supply the server name or IP address of the machine on which the TaskCentre Server is installed.

The agent name can be changed if required. This can be beneficial for identification purposes when multiple SQL agents are in use.

You click Save to continue.

Authentication Required

The Configurator will then prompt you for the password of the TaskCentre Administrator account to complete the agent registration with the TaskCentre server.

You must ensure that you are not logged on elsewhere with the Administrator account, this will prevent the registration from being completed.

Installing and Configuring the Microsoft SQL Server Trigger Agent: In Summary

The Microsoft SQL Server Trigger Agent is one of several Event agents that can be installed as part of a TaskCentre deployment. It is responsible for notifying TaskCentre to trigger a task to run when changes are identified within a SQL database.

This agent must be installed on a machine running SQL Server. It can be installed alongside the TaskCentre Server or standalone.

To install the SQL agent you select it as a feature within the TaskCentre installer. You then carry out configuration of the agent using the Configurator application which opens immediately after install.

The main configuration involves registering the agent with a SQL Server instance and with the TaskCentre Server, after which it will become operational. The Microsoft SQL Server Trigger tool can then be used to trigger a task based on an UPDATE, INSERT or DELETE type event occurring within a database.