February 3, 2011 /Comments Off on Millions of records in the BAMAlertsApplication and how to get rid of them (NSVacuum to the rescue)
As a BizTalk consultant I always implement BAM to do basic auditing. This is besides the BAM a business analist would want to see. The basic functionality of this audit trial is:
When was the message received
Where did it came from
Where did it go
What happend to the message
Any important business decision made in an orchestration
I write this audit data into a BAM view, and it has proven to be valuable information. From time to time you will get questions regarding messages and it’s always nice to have this information. Besides that you can set nice alerts ( if you use them) on specific events and have people mailed in case things go wrong. So this is nice and i wouldn’t want to do an implementation without it.
But there is a downside to it. All these audit records also write some data in a not very well documented database BAMAlertsApplication. And over time there could be millions of rows in them. Below is a sample of the BAMAlertsApplication
The records are just piling up and are consuming more and more resources from your SQL server. I had noticed this behaviour before and posted a question about it on the MDSN forums see “How to clean up the BAMAlertsApplication database.” And the answer of a MS Employee was to open a case by Microsoft. Yesterday the BizTalk Administrator of the customer that I work for, asked me if there was anything I could do about the size of this database that just kept on growing and growing. The administrator even pointing me to my own discussion on MSDN and stated that he wanted to indeed start a case with MS.
This triggered me to have a look at the database and by looking at the stored procedures ( I was looking for remove/archive/delete stored procedures) I noticed the NSVacuum stored procedure. This triggered me to see what it did. So I turned to the almighty google to see what it knew about NSVacuum. I got only 2 results !.The first post wasn’t really encouraging, since it increased the databases
But looking at the code of the stored procedure I was convinced that it did some cleaning. So I dropped the “BizTalk” keyword from the search to have another look…..
This was not too encouraging either, only three real results this time. But fortunately the first one pointed me to a Microsoft document with usefull information. It turned out that NSVaccum was exactly what we needed. And after some runs the databas has now shrunk to a more reasonable size. See the picture below.
So, the takaway is….., do NOT forget to schedule NSVacuum if you are using BAM !….
I really hope this is usefull for other people in the future, if it is, please leave reaction on my blog, it will keep me motivated to share my BizTalk experiences with the community !
July 20, 2009 /Comments Off on Custom BAM and making it really work
I have a solution where I use BAM to build a complete audit trail of a message flow.
Custom components write a message to a predefined BAM view whenever you want it to. Normal points where you would use this functionality is in receive & send pipelines. And if an important business decision was made you could write that to the audit trail as well. Below is an example of how to do this in an expression shape inside an orchestration.
AuditData(XlangMessage,”The order was rejected because of <Some reason>”,bool includeMessage)
By doing it like this you can have a very nice Audit trail for every BizTalk solution without doing too much for it. I was very happy with the Audit trail created by the applications that used the custom BAM solution. However a person dealing mainly with functional support would want some added functionality. The wish to add functionality to an entry in BAM stayed in the back of my head and after a while I read a very good article by Gregory van de Wiele and thought it would be nice to indeed have links that really worked.
After applying his tips I was able to add some extra functionality to the BAM page. Below is an example of this.
View Audit trail in Audit portal is a hyperlink that will jump to a website that is designed to show an audit trail of a message.
View Errors in FaultPortal (If any) will show all the errors that have occurred.
View entire message will use Gregory’s tip and show you the entire message. (the field longreferencedata contains the original message) Below is an example of this.
So now i was able to give the end user some useful information. He could see the entire audit trail in the Audit Portal (which is just another faster view on the same BAM table) , See messages in the FaultPortal (if there were any) and he could see the entire message. But what if the end user had a question about this record. There is a nice button on the bottom of this screen ‘Request technical assistance’. as seen in the screenshot below.
I clicked this button to see what would happen, but the only thing I could see was the following message.
BAM would give me only a message stating that some of the reference data was not found. What kind of reference data I was wondering. So I did the same as Gregory, and did some reflection on BAM. And then it became clear to me. Since the entry in BAM was not put there via a regular tracking profile, some data needed there was not present. After some sniffing around with Reflector i could see that the ‘Technical Assistance’ button was relying on ‘BizTalkService’ and ‘MessageId’ (all case sensitive). So I used Gregory’s post to fill those as well.
After these adaptations the entry in BAM has gained in functionality.
Show complete Audit trail
Show errors if there are any
Show entire message
Call for technical assistance
So a person who is dealing with mainly functional issues could see all the date he needs to see. And if he thinks a problem has a technical background, he could call the guys who are responsible for the technical support like this.
This will result in a message in the eventlog like this :
If the people who are responsible for technically monitoring BizTalk have SCOM setup properly they could easily have an alert raised for these kind of messages and respond to them. So you will have a link from functional support to technical support without doing anything for it.