[CLR] Soup-to-nuts, making a generic CLR to perform Web API POST calls, via SQL Server

(this is the part I'm both looking forward & dreading, where people see my CLR code and find it ...lacking)

We are trying to use ~~New Relic~~ a cloud-based monitoring company to build alerting off of metrics inside our SQL Server database. Not of data ABOUT our SQL Servers, but data stored IN SQL Server. We have a bunch of front-end servers that handle transaction processing. Once the transaction is done, we send copies of the transaction, including all the metadata around the transaction (how long did it take, where did it come from, where did it go, etc), to SQL Server, which then parses it.

So, how do get this data into our cloud-based monitoring?

What we settled on was building a CLR that would make the web calls, feeding it our data via a FOR JSON query. We would then log the results into a separate table to make sure everything worked as expected. I made this as generic as possible so that others could use it.

So let's go through the steps.

Create the .Net code necessary
Create a CLR script for compilation
Compile the CLR
CREATE the ASSEMBLY
CREATE the PROCEDURE
Call the procedure
Run it automatically

Step 1: Create the .Net code for the CLR.

Originally pilfered from this Stack Overflow item: https://stackoverflow.com/questions/28435637/sending-http-post-request-from-sql-server-2012-or-sql-clr-c-sharp. Many thanks to @Dennis! There are a lot of examples, all of which slightly differently. And one comment in here (https://sqlsunday.com/2013/03/03/web-requests-using-clr-proc/) made me super paranoid, since it spoke of a potential long-term memory leak in case of connections issues. So I brought it to one of our developers, who proceeded to slightly change it so that it looked like...

Step 2: CLR script to compile.

The script I was given was pretty much complete, but I wound up having to add some headers ("using", though there's 2 different types of USING in the code) so it would compile. I also added the X-Insert-Key header here.

Save this script as webPOST.cs

Step 3: Compiling the CLR.

We're cheating, somewhat. We will come back and properly sign and certificate-ize this, but we wanted to get it up and testing. So, open a command line and navigate to the folder where you saved the script from Step 2. Open a command line, and run the following statement:

"C:\Program Files (x86)\MSBuild\14.0\Bin\amd64\csc.exe" /target:library webPOST.cs

That worked on mine, and left a "webPOST.dll" file alongside my webPOST.cs. Copy that DLL over to a folder on your SQL Server.

Step 4: Creating the Assembly.

Like I said at the very beginning, we're cheating here. There's two ways to create the assembly. (aka putting the DLL inside SQL Server). The first involves creating a key (inside Visual Studio; no good way to do it via code), signing the assembly, creating the logins & users necessary to execute the assembly.

The second one is to create a separate database that has virtually no permissions, and that's NOT a good idea. Here be DRAGONS, people! Big nasty sticky security issues. Good "TRUSTWORTHY SQL SERVER" and READ.

CREATE DATABASE webposttest
go
ALTER DATABASE webposttest SET TRUSTWORTHY ON
go
CREATE ASSEMBLY webPOST FROM 'c:\temp\webPOST.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS;

What's EXTERNAL_ACCESS? There's more info about CLRs online, but it comes down to "give this code access to resources outside the SQL Server, but still keep it in the protected memory space". Which means that it _shouldn't_ be able to take your SQL Server down. There are several examples out there using UNSAFE. DON'T USE UNSAFE unless you have a really excellent reason. Especially since we're able to do all this with EXTERNAL_ACCESS.

Step 5: Creating the stored procedure.

This is your T-SQL interface into the CLR. Note all the NVARCHARs, since .Net assumes it's all unicode.

CREATE PROCEDURE webPost
(@urlToSendTo     nvarchar(400)
,@appHeader       nvarchar(40)
,@xInsertKey      nvarchar(40)
,@dataToSend      nvarchar(MAX)
,@responseFromWeb nvarchar(MAX) OUTPUT
,@error           nvarchar(MAX) OUTPUT)
AS

EXTERNAL NAME webPost.StoredProcedures.POSTWebRequest

GO

Step 6: Call the procedure.

Let's do a simple test. I'm converting the login_time because they seem to want UNIX "epoch" time. So long as everything's since 1970, you're good (well, until the epochalypse occurs - see "Year 2038 problem"). You need the eventType to tell you what "set" this is part of, and they say they'll only accept 1000 events at once (it does seem to vary; I'm sending more than that)

DECLARE @responsefromweb NVARCHAR(MAX), @error NVARCHAR(MAX), @JSONset NVARCHAR(MAX)

SELECT @JSONset = (SELECT top 1000
        'SQLSysProc' AS eventType,
        kpid,
        lastwaittype,
        DATEDIFF(SECOND, {D '1970-01-01'}, login_time) AS login_time
FROM    sys.sysprocesses
FOR JSON PATH);

EXEC webpost @urltosendto = 'https://insights-collector.newrelic.com/v1/accounts/SOMEBIGNUMBERHERE/events',
@appheader = 'json',
@xinsertkey = 'yourAPInameGOEShere',
@datatosend = @JSONset,
@responsefromweb = @responsefromweb OUTPUT,
@error = @error OUTPUT

SELECT @responsefromweb, @error

...and what do we get back?
{"success":true} for the @responsefromwebSP