And How is it different from just making an SQL Server accessible from the internet?
Are you asking what is the difference from your point of view as an consumer of the service, or from the technological point of view of how is implemented?
From the point of view of how do you consume the SQL Azure service is the same argument that pertains every other aspect of Software as a Service discussion: reduce initial investition cost, scale as you grow, reduce the management and administration overhead, service level agreement on availability, scalability and disaster recoverability, continous upgrade of hardware, no licensing fees and so on and so forth. SaaS it makes a tonne of sense from business point of view for any operation, and specially for start ups. I won't go into more detail as other answers already covered this.
From a technical point of view, SQL Azure is far from an instance exposed on the internet. At PDC 2008 there were some presentations on the internal details of how SQL Cloud was working. SQL Azure exposes a different access model (T-SQL in SQL Azure vs. Containers and Entities in SQL Cloud), but some of the technical details presented about Cloud are probably still valid.
The Cloud had no individual SQL Servers, it is build on a mesh of thousands of SQL engines (code base common with SQL Server 2005, but not actual SQL instances) that cooperate in storing the data and servicing queries.
In Cloud the data (the 'Data Fabric') was partitioned into fragments that were replicated into at least 3 safety copies. If any fragment copy is lost, another the remaining copies are clonning a new copy to bumb up the count of redundand copies back to 3. Each fragment has a master copy that is used both for reads and writes. Each engine is hosting a multitude of fragments, some master some copies. No two copies of the same fragment are ever hosted on the same engine. As updates are done to the master copy, the engine ships the changes over to the fragment redundant copies in real time.
These, and other details about how the 'Data Fabric' is implemented, how the various automation auto-pilot failure detection and similar topics are presented in SQL Services: Under the Hood.
Some of the things have changed for sure with the changes from the SQL Cloud to the SQL Azure, but I'm sure many of the 'under the hood' facts are still valid.
PS: is incredible the amount of spam comments Channel9 is letting in...