Using SQL CLR to execute web services

Question

Using SQL CLR to execute web services

Viewing 15 posts - 16 through 30 (of 37 total)

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

Matt Miller (4/4/2008)
ALZDBA (4/3/2008)
Matt Miller (4/3/2008)
...
By the way - the relevant reply seems to be on page 17....
(that was a dirty trick there, Johan!)
...
Dynamic paging ... :Whistling:
In my SSC-forum-profile I've set "Posts Per Page" to 50 :w00t:
Hence the faulty page ref...:blush:
The next time I should just refer to the forums page 1 .....
This way the click-reader will get the full message history as well...;)
Forgot all about that feature...hehe. There's supposedly some way to create direct links to posts, too, but I forget what that is right now.
Well - it was a good re-read anyway:)

I've got my "Posts per Page" set to 50, as well. Really helps on the long threads.

Also, the way to link to a post directly is to do something like this...

http://www.sqlservercentral.com/Forums/Topic478505-386-1.aspx#bm478531

Everything up to the #bm is the main link that takes you to the first post of the thread. Everything after the #bm is the "Post #" located in the bottom left corner (extreme bottom left). For example, the link above will take you to the third post of this particular thread.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 2

so the link should be

http://www.sqlservercentral.com/Forums/FindPost457289.aspx

(as stated when you click the left-bottom number)

If you specify:

http://www.sqlservercentral.com/Forums/Topic478505-386-1.aspx#bm457289

You only get directed to the page, bot not positioned at the reply itself.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Paul_J Hall of Fame Points: 3698 More actions · Answer 3

As has been said before, it depends on why need the web service in the first place. IMHO you shouldn't be calling a web service that can take a while to do something.

We have one CLR proc running here that calls a service. It simply calls a method that queues some work to be done and that's it. The service then does the work whenever it can and reports the results back to the database. By design, our need for the web service was to execute out of SQL server.

jezemine SSCrazy Eights Points: 8059 More actions · Answer 4

seems like a bad idea to me. even apart from the security risks of having to allow unsafe assemblies.

databases tend to be the bottleneck in many tiered architectures I've seen, since it's not easy to share the load of one db among many servers (it's trivial to scale out web servers, by comparison). calling web services from sql will add even more latency since sql server will now have to wait for a response from some web server elsewhere on the network.

don't make the bottleneck worse - let some other server that's not so busy make that call.

I hope you aren't thinking of making a web service call in a CLR trigger - say goodbye to perf if you do...

---------------------------------------
elsasoft.org

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

Hoo boy! That's one of the things I've not liked about CLR's since I first heard of them... people doing things they shouldn't be doing to/from the database...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 6

In our case, the web service is being invoked from a SQL job off hours when activity on the server is at a minimum.

I share concerns you guys have in terms of security/performance. I just don't think this is an "all-or-none" issue. There are ways to ensure security and performance are not compromised, as long as one is aware of the risks.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 7

Marios Philippopoulos (4/17/2008)
...invoked from a SQL job ....

Especialy in that case, put it in a effective .net application or SSIS package and don't poison your db-server with it !

Prepare the data (i.e. access the web-service and validat the data) and then load it into your db !

Putting it in the db-engine itself opens risks of (unintended) abuse.

You don't plan to use it in-process or during office-hours, but all of a sudden someone called the proc/function within an application, and off you go...

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

jezemine SSCrazy Eights Points: 8059 More actions · Answer 8

exactly. you could just add a step in the job that invokes a .net console app using the CmdExec subsystem in agent. this console app could do anything, for example call a web service.

---------------------------------------
elsasoft.org

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 9

Thanks for your thoughts folks. I will keep them in mind moving forward.

I will update this thread with new developments.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

gaston_e_nusimovich SSC Journeyman Points: 76 More actions · Answer 10

I will try to give you a possible answer to what I gather are your concerns.

You can design your security setup for the Web Services Invoker (SQL Server) in such a way that you will not compromise the security of the server.

Yo can use (SQL Server) Credentials, (SQL Server) Proxies to give "execute as" permissions to specific steps of a given job.

You can use an internal DMZ to isolate the SQL Server from the Web Services.

Are you using web services between SQL Servers (the web services are actual SQL Servers exposing processes throught WSs) so they do not directly invoke processes among them?

If that is the case, IMHO you should try Service Broker as a better way for SQL Server servers to indirectly invoke processes among them.

Best regards, Gastón

tom-505312 SSC Enthusiast Points: 184 More actions · Answer 11

I have been doing Web services via CLR for well over a year now and it works like a charm. (Here comes the flames):D

The key is you just have to watch your memory footprint and streams are a must.

even large volumes of data can be handled if the service is chunking it, I have been able to return ~2 million rows ~100 cols table from single sproc call ( from salesforce.com) without a missing a lick using a SQLPIPE. The responses from the service are chunked (~2000 rows per call) and was even able to do multiple calls at a time (async). however, i did not see much of a performance gain doing it that way, but it could be the proxy limiting the bandwidth.

I also have other jobs making an average of 50 web service calls/hr throughout the day without any real issues. I have not tested doing real-time hits from say SSRS. I cache the data, and make requests at intervals.

If I remember correctly you can also stream via SqlXml and SqlBytes using a udf, but the largest volume I have tested is ~100mb.

I'll test this tomorrow with 1GB file to see if it doesn't choke the dev box:>

anyhow, I'm not suggesting you push around massive data I'm just showing that CLR of is capable of heavy lifting via Web services, file system, FTP, Zips, etc... without memory timeouts if you stream it properly.

-tom

Thomas SNyder-379443 SSC Enthusiast Points: 159 More actions · Answer 12

Just tried a 1GB file and took 1m 23sec to work,

create table delme (data varbinary(max))

insert delme(data)

Select dbo.udf_FileToBytes('\\MYDBSERVER\SYS\Data\Import\data.sql')

i didn't have a huge xml file handy to test the SqlXml, I can tell you that SqlChar does not stream so here's the workaround:

create table delme (data varchar(max))

insert delme(data)

Select Convert(varchar(max),dbo.udf_FileToBytes('\\Esrs66\SYS\Data\Import\data.sql'))

---------------------------------------------------------------------------------------------------

Ok now for the bad news, I just tried a 6.7GB file and it failed 3m 18sec in. the box is using Win32 maybe the 2 GB app space limit caused this, the time of abortion seem to indicate it was about 2GB in before failure.

here's the error:

.NET Framework execution was aborted. Another query caused the AppDomain MYDATABASE.dbo[runtime].180 to be unloaded or an unhandled .NET exception happened.

at System.Threading.Thread.AbortInternal()

at System.Threading.Thread.Abort(Object stateInfo)

at System.Data.SqlServer.Internal.ClrLevelContext.CheckSqlAccessReturnCode(SqlAccessApiReturnCode eRc)

at System.Data.SqlServer.Internal.ClrLevelContext.XvarProxyWrite(CClrXvarProxy* pXvarProxy, UInt64 iPosition, Byte* pbBuffer, UInt32 cbCount)

at System.Data.SqlServer.Internal.ClrLevelContext.System.Data.SqlServer.Internal.IXvarProxyAccessor.XvarProxyWrite(CClrXvarProxy* , UInt64 , Byte* , UInt32 )

at System.Data.SqlServer.Internal.StreamOnBlobHandle.Write(Byte* pbBuffer, UInt64 offset, UInt32 count)

at System.Data.SqlServer.Internal.XvarBlobStream.ReplaceWithSqlBytes(SqlBytes psbySource, Int64 sourceOffset, Int64 cbLength)

at System.Data.SqlServer.Internal.XvarBlobStream.ReplaceWithSqlBytes(SqlBytes psbySource)

-tom

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 13

Thank you all for the great turnout and discussion, please keep it coming! 🙂

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 14

Thomas SNyder (5/16/2008)
Just tried a 1GB file and took 1m 23sec to work,

Just curious... How many rows and columns, Tom?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

tom-505312 SSC Enthusiast Points: 184 More actions · Answer 15

Jeff,

The 1 GB test file was not a table, it was just a text file. the 6.7 file that caused the failure was a binary (.mdf) file.

This was just to demonstrate that it is possible to 'truly' stream the data. (not fully consuming the data to memory)

-Tom