BizTalk Server: Support notification email on SendPort/ReceiveLocation status change

Article
1/17/2024

Introduction

BizTalk administration is an important part of an organization. For critical service integration it is very useful to be notified when things go wrong at the endpoint level (Send Ports/Receive Location) in a production system. Due to various reasons (intentional or unintentional), status of a send port or a receive location can change and sometimes it is not noticed for some time. A common solution is for a problem to generate an email to be sent to support staffm.

This helps to automate a part of BizTalk monitoring activity, ideally monitoring BizTalk artifacts status. Below steps helps you if you are setting up email alerts for your support group when things to wrong.

Overview

BizTalk server uses SQL database BizTalkMgmtDb to store all information about the BizTalk Server environment.
SQL server trigger on the table related to the artifacts will be used to detect status change.
SQL Server’s *Database Mail *feature to send emails.

Step by Step

Below steps describes step by step process.

Create Database Mail Profile

Below steps shows Database Mail Configuration Wizard to create a SQL Server mail profile, but you can use Transact-SQL too.

*Note: you need sysadmin server role to perform action *

Go to the SQL Management Studio and Explore Management

Right click to the “Database Mail”
Click to the “Configure Database Mail”
Click Next

Click Next

Click Add to add SMTP account.
Click on “New Account” to add new SMTP account.

Furnish Mail Server details, Authentication methods etc and click OK and then click Next

Click “Next” -> "Next", then "Finish"

Test Database Mail

Now verify that database mail works

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'BizTalkSupportUser_Profile',
    @recipients = 'supportgroup@MyOrg.com',
    @body = 'The stored procedure finished successfully.',
    @subject = 'Automated Success Message'  ;

Email works fine when

Message: “Mail (Id: 1012) queued”

And email appears on supportgroup@MyOrg.com

Works :)

Create triggers on BizTalkMgmtDb

For Receive Location

Execute below T-SQL to create a trigger in Management Database to detect Receive Location status change

IF OBJECT_ID ('ReceivePortChangeNotification', 'TR') IS NOT NULL
   DROP TRIGGER  ReceivePortChangeNotification;
GO
 
CREATE TRIGGER  ReceivePortChangeNotification
        ON BizTalkMgmtDb..adm_ReceiveLocation
        AFTER UPDATE
AS
    if((select Disabled from inserted) <> ((select Disabled from deleted)))
    BEGIN
        DECLARE @NewLineChar VARCHAR(5)='<br/>'
        DECLARE @To as  VARCHAR(255) = 'biztalksupportteam@MyOrg.com'
        DECLARE @Subject as VARCHAR(255)
        DECLARE @Body as VARCHAR(255)
        DECLARE @ReceiveLocationName as VARCHAR(255) 
        DECLARE @Status varchar(10)
        DECLARE @DateModified datetime
        SELECT @ReceiveLocationName = name, @DateModified = DateModified, @Status = CASE  WHEN [Disabled]=0 THEN 'Enabled'  WHEN [Disabled]=-1 THEN 'Disabled'  END  from  inserted;
 
        SET @Subject = 'BizTalk ReceiveLocation "' + @ReceiveLocationName + '" Status Changed Notification!'
        SET @Body = 'Dear Support,' + @NewLineChar +
                    'Please be adviced that the status of the receive location in subject line has been changed on: ' + convert(varchar(25), @DateModified) + @NewLineChar +
                    'New status is: ' + convert(Varchar, @Status) + @NewLineChar +
                    'Take an appropriate action!' + @NewLineChar +
                    'Kind Regards' + @NewLineChar +
                    'BizTalk Server'
 
        EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'BizTalkSupportUser_Profile',
            @recipients = @To,
            @body = @Body,
            @subject = @Subject,
            @body_format='HTML';
    END                
GO

For Send Port

Execute below T-SQL to create a trigger in Management Database to detect Send Port status change

IF OBJECT_ID ('SendPortChangeNotification', 'TR') IS NOT NULL
   DROP TRIGGER  SendPortChangeNotification;
GO
 
CREATE TRIGGER  SendPortChangeNotification
        ON BizTalkMgmtDb..bts_sendport
        AFTER UPDATE
AS
    if((select nportStatus from inserted) <> ((select nportStatus from deleted)))
        BEGIN
            DECLARE @NewLineChar VARCHAR(5)='<br/>'
            DECLARE @To as  VARCHAR(255) = 'biztalksupportteam@MyOrg.com'
            DECLARE @Subject as VARCHAR(255)
            DECLARE @Body as VARCHAR(255)
            DECLARE @SendPortName as VARCHAR(255) 
            DECLARE @Status varchar(10)
            DECLARE @DateModified datetime
            SELECT @SendPortName = nvcName, @DateModified = DateModified, @Status = CASE  WHEN nportStatus=1 THEN 'Unenlisted'  WHEN nportStatus=2 THEN 'Stopped'  WHEN nportStatus=3 THEN 'Started'  END  from  inserted;
 
            SET @Subject = 'BizTalk SendPort "' + @SendPortName + '" Status Changed Notification!'
            SET @Body = 'Dear Support,' + @NewLineChar +
                        'Please be adviced that the status of the sendport in subject line has been changed on: ' + convert(varchar(25), @DateModified) + @NewLineChar +
                        'New status is: ' + convert(Varchar, @Status) + @NewLineChar +
                        'Take an appropriate action!' + @NewLineChar +
                        'Kind Regards' + @NewLineChar +
                        'BizTalk Server'
 
            EXEC msdb.dbo.sp_send_dbmail
                @profile_name = 'BizTalkSupportUser_Profile',
                @recipients = @To,
                @body = @Body,
                @subject = @Subject,
                @body_format='HTML';
     
    END            
GO

Please Note: if you wish to notify any change on the receive location/send port to be notified, you can remove the “if condition”.

You can extend this functionality to monitor Orchestration status change on table bts_orchestration with column nOrchestrationStatus, Sendport group on table bts_sendportgroup with nPortStatus.

Additionally: don’t forget to disable these triggers if you are doing some activity as a part of scheduled maintenance.

Verification

If you have implemented this above in non production system (like QA, STG) you can try changing the receive location and/or send port and refresh the mailbox (biztalksupportteam@MyOrg.com).

Share via