Blob leases: how to break them from SQL Server (using SQL CLR and Windows Azure REST API)
SQL 2014 will give you a terrific feature: SQL XI. It will allow you to store your datafiles and tlogs directly in Windows Azure Page Blobs. You can read about it in this great post: http://blogs.msdn.com/b/igorpag/archive/2013/10/23/create-a-sql-server-2014-database-directly-on-azure-blob-storage-with-sqlxi.aspx. As you can see, however, SQL Server places a lease on each blob in use. Think about Azure leases as the disconnected (aka in cloud) way of getting an exclusive lock on a file (aka blob). You can read more about it in this blog post: http://blogs.msdn.com/b/windowsazurestorage/archive/2012/06/12/new-blob-lease-features-infinite-leases-smaller-lease-times-and-more.aspx. For our purposes suffice to say that anybody with proper credentials can place an infinite exclusive lock on your blobs. While SQL Server would not do this – it will use renewable leases that will last 60 seconds at maximum (see http://msdn.microsoft.com/en-us/library/windowsazure/ee691972.aspx) – it’s still worth noting that there is a specific REST API call to call in order to break a lease. This call is documented here: http://msdn.microsoft.com/en-us/library/windowsazure/ee691972.aspx. As you know be reading this blog, it’s very easy to call REST APIs from SQL CLR. All we have to do is to format the required HTTP headers properly. Before moving to the code, however, I would stress the fact that even the break call can have a configurable grace period. Let me quote the official documentation:
[…]
Break: Breaks the lease, if the blob has an active lease. Once a lease is broken, it cannot be renewed. Any authorized request can break the lease; the request is not required to specify a matching lease ID. When a lease is broken, the lease break period is allowed to elapse, during which time no lease operation except break and release can be performed on the blob. When a lease is successfully broken, the response indicates the interval in seconds until a new lease can be acquired.
A lease that has been broken can also be released, in which case another client may immediately acquire the lease on the blob.
[…]
In other terms: during the breaking grace period the lease can only be released or can be broken again (shortening its breaking time) but it can’t be renewed.
Note that nothing prevents the creation of a new lease.
Using our Azure SQLCLR library we can find the lease status of our blob in a very easy way. For example:
In my sample I have a blob (called txtfromsqltoo.txt) that has a valid lease without expiration (the infinite value of LeaseDuration field). Trying to delete that blob would give us:
What we want is a Stored Procedure to call to tell Azure to break the lease. Something like:
Then, after the grace period (in the previous image I’ve specified 15 seconds), the lease would be broken:
Allowing me to delete the blob:
Here is the code for the REST API call:
public static Dictionary<string, string> LeaseBlob(
string accountName, string sharedKey, bool useHTTPS,
string container,
string blobName,
Enumerations.LeaseOperation leaseOperation,
Guid? leaseId = null,
int? leaseBreakPeriod = null,
int? leaseDuration = null, // use -1 for infinite
Guid? proposedLeaseId = null,
int timeoutSeconds = 0,
Guid? xmsclientrequestId = null)
{
string strUrl = string.Format("{0:S}/{1:S}/{2:S}", GetBlobStorageUrl(useHTTPS, accountName), container, blobName);
strUrl += "?comp=lease";
if (timeoutSeconds > 0)
strUrl += string.Format("&timeout={0:S}", timeoutSeconds.ToString());
System.Net.HttpWebRequest Request = (System.Net.HttpWebRequest)System.Net.HttpWebRequest.Create(strUrl);
Request.Method = "PUT";
Request.ContentLength = 0;
#region Add HTTP headers
string strDate = DateTime.UtcNow.ToString("R");
Request.Headers.Add("x-ms-date", strDate);
Request.Headers.Add("x-ms-version", "2012-02-12");
if (leaseId.HasValue)
Request.Headers.Add("x-ms-lease-id", leaseId.Value.ToString());
Request.Headers.Add("x-ms-lease-action", leaseOperation.ToString().ToLowerInvariant());
if (leaseBreakPeriod.HasValue)
{
if (leaseBreakPeriod < 0 || leaseBreakPeriod > 60)
throw new ArgumentException("lease period should be between 0 and 60 seconds (passed value is " + leaseBreakPeriod + ")");
Request.Headers.Add("x-ms-lease-break-period", leaseBreakPeriod.Value.ToString());
}
if (leaseDuration.HasValue)
{
if ((leaseDuration != -1) && (leaseDuration < 15 || leaseDuration > 60))
throw new ArgumentException("lease period should be between 15 and 60 seconds or -1 for infinite leases. (passed value is " + leaseBreakPeriod + ")");
Request.Headers.Add("x-ms-lease-duration", leaseDuration.Value.ToString());
}
if (proposedLeaseId.HasValue)
Request.Headers.Add("x-ms-proposed-lease-id", proposedLeaseId.Value.ToString());
if (xmsclientrequestId.HasValue)
Request.Headers.Add("x-ms-client-request-id", xmsclientrequestId.Value.ToString());
#endregion
Signature.AddAzureAuthorizationHeaderFromSharedKey(Request, sharedKey);
using (System.Net.HttpWebResponse response = (System.Net.HttpWebResponse)Request.GetResponse())
{
Dictionary<string, string> d = new Dictionary<string, string>();
foreach (string header in response.Headers)
{
d.Add(header, response.Headers[header]);
}
switch(leaseOperation)
{
case Enumerations.LeaseOperation.Acquire:
if(response.StatusCode != System.Net.HttpStatusCode.Created)
throw new Exceptions.UnexpectedResponseTypeCodeException(System.Net.HttpStatusCode.Created, response.StatusCode);
break;
case Enumerations.LeaseOperation.Break:
if (response.StatusCode != System.Net.HttpStatusCode.Accepted)
throw new Exceptions.UnexpectedResponseTypeCodeException(System.Net.HttpStatusCode.Accepted, response.StatusCode);
break;
case Enumerations.LeaseOperation.Change:
if (response.StatusCode != System.Net.HttpStatusCode.OK)
throw new Exceptions.UnexpectedResponseTypeCodeException(System.Net.HttpStatusCode.OK, response.StatusCode);
break;
case Enumerations.LeaseOperation.Release:
if (response.StatusCode != System.Net.HttpStatusCode.OK)
throw new Exceptions.UnexpectedResponseTypeCodeException(System.Net.HttpStatusCode.OK, response.StatusCode);
break;
case Enumerations.LeaseOperation.Renew:
if (response.StatusCode != System.Net.HttpStatusCode.OK)
throw new Exceptions.UnexpectedResponseTypeCodeException(System.Net.HttpStatusCode.OK, response.StatusCode);
break;
}
return d;
}
}
Remember to check for the right HTTP status code; it's operation dependent (as it should be in a RESTful interface).
Happy coding,