Hello,

How do can I let a SQL service broker use a WCF service? To make it pratical, the example found on the MSDN website for the calculator: http://msdn.microsoft.com/en-us/library/ms733069.aspx

I want to add the required values in a SQL table, the service broker should pick it up and then execute it by connecting to a internally hosted example of the calculator here above. I prefer to use the well formed xml method (loaded from the script).

This is more to understand the concept, I will use it in a project with a different script, but want to understand how it is done.

Thanks for your help.

One way would be to use a Windows service to read the Service Broker queue.  When a message arrives, the service would process the request.

The alternative is to use activation and SQL CLR, but it gets messy if you want SQL to call out on a WCF service.

Hello,

The reason I was looking to do it with a WCF service was because I have been reading a few days on the subject and I can not find any (working) example in combination with a SQL service broker and a Windows service. Secondly I am still not very confident to expose a Windows service to a solution that will be driven from a (secure) website connected on the internet. I prefer to work in the "safety" of a hosted webservice. I can be wrong in this, it is probably just because I don't have expierence in deploying Windows services to applications.

Why is it exactly that a Windows Service is less messy? Could you maybe help me on some basic example (of both Windows service & WCF service)? The calculator example in my first post may be a short and easy candidate for an example? It probably will help a lot of others as I see a lot of open questions on this subject.

Thank you!

In a hosted environment, you should just start a background thread in your web app, and have it talk to Service Broker.  A Windows service is more appropriate for large scale sites that have multiple web servers.

One problem with using SP activation is that your CLR SP will need elevated privileges in order to make a WCF call.  Also, SQL CLR doesn't support 100% of the .NET libraries, so you may find certain classes you need aren't available there.  Doing most of the work in a background thread eliminates those issues.  That way, you can let SQL handle the persistent queue side of things, and all of your business logic can be on web tier.

OTOH, if your WCF service is doing something that can be easily expressed in T-SQL instead, then activation might be a better alternative.

Hello Rick, You're right. The system.serviceModel.dll is not in the list of supported CLR assemblies: http://msdn.microsoft.com/en-us/library/ms403279.aspx This would require me to register it as an unsafe assembly... A nice example can be found here: http://nielsb.wordpress.com/sqlclrwcf/

Hello,

Sorry to come back on this topic again.

While searching for examples and testing things with WCF and SQL I almost always see a very frustrating note in those articles "This is not something you should use in a production environment". But why not? What is actually the problem with this? Is it not secure? Or not reliable? First it looked like the writer mend that his code needed some fine tuning before being used in production, but more and more I see that (to me) correct implementations have this comment too. The most understandable comment I have seen so far is that MS SQL is not an application server, but a database server. This makes sense.

Is it the "PERMISSION_SET" for the assembly that needs to be set to unsafe? Or the trustworthy that need to be enabled? Or is CLR stored procedures not the way to go? Or is it just because it is unknown or uncommon technology or method?

For my project I don't want to have any direct actions between the web service and the end user who will be using a web based application. The idea was to insert the required action to a table from a webpage that is submitted by an external customer. A trigger linked to this table adds it to the service broker queue (after some security checks) and the service broker launches a script that executes the action to the internally hosted WCF service. This service reports back the result to the service broker and flags the action as success or failed.

The reasons for not having the user execute it directly are:
- being able to stop processing and delay to a later point in time
- no error message to the end user if an action fails.
- keep a history of the actions
- security: the WCF service is hosted internally in the back end. The end user has only write access to add something in the process table. I want the communication between front and backend limited (firewalled).
- I don't like threading in ASP.NET. It feels like using something where ASP.NET is not designed for.
- I want to have the load away from the frontend web server and set on the much more perform ant SQL server in the backend.
- Role back the actions
- invoicing is linked to the actions performed by the end user

I investigated the suggestion by Rick and can implement Windows Services that serves the WCF web service. The only problem is that I don't see any difference in the implementation in SQL. The database still needs to be set as trustworthy as I have to access an application external to SQL. The only advantage, as Rick point out in his post, is the unsupported .net libraries. I want not able to find a Web services example using a Windows service in SQL. If someone could provide me a basic example it would be very helpful.

Sorry for the long post. You suggestions and clarifications are much appreciated. 

The reasons why it's not a good idea to have SQL call WCF include things like:

-- Allowing your DB server to call out at all introduces security risks
-- In order for the DB to use WCF, you will have to enable unsafe libraries, which can introduce security and stability problems into the DB
-- It's architecturally better to have let the DB focus on data-oriented tasks

If it's important to be able to use the speed of the DB machine, then a Windows Service would be a better way to go.  You should be able to put all of the WCF access code in the service, and avoid the requirement to enable anything unsafe in SQL Server.

The way a service would work is that you would have a thread that reads from the Service Broker queue, in a transaction.  When a message arrives, the service would process the request -- using WCF or whatever -- and then commit the transaction.

I suggest looking into ASP.NET threading in more detail.  It is actually a very powerful model.  ASP.NET, and .NET in general, are actually extremely well designed in that area.

In case it might help, I have a detailed example in my book (see my signature) of how to use Service Broker from an ASP.NET background thread, to handle messages that are sent from web pages.