| Comments

For the past 6 years I’ve run this blog on the Subtext project (an Open Source software project which is an ASP.NET blog framework).  It has served me very well with being flexible and allowing me to customize things that I want.  It is based on SQL Server and uses stored procedures and relational database “stuff” to accomplish the goals of the design. 

Recently I saw the news from Scott Guthrie about the reduction in pricing on some Windows Azure products, introducing a 100MB pricing option for SQL Azure which you can read about here and here.  I thought this would be a good time to start looking at moving some of my infrastructure of my blog “to the cloud” so I started looking at the details…after all, $5/month seemed reasonable for my database.

Now, I’m only talking about the database portion here…not the ASP.NET application.  Right now I’m happy being in complete control of my own server and have no need for moving the .NET site at this time.  Perhaps in the future, but given the pricing on app hosting, it just doesn’t make sense for my little site here.  I have, however, had some issues with the database infrastructure over the past year with some latency, outages and just age of the server it is hosted on for my site (not the same as my web app).  Because of this I was investigating SQL Azure.

I’m happy to say that this site now runs the data side on SQL Azure…but the process was not without hiccups.  I wanted to share some frustrations I had so that you might be able to avoid them.  My comments below are related to migrating an existing SQL Server database to SQL Azure and not creating a new one from scratch.

My environment

To set the stage, it is fair to note my particular environment for migration.  My database resides on a Windows 2003 server and is a SQL Server 2005 instance/database.  My web application also resides on a Windows 2003 server and is configured in a virtual LAN to have access to the database infrastructure server. 

Yes I realize these are not the “latest” in server products, but I also don’t think they are too old relatively speaking.  They work to my needs and I hadn’t needed any of the new features for a long while (I’ve been desiring to move to Windows 2008 and ASP.NET MVC for Subtext though).

Getting the necessary tools

I chuckled a bit at how many client tools I actually needed to complete this process of moving things to the ‘cloud’ during this process.  I’ve had experience in the past moving SQL databases around using SQL Management Studio (SSME) and other script methods.  I thought this would be very similar using the import/export capabilities nicely provided in SSME. 

I was wrong.

In doing this I started on the Windows Azure web site for some data.  Now, I clearly didn’t navigate deep enough (more on that in a moment) because I wasn’t finding what I was looking for in a “migrating an existing database to Azure” article.  So I did what anyone else would do and searched the web for ‘migrate sql to azure’.  I was presented with a first top choice titled Migrating Databases to SQL Azure.  This provided me with a few options, but no really good end-to-end example.  Truth be told, I tried a few of these and was completely frustrated because the details were not complete.

In the end I found what I needed in the following tools that were essential to me.  Here were the tools that you will need:

The last 2 bullets (direct connectivity) are needed to ensure you can do this from your machine.  The last one – access to your SQL Azure server – isn’t entirely intuitive that you need it set up in advance, but you do.  Another thing to note is that in order for the SQL Azure Migration Wizard to work, your SSME installation must be 2008 R2 SP1.  The link above is to SP1 but I could not find a download for just the SSME tool for R2 SP1, so I downloaded the full SP1 of SQL Express With Tools and just installed the management studio.

Setting up the connections

Once you configure your Windows Azure account, you’ll need to create a SQL Azure server.  This initially confused (and concerned) me because I only wanted my 100MB database account and not anything that will bump up my compute time costs.  However I’ve been assured this just represents the “instance” of your DB and not a compute server.  So you’ll need to configure that first.

To do this you’ll login to your account at https://windows.azure.com and select the subscription server you set up for SQL Azure.  You’ll then want to add a firewall rule to your server.  The “Add” button shows your current IP address so you can add just that if you’d like.

SQL Azure Server Configuration

This takes a few minutes to propagate so I’d do this first.  Once you have this you can configure this connection in SSME to connect to.  Your server name is on the right hand side of this screen (blurred for my account) and is something like XXXXXX.database.windows.net.  In SSME you will connect to this as XXXXXX.database.windows.net,1433 as the server name.

Start by adding a connection in SSME for your current database and your SQL Azure server you just configured.  Do not create any database yet at this time on SQL Azure.

Exporting the current database schema

I tried a few different methods, but by far the easiest was the Data-tier Application method.  To do this go to your existing database in SSME and right-click, choose Tasks and then Extract Data-tier Application:

Extract Data-tier Application

This will create a “dacpac” file via the wizard you will be presented with.  Essentially this extracts the schema and objects for you.  Now why this instead of just a normal TSQL script?  Your mileage may vary, but this was the only method I had real success with in my configuration.

Creating the database from the DACPAC

Once you have the exported .dacpac file go back to SSME and on your SQL Azure instance right-click and choose Deploy Data-tier Application:

Deploy Data-tier Application

This will create the database and schema for you, but not any data.  This is another wizard that walks you through this process.  Once complete you should have a new SQL Azure database matching the schema from your original one.

Migrating the data

Once I had the new schema in my SQL Azure database I was ready to move the data.  This is where the SQL Azure Migration Wizard comes into play.  Launch that tool and you will be asked to choose a source and destination target.  For the source, connect to your original database and after specifying the connection information (I chose just the db, not Master and was fine), click the Advanced button and change to Data only:

Migrating data only

You will then start the process and notice that it is basically doing bcp.exe commands for you to extract the data:

Migrating data

Once this is done you will select the destination – your SQL Azure DB that was just created.  Now since SQL Azure may not have the same features as your source database there may be some conflicts.  The migration wizard tool will stop you on errors on the bcp import commands and give you a chance to resolve/continue those conflicts.  As an example, some of my clustered indexes didn’t transfer over in the schema creation (no idea why) and I needed to re-create those before two tables could be imported.  No big deal, but it was cool that the import was “paused” for me with a Retry function so that I could do this without starting all over.

Migration clean-up

There were a few things that didn’t migrate well for my Subtext experiment here.  First, even though stored procedures in my source database had correctly identified some parameters as ‘out’ it seems they didn’t transfer well.  I’m not sure if this is an issue with the Data-tier Application export or something in SQL Azure, but it required me to go back and ALTER those procs with the correct flag of OUTPUT.  Luckily I could actually do this through the Silverlight application for managing my database just fine after the database was configured:

Alter stored procs in portal

Logins also didn’t transfer, but users did.  My web app doesn’t use an admin user so I wanted to make sure I had a correct login for that.  Through SSME connected to SQL Azure, there is not the GUI interface for doing these and you have to use all TSQL scripts.  The commands in SSME when connected to SQL Azure DB will generate the template for you and you just put in the right values.

Subtext uses some system stored procedures in some admin screens that I use and sp_spacesed is not available.  Luckily others have seen this and I just needed to modify some areas to use similar scripts.

General frustrations

Some of you may be looking at this and wondering why I had so much trouble.  Why didn’t I just read this document (don’t you hate me for putting that last) that walked me through similar steps (minus migration wizard)?  That would be a good question.  That document didn’t show up in search for me anywhere and it is under the “Develop” section of the Azure information site.  I didn’t think to look for as I wasn’t developing anything just yet.  I only found out about that link from my friend Peter Laudati.  It would have saved me some time, but not most.  The first link on that site shows to download SSME R2 RTM…but the migration wizard requires SP1 and without it you’d see an error message about some missing SQL types (Smo). 

Why didn’t I just use the migration wizard for schema *and* data…why two steps?  That’s a good question.  Frankly I don’t know why the migration wizard itself didn’t work for me for the wholesale schema+data approach.  It could be my SQL2005 version or something.  But for me, it just didn’t work.  The steps above were the only paths that worked for me and my SQL2005 database to migrate.

Summary

While I was successful in finally migrating my database, discovering the proper steps wasn’t as in-my-face as it should be.  There are pieced together areas about migrating, but the MSDN article I expected to be a more full-featured end-to-end example.  I remember there was a big push for Access->SQL Server and there was an “upsizing wizard” provided from Microsoft to move that data.  I wish that Azure had something more one-stop like this.  The migration wizard seems like a first approach, but didn’t work as smooth as a one-stop solution for me…hopefully it does for others.  It would have also been nice to have this actually integrated into the tools of the Azure portal.  Let me provide a connection to my existing database and just magically create the Azure one for me – that would have been awesome.

Once my migration was complete, everything in SQL Azure is working with my app as expected and the tools are familiar to me to do any maintenance on the data that I need.  I like the Silverlight management interfaces via the web that I can get a snapshot of my datbase at anytime and they even display query performance on the dashboard which is cool.  I don’t have access to the log files nor am I able to run DBCC commands anymore, but I’m trusting that SQL Azure is more efficient than my own DBA skills of old and that my database will be managed effectively with regard to these items. 

Hope this helps.

| Comments

Here’s how it started…

Lisa (my wife) [shouting from office into the kitchen]: Tim, what’s this Amazon charge for $193?
Me [thinking what I may have purchased and not remembered]: Um, don’t know…let me look.

I then logged into my Amazon account to see what order I may have forgotten.  Surely I didn’t order $200 worth of MP3…that’s ridiculous.  Sure enough nothing was there.  Immediately I’m thinking fraud.  I start freaking out, getting mad, figuring out my revenge scheme on the scammer, etc.

Then it hit me: Amazon Web Services account.

The Culprit

Sure enough I logged in and my January 2010 billing account was $193 and change.  Yikes.  Well, I could let the (what has been averaging) $30 or so charge slide under the family CFO radar for a while…but this $193 charge…the chief auditor herself caught that one.

So I panicked.  I needed to figure out where/what the spike was.  I logged into the Amazon Web Services management console (I only use the S3/CloudFront storage in their services right no) to see what was going on.  I see ‘Usage Reports’ and click.  I’m met with essentially a bunch of useless data really.  No offense to Amazon, but really the usage reports weren’t really helpful at all.  First, they gave me a Resource ID which I thought would represent the URI I was looking for.  Nope, Resource ID == Bucket.  And they didn’t even put the bucket name in the report!

For some perspective, here’s essentially what I’m used to – here’s my December 2009 billing statement details:

December 2009 S3 CloudFront Billing

Anyhow, after some hunting it was obvious that I wasn’t going to figure out what bucket objects/unique URIs were causing my spike.  This was primarily because I didn’t have logging turned on at all on my buckets.  I had in the past but really didn’t think I needed it so I turned it off.

I was wrong – go now and enable logging.

While I was searching for a solution to understand my traffic, I was curious for where my traffic was.  Like I said, I’d been averaging (actually *peaking*) at about a $30 charge for the S3 hosting.

NOTE: I use S3 for all my image/screenshot/sample code file hosting.  I’ve invested in S3 for a long time and built my blogging workflow around it with building tools like S3 Browser for Windows Live Writer.

What was interesting was my most usage of my CloudFront data was coming from Hong Kong.  Compare to above the December 2009 billing to this January 2010 billing:

January 2010 Blling Statement

Yeah, that was my reaction too.  I went from roughly 40GB of transfer bandwidth to over 960GB in one month.  I suspected I knew what happened, but needed to confirm before I changed things. 

Implementing Logging for Statistics

The problem was that I didn’t have logging enabled and I was pretty much stuck.  I needed to get some data from the logs before being for sure.  I quickly found S3Stat and it appears to be the de-facto reporting for Amazon S3 log files.  I signed up for the free trial and generated a new access key to give them.

NOTE: They have a ‘manual’ option which means a lot more work.  I simply generated a NEW S3 access key for this specific purpose.  That way I didn’t have to give them my golden key I’ve been using in other places and can shut this off at any time without issue to my other workflows.

24 hours later, I had some reports.  Wicked cool reports.  Here’s a list of what I’m currently looking at:

  • Total hits, total files, total kbytes
  • Hits/files per hour/day
  • Hourly stats
  • Top 30 URIs
  • Top URIs by kbytes used
  • Top referrers (find out who’s using your bits without you knowing)
  • User agents
    Here’s a quick snapshot of one:
    S3Stat sample report image

Wow…honestly…THIS is what I was expecting when I see “usage” data reports.  S3Stat is awesome and you should use that now.  Yes, I’m buttering up to them…but they have a great tool here for $5/month if you are a heavy Amazon S3/CloudFront user.  Amazon frankly should just buy them and integrate this into their management console.  You can see other examples of their report outputs on their site at http://www.s3stat.com

What I also found out is that the tool I use for my desktop usage of S3/CloudFront (outside of my blogger workfow and S3Browser) has S3Stat integration built in!  I use CloudBerry’s S3 Explorer Pro for managing my S3 content.  It’s awesome and you should look at it.  When I look at the logging features in CloudBerry I see this:

CloudBerry S3Stat dialog

And after enabling the logging, within CloudBerry I can view the log data within the tool:

CloudBerry view logging

Summary

Wow, this is incredibly helpful and insightful data.  I now know who/how/when my cloud storage data is being used in various ways I can see the data.  S3Stat immediately showed me incredible value within less than 24 hours of enabling it.  I know can confirm the culprit of the burst of usage and plan accordingly.

Now, to be clear I’m not complaining about the cost of cloud storage.  That has been clear to me from the beginning.  Nothing is hidden and I’m not an idiot for not understanding it.  What I did not account for was the popularity of some files…and then the ones that just happened to be the largest.  I could not have personally thought I’d see a 920GB spike in one month of usage…but now I know…and have to alter some plans. 

Hopefully this is helpful for some who are just exploring cloud storage solutions/services.  Make sure you have instrumentation and logging capabilities turned on so you can identify and tune your situations.  For me, S3Stat and CloudBerry are winners for my personal usages.  If you are an Amazon S3 customer, I recommend looking at S3Stat and turning on logging immediately!


| Comments

Many have inquired if Silverlight Live Streaming had a replacement since the announcement of it being deprecated.  The SLS team blog pointed to Azure as a possible solution.  Since it doesn’t seem like anyone except James has really tried this, I decided to dust off my Azure account information and give it a try.

First, a note about SLS.  As I’ve said before I don’t think the offering was named very well from the start.  “Streaming” implies a specific technical connotation to most folks.  In fact, the media (or other files) hosted on SLS were never streamed as you might be thinking they were.  It was always a media file hosted on a content delivery network (CDN) and distributed delivered via basic progressive download…not streamed.  Now that we got that out of the way, let’s move on.

Setting up Azure

I’ll be honest.  I’m not sure how you go about getting an Azure token/invite at this point.  But you’ll need one.  Those who attended PDC08 already have access to one.  I believe MSDN subscribers will have access at some point but I don’t see it enabled on my account just yet either.  Bottom line is you’ll need an invitation token.  I cannot help you in this regard, so don’t even ask…I wish I could, but I can’t.

Once you have an Azure account, you want to create a Windows Azure Storage instance and give it a name.  I recommend not using special characters in the name as some of the tools I’m explaining below had problems with that.  I gave mine a simple name “timheuerblob1” and let it provision.

Azure Storage Account creation

Once you have that you’ll see a bunch of configuration screen details:

Azure configuration details

I’d recommend doing the following tasks first before you proceed.  In the bottom you’ll see an option to enable CDN.  For this purpose of hosting files/storage I recommend putting it on.  Additionally, this provisioning of CDN can take some time so do that first.  The next step is to configure your custom domain names (if you’d like) to map to the CDN and/or blob storage endpoints.  This is a simple/fast process but requires you to understand DNS entries and have the ability to modify your domain’s DNS information.  You can find step-by-step instructions at Steve Marx’s blog post here.  Without enabling this CDN feature, you really aren’t going to get the same level of scale as you did with SLS.

The key things you will need for the next part are the Primary Access Key and the Endpoints.  Make note of them.

Tools to upload content

Windows Azure blob storage is exactly what it says: storage.  Right now there aren’t any provided tools from Windows Azure to really manage the contents of the storage blob containers (unless I’m mistaken, and if so, please correct me).  There is an API for you to get access to and publish content, but if you’re wanting to use it like an SLS replacement you’re likely hoping for an easy tool to upload/manage the contents.

Enter two tools: Azure Storage Explorer and Cloud Storage Studio.  Both of these are Windows clients (WPF incidentally) and are installed on your machine.  Cloud Storage Studio actually has an online version if you wanted to use that offering.  I did not try that tool as I’m okay using a client tool.

Azure Storage Explorer is a CDDL licensed CodePlex project (which actually has no source code checked in at this point) provided by Neudesic – a Microsoft partner.  It is a simple UI that requires you to input your blob storage name, access key and endpoints in the Storage Settings dialog.  I initially had problems with this as my name had a special character in it.  I deleted my initial (and old) instance and recreated a new one in Azure and didn’t have problems after that.

Azure Storage Explorer screenshot

It was pretty simple to use and understand.  Create a container, upload stuff into it.  Done. 

Cloud Storage Studio is a commercial offering and unclear if it is going to be free always or just in CTP mode right now.  I couldn’t find information that jumped out at me on their site to let me know. 

Cloud Storage Studio screenshot

Cloud Storage Studio wouldn’t let me resize the window and that was frustrating.  Other than that it operated as expected, same thing – blob container select and upload.

While both tools were providing me basic blob container and upload capabilities, I felt the upload was slow (comparing to my Amazon S3 experience in creating a tool for content management).  I’m not sure if that was the app, Azure, or the nature of the API.  Also, neither tool seemed to enable me to set the content-type of the contents of the blob I was uploading.  I see this to be a problem for some.  The default of application/octet-stream isn’t going to work for all scenarios (for example I cannot use it to host XAPs and embed them because I would need to be able to set the content-type header).

Side note: Be sure to check out James Clarke’s CodePlex work on a Windows PowerShell script that will do encoding via Expression Encoder and then trigger a publishing plugin – such as one that will automatically upload to an Azure blob storage account.

Other than that, they served the need of filling the gap for a UI tool that already worked out the API stuff.

UPDATE: CloudBerry for Azure

I cannot believe I didn’t know about this one, since CloudBerry is what I use for my Amazon S3 storage and is AWESOME.  I was directed to CloudBerry for Azure Blob Storage (oddly by their competitor above) and I downloaded it immediately. 

CloudBerry Azure Blob Storage explorer

It met every expectation I had and has been the fastest client.  To me, the other clients must meet this bar of functionality *for blob storage interaction* to be considered for me to use.  The one thing it doesn’t have is custom CDN URI generation in its “WebURL” functionality – but I suspect this might be an Azure API limitation (I’m not familiar with the Azure API).

UPDATE 2: Cloud Storage Studio

Be sure to read the comments below from the team at Cloud Storage Studio.  They were kind enough to reach out to me to clarify some things above and listen to my feedback.  They are ACTIVELY looking to improve their product and provide value-add to Azure users/developers.  I sincerely appreciate when people reach out to their customers for feedback.  It’s been a great dialog with them helping them understand how I would use Azure and what my tools needs might be.

My Silverlight media test

Using my new found tools and some sample media files I uploaded a 720p high-def 30 second clip (30MB) to my blob storage account.  I then used put together a page with a simple player and pointed it to the Azure-hosted media.  I first pointed the media player to the basic blob endpoint.  The result of which is here: Non-CDN Hosted Media Playback.

Users who tested it with me said that the video buffered quite a bit and in remote areas (Australia) it took about 1.5 minutes to download the video.  This is again because this is still not streaming but just progressive download.

I then changed the media URL to the CDN version (after waiting 24 hours for propagation, etc. just to be sure) and have the CDN Hosted Media Playback.

I have to be honest and say that the CDN versions seemed to be better at consistent playback after the initial buffer of video (i.e., I received no more future buffering).  I’d be curious as to what other international people see on the CDN version as it would give a better view of how well the CDN delivers content globally.

Summary

I’m not sure if this will be a solution for everyone, but if you are looking for a Microsoft offering for cloud/CDN storage, give Azure a try.  NOTE: It is not a free service once it goes into production!  In fact, most CDN/cloud storage solutions you will find are not.  I’m currently using Amazon S3 for mine and have been for a long while.  It, too, is not free.  (Here’s the Amazon S3 CloudFront version of same media file.)  Again, here are the links to the three different video hosted locations/tests I did:

Either way, now that SLS is gone as a CDN/free hosted service where many were putting their media, you’ll have to look for another option.  Azure is one of those options.  Does it provide an automatic skin for a media player?  No.  You’ll have to do that yourself.  Is it streaming?  No, but neither was SLS.  Is there streaming/smooth streaming on the horizon?  I don’t know…but it would be cool if there was!

Hope this helps.

| Comments

I’ll be joining Dan Wahlin and Rob Bagby on 18 May in Phoenix for a Best of MIX event.  I’ll be presenting Silverlight 3 and building data-driven line-of-business applications.  All demo, all tech, all Visual Studio as my presentation tool :-).

Come out and join us for the event!

Best of MIX Agenda

Monday, May 18, 2009
1:00 PM - 5:00 PM
Wells Fargo Conference Center
100 West Washington Street, MAC S4101-017
Phoenix Arizona 85003

What’s New in Silverlight 3?

Are you interested in building business-focused Rich Internet Applications (RIAs)?  Would you like to take advantage of 3D in the browser, but assume it is too hard?  Have you wanted to take a Silverlight application offline?  Then this session is for you.  We will explore and illustrate the new features of Silverlight 3, including the following:

· Support for perspective 3D

· Offline Support

· .NET RIA Services which simplifies the traditional n-tier application pattern by bringing together the ASP.NET and Silverlight platforms

Building Web Applications with Windows Azure

This session will begin with a brief overview of Azure and discuss some of the announcements made at MIX.  We will then illustrate through demo how to build a Windows Azure application from the ground up.  We will illustrate how to consume Azure Table Storage, how to host services, web pages and Silverlight components, as well as how to deploy your solution to the cloud.

MVC 1.0 vs ASP.Net Webforms

Have you heard about the new ASP.NET MVC  framework from Microsoft and wondered what it was all about? Are you curious whether this replaces ASP.Net Web Forms?  In this session you will learn how to use the model-view-controller (MVC) pattern to take advantage of your favorite .NET Framework language for writing business logic in a way that is de-coupled from the views of the data.  In addition, we will talk about the pros and cons of both MVC and Web Forms, how to determine the best choice for a specific project, various techniques and patterns used to build MVC applications vs. Web Forms applications, and the implications for using each approach.

Register for the Event