Configure the Exception Management Framework in the ESB toolkit to route fault messages to SQL Azure

The ESB Exception Management Framework provides the ESB Exception Off-ramp that subscribes to all ESB-generated fault messages, but also to exceptions generated by the BizTalk Server failed message routing mechanism. The component routes fault messages to the ESB exception management database, but is it also possible to configure it to route to SQL Azure?
The Exception Management Framework in BizTalk 2013 still uses the SQL adapter to send messages to the EsbExceptionDb database. The SQL adapter runs distributed transactions and uses the Data Transaction Coordinator (DTC). In Azure you can’t use DTC so let’s see what we have to modify to make it work.

 

Steps

The following tasks must be performed to route fault messages to SQL Azure :

  • Create a new SQL Azure Database
  • Copy the necessary tables and procedures from the ESB exception management database to the SQL Azure database
  • Create a new map in Visual Studio to map to the stored procedure in the SQL Azure database
  • Modify the ESB Exception Off-ramp in BizTalk to use the WCF-SQL adapter and the new map.
 
Create a new SQL Azure Database
Open the Windows Azure Management Portal in the browser.
Location: https://manage.windowsazure.com/
 
To create a database, click SQL DATABASES in the left-hand navigation pane and click on CREATE A SQL DATABASE.
CreateSqlDatabase_0
 
Enter Login Name, Password and the location of the Data Center.
CreateSqlDatabase_1
 
Enter Database Name, Maximum Database Size, Collation and the name of the Subscription.
CreateSqlDatabase_2
 
 
Copy the necessary tables and procedures to the SQL Azure database
In SQL Server Management Studio, connect to the EsbExceptionDb database from BizTalk.
 
Right-click on the Fault table and script the CREATE TABLE statement to a File or a New Query Editor Window.
ScriptTable
 
Also script the stored procedure: usp_insert_Fault.
 
Note
The Exception Management Framework also uses other tables and stored procedures but in this example we are only using the main table. *(See Conclusion)
 
In SQL Server Management Studio connect to the newly created SQL Azure database.
ConnectToServer
 
Open a New Query Window and use the generated scripts to create the Fault table and the usp_insert_Fault stored procedure on the SQL Azure database.
CreateTable
 
Note
Not all keywords are supported in SQL Azure. Remove the ON [PRIMARY] keywords from the CREATE TABLE statement otherwise you will get an error like:
'Filegroup reference and partitioning scheme' is not supported in this version of SQL Server.
 
 
Create a new map in Visual Studio to map to the stored procedure in the SQL Azure database
The map in the ESBFaultProcessor pipeline in the ESB Exception Off-ramp is for the SQL adapter so you have to create a new map for the WCF-SQL adapter.
 
In Visual Studio create a new BizTalk project
 
To create a .XSD schema for the usp_insert_Fault stored procedure:
In the Solution Explorer right-click the BizTalk project and add a generated item. Choose: Consume Adapter Service.
ConsumeAdapterService_0
 
Connect to the SQL Azure database and select the usp_insert_Fault stored procedure. Click on OK.
ConsumeAdapterService
 
The ESBFaultProcessor pipeline in the ESB Exception Off-ramp creates a FaultMessage. The FaultMessage schema is located in the Microsoft.Practices.ESB.ExceptionHandling.Schemas.Reporting.dll library. Create a reference to the library to use the schema.
 
Create a new map and connect the FaultMessage schema to the usp_insert_Fault schema.
Map
 
Note

I’ve got the following exception when I close the map in Visual Studio 2012. A Beta bug?!

Application: devenv.exe
Framework Version: v4.0.30319
Description: The process was terminated due to an unhandled exception.
Exception Info: System.NullReferenceException
Stack:
at Microsoft.BizTalk.Mapper.MapperVsPackage.ConnecterShape.ConnecterPosition(System.Windows.Forms.TreeNode, Microsoft.BizTalk.Drawing.DrawingControl, Int32, Int32)

 
 
Modify the ESB Exception Off-ramp in BizTalk to use the WCF-SQL adapter and the new map.
Deploy the BizTalk project with the created usp_insert_Fault schema and map to the Microsoft.Practices.ESB application.
 
Go to the ALL.Exceptions Send Port in the Microsoft.Practices.ESB application. Right-click the send port and choose: Properties...
 
Change the transport Type to WCF-SQL and click on Configure..
WcfSendPort_Properties
 
Change the URI to the SQL Azure database and set the Action.
In the binding page set UseAmbientTransaction = false.
On the Credentials tab set the username and Password.
Click on OK.
WCF-SQL_Properties
 
In the Send Pipeline properties set the new map
Also disable the ESB BAM tracker *(see Conclusion)
Click on OK.
ConfigurePipeline
 
Now the ESB Exception Off-ramp is configured to send messages to SQL Azure!
ExceptionSendPorts
 

 

Test

Enable routing of failed messages on the Send Ports in a BizTalk application to capture BizTalk errors.

View the BizTalk errors in the SQL Server Management Studio
FaultTable
 

 

Conclusion

It is possible to modify the ESB Exception Off-ramp and send error messages from BizTalk to SQL Azure. However in this example I’m only using the main Fault table. I didn't try it with multiple tables but it should also be possible. Furthermore I disabled BAM in the ESB Exception Off-ramp. That's also a nice challenge to get it working.
In the next post I will try to create a portal or a website so that a user can view the faults without de need of SQL Server Management Studio.

You can download the sample schema and map here: http://www.ithero.nl/downloads/itHero.ESB.ExceptionHandling.zip
 

Stay tuned!! Winking smile

About the author

Tomasso Groenendijk lives in Netherlands and is a senior integration consultant at Motion10. He has over 15 years’ experience in software development and software design. Tomasso is specialized in application integration with a strong interest in the Windows Azure cloud platform. In 2014, 2015 and 2016 Tomasso was awarded with the Microsoft Azure MVP award. He is an active contributor to the integration community through his work on his blog, MSDN samples, TechNet Wiki and also speaking on events.

 
 
 
MVP profile Tomasso Groenendijk
 
 
Sentinet Product Specialist
 
 
BizTalk360 Product Specialist

Month List