First, let’s start from advantages of Service Broker (SB) – why it can be useful. I will list main points and examples how we use it.
- SB provides mechanism of asynchronous queue. This gives us an opportunity to move some hard SQL procedures from users’ transactions.
- E.g.: we had stored procedures that log changes in the object to special history tables. If object contains head table and several line tables, this procedure can take time equal to document save time. We move execution of this stored procedure to the Service Broker thus improving system performance.
- The most important thing is that SB performs its message creation inside main transaction. In case of any error everything will be rollbacked.
- Database integrity is really everything. If we cannot trust Service Broker it would be of no use.
- SB gives us a possibility to transfer data to other systems and applications without any additional scheduling.
- Our systems have a huge number of interfaces to other databases and applications (about 100). Before SB we have to create for each interface a job (or add a new step to existing job). Jobs were scheduled to execute at some time intervals – this was not very good for business as they need data transfer ASAP, also it added additional load on server, as we have to look into history tables to find if anything was changed. SB solves all this problems, as data is transferred just in several minutes after document is saved and this activity is performed only when document is modified.
- In case of error it would automatically stop processing. No endless attempts.
- Before SB implementation jobs were sending errors any time a problem had occurred. We have to create complex algorithms to stop processing record after a number of unsuccessful attempts. They were unique for each object, so hard to maintain and expand. SB has solved this problem.
Below I would like to dwell on some disadvantages of SB.
- No simple way to view error log of message processing.
- Quite impossible to restore message that failed with error to be processed again.
- No mechanism to execute external Web-method as message processing (we have found only way through CLR with UNSAFE option, but it was banned by our admins).
So, in order to maximize advantages and get rid of disadvantages we have created the special log table, which contains all information we required to analyze the work of SB and in case of need to reinitialize the message for retreatment.
The description of fields:
- Contract – contract that is used to process queue;
- TargetService – target service;
- MessageType – message type;
- MessageBody – message body, packed in XML. We usually put there information to directly identify the processed record (ID, type of operation, date time when in was put into queue, etc)
- ConversationHandle – dialog handler;
- MessageSequenceNumber – we do not use this field now ))).
- MessageError – if error occurs while processing message, we store it in this field;
- StateId – result of the message processing (1 – ok, 2 – error, 3 – the message was excluded from the queue);
- VerDateTime – message processing date time.
- InitService – service that initiates message queuing;
- QueueStatID – reference to the table in which we store queue names.
So in this table we have all information required for analysis and re-queuing. In the next post I’m planning to speak about how we put messages to queues.