I was looking for some examples on how to consume a WCF service with SQL service broker. When reading this I am a bit confused... :hehe:

I need to enable an automated system for our external contractors/customers to setup services (general things like creating users, directories, ...) on our systems. The external contractors are NOT trusted employees, so one of the primary goals is security. They need to pay per object they use/create.

The actual execution of the code that is creating accounts, directories,... is done by calling an internally hosted WCF service (on IIS 7). This works fine.

My point is that I was searching a good driving force to use this WCF service. Therefore my idea was to use SQL Service Broker. All data (number of objects per customer, ...) is currently in a SQL 2005 database (we plan to migrate to 2008 before launching it).

The flow would look like this:

1. secure website with WCF services that just store the contractors request in the database tables

2. a trigger (?) passes this request to the service broker

3. the service broker calls the secure WCF services that actually execute the action (eg.: create user).

4. service broker reports back that action was succes or not (and handles of invoicing...)

By doing this I wanted to:

- move the load away from the externally available web server that hosts the WCF to the backend and more performant SQL server

- add more security (the WCF exposed to the external world only inserts values in a table no real action is done)

- when for some reason something goes wrong with a script (timeout, error, ...) we can replay it and no error is send directly to the external executor.

- we can delay executing (stop processing in the weekend or when systems are patched for example) while customers still can add changes

- require a form of auditing; who creates what, when, how many, ...

- ...

BUT, when reading the previous posts I wanted to know what your idea is of this plan? The key in this workflow is SQL that activates the WCF service with service broker.

Is it a good idea or do you suggest other things? The options I have are:

- CLR stored procedure makes the WCF call to a hosted IIS website (some security issues may be a problem I was told)

- deploy the WCF to a Windows service, would be easier(?)

If there are some basic example, then they are more then welcome.