in it's most basic form, Azure is just a highly available web-hosting environment - if you have an ASP.Net web application, you can deploy it to cloupapp.net and it should work.
To try it out, get yourself a Vista/7 machine, download the Azure SDK and VS Tools, and create a new Azure application. There are 2 main parts at this point, the Cloud project, and an ASP.Net Web Application. The ASP.Net will have a "web-role" relationship with the Cloud project. This is as it sounds, it is the visual front-end to the Cloud application, that interacts with visitors.
You can, at this point, just leave it there - it's a normal ASP.Net application with very good hosting. Your SQL connection strings should work, though you may want to consider SQL Azure. You can also host WCF services.
As Manoj points out, Azure does have a different programming model which you can take advantage to produce very robust applications. Azure also has the concept of Worker Roles, which are similar to Managed Services, in that they perform processing without a public interface. Instead, your web-roles take the requests, place them on the Queues, and the worker-roles then pick them up, process and send back responses.
It's a very powerful system, which I haven't fully explored, but the good news is that you don't have to be an immediate expert in the whole system, but can create simple ASP.Net sites as web-roles, deploy those then expand from there.
Have a go, it's well worth it
Toby