Another way to handle DELAYs for events

SeanR · 22-10-2019 18:36:54

We currently have a very convoluted way to handle the processing of delays on events that are sent to us from an EMS system which involves 2 database tables and writing and later deleting signal registry records etc etc.

A delay process can EASILY be accomplished using MSSQL JOBS or MYSQL Events. At this point I have only tested this in MYSQL (I will post follow up on how to create MSSQL Jobs in a future comment)

The theory behind this method is as follows:

A first contract reads the X-DEC file which is sent to us from the EMS tool.
This first contract simply writes a record into the signal registry (if the state is not Repaired). However, it writes the record with a 'processedflag' value of something other than 0,1 or 2 (Let's use 999 in this example)
A second contract only processes this signal registry. It is configured to only process records with a 'processedflag' of '0'
We create a MYSQL Event that will run every minute. When this event runs it will check all the records in the signal registry that have a 'processedflag' of '999' and that are older than the records 'epochtime' plus a specified delay interval.
On finding a matching record it will change this 'processedflag' to '0' so that the second contract can process it.

To create the MYSQL Event to the following:

Enable the MYSQL Event_Scheduler
```
set global event_scheduler = on;
```

Create an Event which will run every minute.

CREATE EVENT scomdelay
ON SCHEDULE EVERY 1 MINUTE
DO
  update sigreg set processedflag = 0 where processedflag = '999' and (select Date_add(epochtime, interval 5 minute)) < now();

The above will simply run the update query every minute. When the delay has expired (in the code example above we are delaying for 5 minutes) the record will be updates setting it's processedflag to 0.

NOTE: you can create multiple MYSQL Events for different 'actionName' in the signal registry simply by changing the update line in the above code block.
E.G.

update sigreg set processedflag = 0 where processedflag = '999' and actionName = 'ACME_Create' and (select Date_add(epochtime, interval 5 minute)) < now();

In this above example ONLY records that have the 'actionName' of 'Acme_Create' and a 'processedflag' of '999' will be modified.

To check if your Event has been added correctly to can run:

show processlist;

which will show you the process 'User' as event_scheduler. If you do not see your event in that list simply run the

set global event_scheduler = on;

command again.

SeanR · 22-10-2019 18:50:55

So what should happen if the X-DEC we received has a 'Repaired' state in it?

Well I think we have one of 2 options that we need to test in a busy environment:

We could simply change the sigreg record's 'processedFlag' to 1. That way the 2nd contract would never process that record.
We could delete the record from the sigreg if the 'processedFlag' is still '999'. This will not cause X-ServiceBroker to crash as the 2nd contract will never read that record in the first place.

SeanR · 23-10-2019 11:12:48

Add Delay events to MSSQL

We can delay events in MSSQL in a very similar fashion as what was describes in the original post. The steps to perform are as follows:

Make sure that SQL Server Agent(MSSQLSERVER) windows service is running.
In Microsoft SQL Server Management Studio expand the SQL Server Agent option at the very bottom of the "Object Explorer" pane.
Right Click on Jobs and select New Job... from the dropdown menu
The following window will appear. Give your new Job a Name (E.g. SCOMDelay) - You can include a description at this point as well if required.
Next, Click on the Steps Page (in the top left panel) and then Click on the 'New' button at the bottom of the window.
In the 'New Job Step' window Provide a 'Step Name' (E.g. CheckDelay). NOTE: Make sure you select the correct database from the 'Database:' dropdown menu. In the Command textarea paste the following:
```
UPDATE sigreg SET procFlag = '0' 
WHERE procFlag = '999' 
AND (SELECT DATEADD(minute, 5, epochTime)) < GETDATE();
```
NOTE: this code uses a 5 min delay, however, you can change that to any number of minutes you require.
Then Click on 'OK' - You will be taken back to the 'New Job' window
Now select the 'Schedules' Page (in the top left panel) abd Click on the 'New' button at the bottom of the window. A 'New Job Schedule' window will popup.
Give this new schedule a 'Name' (E.g. SCOMDelaySchedule)
Make sure you Select Frequency to 'Daily'
Under 'Daily Frequency' Click the 'Occurs every:' radio button and change the drop down menu from 'Hour(s)' to 'Minute(s)'

Then Click on 'OK'
Nothing further NEEDS to be done however should you wish to add Alerts or Notifications you can. These can also be added at a later time as well.

You should now see the Job you just created under the 'Jobs' menu which is under 'SQL Server Agent'.

Should you wish to check the status of your Job and when it will run again (as well as what the Last Outcome was for the last time you job ran) you can open the 'Job Activity Monitor' window.

SeanR · 28-10-2019 15:19:06

Jobs in SQL Express

The functionality of creating SQL Agent Jobs is not available in SQL Server Express Edition. An alternative is to execute a batch file which executes a SQL script using Windows Task Scheduler.

In order to do this first create a batch file named sqljob.bat

sqlcmd -S servername -U username -P password -i path/of/sqljob.sql

Replace the servername, username, password and path with the relevant values.

Then create the SQL Script file named sqljob.sql

USE [databasename]
--T-SQL commands go here
GO

Replace the [databasename] with your database name. The USE and GO is necessary when you write the SQL script.

sqlcmd is a command line utility to execute SQL scripts. After creating these two files execute the batch file using Windows Task Scheduler set to run every minute.

SXI Forum

#1 22-10-2019 18:36:54