If you are more of a “behind-the-scenes” kind of developer though, you may prefer to make your SQL tables using good old SSMS and some scripts. In short 5+ minute video below, I walk you through the steps and gotchas around creating tables directly in SQL Azure, but consuming them via Windows Azure Mobile Services.
As for the gotchas, there are two that come to mind:
Your tables must have a primary key column called “id”.
Your table names should be “singular” (think “Category” not “Categories”) to allow for more appropriate class names on the client side. You wouldn’t want to create a class called “Categories” when it really represents a single category.
If you want a copy of the SQL script I use in the video blow, you can get it here: http://aka.ms/wcts
It is easy to forget that an Azure SQL Database exists on an Azure SQL Server. One Azure SQL Server can support multiple Azure SQL Databases. The biggest benefit to a single Azure SQL Server hosting multiple databases is that you have a single administrative login and set of firewall rules that affect all databases on that server.
This can lead to confusion however if you have had a server in place for some time, and add a new database without remembering the original server credentials or firewall rules.
To remind yourself of the administrative login for your database, first login to the Azure Management Portal (https://manage.windowsazure.com). Along the left hand slide, select “SQL DATABASES”. On the “DATABASES” page, find your new database in the list, and locate the server name from the “SERVER” column (FYI, the real server names have been truncated in the screenshots below for security purposes):
If you click on the name of the server above, you will be taken to the “DASHBOARD” page for that server. From there, along the right hand side under “quick glance” you can see the “ADMINISTATOR LOGIN” name, and if needed click the link further up on the “Reset Administrator Password” link.
Once you have a figured out, or if needed reset, the administrator credentials. You can now log in an create additional logins and user accounts using the CREATE LOGIN and CREATE USER statements…