Table of Contents

​

Introduction

BizTalk administration is an important part in an organization. For critical service integration it is also a requirement to get notified when things go wrong at the endpoint level (Send Ports/Receive Location) in the production system. Due to various reasons (intentional or unintentional), status of a send port or a receive location gets changed and sometimes it is not known for longer time. Many people prefer emails to trigger a support person who is responsible for the operation. 

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).

See Also

Another important place to find a huge amount of BizTalk related articles is the TechNet Wiki itself. The best entry point is BizTalk Server Resources on the TechNet Wiki.