I am trying to port my Android application into a Cloud based one. Where I wanted the data stored in cloud. I am a .NET guy so looking into Azure. I see Azure Mobile services which allows me to create tables and ability to authenticate. I also Looked into Azure SQL database.
I would like to know what is the difference between these two services and what are the scenarios using mobile services gives values than using Azure SQL database
Actually, the tables you see listed in Azure Mobile Services are actually tables that are in an Azure SQL Database instance. The Mobile Services provides an abstraction layer on top of the tables in SQL Server. If you use the current JavaScript back-ended mobile services you'll see the table in the management portal and it uses the dynamic nature of JavaScript to provide what appears to be a NoSQL like experience with Azure SQL DB in that you can send in an object and properties it doesn't recognize will be added to the table schema (you turn this off when you ship to production :) ). The big thing to remember is that under the hood the database is an Azure SQL DB, so you should have access and do anything via the one created by Azure Mobile Services as you can with the raw Azure SQL DB.
By using Azure Mobile Services you get an API hosting layer that you can use to do direct access to the tables (based on the permissions you hinted at) but also to just about anything utilizing the custom API feature.
If you chose to bypass the Azure Mobile Services you'd likely want to have some API layer between your Android app and the data for a variety of reasons (security, abstraction of data location, etc.). In which case you'd have to write and host that API layer somewhere. Other options within Azure would be a Web API site in Azure Web Sites, an API hosted in an Azure Cloud Service or all the way up to an Azure VM; however, as you slide up that scale you're taking on more and more responsibility and work.
The scenario that is useful to use Azure Mobile Services over the Azure SQL DB is pretty much the scenario you have. You don't have to maintain your own hosting API layer, just the code the API layer executes. You can scale an necessary (to a very large scale indeed) and also get features like the push notifications and web jobs (though those are also things you can do via other services in Azure if you'd like). Thinks of Azure Mobile Services as a higher level grouping of Azure services that are helpful to mobile developers.
