The Intro to Windows Azure page on the Azure site gives a very good and clear overview of the different compute, data, networking, analytics, messaging, caching and identity solutions on offer.

• Compute/Execution Models
• Data storage
• Networking
• Business Analytics
• Underlying apps (Messaging, Caching, Identity)
• APIs (Marketplace, Store, Media, etc.)

Compute

Virtual Machines - These are entire virtual machines backed with APIs and golden images. VHDs can be used and transferred from onsite to cloud as needed. Anything you can think of using a server for can be done with Virtual Machines and they're completely customisable. You have the overhead of maintaining them though.

Web Sites - These are managed sites, much like you'd get from a website provider like GoDaddy or Fasthosts. They're managed by Azure and can be shared instances (so on the same box as other companies websites), or standard (one website per box). The standard versions can be scaled. These are managed by Azure infrastructure and provide APIs and support for Node.js, PHP and Python. Traffic is distributed and managed by Azure Web Sites. Once a site is running, they can be scaled out automatically or manually. They also support MySQL and WordPress, Joomla and Drupal. These are ideal for small websites created in Visual Studio and uploaded automatically or via API. Websites don't give you admin access to the box so you can't remote in - Cloud Services do give you these options.

Cloud Services - These are one of the most flexible solutions. These are web apps (web facing, hosted by IIS) and/or compute roles hosted in a managed execution environment (think automatically started console app). These roles are managed by Azure and can be scaled automatically or manually. These roles are restarted approx once per day and are automatically updated to the latest version of the operating system as they become available. You can remote to these boxes as an administrator.

Mobile Services - These are several APIs and tools which accelerate mobile development including native client libraries for Windows, iOS, Android and HTML. There are REST APIs for push notifications (SendGrid and Pusher) for each platform and tools to provision storage and databases. These tools have been created as components so you do not have to worry creating any underlying infrastructure yourself.

Data

In all cases, when data is stored, it is replicated across three different computers in the Azure datacentre to provide high availability. You can access the data using RESTful backed client libraries and so can be accessed from anywhere that has internet access (so from the instances, or a phone, or computer via the internet). These can be secured with passwords or certificates, or put behind your own protected endpoint.

SQL Databases - These are MS SQL databases but have a slightly reduced set of commands that can be used with them. They are scalable and are very powerful and fast. See SQL Server Feature Limitations (Windows Azure SQL Database) for more information on the reduced feature set. There are always workarounds for the slight limitations, so don't be put off.

Tables - These are key value NoSQL tables and are stored in Microsoft Azure Table Storage providers which is a RESTful API backed with native client libraries. They're very easy to use to find and store data. These can be searched very quickly.

Blobs - This is key/value storage for unstructured binary data (files). These are slower to search but can store anything.

Networking

Virtual Networking - You can extend your current network on demand to your own part of Azure, and you can bring up or tear down instances and storage as needed. Useful for small networks or providing intranet apps.

Traffic manager - Like AKAMAI and other routing providers, when you have global applications you may wish to shape traffic differently for each country or user profile. If your applications are stored in different datacentres around the world, Traffic manager allows you to direct client requests to certain datacentres.

Analytics

SQL Database Reporting - This is SQL Reporting Services (SSRS) on a hosted Windows Azure Virtual machine. When used in combination with Microsoft Office, you have a powerful way to create reports and charts and directly import them into Excel.

HD Insight - This is Microsofts implementation of Hadoop (MapReduce). HD Insight spreads the job across a configurable level of nodes and uses the Hadoop Distributed File System (HDFS) to share data. HD Insight clusters can be created and destroyed on demand so you will only be charged when you need them.

Messaging

Queues - You can think of Azure Queues as high availability distributed ordered lists, like a distributed MSMQ. There are RESTful backed client APIs for them. You can also query and add items on the fly. Queues can be created and destroyed programatically. They are extremely quick and are great for sending information from a source which does not need acknowledgement or where the volume will change over time. These are effectively store and forward queues, so you can get one or more Worker Roles to later grab the data and work with it.

Service Bus - These are similar to Queues but also provide a publish-and-subscribe model (queues just push and pop). The Service Bus has the notion of Topics which can be subscribed to by clients. Service Bus therefore allows one-to-many communcation and allows direct one-to-one communcation via its Relay Service, where messages can get through firewalls because both endpoints are connected to secure cloud endpoints. EasyJet use this to connect its halo systems from secure backends to terminals in the airport

Caching

Application Caching - There are a number of solutions here. This can be either single instance in-memory caching, multiple instance in-memory caching, or single/multiple filesystem backed caching. Caching is key value and can expire. You can opt for a single instance to reserve a percentage of space for caching, or multiple instances to reserve a percentage or spaces, or you can reserve an entire instance for distributed caching. See more at Windows Server AppFabric Caching Features

CDN - Content Distribution Network allows content to be cached closer to the end user. So if there's a server that's physically closer to the user, the content can come from that server, instead of another one on the other side of the world. Think Akamai

Identity

Windows Azure Active Directory - This is AD in Azure for cloud applications and it comes with a RESTful API. This isn't full blown AD, but if you need AD, you can use a virtual machine with AD installed on it.

Windows Azure AD Access Control - This provides a third party identity store and can be run standalone with certificates or can be extended to work with third party identity providers such as Facebook, Google and others. Access Control acts as an intermediary for the other providers and consolidates them all into a single access model and API. You can use this approach for single-sign-on for many providers to your app. Standalone identity providers can be excessively complex because they must self sign their own certificates and can be difficult to diagnose.

I hope that gives you an overview of the different offerings. See more info at the Windows Azure fundamentals page