October 11, 2006 at 11:45 am
I am working on a client who wants to integrate transactional messages between a SQL Server database and an Oracle database.
I don't think Service Broker works for this scenario. Can I use Integration Services to achieve this type of messaging or do I need to employ an EAI tool such as Websphere?
Thank You
Greg
October 11, 2006 at 12:03 pm
SQL2005 has great support for replication to/from Oracle.
It is simple and traight farward especially if data is flowing in one direction. We use it to support multiple servers and it runs error free but only in one direction fashion.
We also use IBM Information Integrator to move data off DB2 into SQL and it has pros and cons but overall it ok if you have the money and expertise.
DataMirror has great ETL utilities also; you may find it viable.
Bottom line if data flows in one direction only the best would be sql2005 transactional replication to Oracle and it is free with SQL ! hassle free and can be setup in minutes. The only thing to be careful of is that if data is flowing from Oracle into SQL, then SQL replication will create triggers on the Oracle tables to collect changed data and that may hinder performance on the Oracle server; so use it after testing and with caution if the Oracle applications are write intensive.
October 11, 2006 at 12:51 pm
Thanks for the reply. That sounds like a good option. I was really looking for an understanding of how SQL Server interacts with an EAI tool such as Websphere. I want to propose a solution that is based on a Service Oriented Architecture (SOA).
Would you know if SQL Server can generate XML messages and post them to an EAI tool?
October 11, 2006 at 10:16 pm
I found this link on the IBM site:
Transforming WebSphere Information Integrator Event Publisher messages with WebSphere Message Broker.
Hope it's useful.
October 12, 2006 at 9:48 am
>>Would you know if SQL Server can generate XML messages and post them to an EAI tool?
It sounds as if you're stuck on the "publishing" side in SQL Server (you probably know how to use Message Broker on the other side, yes?).
Short answer: Yes, it can.
Long answer: There are probably 100 ways a developer might choose to do this, directly building something with CLR code low-level, because just about any environment can post HTTP messages and SQL Server certainly knows how to generate XML.
But if I were you I would start by looking at the SQL Server Notification Services. The only two standard protocols for delivery are FILE and SMTP but there is an IHttpProtocolProvider interface and plenty of scope for working with web services. Look for the topic "Developing a Custom Delivery Protocol" in the docs.
(I think that the reason HTTP isn't a standard protocol is that they couldn't figure out a "standard" thing they'd want to do with the response...)
>L<
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply