Windows Azure SQL Database Marketplace
[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:
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.
protected void Page_Load(object sender, EventArgs e)
Int32 id = Int32.Parse(Request.QueryString["ProductId"]);
(configName, configSetter) =>
CloudStorageAccount cloudStorageAccount =
CloudBlobClient cloudBlobClient =
CloudBlobContainer cloudBlobContainer =
// 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;
// WWB: Get Connection String from .cscfg
String connectionString =
using (SqlConnection sqlConnection =
// WWB: Get The File Name From SQL Azure For This Id
// The FileName is Stored In the Products Table
String fileName = FetchFileName(sqlConnection, id);
CloudBlockBlob cloudBlockBlob =
// WWB: Set the Blob Options So That We Only Upload Once
BlobRequestOptions blobRequestOptions = new BlobRequestOptions();
// WWB: Open a Stream to SQL Azure
using (SqlStream sqlStream = new SqlStream(
"ProductID", SqlDbType.Int, id))
// WWB: Upload To Azure Storage Using Our Stream
// WWB: Redirect This Request For An Image To Blob Storage URL
catch (StorageClientException storageClientException)
private String FetchFileName(SqlConnection sqlConnection, Int32 id)
String sql = "SELECT [ThumbnailPhotoFileName] FROM" +
"[SalesLT].[Product] WHERE [ProductID] = @Id";
using (SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection))
using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
ArgumentException("Id Not Found",
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.
Do you have questions, concerns, comments? Post them below and we will try to address them.