04 November 2024

Set up Data Engine by deploying the Tribal Data Store (TDS) on SITS:Vision and then installing the SITS agent. Note that Tribal professional services must have ensured that the SITS:Vision database meets the requirements for importing data to Data Engine.

Prerequisites

Before you start, ensure that the following prerequisites have been met:

SITS connector host

A Windows Server machine or VM to host the SITS Connector, running a Microsoft-supported version of Windows that has .NET Runtime 8 core and ASP.NET Core Runtime 8 installed. Administrator or elevated access is required to install software and services.

Download .NET Runtime 8 here: Download .NET 8.0 (Linux, macOS, and Windows) (microsoft.com)

SITS:Vision account

A SITS:Vision account.

Tribal Data Store (TDS)

SITS must have the Tribal Data Store (TDS) component installed. The latest Tribal Data Store (TDS) releases, including an appropriate SITS agent, are managed by the SITS team and are made available in MySITS as a Software Config Pack or Software Update. The latest SITS agent may also be obtained from the Tribal Platform Engineering team.

Data Engine access

Access to the Data Engine web app. A user with the Data Engine Manger role and access to the Data Engine web app, is required to the finalise the SITS agent configuration, enable the SITS agent, and schedule the SITS agent to perform a full load to start transferring data.

Database connection string

Tribal Platform Engineering will supply the SITS database connection string if cloud hosted, an access token secret for the SITS agent, and oversee any networking and firewall requirements.

Process

The Data Engine installation process consists of the following steps:

1. Deploy the Tribal Data Store (TDS) record from SITS:Vision.

2. Run the permissions script.

3. Install the SITS agent on a Windows server and then configure the SITS agent.

4. Configure the SITS agent and start the services. Use the Data Engine web app to configure the SITS agent and schedule a full load to start the SITS agent.

5. Monitor the full load and check for errors on the Message types page of the Data Engine web app. For more detailed errors, use the Windows event viewer on the host server of the SITS agent.

6. If required, adjust the default configuration settings of the SITS agent to improve performance and reliability. For more information, go to Troubleshooting.

Deploy the Tribal Data Store (TDS)

Deploy the Tribal Data Store (TDS) in SITS:Vision as follows:

1. In SITS:Vision, run the TDS (Tribal data store) program. Select the version of the TDS record you require to be installed using the scroll-bar. Then, select Deploy as shown in the image Tribal data store configuration.

   

2. On the Confirmation screen, select Deploy default Tribal views as shown in the image Tribal Data Store (TDS) deployment.

   Attention. Edit views enables the editing of the tables and attributes included in each message type. This must only be edited in consultation with Tribal professional services.

   

3. Check that the Tribal Data Store (TDS) has deployed successfully, as shown in the image Tribal Data Store (TDS) deployed.

   Attention. Redeploy starts the redeployment of the current version of Tribal Data Store (TDS). This must only be done in consultation with Tribal professional services.

   

Run the permissions script

After deploying the Tribal Data Store (TDS) or upgrading the Tribal Data Store (TDS) to a new version, you must run the permissions a script to give the SITS agent access to the SITS tables, views and stored procedures used by the Data Engine, and by extension, deny access to any other database objects.

Run the permissions script as follows:

1. Obtain the permissions scripts, which are supplied with the TDS Software config pack on MySITS and are available upon request from the Tribal Support team. The permissions script files are as follows:

   • ODS-permissions-oracle.sql

   • ODS-permissions-sql-server.sql

2. Use a system admin database account to load the file with the version that matches your SITS database into SQL IDE. Then, edit the permissions script as follows:

   SQL Server permissions script

   Amend the parameters section at the top of the permissions script as detailed below.

   /* PARAMETERS */

   DECLARE @Username nvarchar(128) = '<Login and user>'

   DECLARE @Password nvarchar(128) = '<Password>'

   Where:

   • <Login and user> is the database user's username of the SITS agent.

   • <Password> is the database user's password of the SITS agent.

   Attention.

   If the user already exists, the user will be dropped and recreated. New access rules are then assigned to the new user.

   Oracle permissions script

   Amend the parameters section at the top of the permissions script as detailed below.

   /* PARAMETERS */

   DECLARE v_Username varchar2(128) := '<Login and user>';

   v_Schema varchar2(128):= '<Schema>';

   Where:

   • <Login and user> is the SITS agents database user’s user name.

   • <Schema> is the database schema name, such as SITS_HESSLE_PROD.

   Attention.

   The database user should be created prior to running the script

3. Run the script and check the output. Ensure that no errors are generated. If the script fails to run, consult Tribal Support.

Install the SITS agent

Install the SITS agent on the host server as follows:

1. Log into the server as a user with the rights to install software or run with elevated permissions when installing software and services.

2. Add a new event log for the SITS agent. The event log name must be unique. However, this is restricted to the first eight characters of the event log name.

   Open a PowerShell console window and create a new event log source for the SITS agent using the following command:

   Copy

   SITS agent PowerShell command

   New-EventLog -LogName 'TDE live SITS Connector' -Source 'TDE live SITS Connector'

3. Optional. Create additional event log sources if you are installing multiple instances of the SITS agent. For example, to connect to test and live environments. In the PowerShell console window, create a second event log source for the SITS agent using the following command:

   Copy

   SITS agent PowerShell command

   New-EventLog -LogName 'TDE test SITS Connector' -Source 'TDE test SITS Connector'

4. Copy the ZIP file Data Engine SITS Connector_VX.XX.zip to the server and extract the SITS agent setup files to an appropriately named folder. Note that if you are setting up multiple SITS agents you must extract a copy of the SITS agent files to different folder for each SITS agent. For example, C:\Tribal Data Engine Production and C:\Tribal Data Engine UAT.

5. Configure the mandatory SITS agent configuration settings. Go to the folder containing the extracted SITS agent set up files and then use a text editor, such as Notepad++, to add the mandatory values to the JSON file appSettings.json as detailed in the table SITS agent mandatory configuration values.

   SITS agent mandatory configuration values

   Value	Description
   TenantName	Tenant name for the institution, such as hessle.
   IdentityServerAccessTokenUri	Identity server token access URI, such as https://eu-uks-prod-ids-envn.azurewebsites.net/hessle/connect/token.
   ManagementApi.ManagementApiUri	Data Engine management API URI, such as https://api.tribaledge.com/emea/odsservice.
   ManagementApi.ClientId	Data Engine client identifier must be set to ODSConnector.
   ManagementApi.ClientSecret	Data Engine client secret, such as 34ZMhKx0UUaOOVtPBEskf7loFkiQUTZQoHYxxUNsdMhUh8hZ. The initial secret will need to be provided by the Platform Engineering team. Note that after setting up the initial SITS agent, the client secret is rotated upon starting the SITS agent and then automatically every seven days thereafter. The initial secret can therefore not be reused once the SITS agent has been started. New rotated secrets will automatically be updated and encrypted in the file appSettings.json.
   ManagementApi.Scopes	Data Engine permissions scope must be set to Ods.Api.Manage.

   Attention. Note that:

   • Other values in the file appSettings.json contain default or empty values.

   • Default values, as detailed in the table SITS agent default values, should only be changed after consultation with Tribal professional services as this may have an impact on the behaviour of the SITS agent.

   • Empty values are populated and encrypted by the SITS agent once the service is started.

   SITS agent default values

   Value	Description
   CommandTimout	Database connection time out in seconds. The default value is 30 seconds.
   PollingInterval	Polling interval in milliseconds. The default value is 15000 milliseconds or 15 seconds.
   ChangeTrackingOffset	Change tracking interval in milliseconds. The default value is 10000 milliseconds or 10 seconds.
   CompressionEnabled	If True, instructs the Connector to compress messages prior to sending to the Data Engine event hub. Generally, this is desirable as more messages can be batched into a single transaction and very large messages can be accommodated without exceeding the Event Hub’s 1MB message size limit.
   Throttle.ThrottledEnabled	If True, instructs the SITS agent to reduce load on the SITS database by running processes in sequence and with delays between database tasks. This setting is recommended but will result in longer full-load tasks.
   Throttle.Input.Threshold	Amount of pending work, incoming database records, that will trigger database throttling, if enabled.
   Throttle.Input.DelayInMillisecs	Length of time between SITS database tasks when throttling is active.
   Throttle.Output.Threshold	Amount of pending work, outgoing event-hub messages, that will trigger event-hub throttling, if enabled.
   Throttle.Output.DelayInMillisecs	Length of time between event-hub tasks when throttling is active.
   PostBatch.MaxCount	Limit on how many records are batched in a single database task Requires TDS 4.2 or greater to be installed in SITS.
   PostBatch.DelayInMillisecs	Delay before the next batching task.
   CleanUp.MaxCount	Imposes a limit on how many records are deleted from the message queues in a single database task. Requires TDS 4.2 or greater to be installed in SITS.
   CleanUp.DelayInMillisecs	Delay before the next batching task.
   ContinueAfterMessageTypeFailure	If True, instructs the SITS agent to continue processing the remaining message types if one message type has faulted and requires attention.
   CircuitBreakerDelayMinutes	Length of time before a faulted message type is retried. This allows time for the fault to be corrected without generating further errors.

   For example, the image JSON file values shows an example configuration for the SITS agent appSettings.json file in Notepad++.

   

6. Install the SITS agent as a service. Open Powershell or command prompt terminal using an administrator account or an account with elevated privileges, and then run the executable to install the SITS agent as a Windows service.

   For example, navigate to the C:\Tribal Data Engine Production and then run the following command:

   Copy

   SITS agent installation command

   Tribal.OnPremise.ODSWorker.exe install --localsystem  -servicename "TDE Production Agent" -displayname "TDE Production Agent"

   Wait while the SITS agent is installed as a Windows service.

7. Optional. If you are installing multiple instances of the SITS agent, install the next SITS agent as a Windows service. Navigate to the folder containing the executable and settings for the new instance of the SITS agent, such as C:\Tribal Data Engine UAT, and then run the following command:

   Copy

   SITS agent installation command

   Tribal.OnPremise.ODSWorker.exe install --localsystem  -servicename "TDE UAT Agent" -displayname "TDE UAT Agent"

   Wait while the SITS agent is installed as a Windows service.

8. Check the SITS agent service. Go to Services and check that the TDElive service is running and set to start automatically.

Upgrade a SITS agent

Upgrade a SITS agent as follows:

1. Log in with an administrator or elevated account and run the Services app. Locate and stop the existing SITS agent’s service.

2. Unzip the SITS agent files into the SITS agent’s folder either by deleting the original files, which is recommended, or overwriting with the new copies.

3. Reinstate the file appSettings.json from the back-up copy. Typically, no further changes are required. However, consult the release notes supplied with the service or update pack as later versions of file appSettings.json may contain new settings that should also be included.

4. Reapply any changes made to the original file MessageConfig.json to the new version of the file. The file MessageConfig.json contains internal configuration settings, but may occasionally be amended during installation if messages are larger or more complex than on a standard installation.

   The most likely changes to this file will be to the DbPageSize setting. Each message has its own DbPageSize so they can be changed independently. Occasionally, this setting may have been reduced in your original MessageConfig.json to improve the reliability of full-load tasks. Compare and, if necessary, make the same changes to the new copy of the file MessageConfig.json.

   Attention.

   The format of the file MessageConfig.json may change. Therefore, it is advisable to amend the file rather than overwrite the file with an older copy.

5. Restart the service and check the event viewer to ensure the upgrade has been successful.

6. Add a new event log for the SITS agent. Note that the first eight characters of the event log name must be unique. For ease of maintenance, it is recommended that both the source name and the log name should match the service name, such as TDE Production Agent or TDE UAT Agent. The new event log is created when the service is started and begins to log events.

7. Open a PowerShell console window using an administrator account or an account with elevated privileges. Then, create a new event log source for the SITS agent using the following command:

   Copy

   SITS agent PowerShell command

   New-EventLog -LogName 'TDE Production Agent' -Source 'TDE Production Agent'

8. Optional. Create additional event log sources if you are installing multiple instances of the SITS agent. For example, to connect to test and live environments. In the PowerShell console window, create a second event log source for the SITS agent using the following command:

   Copy

   SITS agent PowerShell command

   New-EventLog -LogName 'TDE UAT Agent' -Source ‘TDE UAT Agent'

Configure the SITS agent and start the services

Before starting the services, complete the setup by entering the database connection string and related settings as follows:

1. Log in as a user with the Data Engine Manager role or with the Configure Data Engine permission. Go to Data Engine > and then SITS agent configuration and configure the SITS agent as follows:

   Agent enabled

   If selected, the SITS agent is enabled once the service is started.

   Database type

   The SITS database type SqlServer or Oracle.

   Database connection string

   The connection string for your database, as detailed in the table Database connection strings. After entering and saving the connection string, the connection string is always displayed masked.

   Database connection strings

   Database	Connection string
   Oracle	Format: user id=username;password=password;data source=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hostname)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=service name))) Example: user id=tribal.dataengine;password=vM!7Vnx7heKBJDSE;data source=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hessle.ac.uk)))
   SQL Server	Format: Server=hostname;Database=database name;User Id=username;Password=password Example: Server=sql.hessle.ac.uk;Database=SITSDatabase;User Id=tribal.dataengine;Password

   Polling Interval (millisecs)

   The interval that the SITS agent checks for new data to transfer to the Data Engine database. The default is 15000ms (15 seconds).

   Change tracking interval (milliseconds)

   The change tracking interval is used by the SITS agent to create a buffer to allow it to detect changes current hidden in transactions. The value should be set to be greater than the time expected for any long-running transactions to complete in the SITS database to ensure no changes are missed by the SITS agent.

   Database connection timeout (seconds)

   The duration, in seconds, before the SITS agent aborts an operation. For large SITS databases, it is recommended increasing this value to, for example, 60 or 90 seconds.

   Environment notes

   Notes about the environment and tenant for administrative purposes.

   The image SITS agent configuration shows the settings of a SITS agent.

   

2. Start the SITS agent service. Log in to the host computer of the SITS agent with an administrator account or an account with elevated privileges. Then, start the Services app. Select the installed SITS agent service, such as TDE UAT Agent and then open the shortcut menu and select Start. The service should start in a few seconds and have the status Running.

   The image Services shows the SITS agent service TDE UAT Agent in the Services app.

   

3. Check the SITS agent is running correctly. Open the Windows Event Viewer application and open the event log created for the service. If working correctly, the SITS agent should be logging information at regular intervals. Scan down the list and check for any error events.

4. Schedule a full-load after obtaining customer approval., On the Data Engine web app, go to SITS agent configuration. Then, under Schedule tasks, go to the full load schedule menu options ⋮, select Edit and enter the Scheduled date and Scheduled time of the full load.

   Attention.

   Full loads must be done with the customer’s approval as they may have an impact on the SITS database performance.

5. Monitor the full load after the scheduled start on the Message types page of the Data Engine web app.

   Attention.

   The Message types page doesn’t automatically refresh so periodically use the browser, using Ctrl+R, to update the message types.

   If a message type has errors, select the row of message type to display a list of recent errors, including an error message and error detail, to help identify the issue. Errors may be process-related or data-related and may affect single messages or multiple messages.

   Once the standard full-load is working, an Attribute full-load should also be scheduled. Check the Message types page and the Window event logs on the SITS agent’s host server to ensure that the full-load is working and error free.

Troubleshooting

Troubleshooting timeout errors and data issues.

Timeout errors reading SITS data

Troubleshoot timeout errors reading SITS data as follows:

1. Ensure the throttling options are enabled in file appSettings.json. This will reduce the load on the SITS database.

2. Increase the Database connection timeout value on SITS agent configuration page. This will give long-running database task initiated by the SITS agent more time to complete before failing.

3. Reduce the DbPageSize for the failing messages in the file MessageConfig.json. The default is 5000, so consider reducing the value to, for example, 2500. The SITS agent will then request smaller pages of data from SITS for that message type.

   The image DbPageSize shows the DbPageSize in the file MessageConfig.json.

   

Timeout errors writing to the Event Hub

Troubleshoot timeout errors writing to the Event Hub as follows:

1. Ensure the throttling options are enabled in the file appSettings.json. Consider reducing the Throttle.Output.Threshold and increasing the Throttle.Output.DelayInMilliseconds to decrease the load on the Event Hub.

2. Reduce the EventBatchSize setting for the failing messages in the file MessageConfig.json. The default is 5000.

Timeout errors in the Mediator (writing data to the Data Engine database)

Troubleshoot timeout errors in the Mediator, writing data to the Data Engine database, as follows:

1. Ensure the throttling options are enabled in the file appSettings.jsonON. Consider reducing the Throttle.Output.Threshold and increasing the Throttle.Output.DelayInMilliseconds to decrease the overall data throughput.

2. Consider upgrading the database specification to increase CPU and memory availability.

3. Temporarily reduce the load on the Data Engine database using the Mediator environment variables. Decrease the SQLBatchSize setting and increase the SQLCommandTimeout setting. The settings would be reset to default values whenever a new version of the Mediator function is deployed.

   Attention.

   The Mediator environment variables are only accessible by Tribal Platform Engineers in the Azure Portal. A request for new default values could be requested if the issue is likely to affect multiple customers.

Issues resulting from bad customer data – SITS agent

Data issues detected by the SITS agent are usually caused when data from the Data Engine views cannot be successfully joined to form a valid Data Engine message. The data returned to the SITS agent may, for example, have records with the same primary key, or may contain orphaned records with a missing or bad foreign key. The underlying fault may be invalid data in SITS, unexpected usage of the data in SITS, or problems with the views, particularly if a view has been customised. As the data is processed in batches, this type of issue usually results in the message type faulting until the problem has been resolved, either by cleaning up invalid data, or redesigning or correcting the views causing the data issues.

The Tribal Professional Services team can usually advise on the best course of action.

Issues resulting from bad customer data – Mediator

Data issues detected by the Mediator are usually caused by bad data within records such as date fields with invalid dates, unexpected number formatting, or missing mandatory data. Only invalid records are affected. The error should identify the problem record or records which should then be corrected in SITS. Once corrected, the records will be reprocessed.