calling wcf service from .net sql server project

Question

calling wcf service from .net sql server project

aparna.athmanathan

Old Hand

Points: 380
More actions
June 1, 2009 at 10:58 pm

#210322

Hi,
I am calling wcf service from .net sql server project.My requirement is report generation should happen fast sine we have planned to place wcf service and database engine in the same environment.Before that we were using remoting proxy which will be in aplication server and this will be communicating with database engine.
So we thought this is causing the performance overhead and all remoting stuff we converted as wcf service and hosted in the db server.since wcf and db server is in the same system we though now report generation will happen fast.But it takes more time than usual.
I can able to use only basichttpbinding but not wshttpbinding since we even after increasing the sendtimeout it's taking more time proceses the request and I always get timeout exception.I never saw my report but with basichttpbinding atleast the report is getting generated but no improvement in performance.
Hope I have clarified my problem Clearly.
Please help me in this.
Thanks,
Aparna

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

Why are you trying to do this through SQL Server? Why don't you let your application talk to the WCF Service directly to get the report? Calling WCF through SQL doesn't make sense in most cases, but it really doesn't make sense here.

aparna.athmanathan Old Hand Points: 380 More actions · Answer 2

We don't have any choice but to consume the wcf service from SQL Server Project only since we can't change the existing architecture.

Considering this scenario,please suggest.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 3

You are already changing the architecture based on the original design you mention in your first post. I don't understand what would make doing it the correct way, any more difficult than trying to hack together something that:

A. Won't scale.

B. Is not a supported implementation by Microsoft.

C. Is likely to cause problems with memory usage in SQL Server.

Be that as it may, what is the performance of the WCF Service when called from a WCF client that is not SQL Server? You need to isolate where your bottleneck is, likely in your Service and not SQL Server as a starting point. If your service returns instantly outside of SQL Server, then I don't know what to offer because you aren't using a supported method inside SQLCLR, so all bets are off for how it would work.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

If you are stuck using WCF, then I would recommend that you look at using SQL Server External Activator (EA) instead of SQLCLR. It says that it is for SQL Server 2008, but it can be used with 2005 also.

This should address any of the problems that you are having with CLR.

[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]

aparna.athmanathan Old Hand Points: 380 More actions · Answer 5

I have discussed about your suggestion with my manager.I have been allowed to do only in this way.Please Help.:-(.

For your clarity purpose I have given the service and th sqlclr part of my code.

Firstly I have created the following assemblies in database in which I am going to deploy my sqlclr project.

Im sql server 2005 db,I have created certain assemblies to support my sql server project.

-- Turn advanced options on

EXEC sp_configure 'show advanced options' , '1';

go

reconfigure;

go

EXEC sp_configure 'clr enabled' , '1'

go

reconfigure;

-- Turn advanced options back off

EXEC sp_configure 'show advanced options' , '0';

go

________________________________

use custdb

ALTER DATABASE custdb SET TRUSTWORTHY ON

reconfigure

_________________________________

CREATE ASSEMBLY

SMDiagnostics from

'C:\Windows\Microsoft.NET\Framework\v3.0\Windows

Communication Foundation\SMDiagnostics.dll'

with permission_set = UNSAFE

GO

CREATE ASSEMBLY

[System.Web] from

'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Web.dll'

with permission_set = UNSAFE

GO

CREATE ASSEMBLY

[System.Messaging] from

'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll'

with permission_set = UNSAFE

GO

CREATE ASSEMBLY

[System.IdentityModel] from

'C:\Program Files\Reference Assemblies\Microsoft

Framework\v3.0\System.IdentityModel.dll'

with permission_set = UNSAFE

GO

CREATE ASSEMBLY

[System.IdentityModel.Selectors] from

'C:\Program Files\Reference Assemblies\Microsoft

Framework\v3.0\System.IdentityModel.Selectors.dll'

with permission_set = UNSAFE

GO

CREATE ASSEMBLY -- this will add service modal

[Microsoft.Transactions.Bridge] from

'C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication

Foundation\Microsoft.Transactions.Bridge.dll'

with permission_set = UNSAFE

GO

_________________________________________

Secondly,In WCF Console application,main method I have written the following code:

WSHttpBinding httpBinding = new WSHttpBinding(SecurityMode.Message);

httpBinding.Security.Message.ClientCredentialType = MessageCredentialType.None;

httpBinding.Security.Transport.ClientCredentialType = HttpClientCredentialType.None;

httpBinding.MessageEncoding = WSMessageEncoding.Mtom;

httpBinding.MaxReceivedMessageSize = Int32.MaxValue;

httpBinding.MaxBufferPoolSize = Int32.MaxValue;

httpBinding.SendTimeout = TimeSpan.FromMinutes(20);

httpBinding.ReceiveTimeout = TimeSpan.FromMinutes(20);

httpBinding.ReaderQuotas.MaxArrayLength = Int32.MaxValue;

httpBinding.ReaderQuotas.MaxStringContentLength = Int32.MaxValue;

ServiceMetadataBehavior smb = new ServiceMetadataBehavior();

smb.HttpGetEnabled = true;

host.AddServiceEndpoint(typeof(ILPAccess), httpBinding, "MyService");

host.Description.Behaviors.Add(smb);

host.Open();

Console.WriteLine("Service started..");

Thridly,In my SQL Server project,I added a service reference and in one of the method call I have written the following code:

WSHttpBinding httpBinding = new WSHttpBinding(SecurityMode.Message);

httpBinding.Security.Message.ClientCredentialType = MessageCredentialType.None;

httpBinding.Security.Transport.ClientCredentialType = HttpClientCredentialType.None;

httpBinding.MessageEncoding = WSMessageEncoding.Mtom;

httpBinding.MaxReceivedMessageSize = Int32.MaxValue;

httpBinding.MaxBufferPoolSize = Int32.MaxValue;

httpBinding.SendTimeout = TimeSpan.FromMinutes(20);

httpBinding.ReceiveTimeout = TimeSpan.FromMinutes(20);

httpBinding.ReaderQuotas.MaxArrayLength = Int32.MaxValue;

httpBinding.ReaderQuotas.MaxStringContentLength = Int32.MaxValue;

LPAccessClient lp = new LPAccessClient(httpBinding, endpoint);

lp.WCFMethod.

LPAccessClient is the proxy name I should use which I found in the reference.cs file.Then this sql server project is deployed in sql server 2005.

This sqlclr dll is referred in my client application to generate the reports.But when I use BasicHttpBinding report is running whereas WSHttpBinding My report id failing.Reason being Timeout exception.

Important Note:Since SQLCLR does not understand the config files,I have written everything in c# like opentimeout,readerquotas both in service end and also in sql server project which is consuming the wcf service

What type of wcfbinding SQLCLR supports?It seems like it will support only BasicHttpBinding.

Finally,when I use my client application to run the report which is referring this deployed dll.Report is failing.Only BasicHttpBinding is working.

What is missing in my code?

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

aparna.athmanathan (6/2/2009)
I have discussed about your suggestion with my manager.I have been allowed to do only in this way.Please Help.:-(.

Sorry, but No, I will not. Please go back and re-read Jonathan Kehayias's post.

I will not help you to implement an unreliable, unsupported method that is inferior in almost every way to a simpler, more reliable, supported, method that is easily available to you without some kind of reasonable explanation. Thus far you have not provided a single reason why your company could not do this correctly. And no, "My Boss is an idiot" is not a sufficient excuse. What would he say if we had told you that it was impossible?

The only thing that I can tell you is that this can not or should not be done. Let us know when you are ready to try something sensible.

[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]

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 7

aparna.athmanathan (6/2/2009)
Important Note:Since SQLCLR does not understand the config files,I have written everything in c# like opentimeout,readerquotas both in service end and also in sql server project which is consuming the wcf service
What type of wcfbinding SQLCLR supports?It seems like it will support only BasicHttpBinding.
Finally,when I use my client application to run the report which is referring this deployed dll.Report is failing.Only BasicHttpBinding is working.

First SQLCLR does understand config files, and System.Configuration is one of the available and trusted DLL's. SQLCLR does not support WCFBinding or WCF at all. They are not in the approved assembly list for SQLCLR and are not supported for use by Microsoft, that is why you had to turn TRUSTWORTHY ON which is not recommended by Microsoft for security reasons, and why you had to run in UNSAFE, also not recommended because the code isn't guaranteed to not crash the SQL process when running UNSAFE.

Give your manager the link to this thread. He/She obviously has no clue what you are actually doing, or the problems that it poses. Just because you can make a oval tire and put it on a car doesn't mean it is the correct thing for a car to roll down the street on. It might work eventually, but it isn't gonna be a fun ride.

aparna.athmanathan Old Hand Points: 380 More actions · Answer 8

I told sql server cannot understand config files because initially everything was in config file both in service and also in sql server project which is consuming that service but I was always getting endpoint not found exception.By little googling I came to know that I should do everything in .cs file.It started working for me.

I used the following link for my work in which they used wsHttpBinding but not working for me.Only basicHttpBinding is working.

http://www.codeproject.com/KB/database/WCFFromSQL.aspx?display=Print

calling wcf service from .net sql server project

Cookies on SQLServerCentral