Get Started with SQL Azure: Resources

  • Article

Earlier this month, SQL Azure prices were drastically reduced, and a new 100mb for $5 bucks a month pricing level was introduced. This news has certainly gotten some folks looking at SQL Azure for the first time.  I thought I’d share some resources to help you get started with SQL Azure.

Unlike the new “Developer Centers” for .NET, Node.js, Java, and PHP on WindowsAzure.com, there does not appear to be a one-stop shop for finding all of the information you’d need or want for SQL Azure.  The information is out there, but it is spread around all over the place.

I’ve tried to organize these into three high-level categories based on the way one might think about approaching this platform:

  • What do I need to know about SQL Azure to get started?
  • Can I migrate my data into it?
  • How do I achieve scale with it?

Note: This is by no means an exhaustive list of every SQL Azure resource out there. You may find (many) more that I’m not aware of.  You also may come across documentation & articles that are older and possibly obsoleted by new features.  Be wary of articles with a 2008 or 2009 date on the by line. Smile

Getting Started

Understanding Storage Services in Windows Azure

Storage is generally provided “as-a-service” in Windows Azure.  There is no notion of running or configuring your own SQL Server ‘server’ in your own VM.  Windows Azure takes the hassle of managing infrastructure away from you.  Instead, storage services are provisioned via the web-based Windows Azure management portal, or using other desktop-based tools. Like in a restaurant, you essentially look at a menu and order what you’d like.

Storage services in Windows Azure are priced and offered independently of compute services.  That is, you do not have to host your application in Windows Azure to use any of the storage services Windows Azure has to offer.  For example, you can host an application in your own datacenter, but store your data in Azure with no need to ever move your application there too.  Exploring Windows Azure’s storage services is an easy (and relatively low-cost) way to get started in the cloud. 

There are currently three flavors of storage available in Windows Azure:

  • Local storage (in the compute VMs that host your applications)
  • Non-relational storage (Blobs, Tables, Queues – a.k.a. “Windows Azure Storage”)
  • Relational storage (SQL Azure)

For a high-level overview of these, see: Data Storage Offerings in Windows Azure

Note: This post is focused on resources for SQL Azure only. If you’re looking for information on the non-relational storage services (Blobs, Tables, Queues), this is post “is not the droids you’re looking for”.

Getting Started With SQL Azure

I started my quest to build this post at the new WindowsAzure.com (“new” in December 2011). Much of the technical content for the Windows Azure platform was reorganized into this new site.  Some good SQL Azure resources are here.  Others are still elsewhere.  Let’s get started…

Start here: 

  • What is SQL Azure? –  This page on WindowsAzure.com explains what SQL Azure is and the high-level scenarios it is good for.
  • Business Analytics – This page on WindowsAzure.com explains SQL Azure Reporting at a high-level.
  • Overview of SQL Azure – This whitepaper is linked from the “Whitepapers” page on WindowsAzure.com.  It appears to be older (circa 2009), but appears to provide a still relevant overview of SQL Azure
  • SQL Azure Migration Wizard (Part 1): SQL Azure – What Is It? – This screencast on Channel 9 by my colleagues Dave Bost and George Huey may have “Migration Wizard” in the title, but it goes here in the first section. These guys provide a good high-level overview of what SQL Azure is.

Get your hands dirty with the equivalent of a “Hello World” example:

BIG FLASHING NOTE #1:

You must have SQL Server Management Studio 2008 R2 SP1 to manage a SQL Azure database!  SSMS 2008 and SSMS 2008 R2 are just NOT good enough. If you don’t have SSMS 2008 R2 SP1, it will cause a gap in the space time continuum!  The errors you will receive if you don’t have SSMS 2008 R2 SP1 are obscure and not obvious indicators of the problem.  You may be subject to losing valuable hours of your personal time seeking the correct solution.  Be sure you have the right version.

BIG FLASHING NOTE #2:

You CAN run SQL Server Management Studio 2008 R2 SP1 even if you’re NOT running SQL Server 2008 R2 SP1.  For example, if you need to still run SQL Server 2008 R2, 2008, or older edition, you can install SSMS 2008 R2 SP1 side-by-side without impacting your existing database installation.  Disclaimer: Worked on my machine. 

BIG FLASHING TIP:

How can I get SQL Server Management Studio 2008 R2 SP1?

Unfortunately, I found it quite difficult to parse through documentation to find the proper download for this.  Searching for the “ssms 2008 r2 sp1 download” on Google or Bing will give will present you with Microsoft Download center pages that have multiple file download options.  I present you with two options here:

  • Microsoft SQL Server 2008 R2 SP1 – Express Edition – This page contains multiple download files to install the Express edition of SQL Server.  The easiest thing to do here is download either SQLEXPRWT_x64_ENU.exe or SQLEXPRWT_x86_ENU.exe depending on your OS-version (32 vs 64 bit).  These files contain both the database and the management tools.  When you run the installation process, you can choose to install ONLY the management tools if you don’t want to install the database on your machine.
  • Microsoft SQL Server 2008 R2 SP1 – This page contains multiple download files to install just SP1 to an existing installation of SQL Server 2008 R2.  If you already have SQL Server Management Studio 2008 R2, you can run SQLServer2008R2SP1-KB2528583-x86-ENU.exe or SQLServer2008R2SP1-KB2528583-x64-ENU.exe, depending on your OS version (32 or 64 bit) to upgrade your existing installation to SP1.

The reason I call so much attention to this issue is because it is something that WILL cause you major pain if you don’t catch it.  While some documents call out that you need SSMS 2008 R2 SP1, many do not provide the proper download links and send you on a wild goose chase looking for them.  Thank me.  I’ll take a bow. Smile

The next place I recommend spending time reading is the SQL Azure Documentation on MSDN.

Content here is broken down into three high-level categories:

You can navigate the tree on your own, but some topics of interest might be:

  • SQL Azure Overview - I’m often asked what’s the difference between SQL Azure & SQL Server. This sheds some light on that.
  • Guidelines & Limitations – This gets a little more specific on SQL Server features NOT supported on SQL Azure.
  • Development: How-to Topics – There’s a smorgasbord of “How To” links here on how to connect to SQL Azure from different platforms and technologies.
  • Administration – All the details you need to know to manage your SQL Azure databases.  See the “How-to” sub-topic for details on things like backing up your database, importing/exporting data, managing the firewall, etc.
  • Accounts & Billing in SQL Azure – Detailed info on pricing & billing here.  (Be sure to see my post clarifying some pricing questions I had.)
  • Tools & Utilities Support – Many of the same tools & utilities you use to manage SQL Server work with SQL Azure too.  This is a comprehensive list of them and brief overview of what each does.

Windows Azure Training Kit – No resource list would be complete without the WATK! The WATK contains whitepapers, presentations, demo code, and labs that you can walkthrough to learn how to use the platform. This kit has grown so large, it has its own installer! You can selectively install only the documentation and sample labs that you want.  The SQL Azure related content here is great!

Migrating Your Data

The resources in the previous section should hopefully give you a good understanding of how SQL Azure works and how to do most basic management of it.  The next task most folks want to do is figure out how to migrate their existing databases to SQL Azure. There are several options for doing this.

Start here: Migrating Databases to SQL Azure – This page in MSDN provides a high-level overview of the various options.

Three migration tools you may find yourself interested in:

  • SQL Azure Migration Wizard – The SQLAzureMW is an open source project on CodePlex.  It was developed by my colleague George Huey. This is a MUST have tool in your toolbox! 

    SQLAzureMW is designed to help you migrate your SQL Server 2005/2008/2012 databases to SQL Azure. SQLAzureMW will analyze your source database for compatibility issues and allow you to fully or partially migrate your database schema and data to SQL Azure.

    SQL Azure Migration Wizard (SQLAzureMW) is an open source application that has been used by thousands of people to migrate their SQL database to and from SQL Azure. SQLAzureMW is a user interactive wizard that walks a person through the analysis / migration process.

  • SQL Azure Migration Assistant for MySQL

  • SQL Azure Migration Assistant for Access

Channel 9 SQL Azure Migration Wizard (Part 2): Using the SQL Azure Migration Wizard - For people who are new to SQL Azure and just want to get an understanding of how to get a simple SQL database uploaded to SQL Azure, George Huey & Dave Bost did a Channel 9 video on the step by step process of migrating a database to SQL Azure with SQLAzureMW.  This is a good place to to get an idea of what’s involved.

Tips for Migrating Your Applications To The Cloud – MSDN Magazine article by George Huey & Wade Wegner covering the SQLAzureMW.

Overview of Options for Migrating Data and Schema to SQL Azure – I found this Wiki article on TechNet regarding SQL Azure Migration.  It appears to be from 2010, but with updates as recent as January 2012. The information here appears valid still.

Scaling with SQL Azure

Okay, you’ve figured out how to get an account and get going. You’re able to migrate your existing databases to SQL Azure.  Now it’s time to take it to the next level: Can you scale?

Just because you can migrate your existing SQL Server database to SQL Azure doesn’t mean it will scale the same.  SQL Azure is a multi-tenant “database-as-a-service” that is run in the Azure datacenters on commodity hardware.  That introduces a new set of concerns regarding performance, latency, competition with other tenants, etc.

I recommend watching this great video from Henry Zhang at TechEd 2011 in Atlanta, GA:

There is a 150GB size limit on SQL Azure databases (recently up from 50GB). So what do you do if you’re relational needs are greater than that limit?  It’s time to learn about art of sharding and SQL Azure Federation.  While SQL Azure may take away the mundane chores of database administration (clustering/replication/etc), it does introduce problems which require newer skillsets to solve.  This is a key example of that.

Start off by watching this video by Chihan Biyikoglu from TechEd 2011 in Atlanta, GA:

In this talk Chihan explains what a database federation is, and how they work in SQL Azure.

Note: This talk is from May 2011 when SQL Azure Federations were only available as a preview/beta. The SQL Azure Federations feature was officially released into production in December 2011. So there may be variances between the May video and current service feature. He released a short updated video here.

Next read George Huey’s recent MSDN Magazine Article:

  • SQL Azure: Scaling Out with SQL Azure Federation In this article, George covers the what, the why, and the how of SQL Azure Federations, and how SQL Azure Migration Wizard and SQL Azure Federation Data Migration Wizard can help simplify the migration, scale out, and merge processes. This article is geared to architects and developers who need to think about using SQL Azure and how to scale out to meet user requirements.  (Chihan, from the previous video, was a technical reviewer on George’s article!)

Follow that up George Huey as a guest on Cloud Cover Episode #69:

  • Channel 9 Episode 69 - SQL Azure Federations with George Huey – George covers a lot of the same information with Wade Wegner in a Channel 9 Cloud Cover session on SQL Azure Federations. As the article above, this video covers what SQL Azure Federations is and the process of migrating / scaling out using SQL Azure Federations and some of the architectural considerations that need to be considered during the design process.

As a follow-up to the SQL Azure Migration Wizard, George has also produced another great tool:

  • SQL Azure Federation Data Migration Wizard (SQLAzureFedMW)
    SQL Azure Federation Data Migration Wizard simplifies the process of migrating data from a single database to multiple federation members in SQL Azure Federation.

    SQL Azure Federation Data Migration Wizard (SQLAzureFedMW) is an open source application that will help you move your data from a SQL database to (1 to many) federation members in SQL Azure Federation. SQLAzureFedMW is a user interactive wizard that walks a person through the data migration process.

That about wraps up my resource post here.  Questions?  Feedback?  Leave it all below in the comments!  Hope this helped you on your way to learning SQL Azure.