"Flush failed to run" SQL error with BAM API

Today I encountered an unusual error when executing a pipeline component that utilises the EventStream API to write to BAM. The failure that showed up in the event log looked something like this:

A message received by adapter "WCF-SQL" on receive location "MyReceivePort" with URI "mssql://MyDatabaseInstance/MyDatabase?InboundId=Employee" is suspended.
Error details: There was a failure executing the receive pipeline: "MyReceivePipeline, MyAssembly, Version=1.0.0.0, Culture=neutral, PublicKeyToken=1a2b345c67d89e0f" Source: "Log Message To BAM Receive" Receive Port: "MyReceivePort" URI: "mssql://MyDatabaseInstance/MyDatabase?InboundId=Employee" Reason: Flush failed to run. 

A quick Google search pulled up this helpful post by Yossi Dahan, which pointed me in the right direction. I knew that the connection string was all right, and I was using the BufferedEventStream rather than the DirectEventStream that Yossi referred to. (Incidentally, when using the BufferedEventStream your connection string actually points to the BizTalkMsgBoxDb database rather than the BAMPrimaryImport database.)

However, the clue was really in the second part of Yossi’s suggestion (and also in an anonymous comment), "…and related permissions…".  I could see that the BizTalk Application Users domain group had been assigned all of the appropriate roles in SQL Server, and I knew that all the host accounts had been dutifully added to this group when BizTalk was installed.

Er… hang on a moment. I double checked and found that the SQL Adapter was running under a new dedicated host account that had been created specifically for the data warehouse. A simple check on the account using the "net user /domain" command prompt unveiled the culprit. This account had not be granted membership in the BizTalk Application Users domain group.

Once that was accomplished, everything worked smoothly.

It would be nice to actually see an error that hinted towards permission issues. Perhaps the detail was buried somewhere in an inner exception, but the logging does not go past the first level.

Gotchas When Accessing an IaaS SQL Server in an Azure Cloud Service

It’s not that uncommon a scenario… You implement a spectacular BizTalk Server integration solution with well-designed Business Activity Monitoring, and you want to expose all that BAM goodness out through a custom-designed Web application built with HTML5, etc.

What might make this scenario slightly less common is when you’re hosting the BizTalk Server infrastructure in Azure. In this case, your custom Web application needs to be hosted within the same virtual network in order to access the BAM databases – unless of course you want to expose your SQL Server to the world by establishing a TCP endpoint on a port like 1433 (usually not a great idea).

Establishing that connectivity should be trivial – and it probably is if you get past a few potential stumbling blocks. I thought I might list some of them here in hopes that it may be helpful to others who try this.

Read more of this post

BizTalk SQL Agent Jobs – Configuration Errors

In a previous post, I talked about how to configure the BizTalk SQL Agent jobs responsible for backing up the databases and purging the tracking data. These jobs are not configured by default, but their operation is essential for keeping your BizTalk system running smoothly.

Today, I inherited a developer VM at a new client with a warning that it was “running a bit slow…”.  Upon investigation, I discovered that the Backup BizTalk Server job had not been configured, so I promptly set about doing that task. However, I uncovered an error once I tried to run the job the first time:

Executed as user: NT AUTHORITY\SYSTEM. The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE. [SQLSTATE 42000] (Error 4208)  BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013).  The step failed.

Read more of this post

Solving a Map Reference to a Deleted Schema

Recently a client got into a bit of “hot water” in their BizTalk integration environment because changes to a Common BizTalk application were not deployed prior to one of the dependent applications. This resulted in an error whenever trying to manage the environment’s applications via the BizTalk Admin Console (even when we followed the hint to refresh the console view):

Schema referenced by Map ‘Test.DependentApp.DependentSchema_to_CommonSchema’ has been deleted. The local, cached version of the BizTalk Server group configuration is out of date. You must refresh the BizTalk Server group configuration before making further changes.
(Microsoft.BizTalk.Administration.SnapIn)

The issue was that a new schema contained in the updated Common project was missing. Easy enough to understand, and the steps to resolve it would appear to be simple enough:

  1. Undeploy the dependent application
  2. Redeploy the Common application (using the new version)
  3. Redeploy the dependent application

However, the error above thwarted us at the very first step: we could not delete the dependent application either through the Admin Console or via BTSTask! Read more of this post

Configuring the BizTalk SQL Agent Jobs

One of the things people often forget to do when installing BizTalk into an environment for the first time is to configure and enable two critical SQL Server Agent jobs:

  • Backup BizTalk Server
  • DTA Purge and Archive

These jobs are not enabled by default because they both require you to supply UNC paths to the backup and archive file locations, respectively.  While backups and archiving may not seem like the most immediate priority (especially in non-production environments), it is nonetheless essential that these jobs run successfully as they keep the BizTalk databases “lean & mean” by purging old data. I’ve seen client tracking databases grow to over 200GB in size when the latter job is not running properly (the Microsoft recommended size is <5GB!!)

Read more of this post

John Glisson - Geek of the Cloth

Thoughts on integration, technology and what-not...

prashantbiztalkblogs

My BizTalk Experiences

The CRUCIBLE

THINK: It's not illegal....yet.....

paulbouwer.com

life and technology

Abdul Rafay's BizTalk Blog

My experiences with BizTalk related to architecture, development and performance in my enterprise.

Mike Diiorio

Connected Systems and other thoughts

BizTalk musings

Issues, patterns and useful tips for BizTalk development

EAI Guy.net

Enterprise Applicaiton Integration and SOA 2.0

Connected Pawns

Mainly BizTalk & Little Chess

Man Vs. Machine

Why can't we all just get along?

Adventures inside the Message Box

BizTalk, Azure, and other tools in the Microsoft stack - Johann Cooper

Biz(Talk)2

Talk, talk and more talk about BizTalk

Richard Seroter's Architecture Musings

Blog Featuring Code, Thoughts, and Experiences with Software and Services

Sandro Pereira BizTalk Blog

My notes about BizTalk Server 2004, 2006, 2006 R2, 2009, 2010, 2013 and now also Windows Azure BizTalk Services.

BizTalk Events

Calendar of BizTalk events all over the world!

Mind Over Messaging

Musings on BizTalk, Azure, and Enterprise Integration

The WordPress.com Blog

The latest news on WordPress.com and the WordPress community.