We are trying to use
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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
//See full article at http://thebakingdba.blogspot.com/2017/07/clr-soup-to-nuts-making-generic-clr-to.html | |
using System; | |
using System.Data; | |
using System.Data.SqlClient; | |
using System.Data.SqlTypes; | |
using Microsoft.SqlServer.Server; | |
using System.Collections; | |
using System.Net; | |
using System.IO; | |
public partial class StoredProcedures | |
{ | |
[Microsoft.SqlServer.Server.SqlProcedure] | |
public static void POSTWebRequest(string urlToSendTo, string appHeader, string xInsertKey, string dataToSend, out string responseFromWeb, out string error) | |
{ | |
responseFromWeb = string.Empty; | |
error = string.Empty; | |
try | |
{ | |
string address = urlToSendTo; | |
HttpWebRequest request = (HttpWebRequest)WebRequest.Create(address); | |
request.ContentType = string.Format("application/{0}; charset=utf-8", appHeader); | |
request.Headers.Add("X-Insert-Key", xInsertKey); | |
request.Method = "POST"; | |
using (var streamWriter = new StreamWriter(request.GetRequestStream())) | |
{ | |
streamWriter.Write(dataToSend); | |
streamWriter.Flush(); | |
} | |
var httpResponse = (HttpWebResponse)request.GetResponse(); | |
using (var streamReader = new StreamReader(httpResponse.GetResponseStream())) | |
{ | |
var result = streamReader.ReadToEnd(); | |
responseFromWeb = result; | |
} | |
} | |
catch (Exception ex) | |
{ | |
error = ex.Message; | |
} | |
} | |
}; |
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
Step 7: Run it Automatically
That's my next step! Combine all of it, set up logging, deal with errors, retry logic, etc.
1 comment:
Post a Comment