Call Azure REST API from SQL Server

Call Azure REST API from SQL Server

Microsoft Azure platform can be managed in a number of ways. You can use PowerShell, you can use the Management Portal or you can use the http REST interface. If you happen to manage a lot of Azure items (containers, blobs, etc…) you might have to do some serious PowerShelling. But would not be wonderful to be able to control Azure using our beloved TSQL? In this post I will show you how easy is to wrap the Azure REST API in a SQLCLR dll in order to query Azure using TSQL.

First thing first: let me show you what we want to achieve in this sample (bear in mind this is just sample code to get you started). Suppose we have an Azure storage account; we want to find all the unused blobs in all its containers.

Ideally – as DBAs – we want to have a function that would query Azure for us and give us the results in a table. Something like:

We expect to find all the blobs not modified in the last 30 days, sorted by size:

It’s a dream you might say :). In fact it’s just CLR table-valued function built around a C# DLL. Obviously, our assembly will need the external access security level to call the REST API (http/https). We can do it in two ways: using a asymmetric key bound login (preferred way) or setting the TRUSTWORTHY attribute on the database that will load the assembly.

Notice the GRANT EXTERNAL ACCESS ASSEMBLY privilege granted to our asymmetric key login. Once we have the assembly loaded we just need to wrap the C# functions to a TVF. It’s just a matter of standard T-SQL stuff (some return nodes of the List Blobs REST API are left off for brevity):

Here there are three things worth noting. First, many Azure REST methods might return you a subset of the result. In that case you are required to call the method again adding a marker. This way the next recordset will be the continuation of the previous one. In our sample code we just keep calling the same method until the EnumerationResults/NextMarker node is empty. You can read more about it here: http://msdn.microsoft.com/en-us/library/windowsazure/dd135734.aspx. The documentation says:

Note that the value of NextMarker should be treated as opaque.

That is why we should not expose it to the callers.

Second is the decoration needed to instruct the SQLCLR how to compose the TVF. You need to specify the FillRowMethodName attribute. It must point to a static method that will be called for each item in your IEnumerable result. Here is the – simplified – sequence diagram:

Notice how your DLL gets called for each row. The “Populate output parameters” in our case is very simple:

Below you will find the C# source code to get you started. Remember, this code is just a sample written for educational purposes. Of course there are many methods in the Azure REST API but with little effort you can automate most tasks using your easier-to-understand T-SQL Code.