Many users have taken the inherent functionality of Workflow to extend, customize, and fine tune the capabilities of Servicedesk. Some have simply made some modifications to the core processes of Incident, Problem, and Change management (something no longer permitted in 7.5), others have extended its functionality by adding new, external processes that can query and read the Servicedesk data types (something permitted and encouraged where needed in 7.5) and given their respective businesses the functionality they needed.
Recently, an issue came to light that exposed a design problem. In a Servicedesk 7.1 installation, a customer had generated a custom process that improved their ordering capabilities. The order process worked very well. However, the customer was also experiencing another problem which required a new rollup build to be installed. After installing the rollup build, the Servicedesk system was stable for a few hours, and then existing tickets starting becoming corrupt. Of course, thanks to good procedure, a backup was available and the system was restored to its previous state. But what was deleting the messages?
It was discovered that there had been a change to the Workflow engine to removed messages from projects that no longer existed in the running models. But no models had changed in this particular circumstance, and, in the updates case, only older or unused and unreferenced messages should have been removed from the messages table. The messages that were getting removed were current ones, associated with active, Incident processes.
After review of SQL traces, and setting up a test environment to match the problematic one, it was discovered that the custom order project had the same SERVICEID as the SD.IncidentManagement project. The engine was now pulling all pending timeouts and escalations to process at the same time, and both pointing to the same SYMQ queue. Essentially, Incident Management would be processing some of the order project messages and the order project would be processing some of the Incident Management messages.
In the build of Workflow that was installed prior to the rollup, when the process could not find a component, it would just continue to the next timeout or escalation, and leave the orphaned message intact, generating errors. This was not desired because, as these ‘corrupt’ messages accumulate, they keep getting processed over and over putting an unneeded burden on the server. Eventually, timeouts and escalations take forever to process because each time it runs it has to go through all the corrupt older processes before it gets to any real messages. The fix included in the new rollup removed these broken messages, since it was assumed that the un-referenced messages would now be non- functional anyway. But, as was discovered, if one project processed another’s message and couldn’t find the component, it would remove the message.
So, from a very high level, what was needed was the following:
1. Change the custom project to NOT share the same SERVICEID as another project. This is a project design best practice.
2. Fix all of the existing messages in the database to properly reflect the changed SERVICEID of the project.
3. Then of course, install the rollup build to alleviate the original problem.
Conclusion and Importance of Proper SERVICEID Design:
The instructions and SQL are below to help resolve the issue that was found. However, the important thing to remember is that when developing custom applications that will be processing messages, do not give them or match their SERVICEID. They must be unique.
Steps to Resolve:
Here is the process to repair the broken ServiceID for the custom project. This assumes that the custom project is called ‘ORDERS’
1. Stop Timeouts and Escalations
2. Publish the new Order Management with the Revised/New Service ID (The ServiceID must be set to ‘Orders’, if changed to anything else, the queries below must be modified.)
3. Backup Database
4. Stop IIS and Workflow Service
5. Run Update Queries
5a. Before running the SQL below, do the following in SQL:
5a1. Open the Message Properties Table, and expand it
5a2. Expand the Keys and double-click on the FK_MessageProperties_Messages Foreign Key
5a3. Expand the INSERT and UPDATE Specific Option
5a4. Find the Update Rule, and Change it from 'No Action' to 'Cascade’
5a5. Click Close
5a6. Right-Click on the Tab and click 'Save MessageProperties'
5a7. Click Yes
set ServiceID = 'ORDERS'
where ProjectName = 'EnterCustomProject NAMEwith SameSERVICEIDHERE'
set QueueName = 'local.workflowsqlexchange-'EnterCustomProject NAMEwith SameSERVICEIDHERE.processes'
from Task t inner join Messages m on t.TrackingID = m.MessageId
where t.Originator = ''EnterCustomProject NAMEwith SameSERVICEIDHERE'' and m.QueueName = 'local.workflowsqlexchange-incident.processes'
set QueueName = case
when QueueName = 'local.workflowsqlexchange-incident.assignments' then 'local.workflowsqlexchange-'EnterCustomProject NAMEwith SameSERVICEIDHERE.assignments'
when QueueName = 'local.workflowsqlexchange-incident.tasks' then 'local.workflowsqlexchange-'EnterCustomProject NAMEwith SameSERVICEIDHERE.tasks'
else QueueName end
from Task t inner join Messages m on t.TaskID = m.MessageId
where t.Originator = ''EnterCustomProject NAMEwith SameSERVICEIDHERE''
5c. Please reset the MessageProperties Foreign Key change in Step 5a back to 'No action' before starting Services
6. Start Services
7. Run the Workflow Update Installer, using the New option, Advanced Settings, and unchecking the background processing option. This can be rechecked after installation.