问题描述:

I have an SQL Server database and an intranet web application on a local network. The intranet web application creates records and sends them to the database. I have an Internet web application that I would like to send new records to using the SQL Server database on the local network.

I can't change/modify the intranet web application for various reasons so the only option I have is to create a trigger on the local SQL Server that will send new records to the Internet web application using some sort of http post request or url call.

The INTERNET web application is set up with a RESTful api that can receive new records via form post to a publicly accessible url (e.g. http://www.example.com/records/new).

Does anyone know if sending data (xml, json, plain variables in the url) via a url can be accomplished in SQL Server?

Thanks for any thoughts!

网友答案:

It is possible, but in the real world is a little bit more complicated than the naive approach you envision. Primarily, it is unacceptable to have a trigger wait for a HTTP request:

  • For one, your application will crawl to a screeching halt, because triggers will block resources (primarily locks) waiting for a response from some far and away WWW service.
  • Second, more subtle but far worse, is the issue of correctness in presence of rollbacks. If the transaction that issued to HTTP requests rolls back, there is no way to 'undo' the HTTP request.

The solution is to decouple the trigger from the HTTP request via a queue. The trigger enqueues the request into a local queue and commits, while a separate piece of processing dequeues these requests and issues the HTTP request. This solves both problems pointed out above. You can use ordinary tables for queues (see Using Tables as Queues) or you can use Service Broker, both work well.

Now on how to dequeue this requests and actually place the HTTP call, I strongly recommend using a dedicated process (ie. an application dedicated for this purpose). While it is possible to use SQLCLR, it is a very bad choice. SQL Server resources (specifically workers) are far to precious to waste on waiting for Internet responses.

网友答案:

As a good (rather reliable and scalable) option, you can use SQL CLR to interact with web application / web service.

For example, SQL CLR WebRequest or SQL CLR WCF.

网友答案:

To add to the good answer by Remus Rusanu. And related to the comment of Lehi Sanchez. The problem is that you always have to wait for the http request. The code in the trigger is a step by step execution so the request has to come back so the trigger can finish his execution.

相关阅读:
Top