|
Home > Archive > BizTalk Server General > August 2005 > SQL Integration Design Options
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
SQL Integration Design Options
|
|
|
| Hi,
I hope this hasn'y been asked before, but here goes:
Basically i am trying to decide between two possible desgins for an
integration aproject utilising SQl server. Any opinion/advice on the two
options would be greatly appreciated.
Option1: Standrads design utilising SQL adapter, triggers and Stored
Procedure to retrieve modified/inserted data.
Option2: MSMQ/MSMQT adapter, triggers and Stored Procedure that submit to
MSMQ/MSMQT.
the aim of approach 2 is to essentially bypass any "polling".
Thankyou very much in advance.
| |
| Scott Colestock 2005-08-03, 5:51 pm |
| Can you supply a little more detail about how you would use the components
in 1 and 2 below to accomplish your goal? (And what your goal is?)
Scott Colestock
www.traceofthought.net
"Milo" <Milo@discussions.microsoft.com> wrote in message
news:1F573AEE-58C2-4461-B9FB-89EDFC48F3BE@microsoft.com...
> Hi,
>
> I hope this hasn'y been asked before, but here goes:
>
> Basically i am trying to decide between two possible desgins for an
> integration aproject utilising SQl server. Any opinion/advice on the two
> options would be greatly appreciated.
>
> Option1: Standrads design utilising SQL adapter, triggers and Stored
> Procedure to retrieve modified/inserted data.
>
> Option2: MSMQ/MSMQT adapter, triggers and Stored Procedure that submit to
> MSMQ/MSMQT.
>
> the aim of approach 2 is to essentially bypass any "polling".
>
> Thankyou very much in advance.
| |
|
| Is this an "interface" solution?
I have created several BizTalk applications in the past and have found
that using stored proc's to pass information to MSMQ is not necessarily
the best approach... especially in high frequency transaction
environments. The use of stored procs depends HIGHLY on the usage of
the system that is inserting/updating information in the database. If
you have a very simple database design, where each entity is stored in
one table (e.g. "Users") as opposed to several tables (e.g. "Users",
"UserAddress", etc), then it should be ok. However, when your data
entities are distributed among several tables, managing the queueing
for each of your messages becomes a very tedious and time consuming
task. If you do have a fairly complex database schema, I would
recommend building your MSMQ messaging into your application (if it is
possible). This way you get one message per insert/update, rather then
one message per table that is impacted on your insert/updates. You can
also considerng writing your own adapter that your application uses to
submit messages directly to BizTalk - which will bypass MSMQ entirely.
I have not utilized the SQL adapter in BizTalk 2004, so I cannot
comment on that so much, although I am currently looking into using it
myself.
Hope this helps... good luck with your project!
| |
|
| Hi Scott,
Tthankyou very much for your reply. heres my effort at explaining myself.
Basically I have a stored procedure which will detect and retrieve any data
that has been inserted/updated on the source SQL table. I need to do somoe
"stuff" with this and then update a table on the destination system.
Utilising the SQL adapter I can execute the SP and retrieve data to start
off the process. However this sapproach "polls" the source system by
executing the SP at a predefined interval. I am curently using a trigger on
the source system to place an entry into an auxilary table to idnetify rows
of interest. this auxilary table is then polled by BTS via the SP.
Essentailly I was wondering if the approach described above is ideal, or,
wheter it would be better to place a second trigger on the auxilary table
which would then execute an SP which retreives the data and shells out to the
OS and submits the data directly to MSMQT.
Essentialy in apporach 1 I wouldl be polling the source system at a
predefined interval.
With approach 2, the source system would "push" data to BTS.
Is that explanation any better??
"Scott Colestock" wrote:
> Can you supply a little more detail about how you would use the components
> in 1 and 2 below to accomplish your goal? (And what your goal is?)
>
> Scott Colestock
> www.traceofthought.net
>
>
> "Milo" <Milo@discussions.microsoft.com> wrote in message
> news:1F573AEE-58C2-4461-B9FB-89EDFC48F3BE@microsoft.com...
>
>
>
| |
|
| Hi Goldy
thankyou for your reply.
You havent exactly answered my question, but have certainly provided some
suefull info. Thankyou again.
"Goldy" wrote:
> Is this an "interface" solution?
>
> I have created several BizTalk applications in the past and have found
> that using stored proc's to pass information to MSMQ is not necessarily
> the best approach... especially in high frequency transaction
> environments. The use of stored procs depends HIGHLY on the usage of
> the system that is inserting/updating information in the database. If
> you have a very simple database design, where each entity is stored in
> one table (e.g. "Users") as opposed to several tables (e.g. "Users",
> "UserAddress", etc), then it should be ok. However, when your data
> entities are distributed among several tables, managing the queueing
> for each of your messages becomes a very tedious and time consuming
> task. If you do have a fairly complex database schema, I would
> recommend building your MSMQ messaging into your application (if it is
> possible). This way you get one message per insert/update, rather then
> one message per table that is impacted on your insert/updates. You can
> also considerng writing your own adapter that your application uses to
> submit messages directly to BizTalk - which will bypass MSMQ entirely.
>
> I have not utilized the SQL adapter in BizTalk 2004, so I cannot
> comment on that so much, although I am currently looking into using it
> myself.
>
> Hope this helps... good luck with your project!
>
>
|
|
|
|
|