Posts tonen met het label Biztalk. Alle posts tonen
Posts tonen met het label Biztalk. Alle posts tonen

donderdag 3 oktober 2013

SQL query: hourly processed messages for BizTalk ports

One of my clients wanted to know how many messages were processed on a certain BizTalk port.
To show some statistics, I created a SQL query that lists a message count of the messages processed per hour for a certain Port.

Here's the SQL query I used:
use BizTalkDTAdb

SELECT top 200
      a.[Event/Direction],
      a.[Event/Port],
      CONVERT(VARCHAR(10), a.[Event/Timestamp], 111)   as [date],
      DATEPART(HOUR,a.[Event/Timestamp]) as [Hour],
      COUNT(*) as [Count]  
FROM [dbo].[dtav_MessageFacts] a
inner join dbo.btsv_Tracking_Parts b on a.[MessageInstance/InstanceID] = b.uidMessageID
WHERE a.[Event/Port] <> 'PortSqlMonitor'
AND a.[Event/Port] = 'BizTalkPort'
AND a.[Event/Direction] = 'Receive'
AND a.[Event/Timestamp] > DATEADD(day, -3,GETDATE())
GROUP by  a.[Event/Port], CONVERT(VARCHAR(10), a.[Event/Timestamp], 111), DATEPART(HOUR,a.[Event/Timestamp]), a.[Event/Direction]
ORDER BY CONVERT(VARCHAR(10), a.[Event/Timestamp], 111), DATEPART(HOUR,a.[Event/Timestamp])


dinsdag 17 september 2013

Using Fiddler for viewing exchanged messages with BizTalk

I was trying to view the SOAP messages and headers that were actually exchanged with a certain webservice.
To do so, I used Fiddler2... available from http://fiddler2.com/.

Fiddler2 will allow you to monitor traffic when using HTTP, SOAP or WCF ports in BizTalk. However, Fiddler will not trace any messages sent to endpoints by BizTalk by default as it does not use WinInet. To overcome this issue, you'll need to configure a proxy to allow Fiddler to intercept the messages.
Note that these proxy settings will need to be removed when Fiddler is not running. Because otherwise all traffic through this proxy will not be received by anything.

In the send port of BizTalk go to the Configuration settings. Open the tab 'Proxy' to configure the necessary settings. Now you should change following settings:
  • Server: 127.0.0.1
  • Port: 8888

That's is all you need to do. Now open fiddler2 and process a message. If all is configured correctly, you should see the exchanged messages coming through fiddler.


vrijdag 19 juli 2013

BTUG.be is born

I would like to make a small announcement... today we publicly announced the birth of a brand new Belgian BizTalk User Group, called BTUG.be! We were planning something like this for quite some time now, and you even might have picked up some rumours about it in the past. But as of today it is official, Belgium now has its very own BizTalk User Group.



I'm proud to say that I'm one of the co-founders of this BTUG.be. Together with Sam Vanhoutte we have started this initiative under excellent guidance of Lex Hegt, who shared his knowledge he gained with the Dutch BizTalk User Group.
And to start out, we wanted to make it a joined collaboration of the Belgian BizTalk Community. In this spirit we are glad that also i8c decided to support the initiative and Sven Van den brande agreed to fulfill our board of the BTUG.be. So that makes the all new Belgian BizTalk User Group will be worked out as a joined effort by Cnext, Codit and i8c with in the board at startup myself, Sam Vanhoutte and Sven Van den brande.
I'm already looking forward on having a great time working together with these guys organising all upcoming events.

A very first launch event is being planned to have place in november. For keeping up to date on all matters of this BTUG.be, I'll recommend you subscribe to the mailing list on the website http://www.btug.be and even keep a close eye to the website for all announcements. And certainly don't forget to follow our twitter account @BTUGbe.

And last of all, I would like to call out for sponsors... As the entire organization will be driven on volunteer work. So to cover all the necessary costs we will need to make for hosting events, catering, travel expenses in case of international speakers, etc. we are on the lookout for sponsors to keep it fun and free for everyone. You can always contact us on any questions or to share your interest for sponsoring using this entry form on the website.

maandag 27 mei 2013

BizTalk blog posts 02/2013 - 05/2013

The past time I haven't posted a lot on this blog, especially not much blog posts on BizTalk subjects. This doesn't mean that I haven't done any BizTalk related blog posts, I just haven't done it on my personal blog for a while... I've mostly posted on my companies blog, where I am the sole contributor and administrator.

But for all of you loyal readers of my personal blog, I thought it would be nice to give an overview of all of my latest posts. I'm thinking of doing this every once in a while to keep all of you updated on ALL of my posts.

So here goes for the period till May 2013...
Hope you enjoy these blog posts!

donderdag 11 april 2013

Guide to a BizTalk High Availability Server Environment

I just discovered a series of blog post that I just had to share with all of you... It's a complete guide to set up a High available BizTalk environment.
A perfect walthrough from start to finish to obtain a BizTalk High availability environment.

The guide covers 7 blog posts in total:


Congrats to René Brauwers on the great guide for setting up a High available BizTalk environment.

CU1 for BizTalk 2013... using Microsoft Update!

The Microsoft Team has just announced the release of the very first Cumulative Update For BizTalk 2013.

But an even more surprising news is that it is available using Microsoft Update!... This is the very first time a BizTalk Update can be obtained with Microsoft Update, as before it had to be done by downloading it manually through the hotfix download. An amazing improvement if you ask me, making it a whole lot easier for administrators.

The update will still be available through the hotfix download though.


The Content of the Cumulative Update


The very first CU for BizTalk 2013 brings 3 fixes:
  • User cannot perform certain database-related operations in BizTalk Server 2013
  • BAM tools cannot be configured in a multi-node BizTalk Server 2013 environment
  • The vertical scroll bar on the target schema does not work correctly when you use Visual Studio to design a BizTalk Server 2013 map


How to install using Microsoft Update


The following steps are applicable if you have Microsoft Update enabled in the machine where BizTalk Server is installed.
  1. Log into a server where BizTalk Server is already installed, and Microsoft Update is enabled
  2. Check for windows update
  3. Notice available fixes for BizTalk Server under optional updates
  4. Select the update and install
(Perform the above operations in each of the BizTalk nodes)

The original announcement can be found here.

donderdag 21 maart 2013

Microsoft BizTalk Server 2013 available on MSDN

It took a while, but here it is... BizTalk Server 2013 is now in RTM (Manufacturing)! as of now you can download BizTalk 2013 from MSDN, and it will be available for purchase in April.

Besides all kinds of new features, the new version of BizTalk also brings us a whole new licensing model. At least it is new in terms of BizTalk pricings... it will be a per-core licensing model, just like the SQL Server licensing.

A more detailed info can be found on my companies blog post.
the official announcement of the BizTalk team, can be found in this post on their blog.

woensdag 27 februari 2013

key file authentication with the BizTalk SFTP adapter

Authentication on an SFTP location can be done with simple username/password credentials… However another way to authenticate the sender is by using a Key Authentication file. But how can this last authentication method be used inside the BizTalk SFTP adapter?

the BizTalk SFTP adapter we used to achieve this is the  freely available SFTP adapter on Codeplex. Just download the adapter and install it. And don’t forget to add the adapter in the BizTalk admin console of course.

Now then let’s set up the port in BizTalk. In my example I ‘ve configured a receive location, but obviously the same goes for the send port configuration.

First of all choose the created adapter in the biztalk admin console, as shown below.

Port configuration

To set up the proper configuration for the SFTP port, just press the ‘Configure’-button.

Configure

These parameters need to be set:
  • Schedule: define a timely schedule how often you want to poll the SFTP location (receive location only)
  • File Mask: set the correct file mask
  • SSH Host: the host address of the SFTP location
  • SSH Identyfile: Select the Key authentication file on disk
  • SSH Identyfile Passphrase: the password of the selected key authentication file
  • SSH Remote Path: the path on on the SFTP location
  • SSH User: the user name to authenticate on the SFTP location
Seems pretty straight forward, doesn’t it?
Well there are some things you ‘ll need to take into account to make sure the connection can be made…

Make sure the identyfile type is supported

You’ll need to make sure the identyfile type is supported by the library. The supported versions can be found here. As this is the library which is used by the bizTalk SFTP adapter.

Make sure the identyfile can be recognized

In my case for example, I got an *.ppk file as authentication file. But as it turned out, this wasn’t recognized.
The error message you git is this:
invalid privatekey: D:\tempkey.ppk

Which in the end seemed a bit misleading, as the problem wasn’t the private key for the authentication file. It was just the *.ppk file that wasn’t supported.
To make the key authentication file working, I had to convert the file to an OpenSSH key file. This can be done by using PuttyGen for example, and exporting the authentication file as an OpenSSH key file.

Don’t use both password parameters

Also make sure you only use the intended parameter SSH Identyfile Passphrase, and leave the SSH password blank.
In case both password fields are filled, the SFTP adapter will try to authenticate by the username/password credentials stated. So it won’t offer the specified identyfile to authenticate.




I orignally posted this on my companies blog: http://blog.cnext.eu/2013/02/19/using-key-file-authentication-with-the-biztalk-sftp-adapter/

dinsdag 18 december 2012

BizTalk deployment - How do YOU do it?



For the deployment of BizTalk applications we use a custom made tool within my company Cnext. Now I was curious to have some feedback from the community on the deployment of BizTalk applications. How do you handle the biztalk deployments at your company?

Our deployment manager tool is based on the idea to store all used objects in a database model (BizTalk artifacts, but also stuff like MSMQ, file locations, SQL objects, etc. …). Most of these objects are added using auto discovery of the BizTalk databases, so manually adding of objects is reduced to a minimum. Above all, BizTalk artifacts can have a different configuration (binding) defined per environment (test, dev, prod, …).
It also allows you to define all dependencies. Again most of these dependencies will be defined by the tool automatically.

This way of working makes it possible for the tool to define which actions need to be taken to deploy a certain application (or just a part of the application or only some objects). The deployment manager tool will define which objects need to be removed and redeployed (also unenlisting/disabling en starting/enabling artifacts will be done by itself). As a result, using the tool will allow us to deploy much faster, because the objects to redeploy are reduced to an absolute minimum and no complete redeploy is needed (like BizTalk Deployment framework does for example).

Another very useful and much used functionality is the possibility to define complete business/functional flows, including some generic components. This makes sure you can also deploy or redeploy a complete (new) flow (like an order flow for example) by itself, including all necessary objects (as well receive as send ports, file locations, etc. …).

The deployment can be done cross BizTalk applications. So the separation in applications is no longer deployment dependent.

The most important part is to set the database model correct and keep it this way… this will guarantee a much easier and faster deployment, where each environment has its own version of the current deployed objects.

Please answer following questions:

  1.      Is your BizTalk deployment automated (BizTalk deployment framework, custom scripting with MSBuild or BTSTask, powershell scripts, etc. …), or do you just do manual deployment using MSI and binding files?
  2.        Which deployment tools or scripts are you using, or have you used before? And what are your thoughts of these tools (benefits and complaints)?
  3.        What do you think of a tool like our custom deployment manager tool (using autodiscovery, etc. …)?


Thanks for your replies.
 When there is enough feedback, I'll devote a new post on my analysis.



woensdag 14 november 2012

Blog admin of my companies blog

First of all, I would like to thank all of my regular reader and hope you've al enjoyed my blog posts for almost 2 years now. I already announced in one of my previous posts some time ago that I would join the contributors of the biztalk admin blog at http://www.biztalkadminsblogging.com/ .

Today, I'm proud to announce that there is another brand new blog I will be contributing to on BizTalk and Azure related news, problem solvement, etc.... My companies (Cnext) blog (http://blog.cnext.eu) . Well actually, the blog already existed quite some time (even longer than my own blog :) ), but there wasn't any new posts for over 2 years. Most recently I became the new administrator of the Cnext blog and had the task to revive it. After 1 month, 3 completely new posts, and still some blog posts in the making, it is ready to be shared with all of you... see for yourself at the Cnexts revived blog.

The first thing I did is restyle the entire blog to match the current branding of Cnext. And after publishing some new material, I can say that it is ready to be read!

Now go and check it out.. and keep following all of my blog posts.




More info on cnext can be found on http://www.cnext.eu. And as mentioned the blog will be situated at http://blog.cnext.eu... keep an eye out for all new material to be added soon!

woensdag 7 november 2012

Biztalk 2013 Beta released

Microsoft finally released a beta version of the new biztalk, wich can be found here.
The first thing that should be noticed is that Microsoft decided to change the next versions name from Biztalk 2010R2 to Biztalk 2013. This has some very important consequences… It indicates the next version to be a major release, which implies a much longer support from Microsoft.
Microsoft’s support lifecycle policy says that products will have 5+5 years (mainstream+extended) support. However, that applies to major versions. If the product would still be BizTalk Server 2010 R2, it will not get an extended support lifecycle end date.

So in conclusion, having the next Biztalk version being a full release is an important sign to the market, it implies a much longer support lifecycle for the product.


Biztalk 2013 will have following new features:

  • Integration with Cloud Services: messages using different relay endpoints hosted on Azure.

  • RESTful services: provides adapters to invoke REST endpoints as well as expose BizTalk Server artifacts as a RESTful service.

  • Enhanced SharePoint adapter: the need for dependency on SharePoint farms has been removed, while still providing backward compatibility.

  • SFTP adapter: added an SFTP adapter that will enable sending and receiving messages from an SFTP server.

  • ESB Toolkit integration: ESB Toolkit is now fully integrated with BizTalk Server. Also, the ESB Toolkit configuration experience is vastly simplified to enable a quick setup.

  • Dependency tracking: dependencies between artifacts can now be viewed and navigated in Admin console.

  • Improvements in dynamic send ports: the ability to set host handler per adapter, instead of always using the default send handler of the adapters.

woensdag 24 oktober 2012

Exception handling in BizTalk orchestrations explained

When creating BizTalk orchestrations, we also have some building blocks available to do exception handling. In this blog I'll try to give a very brief explanation of the available building blocks for your orchestration. I won't describe everything in detail, because this would lead us to far (and would give one massive blog post).

For exception handling in orchestration there are 3 shapes available:
  • Throw Exception
  • Scope
  • Compensate

I want go in much details for the Scope and Compensate shapes. If you need more detailed description on how Exception handling can be used with these 2 shapes I would suggest reading this blog from Richard Seroter. And if this still doens't answers all of your questions, let me know... than I can devote another post with a more detailed explanation on some aspects of the exception handling in orchestrations.

Throw Exception

Firstly we have the 'Throw Exception'-shape. this one is the simplest of all shapes for exception handling.
Frankly, it does nothing more than it's name suggests... It just throws an Exception, nothing more fancy. To achieve this you'll need to follow these three simple steps:
  1. Define a variable with the type set to any Exception class you prefer. In below example I've created an exception variable of type Microsoft.Biztalk.Message.Interop.BTSException


  2. Note: The Report To Analyst property in the screenshot exists on many shapes. The property however has no influence on the behavior of your orchestration. I merely is a property used to integrate withe the Orchestration Designer for Business Analysts (ODBA) Visio plugin. If set to false, that shape will be considered as a low level detail that won't be visible in the Visio Designer.

  3. Instantiate the variable and set any values according to the occured error
  4. Now use the Throw Exception shape and configure it ro use the Exception object you've created



Scope

Scopes are used for three reasons
  1. to configure transaction (long running or atomic)
  2. to handle exceptions
  3. to trigger compensating logic
For more info on the transaction types, I'll refer to this blog from Sandro Pereira.

A scope shape can have one or more Exception handling blocks and/or one Compensation Block. The Transaction type you've chosen for your scope will define which of these blocks can be added. An atomic scope can only have a compensation block, and a scope configured with the transaction type non can only have an Exception block. While a long running scope can have both blocks.
For an overview of the possibilities, check the below screenshot.

Note that there could be more than one Exception block, but only one Compensation block added to a Scope

Exception handling block

The Exception handling block is used to catch exceptions that occur inside the scope the block belongs to. The Exception Object Type in the properties of the exception block will define which Exceptions types will be handled by this Exception block.


When an exception occurs, the engine will check for the closest exception block that can handle this type of exception. The consideration of which exception block will be called is determined sequentially, just like in .NET. If no suitable exception handler is found, the default exception handles is triggered, and this means that the compensation blocks for all nested scopes will be called, after which the exception is rethrowed and the orchestration will become suspended.

Compensation block

Compensation is a way to correct or undo logical piece of work that have previously committed. An exception can occur in the orchestration afther succesfully executing all logic in the scope. In this case, the process might be in a state where it is logically incorrect. So the action already performed in a transactional scope need to be compensated... this can be achieved using a compensation block
If no compensation block is added, the default compensation will be performed. this means calling all compensation blocks of the nested scopes, starting with the most recently completed ones, just as with the default exception handler.

Compensate

The compensate shape can only be used inside of an exception handler. The explicit use of this compensate shape, allows us to specify the order of compensation execution ourselfs. So this can be used in cases the default order (starting from te most recently completed ones and working its way back) doesn't fit.
1 compensate shape can only trigger 1 compensation block of a transaction. So the order must be defined using multiple compensate shapes, calling the compensation blocks in the desired order.



Remarks

  • Keep in mind that the default Exception handler, if triggered, will initiate the compensation by calling all compensation blocks for the nested scopes. Compensation blocks of the nested scopes will be called starting with the most recently completed scope and work its way back.

    But whenever you catch an exception using the Exception handler block, then you must explicitly do the compensation.


  • If no custom compensation block has been added, the default compensation will be performed. Just like with the exception handler, this will call the compensation blocks of any nested scopes starting with the most recently completed scope.


  • A Compensate shape can only be used inside an Exception handling block or another Comensation Block. When calling the compensate of the current scope, the default compensatino handler will be triggered instead of the compensation code defined by the compensation block.

dinsdag 9 oktober 2012

BizTalk: using macros in file names

BizTalk allows you to use several macros for defining a file name in a biztalk send port.

The following table lists the supported macros and describes how the File send handler replaces them.

Macro name Substitute value
%datetime%Coordinated Universal Time (UTC) date time in the format YYYY-MM-DDThhmmss (for example, 1997-07-12T103508).
%datetime_bts2000%UTC date time in the format YYYYMMDDhhmmsss, where sss means seconds and milliseconds (for example, 199707121035234 means 1997/07/12, 10:35:23 and 400 milliseconds).
%datetime.tz%Local date time plus time zone from GMT in the format YYYY-MM-DDThhmmssTZD, (for example, 1997-07-12T103508+800).
%DestinationParty%Name of the destination party. The value comes from the message context property BTS.DestinationParty.
%DestinationPartyQualifier%Qualifier of the destination party. The value comes from the message context property BTS.DestinationPartyQualifier.
%MessageID%Globally unique identifier (GUID) of the message in BizTalk Server. The value comes directly from the message context property BTS.MessageID.
%SourceFileName%Name of the file from which the File adapter read the message. The file name includes the extension and excludes the file path, for example, Sample.xml. When substituting this property, the File adapter extracts the file name from the absolute file path stored in the FILE.ReceivedFileName context property. If the context property does not have a value—for example, if a message was received on an adapter other than the File adapter—the macro will not be substituted and will remain in the file name as is (for example, C:\Drop\%SourceFileName%).
Aa578022.note(en-us,BTS.20).gifNote
Correct implementation of this macro requires that the output message is the same message as the received message.
%SourceParty%Name of the source party from which the File adapter received the message.
Aa578022.note(en-us,BTS.20).gifNote
Correct implementation of this macro requires that the output message is the same message as the received message.
%SourcePartyQualifier%Qualifier of the source party from which the File adapter received the message.
Aa578022.note(en-us,BTS.20).gifNote
Correct implementation of this macro requires that the output message is the same message as the received message.
%time%UTC time in the format hhmmss.
%time.tz%Local time plus time zone from GMT in the format hhmmssTZD (for example, 124525+530).

NOTE

The File send handler does not replace the macros with a value if any of the following are true:
  • The corresponding system property is not set.
  • The macro is misspelled.
  • The value for the macro contains symbols that are not valid in the file name.

Using Macros in SMTP Headers

There are however some restrictions when using the macros in SMTP headers.
See this post for more details on this matter.

Below is a short overview of the use of macros in SMTP headers.

Macro Description For use with To For use with CC For use with Subject
%MessageID%Globally unique identifier (GUID) of the message in BizTalk Server. The value comes from the message context property BTS.MessageID.NoNoYes
%datetime_bts2000%UTC date time in the format YYYYMMDDhhmmsss, where sss means seconds and milliseconds (for example, 199707121035234 means 1997/07/12, 10:35:23 and 400 milliseconds).NoNoYes
%datetime%UTC date time in the format YYYY-MM-DDThhmmss (for example, 1997-07-12T103508).NoNoYes
%datetime.tz%Local date time plus time zone from GMT in the format YYYY-MM-DDThhmmssTZD, (for example, 1997-07-12T103508+800).NoNoYes
%time%UTC time in the format hhmmss.NoNoYes
%time.tz%Local time plus time zone from GMT in the format hhmmssTZD (for example, 124525+530).NoNoYes
%SourceParty%Name of the source party from which the File adapter received the message.NoNoYes
%SourcePartyQualifier%Qualifier of the source party from which the File adapter received the message.NoNoYes
%DestinationParty%Name of the destination party. The value comes from the message context property BTS.DestinationParty.YesYesYes
%DestinationPartyQualifier%Qualifier of the destination party. The value comes from the message context property BTS.DestinationPartyQualifier.NoNoYes


Source: http://msdn.microsoft.com/en-us/library/aa578022(v=bts.20).aspx

woensdag 5 september 2012

Delete BizTalk backup files

The SQL Server agent job "Backup BizTalk Server" will not delete the generated backup files automatically. The job does clear the backup history table in the database, but it will never delete the backup files from the disk. Which will of course result in the disk to fill up eventually and the backup job will fail from then on.

Here's a simple stored procedure to call from the "Backup BizTalk Server" job in the "Clear Backup History" step. Just follow these steps:
  1. Open SQL Server management Studio
  2. Open a new query window and connect to the BizTalkMgmtDb database
  3. Execute this script to add a new stored procedure called sp_DeleteBackupHistoryAndFiles
    USE [BizTalkMgmtDb]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[sp_DeleteBackupHistoryAndFiles] @DaysToKeep smallint = null, @UseLocalTime bit = 0
    
    AS
     BEGIN
    
     set nocount on
    
      IF @DaysToKeep IS NULL OR @DaysToKeep <= 0
      RETURN
    
         /*
            Only delete full sets
            If a set spans a day such that some items fall into the deleted group and the other don't don't delete the set
    
            Delete history only if history of full Backup exists at a later point of time
            why: history of full backup is used in sp_BackupAllFull_Schedule to check if full backup of databases is required or not.
            If history of full backup is not present, job will take a full backup irrespective of other options (frequency, Backup hour)
        
        */
            
      declare @PurgeDateTime datetime
    
      if (@UseLocalTime = 0)
        set @PurgeDateTime = DATEADD(dd, -@DaysToKeep, GETUTCDATE())
      else
        set @PurgeDateTime = DATEADD(dd, -@DaysToKeep, GETDATE())
    
      DECLARE DeleteBackupFiles CURSOR
    
      FOR SELECT 'del "' + [BackupFileLocation] +  case right(BackupFileLocation,1) when '\' then '' else '\' end + [BackupFileName] + '"' FROM [adm_BackupHistory] [h1]
        WHERE     [BackupDateTime] < @PurgeDateTime
        AND    [BackupSetId] NOT IN ( SELECT [BackupSetId] FROM [dbo].[adm_BackupHistory] [h2] WHERE [h2].[BackupSetId] = [h1].[BackupSetId] AND [h2].[BackupDateTime] >= @PurgeDateTime)
        AND EXISTS( SELECT TOP 1 1 FROM [dbo].[adm_BackupHistory] [h2] WHERE [h2].[BackupSetId] > [h1].[BackupSetId] AND [h2].[BackupType] = 'db')
    
      DECLARE @cmd varchar(400)
    
      OPEN DeleteBackupFiles
    
       FETCH NEXT FROM DeleteBackupFiles INTO @cmd
    
      WHILE (@@fetch_status <> -1)
      BEGIN
                IF (@@fetch_status <> -2)
                BEGIN
    
                            EXEC master.dbo.xp_cmdshell @cmd, NO_OUTPUT
    
                            delete from [adm_BackupHistory] WHERE CURRENT OF DeleteBackupFiles
    
                            print @cmd
                END
    
                FETCH NEXT FROM DeleteBackupFiles INTO @cmd
      END
    
      CLOSE DeleteBackupFiles
    
      DEALLOCATE DeleteBackupFiles
    
      END
    
  4. 5.Modify the "Clear Backup History" step of the Backup BizTalk Server job to call sp_DeleteBackupHistoryAndFiles, instead of calling the stored procedure sp_DeleteBackupHistory
  5. Make sure xp_cmdshell for the SQL Server instance is enabled. this will be disabled by default. To enable this, execute following SQL script:
    EXEC master.dbo.sp_configure 'show advanced options', 1
    
    RECONFIGURE
    
    EXEC master.dbo.sp_configure 'xp_cmdshell', 1
    
    RECONFIGURE
    
    


This stored procedure is made for BizTalk Server 2010.
Because with the release of BizTalk 2010 there have been changes to the sp_DeleteBackupHistory stored procedures. They added a parameter to the stored procedure to use local time and changed the query to prevent the deletion of the history from the last full backup set forward.

For older versions of BizTalk you should use this script instead:
CREATE PROCEDURE [dbo].[sp_DeleteBackupHistoryAndFiles] @DaysToKeep smallint = null
AS
BEGIN
set nocount on
  IF @DaysToKeep IS NULL OR @DaysToKeep <= 0
  RETURN
/*
  Only delete full sets
  If a set spans a day such that some items fall into the deleted group and the other doesn't, do not delete the set
*/ 

DECLARE DeleteBackupFiles CURSOR
FOR SELECT 'del "' + [BackupFileLocation] + '\' + [BackupFileName] + '"' FROM [adm_BackupHistory]
WHERE  datediff( dd, [BackupDateTime], getdate() ) >= @DaysToKeep
AND [BackupSetId] NOT IN ( SELECT [BackupSetId] FROM [dbo].[adm_BackupHistory] [h2] WHERE [h2].[BackupSetId] = [BackupSetId] AND datediff( dd, [h2].[BackupDateTime], getdate() ) < @DaysToKeep )
DECLARE @cmd varchar(400)
OPEN DeleteBackupFiles
FETCH NEXT FROM DeleteBackupFiles INTO @cmd
WHILE (@@fetch_status <> -1)
BEGIN 

            IF (@@fetch_status <> -2)
            BEGIN
                        EXEC master.dbo.xp_cmdshell @cmd, NO_OUTPUT
                        delete from [adm_BackupHistory] WHERE CURRENT OF DeleteBackupFiles
                        print @cmd
            END 

            FETCH NEXT FROM DeleteBackupFiles INTO @cmd
END 

CLOSE DeleteBackupFiles
DEALLOCATE DeleteBackupFiles
  END
GO



Source: http://www.biztalkbill.com/Home/tabid/40/EntryId/81/Update-to-Stored-Procedure-to-delete-Backup-BizTalk-Server-SQL-Agent-backup-files.aspx

dinsdag 17 juli 2012

BizTalk Server Performance

Have you ever wanted to evaluate the performance of your Biztalk installation?
Evaluating your performance could be done by analyzing quite some counters in the performance monitor of Windows. Or maybe by using the Performance Analysis of Logs (PAL) Tool.

However I just found a recently released tool to benchmark your biztalk environments.
The tool is called blogical and can be found on codeplexe here.

How it works:
  1. After the user has started the application and specified the BizTalk Group, the tool analyzes its configuration, finding all the BizTalk servers, Messageboxes etc.
  2. Secondly, the user gets to select one of two scenarios: Messaging or Orchestration. Each scenario has a set of tested environments such as
    • “Single server (2*Quad CPU, 4GB RAM)”
    • “1*BTS (1*Quad CPU. 4GB RAM) + 1*SQL(1*Quad CPU, 8GB RAM)”.
    • “2*BTS (2*Quad CPU. 8GB RAM) + 2*SQL(2*Quad CPU, 16GB RAM)”.
  3. The user selects the environment which most resembles his/her own.
  4. The user then starts the Indigo Service, a console application hosting a service which will be called from the BizTalk Send port.
  5. As the user clicks “Run test”, the tool continues to start ports and orchestrations. It will also start the Perfmon collector sets if the user has chosen to create those.
  6. As the test proceeds the user can monitor the counter values through the gauges (CPU utilization, Received msgs/sec and Processed msgs/sec). The default test duration is 30 minutes, with a warm-up of 2 minutes.
  7. Finally, the user is presented a result, which is either Succeeded or Failed.

For more information:
Benchmark your BizTalk Server (Part 1)

How to install:
Benchmark your BizTalk Server (Part 2)

BBW Drill Down:
Benchmark your BizTalk Server (Part 3)

High Scores:
http://blogical.se/bbw


Another thing I wanted to share with you regarding performance of Biztalk installations is the Microsoft BizTalk Server 2009 Performance Optimization Guide .
This guide provides in-depth information for optimizing the performance of a BizTalk Server solution.

Also check BizTalk Health Check post on Technet Wiki. It's a list of checkpoints that should be covered when performing a health check

vrijdag 13 juli 2012

Promote using Correlation initialisation inside a loop

I assume you already know how to promote properties when sending a message from an orchestration to the messagebox.
This can of course be achieved by initializing a correlation set with containing all context properties you like to promote. But that 's not why I'm posting this blog. If you need more info on how to promote properties from an orchestration I'll refer you to this blog.

Problem

The problem is when you try to do this from a send shape inside a loop, you'll get an error when building the project in visual studio.
The received error is "Correlation set may be initialized only once".

Solution

The solution to this issue is actually real easy. All you have to do is create a scope inside the loop. And place the send shape (and even other shapes if needed) in the created orchestration. The benefit of the orchestration is that you can define the correlation set on the scope itself (instead of as a global correlation set).
Now can initialize your correlation set on your send shape inside the scope.

And that's all you'll have to do!! It really is that easy! :)

donderdag 12 juli 2012

Passed the Microsoft BizTalk Exam

As of yesterday I can call myself a Microsoft Certified Technology Specialist (MCTS): Microsoft BizTalk Server 2010.

I passed the exam 70-595: Developing Business Process and Integration Solutions by Using Microsoft BizTalk Server 2010. I did this making only a single mistake, not bad at all :).

All I did as preparation was reading the book (MCTS): Microsoft BizTalk Server 2010 (70-595) Certification Guide once. This together with the practical knowledge I had gathered on the job made sure I passed the exam easily.

So all credits go to the authors of the book (Johan Hedberg, Kent Weare , Morten la Cour). They definitely achieved their goal. Also the sample questions (and their answers) were very helpful in the preparation.

woensdag 13 juni 2012

Identify Performance Bottlenecks in BizTalk

I found a series of blogs on how to identify Performance bottlenecks in BizTalk Environments.
Also look at this article on finding and eliminating bottlenecks

Here's an short overview of all the steps, including a link to the more detailed explanation:

Step 1: Monitoring BizTalk Host Instances via Windows Performance Counters


 The first place to measure the BizTalk performance can be done using Performance Counters. These counters let you monitor all components like the message box, orchestrations, adapters, etc… .

Step 2: Analyzing BizTalk Adapters


A deeper look at the adapter performances, again using some performance counters.

Step 3: Analyze Pipelines


It is important to understand the impact of Pipeplines that are used. Because each of the pipelines have a significant impact on the overall BizTalk performance as they perform actions on every single message that gets through BizTalk
One way to analyze what is really going on in pipelines is using an Application Performance Management Solution with Transactional Tracing capabilities, like dynaTrace for example.

Step 4: Analyzing Orchestration


Orchestration can get quite complex sometimes, but how to analyze the performance of these orchestrations?

The actual logic behind the Orchestration Definition is compiled into an assembly, loaded into BizTalk, and gets executed when messages are processed by that Orchestration Definition. A more detailed overview of the execution of such an orchestration can be generated with PurePath for example.

Another way to analyze the orchestration performance can again be done using performance counters.  BizTalk provides a set of counters for the Orchestration Engine as well as for the MessageBox.

Step 5: Analyzing external service calls (SendPorts)


Often BizTalk Orchestration makes a call to an external web service. PurePath can also give you an idea of the time consumed for this external web service call.


FINAL NOTE


As a final note I would like to point out to use the MessageBox Viewer Tool. The tool gives an overview of what might be wrong in your installation.
This tool runs a set of tests and queries against the MessageBox database an creates a nice report. The result can be viewed using the tool or it can generate an HTML report.

dinsdag 12 juni 2012

Best practices on Biztalk Tracking

I announced in a previus post that I joined the blogging team on biztalkadminsblogging.com.
However it took untill today before my first blog post was created.

I gave it a long thought what my first blog post should cover. the result is a post on some best practices for the usage of tracking in BizTalk... hope you like it!

The actual post can be found here http://www.biztalkadminsblogging.com/index.php/item/83-best-practices-for-tracking

maandag 21 mei 2012

Guideline to Solve Performance issues of Biztalk

Typically a performance problem can be narrowed down to one of the following components of a BizTalk Server environment:
·         A receive adapter or the system from which the adapter is receiving documents. For example, if documents are being received by the HTTP adapter at a suboptimal rate then the problem may be with the HTTP receive adapter or with the client that is posting to the HTTP adapter.
·         An orchestration service instance.
·         Performance of the SQL Server that hosts the BizTalk Server databases.
·         A send adapter or the system that the adapter is sending documents to. For example, if documents are being sent by the SQL adapter at a suboptimal rate then the problem may be with the SQL send adapter or with the computer running SQL Server that the SQL adapter is updating.

Use the following guidelines to help identify the components of the BizTalk Server environment that are performing poorly:
·         Capture any warnings or errors generated in the BizTalk Server or SQL Server Event Viewer.
·         Follow the steps in "Identifying Performance Bottlenecks" in BizTalk Server Help at http://go.microsoft.com/fwlink/?LinkId=154238 to help identify performance bottlenecks.
Once the poorly performing component has been identified, follow the appropriate guidelines to help resolve the issue:
Guidelines for resolving performance problems related to send and receive adapters
·         For information about troubleshooting problems with BizTalk Server adapters, see "Troubleshooting BizTalk Server Adapters" in BizTalk Server Help at http://go.microsoft.com/fwlink/?LinkId=154240. This section contains general troubleshooting information including information about how to set up logging for certain adapters, and information that can be used diagnose network problems, problems with MSDTC, problems with the registry, problems with the file system, and problems with IIS.
·         For information about troubleshooting problems with MSDTC, certificates, Enterprise Single Sign-On, and SQL Server, see the appropriate section of "Troubleshooting BizTalk Server Dependencies" in BizTalk Server Help at http://go.microsoft.com/fwlink/?LinkId=154242.
Guidelines for resolving performance problems related to orchestrations
·         For information about modifying the appropriate sections of the BTSNTSvc.exe.config file, see "Orchestration Engine Configuration" in BizTalk Server Help at http://go.microsoft.com/fwlink/?LinkId=154244.
Guidelines for resolving performance problems related to SQL Server
·         SQL Server Profiler can be used to capture Transact-SQL statements that are sent to SQL Server and the SQL Server result sets from these statements. Since BizTalk Server is tightly integrated with SQL Server, the analysis of a SQL Server Profile trace can be a useful tool for analyzing problems that may occur in BizTalk Server when reading from and writing to SQL Server databases. For information about how to use SQL Server Profiler, see "Using SQL Server Profiler" in SQL Server Books Online at http://go.microsoft.com/fwlink/?linkid=104423.
·         SQL Server Management Studio can be used to execute SQL statements directly against SQL Server databases. This functionality may be useful for querying the BizTalk Server databases or for updating the BizTalk Server databases in certain scenarios. For more information about using SQL Server Management Studio to execute SQL statements, see "Writing, Analyzing, and Editing Scripts with SQL Server Management Studio" in SQL Server Books Online at http://go.microsoft.com/fwlink/?linkid=104425.
·         For more information about resolving performance problem related to the BizTalk Server databases, see "Troubleshooting SQL Server" in BizTalk Server Help at http://go.microsoft.com/fwlink/?LinkId=154250.