Writing BLOBs from SQL Azure to Windows Azure Storage

[This article was contributed by the SQL Azure team.]

One of the more interesting things that we can do with the SqlStream class introduced in this blog post is to write to Windows Azure Storage from SQL Azure. Windows Azure storage provides persistent, durable storage in the cloud. To access the storage services, you must have a storage account, created through the azure portal.

Imagine that our company mandates images of their products to be stored in the database along with other product data like title, price, and description. One way to serve those images up on a web page is to retrieve them from the database and stream them to the browser.

However, a better idea might be to read them from the database on the first request, and write them to Windows Azure Storage where they are served up on every request. You would get the data integrity of keeping your images with your other product data in SQL Azure and the performance benefit of streaming a static file from Windows Azure Storage. This is especially beneficial if we can take advantage of the Windows Azure Content Delivery Network.

The following code sample implements this scenario using the Adventure Works database, which stores the product thumbnails as varbinary(max) . The code is designed to do these things:

• Run from Windows Azure platform.
• Serve images from a request to an ASP.NET page using a product id in the query string.
• Reads the image from the Products table in Adventure Works database and writes the thumbnail image to Windows Azure Storage if it doesn’t already exist
• Redirects the browser to Windows Azure Storage to serve the image.
• Uses streaming so that the whole images is not loaded into the memory space.

When the SQL Azure database, the Windows Azure Web Role, and the Windows Azure Storage container are in the same data center, there is no cost to transfer the data between SQL Azure and Windows Azure Storage. Note that this is not a direct transfer from SQL Azure to Windows Azure Storage, the code reads the image from SQL Azure and writes it to Windows Azure Cloud Storage. When you run the code the data passes through your Windows Azure role.

Code

 protected void Page_Load(object sender, EventArgs e)
{
    Int32 id = Int32.Parse(Request.QueryString["ProductId"]);

    CloudStorageAccount.SetConfigurationSettingPublisher(
        (configName, configSetter) =>
    {
         if (!configSetter(
             RoleEnvironment.GetConfigurationSettingValue(configName)))
             RoleEnvironment.RequestRecycle();
    });

    CloudStorageAccount cloudStorageAccount = 
             CloudStorageAccount.FromConfigurationSetting(
                "DataConnectionString");
    CloudBlobClient cloudBlobClient = 
             cloudStorageAccount.CreateCloudBlobClient();
    CloudBlobContainer cloudBlobContainer =
             cloudBlobClient.GetContainerReference("images");
    cloudBlobContainer.CreateIfNotExist();

    // WWB: Set Up Public Access To the Container 
    //(So That We Can Redirect Anyone To the Image)
    // Be careful of the putting confidential information on a public 
    // network, this example is for images.
    BlobContainerPermissions permissions = cloudBlobContainer.GetPermissions();
    permissions.PublicAccess = BlobContainerPublicAccessType.Container;
    cloudBlobContainer.SetPermissions(permissions);

    // WWB: Get Connection String from .cscfg
    String connectionString =
        RoleEnvironment.GetConfigurationSettingValue("SqlConnectionString");

    using (SqlConnection sqlConnection = 
          new SqlConnection(connectionString))
    {
        sqlConnection.Open();

        // WWB: Get The File Name From SQL Azure For This Id
        // The FileName is Stored In the Products Table
        String fileName = FetchFileName(sqlConnection, id);

        try
        {
            CloudBlockBlob cloudBlockBlob =
                cloudBlobContainer.GetBlockBlobReference(fileName);

            // WWB: Set the Blob Options So That We Only Upload Once 
            BlobRequestOptions blobRequestOptions = new BlobRequestOptions();
            blobRequestOptions.AccessCondition = 
                AccessCondition.IfNoneMatch(“*”);

            // WWB: Open a Stream to SQL Azure
            using (SqlStream sqlStream = new SqlStream(
                sqlConnection, "SalesLT",
                "Product",
                "ThumbNailPhoto",
                "ProductID", SqlDbType.Int, id))
            {
                // WWB: Upload To Azure Storage Using Our Stream
                cloudBlockBlob.UploadFromStream(sqlStream);
            }

            // WWB: Redirect This Request For An Image To Blob Storage URL
            Response.Redirect(cloudBlockBlob.Uri.AbsoluteUri);

        }
        catch (StorageClientException storageClientException)
        {
            switch (storageClientException.ErrorCode)
            {
                case StorageErrorCode.ConditionFailed:
                case StorageErrorCode.BlobAlreadyExists:
                    break;
                default:
                    throw;
            }
        }
    }
}

private String FetchFileName(SqlConnection sqlConnection, Int32 id)
{
    String sql = "SELECT [ThumbnailPhotoFileName] FROM" +
        "[SalesLT].[Product] WHERE [ProductID] = @Id";
    using (SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection))
    {
        sqlCommand.Parameters.AddWithValue("@Id", id);
        using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
        {
            if (!sqlDataReader.HasRows)
                throw (new
                  ArgumentException("Id Not Found", 
                     "id"));

            sqlDataReader.Read();

            return 
             ((String)sqlDataReader["ThumbnailPhotoFileName"]);
        }
    }
}

Using the Code

This code is very universal and can be used in other scenarios. One interesting idea is to run this from a locally hosted IIS server using a local SQL Server database. The code would read your local SQL Server, and push the images in your database to the Windows Azure Storage where they could be served off the Windows Azure CDN.

The first request is going to take longer than all the other requests, since it has to read from SQL Azure and write to Windows Azure Storage. Another idea is that you could preload Windows Azure Storage so that the first request does not take as long to respond. If you are uploading images in your Windows Azure Web Role, you could write to Windows Azure Storage after the upload. This would populate the image data in two places, SQL Azure along with the product data and the image container on Windows Azure Storage.

Summary

Do you have questions, concerns, comments? Post them below and we will try to address them.