Architectural (broker) place of SQL Service Broker

Hi,

I am struggling with the position SSB could take in an SOA. If I would want a broker in the general sense, meaning an intermediary sitting between applications which exchange information through messaging, would SSB be a good candidate? I know Biztalk is probably the primary candidate, but in my scenario I would end up with Biztalk apps with empty orchestrations. Also, I think Biztalk is more expensive to manage. So I am looking for a lightweight broker for a simple SOA targeted at application interoperability, no fancy business processes in sight.

I look forward to some responses.

Kind regards,

Neeva

It would be a good candidate if a lot of your processing is going to be taking place in the database (because SB resides in the db). You can write apps that connect to the db and handle SB messages...but they still have to connect to the db. If you don't plan on that, and want to stay away from BizTalk, you can always write an app that uses Messages Queues (the kind that you configure on your server). Does that help?
Tim|||

Hi Tim,

I understand what you say. The problem is that I don't know upfront where processing will take place. The goal is to have a message broker which acts as an intermediary between applications that exchange messages. I do not want to build point to point connections between these applications. My guess is that I then would have to build an intermediary application on top of SSB which would handle all the messaging via an 'SSB-enabled' database. But then I must be capable of calling other applications (possibly through webservices) from SSB, and return the response via the intermediary application to the calling application. This all sounds rather complex and that, I think, is mostly a sign that it is not the best solution. Also, the mix of webservices and SSB messages doesn't feel right.

I would certainly appreciate your comments!

Kind regards,

Neeva

App receiving "Options" message from Service Broker

I have an app receiving messages from SQL Service Broker when data is updated. (Messages are located at http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlnotificationinfo.aspx )

When I run this app against a remote SQL Server, I receive the message "Updated" which I expect.

But when I run the same app against the local machine SQL Server, I receive the message "Options".

Does anyone know if there are SQL Server options that must be set to certain values?
I can't seem to find anything that troubleshoots this message... either from a SQLServer- or a .NET standpoint.

JFoushee wrote:

Does anyone know if there are SQL Server options that must be set to certain values?
I can't seem to find anything that troubleshoots this message... either from a SQLServer- or a .NET standpoint.

From http://msdn2.microsoft.com/en-us/library/ms181122.aspx:

SET Option Settings

When a SELECT statement is executed under a notification request, the connection that submits the request must have the options for the connection set as follows:

ANSI_NULLS ON

ANSI_PADDING ON

ANSI_WARNINGS ON

CONCAT_NULL_YIELDS_NULL ON

QUOTED_IDENTIFIER ON

NUMERIC_ROUNDABORT OFF

ARITHABORT ON|||

On my local machine,
sp_configure 'user options' returns 0 for config_value .

So I ran the enumeration for the options above...

ANSI_NULLS ON 32
ANSI_PADDING ON 16
ANSI_WARNINGS ON 8
CONCAT_NULL_YIELDS_NULL ON 4096
QUOTED_IDENTIFIER ON 256
NUMERIC_ROUNDABORT OFF
ARITHABORT ON 64

4474

And then I ran this against my local machine...

sp_configure 'user options', 4474

and the program suddenly works!

|||

The recommended way of fixing this is to actualy run the SET options from the user connection. This way the app sets it's needed settings overwritting whatever defaults are in the database/instance.

HTH,
~ Remus

|||

Agreed, I was using the example to explain this wasn't the app's fault.
It still manages to work against the remote server with no intervention. (Why?)

Thanks for your assistance.

|||

JFoushee wrote:

It still manages to work against the remote server with no intervention. (Why?)

sp_configure changes the global instance level settings. I'd guess that the remote server and local server are different at the database level settings (ALTER DATABASE ... SET ...)

HTH,
~ Remus

|||

I ran a compare between the two databases.

The local one, in 80-compatibility, needed the various SET options.

The remote one, in 90-compatibility, needed nothing extra to work.

When I changed the local one to 90-compatibility, the program magically worked, with or without the SET options.

(I downloaded the pubs database from MS for the example and attached to both local and remote.)