Thursday, July 20, 2017

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

  1. Create the .Net code necessary
  2. Create a CLR script for compilation
  3. Compile the CLR
  4. CREATE the ASSEMBLY
  5. CREATE the PROCEDURE
  6. Call the procedure
  7. 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

Step 7: Run it Automatically


That's my next step!  Combine all of it, set up logging, deal with errors, retry logic, etc.

Friday, April 21, 2017

[Powershell] Run powershell code 30 seconds after the job it's in finishes(!)

We have a process that, when it fails, has a tracking table, and as each step is run it updates that row in the tracking table.  If the job fails, we know in which step it failed.

I also have a process that looks at failed jobs every minute, and sends out the tracking table info when a SSISDB job fails, so we get a useful message.  (See SSISDB in this blog).

However, the tracking table means that a job will wait to be fixed.  I don't want to update it as part of the job, since then I can't get the state of the tracking table. So what I need is either to kick off another job that waits 2 minutes... or use powershell.  :)

Next up is feeding it a parameter, and setting the background script to use an update statement

Note: works in powershell 2 or greater.  This seems much easier to do in newer versions.

First script "runinforeground.ps1":
start-process -filepath "powershell" -argumentlist "c:\temp\runinbackground.ps1"

Second Script "RunInBackground.ps1":
$now = get-date
start-sleep -s 10
$now | out-file c:\temp\whatisnow.txt #here is where I'd do the invoke-sqlcmd2 and the update



SQL Server Agent Job Step (CmdExec):
powershell.exe "c:\temp\runinforeground.ps1"

Wednesday, March 22, 2017

[Event Notifications] CONNECT and SQL permissions between boxes.

Just ran into this and didn't have it blogged for some reason.

For Event Notifications, you need two permissions on the monitorING server, for the service account running on the monitorED server.  First, there's SQL.  Second, there's Endpoint.  It must have both.

If you just have CONNECT SQL enabled, not the endpoint, then what I saw in sys.transmission_queue was:


An error occurred while receiving data: '24(The program issued a command but the command length is incorrect.)'.

Here's the command to grant it.  Remember that the authorization will be the name you're logged in as, so you might change that or ALTER the AUTHORIZATION afterwards (see http://thebakingdba.blogspot.com/2015/01/event-notifications-changing-security.html)

use [master]
GO
GRANT CONNECT ON ENDPOINT::[ENAudit_Endpoint] TO [yourdomain\monitorEDserviceacct]
GO

Wednesday, March 1, 2017

[Event Notifications] SQL 2016 and a new exclusion

We just rolled a bunch of servers to SQL Server 2016, and we're now getting this in our event log, once every 5 minutes, for each server.  On the plus side, I didn't know about telemetry_xevents before, and it looks like a nice new tool to query.  However, I don't need these.


Here's how to prevent them from showing up.  Super-simple exclusion.


INSERT INTO ENAudit_Exclusion_List
        (exclusion_set,
         exclusion_type,
         excluded_value,
         insert_datetime,
         update_datetime,
         active
        )
VALUES  ('aw', -- exclusion_set - char(2)
         'ObjectName', -- exclusion_type - varchar(128)
         'telemetry_xevents', -- excluded_value - varchar(128)
         GETDATE(), -- insert_datetime - datetime
         null, -- update_datetime - datetime
         1  -- active - bit
        )






Thursday, February 16, 2017

[Forthcoming] Doing it wrong - Hekaton.

Okay, I've got an idea for a series of blog posts.  I've recently implemented In-Memory OLTP (IMOLTP, which I will now start pronouncing Im-Ho-Tep), and learned a bunch in a short period of time.  Don't do it like me.  Do it better.

Many thanks to Ned Otter for helping out a ton during this time.



Create in-memory FG
Create in-memory table
Create second in-memory FG
Create/destroy to new name
Stored procedure not working - still needs to only be in THAT database. No cross-DB work in SPs!
Resource issues!  (show event log)
Missing inserts copying from _live to _main? Why? (all about the COMMITs)
Answer: flip the order we get the IDs.
Pare down for now so we don’t run out of memory
DBCC caused problems with memory
Identity value on both table – have to monitor
Resource governor to the rescue?
 (I did this part, but remember to take the DB offline/online and sweat while it takes forever to come up)
Rebalance the In-memory filegroups if you have older data in there!

[Powershell] scripting out a servers via Red-Gate's SQL Compare

Grant Fritchey posted a basic powershell script that uses Red-Gate's SQL Compare to script out a database (http://www.scarydba.com/2011/01/31/powershell-to-automate-sql-compare/).  When I did it, it skipped partitioning and compression, so I wanted to put down my actual script somewhere for the next time I need this.  Specifically, "/options:none", so everything gets included.

Now that I've done it, I'm looking at the resulting folder structure - which doesn't match the structure I already have in place, using a different script (PoSH + SMO).  But I've written it, so here you go.


set-Location "c:\Program Files (x86)\Red Gate\SQL Compare 12\";
. C:\powershell_scripts\invoke-sqlcmd2.ps1

$Servers = "ser-ver-a","ser-ver-b"
$Path = "C:\temp\decom_server_scripting_via_redgate"

foreach ($server in $servers){
Invoke-Sqlcmd2 -Query "sp_databases" -ServerInstance $Server | ForEach-Object {Invoke-Expression "./sqlcompare.exe /s1:$Server /db1:$($_.DATABASE_NAME) /mkscr:$($Path)\$($Server)\$($_.DATABASE_NAME) /options:none" }
}

Thursday, January 19, 2017

[SSRS] Replacing an image embedded in the report with a new one via RDL manipulation.

Quick and Dirty:

I had a report, and had to change the logo to the new&improved version.  Didn't want to actually TOUCH anything within the report, lest I break it. The person who designed it had the image embedded within the report - please don't do that, but instead reference a file or fileshare or something. (makes replacing it much easier, but then you have to make sure where the images are is "safe" and Highly Available)

Step 1: download the RDL

Step 2: open in Notepad.

Looking near the top...
 

Okay, this is somewhat human-readable. Figure out which image it is.  Mine was simple - only one image, 2 inches wide by 1 inch tall.  Now let's find the actual image.



Okay, that's cool.  It's a PNG file, via MIME (base64) encoding.  Let's see what it looks like.
Take everything within ImageData, copy it, and go to: http://www.askapache.com/online-tools/base64-image-converter/

Paste, and convert.  Verify it's the right image. Yup!

Now what? Get a new image about the same size (so it'll fit when scaled - let's not touch things if we can avoid it).  Save as PNG, even.  Now go back to that askapache, and upload it.  Copy the RAW out, and refresh the page.  Paste it back in and make sure it's showing what you expect (aka the new image). 

Finally, take this new string, replace ImageData with it, save the new version, then upload->replace the RDL. Test and done!

Saturday, December 31, 2016

[presenting] SQL Saturday Nashville, just two weeks away! Jan 14th, 2017




SQLSat Nashville 2017

Thrilled to be presenting again, and starting off 2017 with a bang! 


I'll be talking about my fave topic, Event Notifications, ad how to use them across your environment.  It's my standard talk, to be honest, but I'm really happy to present to a new crowd!  

There's a great list of speakers - just off the top, Kevin Kline, Louis Davidson, Geoff Hiten, Andy Yun, Argenis Fernandez... I am so psyched for this!