June 5, 2008 at 10:34 am
I am supporting a customer who is implementing a Service Broker facility as a way of integrating several different enterprise products (some internal and some third-party). One of the selling points of this approach was Service-Broker's "loose-coupling" approach allowing us to deal with the reality that most of the products "consuming" our messages are not in SQL Server (they are in Oracle, Unix Berkeley DB, etc.) and are also managed independently.
While most of this has been worked out satisfactorily, we are having issues with the integration at one of the boundary interfaces (CLR to Oracle) and I need some insight and advice on best practices and which methods are most likely to work with the least effort.
Because I could not determine how to implement Service-Broker receiver endpoints on 3rd party databases (like Oracle, Berkely, etc.) and because I have found using Linked Servers to non-SQL Server datbases to be very painful and difficult, we decided to send the messages to the foreign databases by writing and calling CLR procedures that used client DB code to add the message data into those databases. The CLR procs do not handle any of the SSB stuff, SQL procedures do all of the Service Broker handling and just call the CLR procs to transmit it to the foreign databases. This seemed like the best way, because the customer already had extensive custom client code to do this for their Windows and Web apps that used these databases.
So far, so good and this does seem to be working out for most of these cases. Oracle, however, is another story...
1) The developer wrote the SendToOracleDB CLR procedure in C# and used the OracleClient assembly, which is listed in the documentation as being an EXTERNAL_ACCESS assembly. It turns out that the doc is wrong, and OracleClient is really an UNSAFE assembly, so we have to load our CLR assembly as UNSAFE also, which we agreed to do for now, until we could find some way to bring the project back to EXTERNAL_ACCESS. I welcome suggestions for how to do this.
2) It turns out that (on its own) OracleClient calls the SystemTransaction library and attempts to join any pre-existing transaction. The transaction sees that it has just been joined by a remote-database and promotes itself to a Distributed transaction, which is then managed by DTC. We do not want any of this and it causes many other problems (see below), but we cannot figure out how to prevent it. There is an example of how to do this in one of the Technet forums (for SqlClient, which does the same thing), but it is written from the standpoint of a Service Broker Activated CLR procedure that handles everything itself and I cannot figure out how or if this can be applied to our situation. Any help on this would be greatlyappreciated (link is here:http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=3396475&SiteID=17 ).
3) For logging, scheduling, management and history purposes, we decided to run the SB Queue servicng procedures for SQL Agent Jobs, instead of using SB Activation. This necessitated using the WAITFOR option of RECEIVE. It turns out that this makes the enclosing transaction an Asynchronous transaction, which apparently DTC wants nothing to do with, returning an error (sorry, don't have the exact text right now) back to the OracleClient when we try to connect. So for testing purpose, I removed the WAITFOR until we could figure out a way around this. Obvioulsy we don't want this as the Job cannot service the Queues correctly this way.
4) Even after removing the WAITFOR it still fails to connect with DTC saying that it was incompatible with the remote version of Oracle which is 9i, but will soon be 10g. When I remove the transaction altogether, it works though. So now we are running and testing with NO transactions in our SQL Receive procedure (this is strongly discouraged for SSB RECEIVE commands). Obviously, we need to fix this too.
More Information:
The client code has to do more than just simple Select's and INSERTs to the Oracle DB, so they are actually ecxecuting stored procedures over there. My experience with DTC leads me to believe that it would have problems with this too.
Our test and development servers for this project, are SQL Server 2005, 32-bit, but the real (Production, and Test) servers are 64-bit which gives me additional concerns about the OracleClient its drivers and compatibility.
Also, I have been reading about the ODP.net oracle assemblies which Oracle developers seem to like more than OracleClient. However, ODP.net is not listed as an "approved" SQLCLR assembly. Of course since OracleClient is now UNSAFE, I am not sure that it makes any difference.
Overall Question: is which horse should we be riding here?
A) the current approach (SSB-SQL calls CLR which talks to the OracleDB)
or
B) use Linked Servers and manage everything through SQL?
or
C) Try to setup SSB endpoints in the OracleDB and use Service Broker to SEND to them?
Also, what about the Drivers/assemblies? Should we be using OracleClient or ODP.net? what version(s)? Do we need anything special for 64-bit/drivers?
Any and all help appreciated.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 10, 2008 at 12:48 pm
bump.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 10, 2008 at 3:35 pm
Well, I haven't tried to do what you're doing, but I have used Oracle via CLR (C# app, not from within SQL Server) and Service Broker, though not in the same application.
I recommend considering an approach that is not in your list of options: write a Windows Service to relay the messages to Oracle. Run the service on the SQL Server machine.
The service is a C# application that receives messages from Service Broker, then calls the appropriate Oracle stored proc. Since the service runs outside of SQL Server, it can call any library.
I've implemented this approach with Service Broker. In my case, a 3rd-party app (outside of my control) updated the SQL Server and I had to write data out a serial port as a result. The serial port write had to be fast and occur only once for each update. The app had to be external to SQL Server due to the serial port (similar to your Oracle issue). I used Service Broker instead of running a loop to continually re-select the updated table because it was faster and couldn't skip or double-process an update.
Looking over that code, I was reminded of how much time I spent writing the threading, the XML processing, and all the roadblocks I found (and mostly resolved) along the way. Yet I am happy with the result. If you're interested in a code sample, I can remove the proprietary bits from the code and post a sample class. Reply if you have any interest in this.
June 10, 2008 at 4:17 pm
Yes, please do.
I had considered that as an option, but was concerned that I would be taking on even more problems.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 11, 2008 at 9:21 am
I've attached two files. Change their extensions to .cs (which is not a permitted attachment type). Add these to a C# class library project. (I always find it helpful to put nothing more than service start/stop code in a service, that way I can also call the library from a command-line project for debugging purposes.) Replace the Example classes with your own.
As the code is right now, it requires references to System.Configuration and log4net for .NET 2.0. The initial project used log4net, and I found the logging statements helpful, so I've left them in (log4net is free). You can replace them with your own logging or remove them entirely.
There is more documentation included in the code comments.
June 11, 2008 at 10:59 am
Thanks, I will take a look at these.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply