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])

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.

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.

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

• BizTalk error: ‘The message found multiple request response subscriptions’: solution to sending messages to multiple request-response send ports
• BizTalk file mask difference for windows 7 and windows server 2008 r2: A difference in file mask usage between windows 7 and windows server 2008 r2
• BizTalk360 v6.0: new features explained: a quick view on some of the improvements introduced in the new BizTalk360 version 6.0
• BizTalk Server 2013 is now RTM!: the announcement of the new BizTalk version release on MSDN, with the new features listed
• BTS2013 features explained: XslCompiledTransform: I started a new series on viewing some of the new features of BizTalk 2013 in more detail, the first one was about the XslCompiledTransform
• BTS2013 features explained: Improvements in dynamic send ports: follow up post on a new BizTalk 2013 feature on the Improvements in dynamic send ports
• BTS2013 features explained: Dependency tracking: an in depth view of the new dependency tracking feature of BizTalk 2013
• First Cumulative Update for BizTalk 2013… available through Microsoft Update!: the announcement that the very first cumulative update was released for BizTalk 2013, and that it even was available through Microsoft Update
• BAM error: Failed to list permissions for BAM view – System.Data.SqlTypes.SqlNullValueException: Data is Null: a solution to a BAM issue I encountered

Hope you enjoy these blog posts!

Became an official BizTalk360 Product Specialist

Yesterday, BizTalk360 introduced the whole new title "BizTalk360 Product Specialist" on their blog. Because of the constantly increasing popularity of the monitoring tool BizTalk360, to streamline the process the new title and badge where introduced. The idea is to honour individuals who have gained adequate knowledge in installing, configuring and implementing BizTalk360 solution at customer sites.

The only way of obtaining this title is by receiving the confirmation from the BizTalk360 team itself. I myself filed a request immediately... And I am proud to announce my request got approved. Now I have the honour to officially call myself a "BizTalk360 Product Specialist"!

As you might have seen, I already placed the official badge at my own blog on the column on the right. I also updated my LinkedIn profile accordingly.

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:

• Part 1: Preparations
• Part 2: Domain Controller Installation
• Part 3: SQL & BizTalk Active Directory Accounts
• Part 4: Prepping our SQL & BizTalk Failover Clusters
• Part 5: SQL Server 2008r2 Failover Cluster
• Part 6: BizTalk 2010 Failover Cluster Creation
• Part 7: BizTalk 2010 Installation, Configuration and Clustering

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.

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.

Error on Calendar/Contacts sharing in Outlook

I 've encountered a problem sharing Contacts from within Outlook. We were trying to share a contactgroup where both parties are from the same company, so on the same Office365 license.
However, on trying to send out a share request we stumbled upon following error:

"Problem: Your organization's policy does not allow you to share with this recipient."

At first it seemed that there should be anything wrong with the organizations settings made in office365. But nothing strange was configured there... Setting the permissions to the other mail addres did seem to work. What got me questioning there would be anyhting wrong in the configurational settings.

It turned out there was a difference when trying to share the contact or setting the permission. And after realising this, even sharing the contact worked just fine... without changing any of the configuration settings!

Solution

What turned out to be the problem than?

when the user started typing the recipient name in the "To" box of the "Share invitation" dialog, the recipient was picked up by autocompletion. However, the autocompletion picked up the recipient from the Contacts instead of from the Global Address List. So the name of the recipient appeared as "User Name <user@company.com>", where it had to be "User Name" when you choose from the Global Adress list.

So on sending the email invitation to share the contact list, Microsoft Exchange considered the recipient to be outside the organization. Which resulted in the above error message of course.

Change SQL backup location

During the installation of a SQL instance, you'll have the ability to define some folders
These locations can be defined:

• User database directory
• User database lof directory
• Temp DB directory
• Temp DB log directory
• Backup directory



Within SQL Server Management Studio you have the ability to change the default location for your Data and Log files for all new databases. Just  right click on the server name and select properties, navigate to the Database Settings page. Here you can find a section Database default locations for changing the data and log directories.



But if you search through all of the pages under Database Settings you will not find anything that shows the default backup directory.  To find this we need to look in the registry.

Open the registry tool REGEDIT and navigate to following key:
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.BTSTPTST1\MSSQLServer

Or something similar for your instance of SQL server. The registry key BackupDirectory is the one you'll need to change to set another default Backup Directory.



Changing the registry can also be done with a T-SQL query command. To do so, you'll be using the extended stored procedures XP_REGREAD and XP_REGWRITE.

Reading the falue in registry can be done by using this command:

DECLARE @BackupDirectory VARCHAR(100)
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',
  @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.BTSTPTST1\MSSQLServer',
  @value_name='BackupDirectory',
  @BackupDirectory=@BackupDirectory OUTPUT SELECT @BackupDirectory

This will result in something similar as this:

Changing the default folder can be done by using the following command

EXEC master..xp_regwrite
     @rootkey='HKEY_LOCAL_MACHINE',
     @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.BTSTPTST1\MSSQLServer',
     @value_name='BackupDirectory',
     @type='REG_SZ',
     @value='C:\Program Files\Microsoft SQL Server\MSSQL10_50.BTSTPTST\MSSQL\Backup'

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.



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



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/

SQL Server Compact 4.0 Tooling in Visual Studio 2010

I developed a program which uses SQL Server Compact. Initially it was developed creating SQL Server COmpact 4.0 databases. Now after reinstalling my laptop, I tried to open the database, but it appaered I was missing the necessary tooling for SQL Server Compact 4.0, I could only open/create SQL Server Compact 3.5 databases.

I've immediatly installed the SQL Server Compact 4.0 runtime, which can be found here.
But this however didn't seem to be enough... I still couldn't open my created SQL compact database.

What I needed to do, was install some additional software using Microsoft Web Platform Installer 3.0.
Make sure you install these packages:

• Microsoft Visual Studio 2010 SP1
• Microsoft Visual Studion 2010 SP1 Tools for SQL Server Compact 4.0

Note that selecting and installing the Visual Studio 2010 SP1 or the Visual Web Developer Express 2010 SP1 also installs the SQL Server Compact 4.0 components that are needed for the tooling support. These components are called as Visual Studio 2010 SP1 Tools for SQL Server Compact 4.0 and SQL Server Compact 4.0. The Web Platform Installer automatically detects whether these components are already installed on the machine, and if they are not installed, the Web Platform Installer will install them. There is no need to separately select the SQL Server Compact 4.0 components in the Web Platform Installer.

The installer for visual studio SP1 can also be found here.

Error: The self-extracting zip file is part of a multidisk zip file

I tried to install the Cumulative update package 14 for SQL Server 2008 R2.

After requesting the update on http://support.microsoft.com/kb/2703280, I got an email containing a link to download the file 449383_intl_x64_zip.exe. After downloading the file an trying to run the self extracting zip file, I got following error:

This self-extracting zip file is part of a multidisk zip file. Please insert the last disk of the set.

There is no other option than pressing the OK-button.And after pressing the OK-button 3 times, I got following error message:


An error occured while unzipping. One or more files were not succesfully unzipped. The error code is 110.

The solution for this seemed to be to just download the file from the given link in my email again.
After this I could succesfully run the self-extracting zip file an install my cumulative update package.