Service Broker Queues Replaced with Queue Tables

In Anthology Student version 21.0 and higher, the dependency on the SQL Server Service Broker subsystem has been removed. The Service Broker Queues have been replaced with SQL Server tables which serve a similar purpose. Messages are added to the queue and then processed in the order in which they were added (first in, first out).

Relationship Between Queue Names and Table Names

There is a relationship between Service Broker Queue names and SQL Server table names. Some queues were built to handle a single Service Broker message type and others only a single message. The concept of message types remains, and the message type is represented as a column name within the new table.

All the ServiceQueue tables have the identical schema:

Service Broker Queue Name to ServiceQueue Table Name Cross-Reference

Service Broker Queue Name	New Table Name
//Cmc/SSBQueue_Accounts_ApplyCredit_Notification	core.ApplyCreditNotificationServiceQueue
//Cmc/SSBQueue_CampusVue_Audit //Cmc/SSBQueue_CampusVue_Audit_SyServiceJobSequence //Cmc/SSBQueue_CampusVue_FaNSLDSStudent_Audit //Cmc/SSBQueue_CampusVue_FaStudentLoanPeriod_Audit //Cmc/SSBQueue_CampusVue_FaStudentLPPaymentPeriod_Audit //Cmc/SSBQueue_CampusVue_FERPAAcademicRecord_Audit	core.AuditServiceQueue
//Cmc/SSBQueue_FinancialAid_AutoAward_EventNotification	core.AutoAwardEventNotificationServiceQueue
//Cmc/SSBQueue_StudentAccounts_Billing_Aggregate_Notification	core.BillingAggregateNotificationServiceQueu
//Cmc/SSBQueue_Academics_CalculateGPA_Notification	core.CalculateGpaServiceQueue
//Cmc/SSBQueue_EXTERNAL_Notification	core.DatabaseTriggeredEventsServiceQueue
//Cmc/SSBQueue_1098T_Sa1098TBatch_Audit_Notification //Cmc/SSBQueue_1098T_Sa1098TBatchDetail_Audit_Notification //Cmc/SSBQueue_CampusVue_AsynchTrg //Cmc/SSBQueue_FASSCR_CalculateEnrollment_Notification	core.DeferredUpdateServiceQueue
//Cmc/SSBQueue_CampusVue_DegreeAuditAsynch	core.DegreeAuditServiceQueue
//Cmc/SSBQueue_Academics_Enrollment_CalculateGPA_Notification	core.EnrollmentCalculateGpaServiceQueue
//Cmc/SSBQueue_Student_Enrollment_GetCourseProgressionStatus	core.EnrollmentCourseProgressionsStatusServiceQueue
//Cmc/SSBQueue_FARefund_AutoOrigination_Notification	core.FaRefundAutoOriginationServiceQueue
//Cmc/SSBQueue_Student_Academics_GradDateExternshipDate_Update	core.GradDateExternshipUpdateServiceQueue
//Cmc/SSBQueue_LMS_Notification	core.LmsNotificationServiceQueue
//Cmc/SSBQueue_CampusVue_MultipleDisb_Recalculation	core.MultipleDisbRecalculationServiceQueue
//Cmc/SSBQueue_CampusVue_SaTransAmount_Recalculation	core.SaTransRecalculationServiceQueue
//Cmc/SSBQueue_CRM_Notification	core.ScheduledActivitiesServiceQueue
//Cmc/SSBQueue_CRM_Notification	core.ScheduledDocumentsServiceQueue
//Cmc/SSBQueue_AcademicRecords_StudentPace_Evaluator_Dates	core.StudentPaceEvaluatorServiceQueue
//Cmc/SSBQueue_Academics_TermGroupEnrollments_Recalculate	core.TermGroupEnrollmentsRecalculationServiceQueue
//Cmc/SSBQueue_CampusVue_TuitionDiscountAutomate //Cmc/SSBQueue_CampusVue_TuitionDiscountPending	core.TuitionDiscountsServiceQueue

Message Processing Stored Procedures

Table Name	Message Processing Stored Procedure – Messages Handled
core.ApplyCreditNotificationServiceQueue	core.sproc_System_Process_ApplyCreditNotificationServiceQueue
core.AuditServiceQueue	core.sproc_System_Process_AuditServiceQueue CampusVue_Audit_AdEnrollSched_Update_Request, CampusVue_FaStudentLoanPeriod_Audit, CampusVue_FaStudentLPPaymentPeriod_Audit, CampusVue_FaNSLDSStudent_Audit, CampusVue_Audit_SyServiceJobSequence_Update_Request, CampusVue_Audit_SyServiceJobSequenceItinerary_Update_Request, CampusVue_Audit_SyServiceJobSequenceTaskSchedule_Update_Request, CampusVue_DegreeAuditAsynch_Request, CampusVue_CmFERPAAcademicRelation_Audit, CampusVue_CmFERPAInfoShareHeader_Audit, CampusVue_CmFERPAStudentAcademicInfoAmendRequest_Audit, CampusVue_CmFERPAStudentInfoRequest_Audit, CampusVue_FERPA_SyAddress_Audit
core.AutoAwardEventNotificationServiceQueue	core.sproc_System_Process_AutoAwardEventNotificationServiceQueue FinancialAid_AutoAward_AcadYearChangesEvent, FinancialAid_AutoAward_ConnectedEnrollmentChangeEvent, FinancialAid_AutoAward_EnrollStatusChangeEvent, FinancialAid_AutoAward_GradeLevelChangeEvent, FinancialAid_AutoAward_InstChargeChangeEvent, FinancialAid_AutoAward_ISIRImportEvent, FinancialAid_AutoAward_ManualAwardsEvent, FinancialAid_AutoAward_NSLDSChangeEvent, FinancialAid_AutoAward_InstChargeChangeEvent
core.BillingAggregateNotificationServiceQueue	core.proc_System_Process_BillingAggregateNotificationServiceQueue StudentAccounts_Billing_Aggregate
core.CalculateGpaServiceQueue	core.sproc_System_Process_CalculateGpaServiceQueue Academics_CalculateGPA
core.DatabaseTriggeredEventsServiceQueue	core.sproc_System_Process_DatabaseTriggeredEventsServiceQueue Multiple – external events, messages handled by external process which execute V1 workflows.
core.DeferredUpdateServiceQueue	core.sproc_System_Process_DeferredUpdateServiceQueue CampusVue_AsynchTrg_AdEnroll_Update_Request, CampusVue_AsynchTrg_AdEnrollSched_Update_Request, CampusVue_AsynchTrg_AdEnrollSchedStatusHistory_Insert_Request, FASSCR_CalculateEnrollment, 1098T_Sa1098TBatch_Audit, 1098T_Sa1098TBatchDetail_Audit
core.DegreeAuditServiceQueue	core.sproc_System_Process_DegreeAuditServiceQueue CampusVue_DegreeAuditAsynch_Request
core.EnrollmentCalculateGpaServiceQueue	core.sproc_System_Process_EnrollmentCalculateGpaServiceQueue Academics_Enrollment_CalculateGPA
core.EnrollmentCourseProgressionsStatusServiceQueue	core.sproc_System_Process_EnrollmentCourseProgressionsStatusServiceQueue Student_Enrollment_GetCourseProgressionStatus
core.FaRefundAutoOriginationServiceQueue	core.sproc_System_Process_FaRefundAutoOriginationServiceQueue FARefund_AutoOrigination
core.GradDateExternshipUpdateServiceQueue	core.sproc_System_Process_GradDateExternshipUpdateServiceQueue Student_Academics_GradDateExternshipDate_Update
core.LmsNotificationServiceQueue	core.sproc_System_Process_LmsNotificationServiceQueue LMS_Notification_AdEnrollSched_Update_Request
core.MultipleDisbRecalculationServiceQueue	core.sproc_System_Process_MultipleDisbRecalculationServiceQueue CampusVue_MultipleDisb_Recalculation
core.SaTransRecalculationServiceQueue	core.sproc_System_Process_SaTransRecalculationServiceQueue CampusVue_SaTransAmount_Recalculation
core.ScheduledActivitiesServiceQueue	core.sproc_System_Process_ScheduledActivitiesServiceQueue CmProcessEvent
core.ScheduledDocumentsServiceQueue	core.sproc_System_Process_ScheduledDocumentsServiceQueue CmDocumentTrigger
core.StudentPaceEvaluatorServiceQueue	core.sproc_System_Process_StudentPaceEvaluatorServiceQueue AcademicRecords_StudentPace_Evaluator_Dates
core.TermGroupEnrollmentsRecalculationServiceQueue	core.sproc_System_Process_TermGroupEnrollmentsRecalculationServiceQueue Academics_TermGroupEnrollments_Recalculate
core.TuitionDiscountsServiceQueue	core.sproc_System_Process_TuitionDiscountsServiceQueue CampusVue_TuitionDiscountAutomate, CampusVue_TuitionDiscountPending

Activation Stored Procedures

One of the features of Service Broker that is not available when using tables, is the concept of an activation stored procedure. With Service Broker, a queue could be defined with an activation stored procedure which would execute as soon as a message is added to the queue.

As a replacement for this feature, a polling mechanism has been added to the service which checks each table for new messages on a scheduled basis. The default schedule is every 1 second. When an entry is detected in the table, the Message Processing Stored Procedure is triggered by the service. From this point onwards, the process is very similar to Service Broker. A thread will be started and will process the messages from the queue. Messages in the queue will be handled in the order in which they were added.

Internal Queue Tables

All messages from the queue tables listed above, except core.DatabaseTriggeredEventsServiceQueue, are processed by executing specific stored procedures that handle the message types.

The message processing stored procedure will pull the message from the queue and use the message and message type as parameters to call stored procedures that contain the business logic associated with the message. These stored procedures are not listed in this document but can be found by reviewing the message handling stored procedures.

External Queue Table

The messages from the queue table named core.DatabaseTriggeredEventsServiceQueue are processed by the external (windows/Webjob) service. The messages from the table are used to raise events using the V1 contracts, which in turn execute any workflows , published for the specific event.

Windows Service for On-Premise

For on-premise deployments, a Windows Service is installed with the service name of CampusVue Message Processing Service. This service starts the Cmc.Core.ServiceModuleHost.exe executable.

The Service Module host modules which have been written to be executed as a service. The module which handles the processing of the Queue tables is named ServiceQueueServiceModule. This module replaces the previous module, ServiceBrokerServiceModule, which was used to handle messages from the Service Broker queues.

The service runs in a continuous loop, pausing for 1 second, then querying all the Queue tables to check if any rows exist. The process uses an IF EXISTS type of query to check the status of each table. When the query returns a true value, the process will start another thread that will execute the stored procedure designed to handle messages for the specific queue.

This stored procedure will then execute and pull messages from the queue until the queue is empty. If there is a large volume of messages in the queue, and the queue is configured to execute on multiple threads, additional threads (up to the number of configured) will be started, that will also process messages from the queue until it is empty. As soon as the queue has been emptied, the queue threads will stop and terminate.

The one exception to this process is the DatabaseTriggeredEventsServiceQueue. When messages are found in this queue, threads will be started to handle the messages. But the difference with this queue is that the messages are returned from the SQL server to the service. The service raises the appropriate event against the V1 entity associated with the message pulled from the queue. This process will continue until the queue has been emptied and then the threads will stop and terminate.

WebJob for Azure

For Azure deployments, the service is deployed as a WebJob. The functionality of the service is identical to the Windows Service for On-Premise deployments. The only difference is that the WebJob is hosted in Azure and is started using the run.cmd, which executes the same executable as the Windows Service with the command line switch /webjob.

The name of the WebJob is: EventNotificationService

Contents of run.cmd: