BizTalk Server error: BizTalk Server cannot access SQL Server

BizTalk Server error: BizTalk Server cannot access SQL Server

A client called me this week to help with their BizTalk Server production environment. BizTalk was not running, and they needed to know the reason why. Quickly, while we investigated the issue, we saw on the BizTalk Server administration console the following error:

BizTalk Server cannot access SQL Server. This could be due to one of the following reasons:

  1. Access permissions have been denied to the current user. Either log on as a user that has been granted permissions to SQL and try again, or grant the current user permission to access SQL Server.
  2. The SQL Server does not exist, or an invalid database name has been specified. Check the name entered for the SQL Server and database to make sure they are correct as provided during SQL Server installation.
  3. The SQL Server exists, but is not currently running. Use the Windows Service Control Manager or SQL Enterprise Manager to start SQL Server, and try again.
  4. A SQL database file with the same name as the specified database already exists in the Microsoft SQL Server data folder.

Internal error from OLEDB provider: “A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)” (WinMgmt)

Cause

In this case, the error message clearly specifies perfect paths to troubleshoot and fix the issue. We knew that the first two and the last one didn’t fit our issue because SQL Server exists, and now one has changed access permission.

So, we immediately focus on point number three: The SQL Server exists, but is not currently running. We had the SQL Server Management Console open, and it appeared to be running, but when we checked the services, we realized that the SQL Server (BIZTALK) was not running but Starting.

But any attempt on our part to quickly try to get the service running was futile. Even restarting the machine was unsuccessful.

This SQL Server behavior surprised me – to be clear, at this point, we knew that this was not a BizTalk Server issue but a SQL Server issue that was affecting BizTalk Server – and that forced me to investigate one of the obvious reasons that everyone says they monitor, but… the free space on the hard drive! And guess what? We had 0 free space on C drive.

And that was the main reason for this issue in our case.

Solution

So, to solve this issue, we had to:

  • First, of course, the quick win approach was to free some space on the hard drive – we were able to clean 5GB.
  • Then, start the SQL Server (BIZTALK) service and dependencies again. After freeing up disk space, we didn’t find any issues in getting this started.
  • And, of course, we asked the IT team to increase the C drive with extra disc space.
  • Finally, we implemented a monitoring script to notify us about disk space issues: Monitoring disk spaces in your BizTalk environment with PowerShell

Hope you find this helpful! So, if you liked the content or found it useful and want to help me write more, you can buy (or help me buy) my son a Star Wars Lego! 

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc.

He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.
View all posts by Sandro Pereira

SQL Script to monitor BizTalk Server SQL Jobs

SQL Script to monitor BizTalk Server SQL Jobs

For those who’ve been following my work, you might recall that I shared a set of PowerShell scripts a while back for monitoring BizTalk Server. One of these scripts was designed to oversee BizTalk Server SQL Jobs, and you can find additional details about it here: BizTalk DevOps: Monitor your BizTalk environment using PowerShell – SQL Agent Jobs Monitoring (Part 2) and here: Monitor your BizTalk environment using PowerShell – SQL Agent Jobs Monitoring.

Within those blog posts, I also provided at least one SQL script for monitoring the status of jobs – whether they were disabled or running as anticipated, such as every minute, every fifty minutes, and so forth. However, it has come to my attention this week that even though they were enabled and running as planned, some of them were not finishing with success, and I was not notified. For this reason, I decided to create a second SQL script to enhance the functionality of the earlier one. This new script is designed to search for any failures that have occurred in the last hour. It will then present me with the most recent failure details, including the error message, for each of the Jobs in an error state.

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

Hope you find this helpful! So, if you liked the content or found it helpful and want to help me write more content, you can buy (or help buy) my son a Star Wars Lego! 

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc.

He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.
View all posts by Sandro Pereira

BizTalk Health Monitor Dashboards Customization: Monitoring BizTalk Host Instances Status

BizTalk Health Monitor Dashboards Customization: Monitoring BizTalk Host Instances Status

Have you noticed that the default BizTalk Health Monitor Dashboard doesn’t monitor/report the status of the BizTalk Server Host Instances?

A few weeks ago, while delivering a BizTalk Server training course, I was presenting and playing around with the BizTalk Health Monitor with my “students” and explaining the importance of actively monitoring your BizTalk Server platform. Because my developer environment is a machine that doesn’t have many errors and is properly configured, like a production environment, it is difficult to show the BizTalk Health Monitor presenting problems, so I decided to stop some of the Host Instances! It is an easy way to “emulate” a problem:

However, when I ran the BizTalk Health Monitor, I realized the Host Instance tile was green!

Notice also that the information provided states that I have 10 Host Instances, and only 8 were running. I was surprised by that behavior. I confirmed with the Microsoft Engineering team, and this is not a bug. Actually, by default, the Host Instances dashboard tile is NOT supposed to report a warning if some Host Instances are stopped. this tile reports, in fact, what MSFT has in the category “BizTalk Host Instance” of the KPI view:

Each default Dashboard tile normally reports the content of one or more categories of different views (Warnings, Summary, Key indicators, Queries Output…); However, the choice of content of these tiles cannot be changed by us.

Now the main question is: Can we put the BizTalk Server Health Monitor “watching” the status of the Host Instances and raising some alerts?

Luckily for us, the answer is yes, and it is not that difficult. The tool allows us to:

  • Add custom rules that will allow us to query the environment.
  • And add our own custom tiles in our profile Dashboard view.

That also means that each profile can have there own monitoring customizations.

To create some custom rules to monitor the status of the Host Instances, assuming that you have the BizTalk Health monitor tool open, we need to:

  • Right-click over the Default profile, and select the Profile settings… option from the context menu.
  • From the Default profile – Profile settings window, select the Rules tab and then click on New rule.
  • From the New Rule – Select Query window, expand the Important target category, then select the BizTalk Host Instances sub-category, and click Ok.
  • On the New Rule (Query: BizTalk Host Instances) window, select the My Rule option from the left tree and:
    • On the Caption property, give a name to the rule: Stopped Host Instances.
    • On the Comment property, provide a small description: Monitoring BizTalk Server Host Instances status.
    • On the Trigger Actions panel, select the Each time a row validated all the Rule conditions option.
    • And click Commit changes.
  • On the New Rule (Query: BizTalk Host Instances) window, select the Condition 1 option from the left tree and:
    • On the Column to Check property, leave the default value: %GLOBALPROP_REPORTVALUE:Running%.
    • On the Operator property, from dropbox, select the option: IS DIFFERENT OF.
    • On the Comparison value property, type Yes.
    • And click Commit changes.
  • On the New Rule (Query: BizTalk Host Instances) window, on the Condition option from the left tree, right-click and select the option New Condition:
  • On the New Rule (Query: BizTalk Host Instances) window, select the Condition 2 option from the left tree and:
    • On the Column to Check property, leave the default value: %GLOBALPROP_REPORTVALUE:Running%.
    • On the Operator property, from dropbox, select the option: IS DIFFERENT OF.
    • On the Comparison value property, type Not Applicable.
    • And click Commit changes.
  • On the New Rule (Query: BizTalk Host Instances) window, select the Add Summary or Warning Entry option from the left tree under the Actions option and:
    • On the Category property, type: Host Instances.
    • On the Severity property dropbox, select the Red Warning option.
    • On the Caption property, type: Host Instances Status.
    • On the Value property, type: Is %GLOBALPROP_REPORTVALUE:Name% running: %GLOBALPROP_REPORTVALUE:Running%
    • And click Commit changes.
  • Finally, click Ok.

You can always try the custom rule by clicking Test.

This rule will gather information about Host Instances that are not running. Now we are going to create another rule to gather information about Host Instances that are running. To do that, we need to:

  • From the Default profile – Profile settings window, select the Rules tab and then click on New rule.
  • From the New Rule – Select Query window, expand the Important target category, then select the BizTalk Host Instances sub-category, and click Ok.
  • On the New Rule (Query: BizTalk Host Instances) window, select the My Rule option from the left tree and:
    • On the Caption property, give a name to the rule: Running Host Instances.
    • On the Comment property, provide a small description: Monitor Running Host Instances status.
    • On the Trigger Actions panel, select the Each time a row validated all the Rule conditions option.
    • And click Commit changes.
  • On the New Rule (Query: BizTalk Host Instances) window, select the Condition 1 option from the left tree and:
    • On the Column to Check property, leave the default value: %GLOBALPROP_REPORTVALUE:Running%.
    • On the Operator property, from dropbox, select the option: IS EQUAL TO.
    • On the Comparison value property, type Yes.
    • And click Commit changes.
  • On the New Rule (Query: BizTalk Host Instances) window, select the Add Summary or Warning Entry option from the left tree under the Actions option and:
    • On the Category property, type: Host Instances.
    • On the Severity property dropbox, select the Information option.
    • On the Caption property, type: Host Instances Status.
    • On the Value property, type: Is %GLOBALPROP_REPORTVALUE:Name% running: %GLOBALPROP_REPORTVALUE:Running%
    • And click Commit changes.
  • Finally, click Ok.

Make sure that the two custom rules are selected, and then perform another analysis of your platform.

Now, what we need to do is to create a custom tile to pin to our dashboard. A custom tile can be indeed created easily from any entry or category of the Warning view, Summary view, Key indicators view, or query output view. And to do that, we need to:

  • After analyzing our BizTalk Server environment, expand the report and then select the Summary option.
  • On the Summary report page, scroll down until you find the Host Instances summary, right-click on Host Instances and select the option Pin to the dashboard and then .
  • A new window will appear, saying that a new item was added to the dashboard. Click Ok.
  • If we now click on the Default profile, we will see that the Favorite tile was added to the dashboard.
  • We can customize the name of that tile by right-clicking and selecting the Edit option.
  • On the Favorite Tile – Favorite window:
    • On the Caption property, type: Host Instances Status.
    • On the Comment property, type: Host Instances Status.
    • And click Ok.

And finally, test it by doing another analysis of the environment.

How amazing is this!

Thanks to all that helped me document this feature. You know how you are!

Hope you find this useful! So, if you liked the content or found it useful and want to help me write more content, you can buy (or help buy) my son a Star Wars Lego! 

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc.

He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.
View all posts by Sandro Pereira

Azure AD application registration monitoring: All you need to know 

Azure AD application registration monitoring: All you need to know 

App registrations is a mechanism in Azure AD allowing to work with an application and its permissions. It’s an object in Azure AD that represents the application, its redirect URI (where to redirect users after they have signed in), its logout URL (where to redirect users after they’ve signed out), API access and custom application roles for managing permissions to users and apps. 

As a matter of fact, through an app registration, you can restrict access to an application to only a specific group of users, if needed. An example of this is a solution I built a few years ago where we had two separate apps: a customer-facing app and a management app. Each had its app registration. I’ve restricted access to only a select group of people responsible for managing the system for the management app. 

Associated with an app registration is a service principal, which is the identity of that application. As you undoubtedly know, a service principal has credentials. However, you may not know that these credentials have an expiry date (end-date). If you’re not aware of that and don’t monitor and manage that, you may end up with applications and services that stop working. 

The Microsoft identity platform handles identity and access management (IAM) only for registered applications. Registering an application creates trust between the application and the Microsoft identity platform. 

The trust is unidirectional which means that the registered application trusts the Microsoft identity platform, but not the other way around. 

In Azure AD, applications can be represented in two ways: 

Application objects – Application objects define the application for Azure AD and can be viewed as the definition of the application. This enables the service to understand how to issue tokens to the application based on its settings. 

Service principals – The instance of the application in the user’s directory that controls connections to Azure AD is known as a service principal. 

Monitoring 

Serverless360 is an out-of-the-shelf platform to keep track of the expiration of client secrets for specific app registrations and delivering notifications prior to the expiration date, prompting you to renew it. 

Navigate to the Monitoring section of the resource to specify the number of days before which the expiry alert must be received, that’s pretty much the user has to configure and the rest of the work the platform will take care for you. 

Can you achieve the same from the Azure portal? 

In this section, we’ll see how we can define an Azure Automation runbook that we can run periodically to detect and get a list of those credentials that are either expired or about to expire. 

Setting up the automation runbook 

Creating an Azure Automation runbook can be done through the Azure portal or a CLI. We’ll show the portal way here. 

We first start by creating an Automation account. In the Azure portal, look for “Automation accounts”, then create a new instance: 

Once the account is created, we need to make a runbook (Use an Automation account to do many tasks where each runbook will handle a given task). 

Go to the “Runbooks” section, then click “Create a runbook” and enter the requested information 

You’re then presented with a screen to enter the code for that runbook. Our code will be in PowerShell. We’ll get to the complete source code in the next section. 

For now, I’ve displayed some sample codes:

  • You can notice, in line 3, that we import the “AzureAD” PowerShell module to interact with Azure AD. We use it at line 13 to get the list of all app registrations. 
  • You can notice that, too, between lines 6 and 9, we are authenticating to Azure AD before getting the list of app registrations (again, at line 13). 
From the toolbar (above the text editor), you can save the runbook, test it, publish it (you need to do that before you can use it in production), and revert to the previous version (in case the new version doesn’t work as expected).
 
We need first to install it since we’re importing a module (here, “AzureAD” at line 3). 

For that matter, at the Automation account level, we click on “Modules”, and we look for “AzureAD”: 

Since that module isn’t installed, we need to install it from the gallery by clicking on “Add a module”. We’ll pick 5.1 as the runtime version: 

The code 

The PowerShell code to be added to the runbook is listed?here. Replace the previous code with this one. 

The code is pretty easy to understand. One thing worth mentioning is the $daysToExpire variable that you’ll have to set to an appropriate value for your scenario. It’s intended to detect the service principals whose credentials are about to expire in the x coming days. 

Configuring the permissions for the runbook 

At this point, if you execute the runbook, you’ll notice that it might not work. That’s because the identity under which the runbook runs doesn’t have permissions to interact with Azure AD. 

An Azure Automation account has an associated identity. Find it in the “Connection” section under “Shared resources” in the Azure portal. 

I’ll choose the “AzureRunAsConnection”, which is of type “Service principal”, and give it the appropriate

To find that service principal in Azure AD, I need to search for the name of the Automation account in the list of “All applications” under “App registrations”: 
Since we want to list app registrations from the Azure AD, we need to assign the directory role “Directory readers” to the service principal associated with our Automation account (the one that will execute the runbook) following the least privileges principle. 
 
So, we go to “Roles and administrators” in our Azure AD tenant and select “Directory readers”: 
Then, we add an assignment to our service principal: 
And we’re done. 

The post Azure AD application registration monitoring: All you need to know  appeared first on Steef-Jan Wiggers Blog.

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.

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

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

Have you ever seen your Monitor BizTalk Server (BizTalkMgmtDb) job failing complaining about the existence of messages without reference counts or more know as messages without RefCounts? What are messages without RefCounts? Do they show in the BizTalk Server Administration console? Are they impacting BizTalk Server performance?

Indeed messages without 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 without RefCounts?

Messages without reference counts (RefCounts) are messages that don’t have correlating rows in the MessageRefCountLog tables and the MessageZeroSum table. Once they are in this state, the MessageBox cleanup job 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 without reference counts is by:

  • Running the Monitor BizTalk Server (BizTalkMgmtDb) job
  • Executing the View COunt of Messages Without 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 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 @nvcAppName nvarchar(256)

CREATE TABLE ##msgs_wout_refs (uidMessageID uniqueidentifier NOT NULL)
CREATE UNIQUE CLUSTERED INDEX [CIX_msg_wout_refs] ON [##msgs_wout_refs](uidMessageID)

INSERT INTO ##msgs_wout_refs (uidMessageID)
SELECT uidMessageID FROM Spool WHERE uidMessageID NOT IN(SELECT uidMessageID FROM MessageRefCountLogTotals UNION
															SELECT uidMessageID FROM MessageRefCountLog1 UNION 
															SELECT uidMessageID FROM MessageRefCountLog2 UNION
															SELECT uidMessageID FROM MessageZeroSum
														 )

DECLARE hostcursor CURSOR FOR 
SELECT nvcApplicationName FROM Applications WITH (NOLOCK) 
OPEN hostcursor
	FETCH NEXT FROM hostcursor INTO @nvcAppName
	WHILE (@@FETCH_STATUS = 0)
	BEGIN
		EXEC ('DELETE FROM ##msgs_wout_refs FROM ##msgs_wout_refs m, [dbo].[' + @nvcAppName + '_MessageRefCountLog] r WHERE m.uidMessageID = r.uidMessageID')
	FETCH NEXT FROM hostcursor INTO @nvcAppName
	END
CLOSE hostcursor
DEALLOCATE hostcursor

DECLARE @count bigint
SET @count=0
SELECT @count = count(*) from ##msgs_wout_refs

SELECT 'Messages w/o Refcounts:  ' + cast (@count as nvarchar(10))

DROP TABLE ##msgs_wout_refs

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 messages that don’t have correlating rows in the MessageRefCountLog tables and the MessageZeroSum table and should align with the ‘Messages w/out RefCounts’ issue that MessageBoxViewer identifies. 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.

BizTalk Monitor Suspend Instance Terminator Service

BizTalk Monitor Suspend Instance Terminator Service

Monitoring a BizTalk Server environment can sometimes be a complex task due to the infrastructure and complexity layers behind the BizTalk Server. Apart from that, the administrator teams need to monitor all the applications deployed to the environment.

Ideally, the administration team should use all monitoring tools at their disposal, whether they are included with the product, such as BizTalk Server Administrative console, Event Viewer, HAT, or BAM. But the main problem with these tools is that:

  • They need manually intervention.
  • Almost all of them requires remote access to the environment.

When an administrator must manually check each server or application by events that may have occurred, that is not a very efficient and effective way to allocate the team’s time nor to monitor the environment.

Of course, they can also use other monitoring tools from Microsoft, such as Microsoft System Center Operation Manager (SCOM), or third-party monitoring solutions such as BizTalk360. These tools should be able to read events from all layers of the infrastructure and help the administration team to take preventive measures, notifying them when a particular incident is about to happen, for example, when the free space of a hard drive is below 10%. Furthermore, they should allow the automation of operations when a specific event occurs, for example, restart a service when the amount of memory used by it exceeds 200MB, thereby preventing incidents or failures, without requiring human intervention.

But the question is: and if you don’t have these tools?

You can archive these tasks in several ways. Many people create custom web portals to emulate some of the most basic tasks of the admin console. One of my favorite options is using a mix of PowerShell, schedule tasks, and/or Azure Services like Logic Apps and Functions. But today I will show you a different or alternative way:

  • Create a Windows Service to monitor suspended Instances and automatically terminate them

Note: of course, this solution can be expanded to other kinds of stuff or add new funcionalities.

BizTalk Monitor Suspend Instance Terminator Service

This is a Windows Service that will be continually monitoring BizTalk Server for specific suspended messages (with an interval of x seconds/minutes/hours defined on code) and termites them automatically.

This tool allows you to configure:

  • The type of suspended messages you want to terminate
  • Terminate without saving the messages or saving them to a specific folder before terminating them.

These configurations are made on the app config of the service:

<ServiceFilter>
	<add key="ServiceClass" value="64"/>
	<add key="ServiceStatus" value="32"/>
	<add key="ErrorId" value="0xC0C01B4E"/>
	<add key="Action" value="Terminate"/>
	<add key="SaveLocation" value="C:ArchiveError1"/>
</ServiceFilter>
<ServiceFilter>
	<add key="ServiceClass" value="4"/>
	<add key="ServiceStatus" value="4"/>
	<add key="ErrorId" value="0xc0c01680"/>
	<add key="Action" value="SaveAndTerminate"/>
	<add key="SaveLocation" value="C:ArchiveError2"/>
</ServiceFilter>

You can also define on the app config file the:

  • Database name, that by default is already BizTalkMgmtDb
  • and the Database Server Host Name, by default localhost

The solution available on GitHub already provides a straightforward setup file.

Download

THIS TOOL IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND.

You can download the BizTalk Server GetTrackedMessage tool from GitHub here:

The post BizTalk Monitor Suspend Instance Terminator Service appeared first on SANDRO PEREIRA BIZTALK BLOG.