SQL Receive Port

[pxb]

I need to poll a table regularly to pick up new records, is SQL Receive Port
the right thing to use? If not, anyone can suggest an alternative approach?

And can SQL Receive Port remove the table record when that record is
"received"?

[BizTalkVirtuoso]

Yes the SQL Receive Port is the way to go. Yes you can have the
record removed from the table but what I would recommend is that you
use a separate table that has a single column that indicates wether or
not the record has been processed. For example if you are querying
for cars then I would have a car_process table also. I would add a
trigger to the car table that inserts a record in the car_process
table whenever a record is inserted in the car table. Then I would
have the SQL receive port query the car_process table for records that
have a process flag set to zero. Inside the same stored procedure set
the process flag to one. This will give you the ability to resubmit
records if you need to simply by chaning the flag status. Also it
moves the SQL lock from the car table to the car_process table.
That's a big deal because by default the SQL Adapter locks the entire
table when querying for new data. So, long story short, if you design
your stored procedures right then everything works great. If you
don't then you have mucho problemas. I use this guide:

http://adambowron.blogspot.com/2007/02/avoid-deadlocks-using-sql-adapter-and.html

Ben McFarlin
Microsoft BizTalk Server Consultant
BlinkCorp

[pxb]

Thanks Ben for the advice, it really helps.

On the other hand, I have also received feedback doubting the polling
approach from Biztalk via SQL receive Port, their concern is on the low
latency caused by a "reasonable" polling interval, instead they suggest
proactively triggering Biztalk via SOAP, but I think there is something wrong
in that approach, wonder if Ben and other experts can share some opinion.

[Dan Rosanova]

Ben does point out a nice approach and the best part is it requires
very little to make it work. It is often better to proactively start
something than to poll, but it all depends on the situation. I’d
start with identifying your latency and throughput requirements.
Those are often opposing, i.e. low latency usually means low
throughput, so you really have to decide on what you need. A strength
of polling is the batching aspect; if 100 records were placed in they
can all be pulled out in a single batch. Also you can set the poll
intervals really short.

I guess I’m trying to say it all depends on your specific
requirements. If you let us know them we might be able to get you
better answers.

Also, keep in mind that if you design this well, you can switch
between the two quite easily (this is just the receive location
really).

Kind Regards,
-Dan

[pxb]

Thanks Dan for the feedback.

I personally actually has concern about the approach of making proactive
calls to Biztalk, let's say if each SOAP message to Biztalk would create an
orchestration instance, and I would need these processing to handle these
messages in sequence, a proactive call can possible create timing or
concurrency issues as each call will force Biztalk to handle it.

Unlike in a Poll approach, Biztalk can grab N number of records, and process
them one by one in sequence.

Do you see any fault in my thinking?

[Dan Rosanova]

Well a lot of the in order stuff depends on what you’re doing. You
may not even need an Orchestration depending what your requirements
are. I generally use Orchestration in about 30-40% of my solutions.
Many can simply be done without it, but if you need it and you design
carefully and test thoroughly it will work out well.

Again a lot of this has to do with latency requirements. If you need
these records out of SQL Server and processing in less than 10 to 30
seconds you may not want to do the polling route. Though you can poll
like every 5 seconds no problem, but again, it depends on what you’re
doing and Ben pointed out that important locking aspect. Also more
polling = more load on the SQL Server.

My biggest concern about the proactive approach is that it sounds like
you need to do more upstream work, like make changes to the calling
application (or allow SQL Server external access to call SOAP, which I
would not recommend). If anything, just have the upstream application
call BizTalk for whatever it is doing (not in addition to what it is
doing). It sounds like it is putting data in a SQL Server; you could
expose that as a BizTalk service that includes adding the data to the
SQL Server, returning the result (like to say it added), and starting
this next processing step (which would equate to the result of the
poll and thus you would not need a poll at all).

Lastly do you need to process them in sequence? In order processing
is not always the easiest thing and isn’t always needed anyway. I
know I keep repeating, but a lot of it depends on the unique needs of
your solution.

Kind Regards,
-Dan

[pxb]

Thanks.

My concern of allowing Biztalk to be called is that
(1) I need sequential processing of the messages so I would rather let
Biztalk poll on the database table and fetch by datetimestamp
(2) If I allow Biztalk to be called then many instances of the web
application (when many users are operating on different pages) would be
calling Biztalk and Biztalk must respond, in contrast, Polling would give
Biztalk better control

By the way, are there any known implementation of Biztalk using the SQL
Receive Port or polling approach? It would be helpful to have some references
for benchmarking.