Scenario:

We send product catalogue to our partner on regular basis via SFTP. There are 3 different types of catalogue message FULL CATALOGUE, PRICE DELTA, and AVAILABILITY DELTA. Price Delta and Availability Delta messages are send every hour, whereas FULL CATALOGUE will be send only once in a month (message size is roughly around 10MB).

Architecture:

Architecture is quite straight forward we have configured a BizTalk "Receive Port" with 3 "Receive Locations" using SQL Adapter. The SQL Receive locations got different polling interval settings as shown below.

PRICE_DELTA: Polls every 1 hour.

AVAILABILITY_DELTA: Polls every 1 hour

FULL_CATALOGUE: Supposed to poll every 720 Hours once.

Through some filter conditions corresponding "Send Ports" will pick up the message and transfer it to our parter via SFTP.

So, where is the bug?

As soon as you enable the FULL CATALOGUE (with 720 Hours polling interval) SQL "Receive Location", you'll see hundreds of messages flooding into your BizTalk server via the SQL Receive Location. If you don't notice it straight away with in minutes you'll have 1000's of messages coming non-stop into BizTalk server and to Message Box, blocking all the network traffice, maximum memory utilization in the Biztalk Host machines and SQL Server.

In our case the effect was pretty bad. We did the configuration at end of business and left it over night. Next day morning, Our Biztalk Server disk space was full (because we temporarily write this particular file to local disk for logging purpose), Event viewer was full of Error Messages related to SQL, pretty much both the BizTalk and SQL machines were  useless.

We started analysing the problem and figured out SQL Adapter polling is working without any issue upto 596 hours, you put any value above 596, your SQL Adapter will start polling messages every 10millisecond or so non-stop clogging the whole Message Box.

Some dissection into Microsoft.Bizatalk.Adapter.SQL.dll

Out of curiosity I just went to figure out the root case for this bug. Lutz reflector came handy to do some dissection of the Microsoft.Bizatalk.Adapter.SQL.dll, which revealed the next polling interval is calculated by the method GetNextActivationTime() inside the TimeIntervalSchedule class, luckily the reflector is showed me the source code as well.

The problem here is "seconds" properties is of type integer (Int32) so the maximum value it can hold is 2147483647. The polling logic will be ok till 596 hours. (i.e 597 * 60 * 60 * 1000 = 2145600000 MilliSeconds), But if you try putting anything bigger than 596 is going to exceed the maxmimum threshold value for Int32 and the function is going to return a DateTime value in the past, which will result in constant polling by SQL Adapter non-stop thinking it has some work to do. Unfortunately in our case it ended up in one of the biggest messages in our system (10MB).

Reproduction of the BUG using windows application. 

You can easily reproduce this behaviour by writing this simple code.

private void button1_Click(object sender, EventArgs e)
{
int f = 597 * 60 * 60;
MessageBox.Show(GetNextActivationTime(f).ToString());
}
public DateTime GetNextActivationTime(int seconds)
{
//return (DateTime.Now + TimeSpan.FromMilliseconds((double)(0x3e8 * seconds)));
return (DateTime.Now + TimeSpan.FromMilliseconds((double)(1000 * seconds)));
}

When I tested this code on 08 Feb 2007, I got the following results

For 596 hours : 05/03/2007 08:47:23

For 597 hours:  14/01/2007 16:45:39 (which is in the past)

Changing the datatype from int to uint will produce the correct result.

So, what's the solution?

1. Option 1: Restrict the User Interface in SQL Adapter to certain values, in our case not more than 597 hours. There is bug in the UI as well, it just throws an exception saying you can enter value more than 65535 (max of uint) inspite of you choosing hours, minutes or seconds.

2. Option 2: SQL Adapter code need a fix.

Sample Application for Download

I'v just put a sample Biztalk Application you can use to see this behaviour. It got 1 Schema to support SQL Adapter, 1 SQL Receive port and 1 FILE send port to "C:\temp". The SQL Adapter will poll data from BtsMgmtDb, here is the query I've used

SELECT NAME, DATEMODIFIED FROM adm_HostInstance FOR XML AUTO

Default binding got the value of 596 hours, which is OK, change it to 597 and see how many messages you are getting on your C:\Temp folder.

Note: Disable the "Receive Location" to stop polling after a minute or so. Dont forget to disable the SQL Receive location after the experiment.

Nandri!

Saravana