Alternative for WCF?

Question

Hi all,

I'd like to hear your thoughts about consuming data from an external process from within sqlserver. Right now i consume WCF services from SqlServer.

My situation is this: I have a report in RS2005, it gets data from a sproc in Sql2005, it's calls a wcf service through sqlclr having all the wcf assemblies loaded. The sproc writes to the service, ad it then writes that back to the database. All this using integrated security.

Now a client where we want to install the software has a policy where they want to put each component(IIS, RS, SQL (grid), CLR, Service, Client through citrix) pretty much all on a seperate (virtualized) box. And now security and performance issues start to boil up, because kerberos wants me to create ServivePrincipalsNames for services, lsass.exe seems to battling with sqlservr.exe about something on some servers, but on others it runs great. Just issues. These would go away with putting it all on the same dedicated server, but we can seem to convince them of doing that.WCf is brilliant outside of Sql, but inside seems to be pushing it. Every problem solved leeds to a new one.
 
So my question, i guess, is: how can i combinedata from an external process with SqlServer data, so can join it aginst tabels, create views over it, pivot etc. WCF with CLR seemed like the obvious way to go, but it's becoming difficult. Is there another way to do this? Can i write drivers that do this, or do the datamanipulatin (joining etc) in C# or something. Linq looks nice, but Sql is pretty handy with the edit sproc and save, create new ones. That flexibilty is hard to roll on your own in code, even with Linq.

Does anyone recognize this these issues? Maybe solved them?

Hope to hear some ideas,

Gert-Jan

Answer 1

 

Have you looked at SQL Service Broker as the possible method of sending data from one SQL Server to another SQL Server - Not sure if both your ends are on SQL

Answer 2

Hi Haroon,

 

No, we have a calculation engine we built ourselves that churns out data. We can connect to it directly just fine, but i want to connect to that data through Reporting Services, Excel, Qlicktech, BO etc. It seemed like a good idea to create a table valued function to get the data from the calcengine into sql, from there i can send it to any tool that can connect to Sql (wich is pretty much everybody).

 

So it's an external non-sql process from where i need to combine the data with sql data.

 

GJ

 

Answer 3

You should also check out ServiceStack, it's a modern, code-first, DTO-driven, WCF replacement web services framework encouraging code and remote best-practices for creating DRY, high-perfomance, scalable REST web services.

There's no XML config, or code-gen and your one clean C# web service is enabled all JSON, XML, SOAP, JSV, CSV, HTML endpoints are enabled out-of-the-box. It includes generic sync/async service clients providing a fast, typed, client/server communication gateway end-to-end!