A fish out of water: Test SMTP using Command Prompt

A fish out of water: Test SMTP using Command Prompt

You may already know that I usually use the series A fish out of water when I want to write something that goes a little bit off-topic on my main blog topic: Enterprise Integration. This time is not an Enterprise Integration topic but is somehow related to it since we are going to learn a possible way to try or troubleshoot SMTP issues that can be used as a channel on our integration solutions.

While working with BizTalk Server, sometimes I had the need to send email notifications or send messages thru email. In some cases, we use Office365 accounts to authenticate on the SMTP Server and send emails thru that account, but in some cases, we may have an internal SMTP Server with anonymous authentication that doesn’t require any account to send emails or a valid email to specify on the from. Of course, normally, that doesn’t mean that it is an “open bar” where everyone can send emails from and to anywhere they want. You should find some limitations thru other types of setting you can perform on the SMTP server or in the network layer like:

  • Only specific machines can access and send emails thru that SMTP Server.
  • You can only use a specific sender, or you can only send emails internally to the organization.
  • and so on.

To try to see if everything is set up correctly before you use BizTalk Server, for example, or even thru troubleshooting errors, you can try to send an email thru the Command Prompt. To do that, you need to:

  • Open Command Prompt using Start > Command Prompt or via Run > cmd
  • You then need to do a telnet to the mail server by typing telnet <domain> <port> (usually, it is 25) and then pressing Enter.
  • Once connected, we must initiate the mail-sending process by typing helo 
    • The server will reply with 250 and Hello if successful.
  • We now need to specify the sending mail address by typing mail from:<email address> and then pressing Enter.
    • The server will reply with 250 if it is a valid sender.
  • After that, we specify the recipients by typing RCPT TO:<email address> and then pressing Enter.
    • The server will reply with 250 if it is a valid receiver.

Most of the time, if errors exist, you will find them in this first part of the process.

Now to actually send an email, you need to:

  • Type data and press ENTER to begin the email content.
  • We first need to set the subject of the email by typing Subject:<Your Subject> and then pressing Enter twice.
  • Now start typing the message content of your email.  
  • To finish and close the message, do the following sequence
    • Press Enter
    • Type . (Period Key)
    • and press Enter again.

You may then receive a server response saying, for example, that the mail was queued for delivery.

  • Type Quit and press Enter to exit telnet. 

Now you just need to verify if your email was received.

Logic App Standard CI/CD from zero to hero whitepaper

Logic App Standard CI/CD from zero to hero whitepaper

Continuous integration/continuous delivery (CI/CD) pipelines are a practice focused on improving software delivery using a DevOps approach.?

A CI/CD pipeline may sound like overhead, but it isn’t. It’s essentially a runnable specification of the steps that any developer needs to perform to deliver a new software product version. In the absence of an automated pipeline, Engineers would still need to perform these steps manually and, therefore, be far less productive.

This is a must to have when deploying resources to Azure! Especially for non-development environments.

In this whitepaper, I will address and explain in a detailed way a complete guide for automating the implementation of Logic Apps Standard using Azure DevOps Pipelines.

I will explain in detail all the basic things you have to know, from the creation of a Logic App Standard on Visual Studio Code to everything you need to create and configure inside DevOps to archive the implementation of the CI/CD process.

What’s in store for you?

This whitepaper will give you a detailed understanding of the following:

  • An introduction to:
    • What are Continuous Integration (CI) and Continuous Deployment (CD)?
    • What are CI/CD Pipelines?
    • What is Azure DevOps?
  • Create an organization or project collection in Azure DevOps
  • Create a project in Azure DevOps
  • Building your Logic App Standard from scratch
    • Publish your code from Visual Studio Code
  • A step-by-step approach to building Azure Pipelines
  • A step-by-step approach to building Azure Release Pipelines

Where can I download it

You can download the whitepaper here:

I hope you enjoy reading this paper and any comments or suggestions are welcome.

BizTalk Server monitoring: View Count of BizTalk Instances Suspended (Non-Resumable)

BizTalk Server monitoring: View Count of BizTalk Instances Suspended (Non-Resumable)

As service instances (receive ports, orchestrations, send ports) process messages moving through BizTalk Server, these service instances can be in one of several states.

  • In Breakpoint: An active orchestration hits a breakpoint, typically one set by a BizTalk Server solutions developer. This state is valid only for orchestrations.
  • Ready to run: A service instance that has been activated but has not yet started running, typically due to temporary unavailability of resources, such as a heavy processing load on the server.
    Active Running service instance.
  • Dehydrated: The instance state persists in the MessageBox database, and no Windows service is running that instance.
  • Completed with discarded messages: The service instance was completed, but some messages were not consumed by the instance.
  • Suspended (resumable): Instance is suspended, you can resume it.
    • Important: Resuming a messaging instance will do the following:
      • Resume the messaging instance.
      • Send the message to the send port. The send port delivers the message to the destination, even if the send port is not in a Started state.
    • Note that when you suspend a scheduled instance and then resume it, the instance goes into a dehydrated state.
  • Suspended (not-resumable): The instance is suspended, but you cannot resume it. You can save the Messages referenced by the instance, and then you can terminate the instance.
    • Note that when you suspend a scheduled instance and then resume it, the instance goes into a dehydrated state.
  • Pending suspend/Pending terminate: A status, not an independent state. You can combine it with other states.
    • A control message to suspend or terminate was sent to a service instance but has not yet been picked up by the instance. Only one pending operation is allowed at a time. When an instance with a pending operation becomes dehydrated, you can terminate the instance.

We can see all of these service instances in all different stages inside the BizTalk Server Administration Console:

The problem with the admin console is that it needs manual intervention to check all of this information, so we need to keep clicking F5 each time we want to check them. That is not an effective monitoring operation and quite a waste of human resource time. Of course, there are several other ways to archive this goal:

  • You can use third-party monitoring tools;
  • You can use PowerShell to extract this information;
  • or you can use a simple SQL query.

Well, here is how you do a simple SQL query to count the number of Non-Resumable suspended service instances:

USE BizTalkMsgBoxDb;
SELECT COUNT(nstate) SuspendedNonResumable  
FROM Instances WITH (NOLOCK)
WHERE nstate = 3

You need to run this script against BizTalkMsgBoxDB, and it will return the count of instances Suspended that cannot be resumable. Equal to what you see in the BizTalk Server Administration Console.

Where can I download it?

You can download the SQL script here:

THIS SQL SCRIPT IS PROVIDED “AS IS” WITHOUT WARRANTY OF ANY KIND.

November 7, 2022 Weekly Update on Microsoft Integration Platform & Azure iPaaS

November 7, 2022 Weekly Update on Microsoft Integration Platform & Azure iPaaS

Do you feel difficult to keep up to date on all the frequent updates and announcements in the Microsoft Integration platform and Azure iPaaS?

Integration weekly updates can be your solution. It’s a weekly update on the topics related to Integration – enterprise integration, robust & scalable messaging capabilities, and Citizen Integration capabilities empowered by the Microsoft platform to deliver value to the business.

Microsoft Announcements and Updates

Community Blog Posts

Videos

Podcasts

How to get started with iPaaS design & development in Azure?

Feedback

Hope this would be helpful. Please feel free to reach out to me with your feedback and questions.

The post November 7, 2022 Weekly Update on Microsoft Integration Platform & Azure iPaaS appeared first on Hooking Stuff Together.

BizTalk Server monitoring:  View Count of BizTalk Instances Suspended (Resumable)

BizTalk Server monitoring: View Count of BizTalk Instances Suspended (Resumable)

As service instances (receive ports, orchestrations, send ports) process messages moving through BizTalk Server, these service instances can be in one of several states.

  • In Breakpoint: An active orchestration hits a breakpoint, typically one set by a BizTalk Server solutions developer. This state is valid only for orchestrations.
  • Ready to run: A service instance that has been activated but has not yet started running, typically due to temporary unavailability of resources, such as a heavy processing load on the server.
    Active Running service instance.
  • Dehydrated: The instance state persists in the MessageBox database, and no Windows service is running that instance.
  • Completed with discarded messages: The service instance was completed, but some messages were not consumed by the instance.
  • Suspended (resumable): Instance is suspended, you can resume it.
    • Important: Resuming a messaging instance will do the following:
      • Resume the messaging instance.
      • Send the message to the send port. The send port delivers the message to the destination, even if the send port is not in a Started state.
    • Note that when you suspend a scheduled instance and then resume it, the instance goes into a dehydrated state.
  • Suspended (not-resumable): The instance is suspended, but you cannot resume it. You can save the Messages referenced by the instance, and then you can terminate the instance.
    • Note that when you suspend a scheduled instance and then resume it, the instance goes into a dehydrated state.
  • Pending suspend/Pending terminate: A status, not an independent state. You can combine it with other states.
    • A control message to suspend or terminate was sent to a service instance but has not yet been picked up by the instance. Only one pending operation is allowed at a time. When an instance with a pending operation becomes dehydrated, you can terminate the instance.

We can see all of these service instances in all different stages inside the BizTalk Server Administration Console:

The problem with the admin console is that it needs manual intervention to check all of this information, so we need to keep clicking F5 each time we want to check them. That is not an effective monitoring operation and quite a waste of human resource time. Of course, there are several other ways to archive this goal:

  • You can use third-party monitoring tools;
  • You can use PowerShell to extract this information;
  • or you can use a simple SQL query.

Well, here is how you do a simple SQL query to count the number of Resumable suspended service instances:

USE BizTalkMsgBoxDb;
SELECT COUNT(nstate) SuspendedResumable  
FROM Instances WITH (NOLOCK)
WHERE nstate = 4

You need to run this script against BizTalkMsgBoxDB, and it will return the count of instances Suspended that can be resumable. Equal to what you see in the BizTalk Server Administration Console.

Where can I download it?

You can download the SQL script here:

THIS SQL SCRIPT IS PROVIDED “AS IS” WITHOUT WARRANTY OF ANY KIND.

BizTalk Server monitoring: View Count of Dehydrated BizTalk Instances

BizTalk Server monitoring: View Count of Dehydrated BizTalk Instances

As service instances (receive ports, orchestrations, send ports) process messages moving through BizTalk Server, these service instances can be in one of several states.

  • In Breakpoint: An active orchestration hits a breakpoint, typically one set by a BizTalk Server solutions developer. This state is valid only for orchestrations.
  • Ready to run: A service instance that has been activated but has not yet started running, typically due to temporary unavailability of resources, such as a heavy processing load on the server.
    Active Running service instance.
  • Dehydrated: The instance state persists in the MessageBox database, and no Windows service is running that instance.
  • Completed with discarded messages: The service instance was completed, but some messages were not consumed by the instance.
  • Suspended (resumable): Instance is suspended, you can resume it.
    • Important: Resuming a messaging instance will do the following:
      • Resume the messaging instance.
      • Send the message to the send port. The send port delivers the message to the destination, even if the send port is not in a Started state.
    • Note that when you suspend a scheduled instance and then resume it, the instance goes into a dehydrated state.
  • Suspended (not-resumable): The instance is suspended, but you cannot resume it. You can save the Messages referenced by the instance, and then you can terminate the instance.
    • Note that when you suspend a scheduled instance and then resume it, the instance goes into a dehydrated state.
  • Pending suspend/Pending terminate: A status, not an independent state. You can combine it with other states.
    • A control message to suspend or terminate was sent to a service instance but has not yet been picked up by the instance. Only one pending operation is allowed at a time. When an instance with a pending operation becomes dehydrated, you can terminate the instance.

We can see all of these service instances in all different stages inside the BizTalk Server Administration Console:

The problem with the admin console is that it needs manual intervention to check all of this information, so we need to keep clicking F5 each time we want to check them. That is not an effective monitoring operation and quite a waste of human resource time. Of course, there are several other ways to archive this goal:

  • You can use third-party monitoring tools;
  • You can use PowerShell to extract this information;
  • or you can use a simple SQL query.

Well, here is how you do a simple SQL query to extract Dehydrated service instances:

USE BizTalkMsgBoxDb;
SELECT COUNT(nstate) Dehydrated 
FROM Instances WITH (NOLOCK)
WHERE nstate = 8

You need to run this script against BizTalkMsgBoxDB, and it will return the count of instances Dehydrated. Equal to what you see in the BizTalk Server Administration Console.

Where can I download it?

You can download the SQL script here:

THIS SQL SCRIPT IS PROVIDED “AS IS” WITHOUT WARRANTY OF ANY KIND.

BizTalk Server monitoring: View Count of Active (running) BizTalk Instances

BizTalk Server monitoring: View Count of Active (running) BizTalk Instances

As service instances (receive ports, orchestrations, send ports) process messages moving through BizTalk Server, these service instances can be in one of several states.

  • In Breakpoint: An active orchestration hits a breakpoint, typically one set by a BizTalk Server solutions developer. This state is valid only for orchestrations.
  • Ready to run: A service instance that has been activated but has not yet started running, typically due to temporary unavailability of resources, such as a heavy processing load on the server.
    Active Running service instance.
  • Dehydrated: The instance state persists in the MessageBox database, and no Windows service is running that instance.
  • Completed with discarded messages: The service instance was completed, but some messages were not consumed by the instance.
  • Suspended (resumable): Instance is suspended, you can resume it.
    • Important: Resuming a messaging instance will do the following:
      • Resume the messaging instance.
      • Send the message to the send port. The send port delivers the message to the destination, even if the send port is not in a Started state.
    • Note that when you suspend a scheduled instance and then resume it, the instance goes into a dehydrated state.
  • Suspended (not-resumable): The instance is suspended, but you cannot resume it. You can save the Messages referenced by the instance, and then you can terminate the instance.
    • Note that when you suspend a scheduled instance and then resume it, the instance goes into a dehydrated state.
  • Pending suspend/Pending terminate: A status, not an independent state. You can combine it with other states.
    • A control message to suspend or terminate was sent to a service instance but has not yet been picked up by the instance. Only one pending operation is allowed at a time. When an instance with a pending operation becomes dehydrated, you can terminate the instance.

We can see all of these service instances in all different stages inside the BizTalk Server Administration Console:

The problem with the admin console is that it needs manual intervention to check all of this information, so we need to keep clicking F5 each time we want to check them. That is not an effective monitoring operation and quite a waste of human resource time. Of course, there are several other ways to archive this goal:

  • You can use third-party monitoring tools;
  • You can use PowerShell to extract this information;
  • or you can use a simple SQL query.

Well, here is how you do a simple SQL query to extract active (running) service instances:

USE BizTalkMsgBoxDb;
SELECT COUNT(nstate) Active 
FROM Instances WITH (NOLOCK)
WHERE nstate = 2

You need to run this script against BizTalkMsgBoxDB, and it will return the count of active (running) service instances. Equal to what you see in the BizTalk Server Administration Console.

Where can I download it?

You can download the SQL script here:

THIS SQL SCRIPT IS PROVIDED “AS IS” WITHOUT WARRANTY OF ANY KIND.

October 31, 2022 Weekly Update on Microsoft Integration Platform & Azure iPaaS

October 31, 2022 Weekly Update on Microsoft Integration Platform & Azure iPaaS

Do you feel difficult to keep up to date on all the frequent updates and announcements in the Microsoft Integration platform and Azure iPaaS?

Integration weekly updates can be your solution. It’s a weekly update on the topics related to Integration – enterprise integration, robust & scalable messaging capabilities and Citizen Integration capabilities empowered by Microsoft platform to deliver value to the business.

Microsoft Announcements and Updates

Community Blog Posts

Videos

Podcasts

How to get started with iPaaS design & development in Azure?

Feedback

Hope this would be helpful. Please feel free to reach out to me with your feedback and questions.

The post October 31, 2022 Weekly Update on Microsoft Integration Platform & Azure iPaaS appeared first on Hooking Stuff Together.

BizTalk Server Maintenance: Repair Refcounts issues for All Messages

In my last two blog posts, BizTalk Server monitoring: View Count of Messages Without Reference Counts (RefCounts) and BizTalk Server monitoring: View Count of Messages With Negative Reference Counts (RefCounts), I explain these two types of Messages without or with negative reference counts and how you can check and monitor them. Not only that, but I also explain what impacts they can have on your BizTalk Server environment.

All of these tasks, including the repair task that I’m addressing here, are available and can be, and should be, executed from the BizTalk Health Monitor tool. For those who are not aware of BHM, BHM is an MMC snap-in that allows the user to monitor the health of BizTalk Server environments and execute maintenance tasks. It is, in a simple way, an aggregator of two familiar tools for BizTalk Server: the old MsgBox Viewer and the old BizTalk Terminator.

However, once again, have you ever been curious to know how you can repair these types of messages? Or did you already face the issue that you cannot open the BizTalk Health monitor because it is failing or you don’t have an Internet connection to update them to the last version? Or maybe you are using an environment older than BizTalk Server 2013?

Well, now you have it here:

TRUNCATE TABLE MessageRefCountLog1
TRUNCATE TABLE MessageRefCountLog2
TRUNCATE TABLE MessageRefCountLogTotals
TRUNCATE TABLE MessageZeroSum

UPDATE ActiveRefCountLog SET tnActiveTable = 1 WHERE fType = 1

OPEN curse
FETCH NEXT FROM curse INTO @nvcAppName
WHILE (@@FETCH_STATUS = 0)
BEGIN 
	-- Truncate local application refcount log if it exists
	EXEC('if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[' + @nvcAppName + '_MessageRefCountLog]'') and OBJECTPROPERTY(id, N''IsUserTable'') = 1) TRUNCATE TABLE [' + @nvcAppName + '_MessageRefCountLog]')           

	-- Find all the message refs which have no associated row in the spool
	EXEC ('INSERT INTO MessageRefCountLog1 (uidMessageID, snRefCount) SELECT uidMessageID, COUNT(*) FROM [' + @nvcAppName + 'Q] GROUP BY uidMessageID')
	EXEC ('INSERT INTO MessageRefCountLog1 (uidMessageID, snRefCount) SELECT uidMessageID, COUNT(*) FROM [' + @nvcAppName + 'Q_Suspended] GROUP BY uidMessageID')
	EXEC ('INSERT INTO MessageRefCountLog1 (uidMessageID, snRefCount) SELECT uidMessageID, COUNT(*) FROM [InstanceStateMessageReferences_' + @nvcAppName + '] GROUP BY uidMessageID')
                
                
	FETCH NEXT FROM curse INTO @nvcAppName
END
CLOSE curse
DEALLOCATE curse

INSERT INTO MessageRefCountLog1 (uidMessageID, snRefCount) 
SELECT uidMessageID, COUNT(*) 
FROM [TrackingMessageReferences] GROUP BY uidMessageID
                
-- Inserting restart message details, for service recovery
IF NOT EXISTS (SELECT TOP 1 uidMessageID FROM Spool WHERE uidMessageID = N'61EAA7FC-AC85-42d9-BF3E-1BED258B82BE')
BEGIN
	INSERT INTO Spool (uidMessageID, nNumParts, nCounter, imgContext) VALUES (N'61EAA7FC-AC85-42d9-BF3E-1BED258B82BE', 0, 0, 0xD4E0906C1849D311A24200C04F60A53302000000090000009800000050006100720074004E0061006D00650073005E0068007400740070003A002F002F0073006300680065006D00610073002E006D006900630072006F0073006F00660074002E0063006F006D002F00420069007A00540061006C006B002F0032003000300033002F006D006500730073006100670065006100670065006E0074002D00700072006F007000650072007400690065007300000001000000010820010000000000000000000700000098000000540069006D0065007200490044005E0068007400740070003A002F002F0073006300680065006D00610073002E006D006900630072006F0073006F00660074002E0063006F006D002F00420069007A00540061006C006B002F0032003000300033002F0078006C0061006E00670073002D00720075006E00740069006D0065002D00700072006F0070006500720074006900650073000000010000000008004A000000300030003000300030003000300030002D0030003000300030002D0030003000300030002D0030003000300030002D003000300030003000300030003000300030003000300030000000)
END

-- Inserting Suspend Control message
IF NOT EXISTS (SELECT TOP 1 uidMessageID FROM Spool WHERE uidMessageID = N'2BE3D5B8-5685-40F2-BD97-51ADA3D02347')
BEGIN
	INSERT INTO Spool (uidMessageID, nNumParts, nCounter, imgContext) VALUES (N'2BE3D5B8-5685-40F2-BD97-51ADA3D02347', 0, 0, 0xD4E0906C1849D311A24200C04F60A53303000000000000001E000000420069007A00540061006C006B0043006F006E00740072006F006C000000020000000008002A000000410064006D0069006E00530075007300700065006E00640049006E007300740061006E00630065000000000000000C0000004A006F006200490044000000020000000008004E0000007B00340037004100450033003300380034002D0031003000410041002D0034003400430033002D0038003200350036002D003200350033003400380045004200390032004200320031007D000000090000009800000050006100720074004E0061006D00650073005E0068007400740070003A002F002F0073006300680065006D00610073002E006D006900630072006F0073006F00660074002E0063006F006D002F00420069007A00540061006C006B002F0032003000300033002F006D006500730073006100670065006100670065006E0074002D00700072006F00700065007200740069006500730000000100000001082001000000000000000000)
END

-- Inserting  Terminate Control message
IF NOT EXISTS (SELECT TOP 1 uidMessageID FROM Spool WHERE uidMessageID = N'57E5E753-0207-435D-8BE7-2B9F3C6556F9')
BEGIN
	INSERT INTO Spool (uidMessageID, nNumParts, nCounter, imgContext) VALUES (N'57E5E753-0207-435D-8BE7-2B9F3C6556F9', 0, 0, 0xD4E0906C1849D311A24200C04F60A53303000000000000001E000000420069007A00540061006C006B0043006F006E00740072006F006C000000020000000008002E000000410064006D0069006E005400650072006D0069006E0061007400650049006E007300740061006E00630065000000000000000C0000004A006F006200490044000000020000000008004E0000007B00350032003500390031004600310031002D0046003700370034002D0034003600330038002D0042004300390041002D003200380034003800380034003600300034003500450032007D000000090000009800000050006100720074004E0061006D00650073005E0068007400740070003A002F002F0073006300680065006D00610073002E006D006900630072006F0073006F00660074002E0063006F006D002F00420069007A00540061006C006B002F0032003000300033002F006D006500730073006100670065006100670065006E0074002D00700072006F00700065007200740069006500730000000100000001082001000000000000000000)
END

-- Inserting Resume in Debug Mode message
IF NOT EXISTS (SELECT TOP 1 uidMessageID FROM Spool WHERE uidMessageID = N'50D173AF-5D6F-4D5F-AE23-1A7178CEBDC3')
BEGIN
	INSERT INTO Spool (uidMessageID, nNumParts, nCounter, imgContext) VALUES (N'50D173AF-5D6F-4D5F-AE23-1A7178CEBDC3', 0, 0, 0xD4E0906C1849D311A24200C04F60A53303000000000000001E000000420069007A00540061006C006B0043006F006E00740072006F006C0000000200000000080026000000410064006D0069006E004400650062007500670049006E007300740061006E00630065000000000000000C0000004A006F006200490044000000020000000008004E0000007B00330032003800330030004500450045002D0035004400330041002D0034003700370037002D0041003200440035002D003100320033004100450035004600340044004500420039007D000000090000009800000050006100720074004E0061006D00650073005E0068007400740070003A002F002F0073006300680065006D00610073002E006D006900630072006F0073006F00660074002E0063006F006D002F00420069007A00540061006C006B002F0032003000300033002F006D006500730073006100670065006100670065006E0074002D00700072006F00700065007200740069006500730000000100000001082001000000000000000000)                
END
--we need to add refcounts for the control messages in our system.

INSERT INTO MessageRefCountLogTotals (uidMessageID, snRefCount) VALUES (N'61EAA7FC-AC85-42d9-BF3E-1BED258B82BE', 1)
INSERT INTO MessageRefCountLogTotals (uidMessageID, snRefCount) VALUES (N'2BE3D5B8-5685-40F2-BD97-51ADA3D02347', 1)
INSERT INTO MessageRefCountLogTotals (uidMessageID, snRefCount) VALUES (N'57E5E753-0207-435D-8BE7-2B9F3C6556F9', 1)
INSERT INTO MessageRefCountLogTotals (uidMessageID, snRefCount) VALUES (N'50D173AF-5D6F-4D5F-AE23-1A7178CEBDC3', 1)

--lets run the stored procedure to process all of this and add up the values to put them in the totals table
EXEC int_PurgeMessageRefCountLog 1, @ret OUTPUT


--Now we have everything in the totals table (nothing is in the zero sum table since we only added references to messages
--currently in the messagebox. There were no releases (ie negative numbers)
--Lets populate the zerosum table with any messages in the spool which are not in the totals table
INSERT INTO MessageZeroSum (uidMessageID)
SELECT uidMessageID FROM Spool s WHERE s.uidMessageID NOT IN (SELECT uidMessageID FROM MessageRefCountLogTotals)

Note: This is part of the script. If you want the full script download it from the link available at the end of the post.

This script rebuilds all the MessageRefCountLog tables and the MessageZeroSum table to resolve the ‘Messages w/out RefCounts’ issue that MessageBoxViewer identifies. While doing this, control messages are rebuilt if needed.

WARNINGS:

  • THIS OPERATION WILL PERMANENTLY REMOVE OR ALTER DATA IN YOUR DATABASE.
  • Before running this script, make sure that:
    • All BizTalk databases should be backed up
    • All host instances should be stopped
    • All BizTalk SQL Agent jobs should be stopped.

THIS SQL SCRIPT IS PROVIDED “AS IS” WITHOUT WARRANTY OF ANY KIND.

Once again, if possible, run these scripts and execute these tasks inside the BizTalk Health Monitor tool.

Where can I download it?

You can download the SQL script here:

BizTalk Server monitoring: View Count of Messages With Negative Reference Counts (RefCounts)

In the previous post, we analyze how you can monitor messages without reference counts (RefCounts), today it will be a similar topic, but this time we will be addressing messages with negative reference counts.

and the same question applies here:

  • Have you ever seen your Monitor BizTalk Server (BizTalkMgmtDb) job failing and complaining about the existence of messages with negative reference counts
  • Or do you ever want to know more about messages with negative RefCounts?
  • What are messages with negative RefCounts?
  • Do they show in the BizTalk Server Administration console? Are they impacting BizTalk Server performance?

Indeed messages with negative reference counts can appear from time to time in our environment, which you should definitely need to monitor. And to response to all previous questions:

What are messages with negative RefCounts?

Messages with negative RefCounts are messages in the MessageRefCountLogTotals with snRefCount less than zero. Once a refcount goes negative, the MessageBox cleanup jobs will not be able to clean up the corresponding messages.

Do they show in the BizTalk Server Administration console?

No, they don’t. The only way for you to know that exists messages with negative reference count is by:

  • Running the Monitor BizTalk Server (BizTalkMgmtDb) job
  • Executing the View Count of Messages With Negative RefCounts available on the BizTalk Health Monitor (maintenance)
  • Or executing a custom query against BizTalk Server databases.

Are they impacting BizTalk Server performance?

Yes, if they are too many.

As I mentioned before, the Monitor BizTalk Server SQL Agent job can detect these kinds of messages. In fact, it is able to identify any known issues in Management, MessageBox, or DTA databases. The job scans for the following issues:

  • Messages without any references
  • Messages without reference counts
  • Messages with negative reference counts
  • Messages with reference count less than 0
  • Message references without spool rows
  • Message references without instances
  • Instance state without instances
  • Instance subscriptions without corresponding instances
  • Orphaned DTA service instances
  • Orphaned DTA service instance exceptions
  • TDDS is not running on any host instance with the global tracking option enabled.

By default, the Monitor BizTalk Server job is configured and automated to run once in a week. Since the job is computationally intensive, it is recommended to schedule it during downtime/low traffic. The job fails if it encounters any issues; error string contains the number of issues found. Otherwise, it runs successfully.

Note: The Monitor BizTalk Server job only scans for issues. It does not fix the issues found.

However, are you ever curious to know how you can find these types of messages? Or did you already face the issue that you cannot open the BizTalk Health monitor because it is failing or you don’t have Internet connection to update them to the last version?

Well, now you have it here:

DECLARE @count bigint
SET @count = 0

SELECT @count = COUNT(*) FROM [dbo].[MessageRefCountLogTotals] WHERE [snRefCount] < 0

IF @count = 0
BEGIN
    SELECT 'There are no negative RefCounts'
END
ELSE
BEGIN
    SELECT COUNT(*) Count, [snRefCount] as 'RefCount Value' 
	FROM [dbo].[MessageRefCountLogTotals] 
	WHERE [snRefCount] < 0
    GROUP BY [snRefCount] 
    ORDER BY [snRefCount] 
END

Of course, needless to say, use it with care and thoughtfulness! It is preferred to call this query inside the BizTalk Health Monitor, and ideally, it is recommended to execute it during downtime/low traffic.

You need to run this script against BizTalkMsgBoxDB, and it will return the count of negative refcounts in the MessageRefCountLogTotals table. Equal to the script on the BizTalk Health Monitor.

Where can I download it?

You can download the SQL script here:

THIS SQL SCRIPT IS PROVIDED “AS IS” WITHOUT WARRANTY OF ANY KIND.