I remember feeling a little nervous when when I first started contributing to stack exchange. It was supposedly unwelcoming and full of unfriendly people. I even planned on going to the version of the site full of database administrators, which obviously would be much worse than the average exchange site. My worries didn’t last long though. Sure, there was the occasional answer with a gruff tone, but I realized pretty quickly that I was getting help from industry experts for free. I interacted with a lot of cool, knowledgeable people (including the guy who owns this site) and I was able to help a lot of people with their problems. I even eventually got to shake hands with Sean Gallardy, the SQL dumping king.
In my opinion, one of the nicest things that you can do on the internet is spend your free time to help someone else with a problem. I also believe that it’s often whiny and counterproductive to complain about the free help that other people are offering. Those beliefs guided my interactions on the site and I ended up ranked #26 in reputation. The DBA stack exchange was a wonderful place of helping. The help wasn’t always perfect, but what is? Especially something given for free?
The Problem
Speaking of imperfections, I recently had a problem at work with CLR functions that were accessing files on Azure Blob Storage. We were planning for a migration to Azure SQL Database and these functions were flagged as an issue because that platform does not support CLR functions. The ideal solution would be to remove all interactions with the binary file data within SQL Server, but the complexity of the project and the migration timelines made this a tall order. So I went looking for a workaround.
I didn’t find anything except for a poorly answered stack exchange where someone had what appeared to be a similar problem to me.
A Solution
After picking at the problem and dealing with AI hallucinations, we eventually found something that worked as a proof of concept in a development environment. I’ll go ahead and reproduce my freshly minted stack overflow answer here:
We need to do something similar as part of a quick migration to Azure SQL Database to replace a CLR function that accessed data in Azure Blob Storage. Basic overview of process:
- Use extended events file names to make read and write requests. You can create one file per request and encode the request information in the file name.
- Use Azure Event Grid to trigger C# function execution whenever a new file is created. Your function code interacts with Azure Blob Storage and returns the necessary data to your Azure SQL Database.
As a first step, create an extended event session with an event condition that will never trigger and without any targets. I used user_event with an impossible filter value for event_id.
Detailed steps for making a read request for a file:
- Take your preferred concurrency protection lock to serialize file creation (
sp_getapplock is a common choice).
- Use
ALTER EVENT SESSION ... ON DATABASE ADD TARGET package0.event_file(SET filename=N'... to create a file. The file name should contain the information necessary to identify the requested file. In our application we use a GUID to identify files so we include the GUID in the file name.
- Use
ALTER EVENT SESSION ... ON DATABASE DROP TARGET package0.event_file; to remove the file target within SQL Server (the file will persist in Azure Blob Storage).
- Release your concurrency protection lock.
- Wait in a loop until the requested data shows up in a cache table.
- While the T-SQL code waits, the C# function triggers off of the file creation. It parses the file name, make the API request to get the file from Azure Blob Storage, and writes the file data to the cache table within Azure SQL Database. In our development environment we experienced typical latency of around 500 ms for this step.
- The T-SQL wait loop ends and your code now has access to the file data within Azure SQL Database.
Detailed steps for making a write request for a file:
- Write the file data along with an identifying ID to a cache table.
- Take your preferred concurrency protection lock to serialize file creation (
sp_getapplock is a common choice).
- Use
ALTER EVENT SESSION ... ON DATABASE ADD TARGET package0.event_file(SET filename=N'... to create a file. The file name can be anything because it just informs your C# code that there’s something to process in the cache table. There’s no need to parse any information from the file name in the C# code.
- Use
ALTER EVENT SESSION ... ON DATABASE DROP TARGET package0.event_file; to remove the file target within SQL Server (the file will persist in Azure Blob Storage).
- Release your concurrency protection lock.
- Wait in a loop until the requested data is deleted from the cache table.
- While the T-SQL code waits, the C# function triggers off of the file creation. It gets the file data from Azure SQL Database, makes the API request to write the file to Azure Blob Storage, and deletes the row from the cache table to signal success.
- The T-SQL wait loop ends and your code proceeds now that the file has been written to Azure Blob Storage.
As an aside, it’s theoretically possible to use sp_trace_generateevent to pass up to 8000 bytes of binary data to an extended event target file. However, extended events are buffered to files and you will likely experience high latency. It is even possible for events to be lost and not written to the file at all. Avoiding these issues is why we went with the approach that creates a single 4 KB file per file request.
Obviously this is a horrible way to abuse Extended Events but this was the best quick fix solution that we found.
Have you ever seen a solution so ugly that it wraps all the way around and starts to look beautiful? Sure, I wouldn’t recommend implementing something like this if you had any other option. But sometimes there’s simply no better option. Anyway, I posted that answer to give a faint sliver of hope to the next poor soul who was trying to solve the same problem. If it inspires someone else to find a solution, that’s awesome. I contribute to stack exchange solely to help others.
Apparently, the Real Problem Was Trying to Help
Some people contribute to the site for other reasons, such as this guy:
I tried to reply with a comment but stack overflow didn’t let me do it. Seemed to be some kind of bug with the site. I’ll implement another ugly workaround and respond here instead.
The question on stack overflow was posted in July 2022 and has 492 views and +3 net upvotes right now as I write this blog post. I can’t believe that I have to explain this, but I ended up at this “old” stack overflow question because that’s where google took me after trying to find a solution for the same problem. Who the hell cares how old the question is? Would the question somehow be better if I called in a favor with Aaron Bertrand and asked him to edit the database to change the year to 2025 instead of 2022? There’s even a meta post about answering old questions. The overwhelming consensus is that it’s perfectly okay to answer old questions.
Besides, are you sure that you want to discount the value of old questions and answers? Here’s one of your top ranked answers:
I got goosebumps while reading it. Truly extraordinary.
With respect to the question I answered, it is perfectly answerable and meets all of the necessary qualifications for a stack overflow question:
The OP doesn’t know how to do something within Azure SQL Database. What is the point of demanding a minimally reproducible example when he doesn’t know how to do it? That’s like asking someone to prove a negative. What do you expect him to provide? A code comment lamenting that he doesn’t know how to do it?
Finally, I followed the principles documented at how to answer while constructing my answer. There is nothing wrong with writing a “non-trivial” or “detailed” answer. There’s even a summary at the top. If you don’t like my answer, great, go down vote it. Please do anything other than arrogantly tut-tutting at me in a comment in a way that’s contrary to stack overflow’s own published principles. Not that it matters because I got no interest in playing unpaid janitor, but I don’t even have permission to close-vote the answer. I just want to help people. Maybe use some of your “community energy” to actually help people? A “Principal Software Engineer at Microsoft” surely can do better than one answer in the last year, right?
Big Data
Perhaps my experience was caused by a difference in culture between the DBA stack exchange and stack overflow. After all, the stats seem to speak for themselves:
- DBA stack exchange: ~0 unfriendly comments on 459 answers = 0% unfriendly rate
- stack overflow: 1 unfriendly comment on 1 answer = 100% unfriendly rate
Database administrators might not be so bad after all!
Final Thoughts
It’s wild how people act on the internet even while presenting their full name, photo, and their workplace. Some people should maybe stick to answering questions about how to reset passwords and leave the technical questions to others. This experience honestly lowered my opinion of the average Microsoft employee. I’d write you guys off completely if Sean wasn’t so good at analyzing dumps. Thanks for reading!